CUBE Introduction-MS-BI(Microsoft –Business intelligence)

Introduction to CUBE and MXD:

·      MDX, an acronym for Multidimensional Expressions, is a syntax that supports the definition and manipulation of multidimensional objects and data.

·          MDX is similar in many ways to the Structured Query Language (SQL) syntax, but is not an extension of the SQL language;

·          As with an SQL query, each MDX query requires a data request (the SELECT clause), a starting point (the FROM clause), and a filter (the WHERE clause).

·          MDX, like SQL, provides data definition language (DDL) syntax for managing data structures. There are MDX commands for creating (and deleting) cubes, dimensions, measures, and their subordinate objects.

·       Key Concepts in CUBE

1.The purpose of Multidimensional Expressions (MDX) is to make accessing data from multiple dimensions easier.
2.A cube is defined by its MEASURES and DIMENSIONS.
3.The measures and dimensions in a cube are derived from the tables and views in the data source view on which the cube is based.
4.The smaller alphanumeric values around the cube are the members of the dimensions. Example members are ground (member of the Route dimension), Africa (member of the Source dimension), and 1st quarter (member of the Time dimension).
5.Example:1.ground,nonground,air,sea,road,rail are the members of ROUTE Dimensions.

2.eastern hemisphere,western hemisphere,Africa,asia,Australia,europ,north America,south America are the MEMBERS of Source dimensions.

·         The following diagram illustrates a cube that employs three dimensions, Route, Service and Time and two measures, Packages and Last.

Each dimension is broken down into different levels, each of which is broken down further into members.

For example, the Source dimension supplies the Eastern Hemisphere level, which is broken down into four members, Africa, Asia, Australia, and Europe.

 As you can see, the querying of even simple data out of a multidimensional data source can be a complex task. A cube can have more than three dimensions, for example, or it may only have one dimension

   As SQL returns a subset of two-dimensional data from tables, MDX returns a subset of multidimensional data from cubes.