Monthly Archives: June 2016

Basic Concepts Used within MDX Query:-

Here you learn what I couldn’t learn in a month 🙂

Multi-Dimensional Expression (MDX)

You can use MDX queries to get at data stored in a SQL Server Analysis Server cube by bringing back facts based on dimensions An MDX expression returns a multi-dimensional result set (dataset) that consists of axis data and cell data.

Cube:-

OLAP Cube is the basic unit of storage for Multidimensional data, on which we can do analysis on stored data and study the various patterns

Dimensions:-

Dimensions, in general, we can say are the Master entities with related member attributes using which we can study data stored in OLAP Cube Quickly and effectively, The primary functions of dimensions are to provide Filtering, Grouping, and Labeling on your data. Dimension tables contain textual descriptions about the subjects of the business.

Measure & Measure Groups:-

Metrics value stored in your Fact Tables is called Measure. Measures are used to analyze the performance of the Business. Measure usually contains numeric data, which can be aggregated against usage of associated dimensions. Measure Group holds the collection of related Measures.

1

Here Basic Terms you should know while working on Multi Dimensional Cube

Introduction to Level, Member, Hierarchy

Level

Generally, Attributes under Dimension are considered as levels, they are also called as Attribute

 Hierarchy.

Let’s take an example of Date Dimension in this we have various levels of Quarter of the Year, Semester of the Year, Week of the Year, Calendar Year, etc.

Members

A key component of the MDX query is member. Each Level contains one or more members.

e.g. Calendar Quarter of Year contains various members like CY Q1, CY Q2, CY Q3, CYQ4.

2

Now we are ready to start playing with MDX Query in our Query Editor Window.

“I think this is the beginning of a beautiful friendship between you and MDX.”

3

Introduction to Axis in MDX Query

MDX queries can have 0, 1, 2 or up to 129 query axes in the SELECT statement. Each axis behaves in exactly the same way, unlike SQL where there are significant differences between how the rows and the columns of a query behave.

Refer to the following table for Axis Numbers reserved and Alias given to them:
Axis Number Alias
0 Columns
1 Rows

Using SQL Server Management Studio (SSMS), we can only browse values on two axis, Columns (Axis 0) and Rows (Axis 1).

 

1. Start with Simple MDX Query

Syntax:  Select From [Your Cube Name] ;.

Which will give you aggregated result as shown in result pane.
4

2. Dropping Dimensions on Axis

Syntax  Select Dimension.Member on Column From [OLAPCubeName ]

Select [Customer].[Customer].[Customer] on columns From [Adventure Works]; 5

3. Using Both the Axis (Rows & Columns)

You can select Dimension or Measure on any Axis.

 Syntax Select [Measure] on Columns,

[Dimension].[Members] on Rows From [Cube Name] ;

Select [Measures].[Internet Sales Amount] on Columns,[Customer].[Customer].[Customer] on RowsFrom [Adventure Works];

6

4. Introduction to .members, and .children in MDX Query

.Members

If you will use this with hierarchy level, then it will retrieve all the values below it and also bring aggregation of that in the form of [ALL].

 Syntax Select [Dimension].[Hierarchy].members on Columns from CubeName

Select [Measures].[Internet Average Sales Amount] on Columns,[Product].[Category].members on RowsFrom [Adventure Works];

7

.Children

When we want to retrieve all members values under particular level of a dimension at that time we use .children ,This will exclude aggregation values [ALL] in your result set.

Syntax  Select [Measures].[Internet Average Sales Amount] on Columns,[Product].[Category].children on RowsFrom [Adventure Works];

8

5. Introduction to Tuple and Set

Tuple:

When we need to place more than one members of a dimension or hierarchy of that dimension on a axis at that time tuple comes into the picture, tuple is enclosed within curly bracket { }, for single tuple bracket is optional.

select {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]} on rows,[Measures].[Internet Sales Amount] on columns from[Adventure Works];

9

Set:

Combination of tuple or tuples will give you set , When You want to include range at that time you can use : instead of separating tuple members by comma if they are belonging to same dimension member.

select {[Date].[CY 2008] : [Date].[CY 2005]}  on rows,[Measures].[Internet Sales Amount] on columns from[Adventure Works];

10

6. Using CROSS JOIN

Cross Join Function returns cross product of one or more sets.

Whenever we need to combine more than one member from same or different dimension at that time we can use cross join. * sign can be use to implement cross join between dimension members.

select {[Product].[Category].children * [Product].[Subcategory].children} on rows,[Measures].[Internet Sales Amount] on columns from[Adventure Works];

11

7. Using NonEmpty

NonEmpty function evaluated first so it will remove rows if there was no data in first measure.

8. Apply Slicing using Where Clause

Select [Measures].[Internet Sales Amount] on columns,[Product].[Product].[Product].members on rows from [Adventure Works]where [Date].[Calendar Year].[CY 2007];

Looks like now you know what is mdx at least; Let’s put a smile on face.