MDX COUSIN FUNCTION

In Multidimensional Expression, Cousin function will write the same level member under the Ancestor specified in the second parameter. For example, If you know the value of first Quarter in Calender year 2014 and you want to find the sales of last Quarter in Calender year 2015 then you can use this MDX Cousin function.

MDX Cousin Function Syntax

The basic syntax of the MDX Cousin is:

COUSIN( Member_Expression, Ancestor_Member_Expression )

Member_Expression: Any Multidimensional Expression that returns valid Member. It will find the member Level. For instance, whether the member is first month of a Quarter, semester or Year etc.
Ancestor_Member_Expression: Any Multidimensional Expression that returns valid Ancestor Member. Cousin function will find the Cousin of First argument present in thisAncestor_Member.

In this article we will show you, How to write Cousin function in MDX query with examples. For this, We are going to use the below show data

MDX Cousin Function Example

In this example we are going to use Cousin function to find the cousin of Q1 CY 2012 in Calender Year 2013


CODE


SELECT
{
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],
COUSIN (
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],
[Date].[Calendar].[Calendar Year].[CY 2013]
)
} ON COLUMNS,

[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]


OUTPUT

Analysis:

In the above MDX Query, We used [Internet Sales amount] on the Rows

[Measures].[Internet Sales Amount] ON ROWS

First line of code after the SELECT will check for Q1 CY 2012 and write the Internet sales amount of Q1 CY 2012. As we all know, Q1 CY 2012 is the First Quarter in Calender year 2012.


[Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],

Below line of code will check for the First Quarter in Calender year 2013( because Q1 CY 2012 is the First Quarter in Calender year 2012) and then write the Internet sales amount of Q1 CY 2013


COUSIN (
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],
[Date].[Calendar].[Calendar Year].[CY 2013]
)


MDX Cousin Function Example 2

In this example we are going to use Cousin function to find the cousin of January 2012 in Fourth Quarter of Calender Year 2013


CODE

SELECT
{
[Date].[Calendar].[Month].[January 2012],
COUSIN (
[Date].[Calendar].[Month].[January 2012],
[Date].[Calendar].[Calendar Quarter].[Q4 CY 2013]
)
} ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]


OUTPUT





Analysis

In the above MDX Query, We used [Internet Sales amount] on the Rows

[Measures].[Internet Sales Amount] ON ROWS


First line of code after the SELECT will check for January 2012 Month and write the Internet sales amount of January 2012. As we all know, January is the First Month of the Q1 CY 2012 in the Calender year 2012.


[Date].[Calendar].[Month].[January 2012],

Below line of code will check for the First Month of the Calender Quarter Q4 CY 2013 (i.e October) and then write the Internet sales amount of October 2013


COUSIN (
[Date].[Calendar].[Month].[January 2012],
[Date].[Calendar].[Calendar Quarter].[Q4 CY 2013]
)


NOTE: If we replace the Calender Quarter from the above lines of code with Calender semester 2, MDX Query will write the First month in semester 2
Finding Range Using MDX Cousin Function

In this example we are going to calculate the Range between one point to other using MDX Cousin Function.


CODE

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
{
[Date].[Calendar].[Month].[January 2012]:
COUSIN (
[Date].[Calendar].[Month].[January 2012],
[Date].[Calendar].[Calendar Quarter].[Q2 CY 2013]
)
} ON ROWS
FROM [Adventure Works]


OUTPUT





Analysis:

In the above MDX Query, We used [Internet Sales amount] on the Rows

[Measures].[Internet Sales Amount] ON ROWS


First line of code after the SELECT will check for January 2012 Month and write the Internet sales amount of January 2012. As we all know, January is the First Month of the Q1 CY 2012 in the Calender year 2012.

[Date].[Calendar].[Month].[January 2012],

Below line of code will check for the First Month of the Calender Quarter Q2 CY 2013 (i.e April)


COUSIN (
[Date].[Calendar].[Month].[January 2012],
[Date].[Calendar].[Calendar Quarter].[Q2 CY 2013]
)


Now the range Operator (:) between the above statements will write all the Calender Month from January 2012 to April 2013.

MDX ClosingPeriod Function

In Multidimensional Expression, ClosingPeriod Function will return the Last Sibling member belongs to the specified member or at specified level.

MDX ClosingPeriod Function Syntax

The basic syntax of the MDX ClosingPeriod is:

CLOSINGPERIOD (Level_Expression, Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.


Level_Expression: Please specify the level you want to navigate.


In this article we will show you, How to write ClosingPeriod function in MDX query with examples. For this, We are going to use the below show data


Following screenshot shows the List of Calender Years inside the Date Dimension

MDX ClosingPeriod Function Example

In this example we are going to find the Last year present in the Calender Year using ClosingPeriod function.


CODE


SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
CLOSINGPERIOD ([Date].[Calendar].[Calendar Year]) ON ROWS
FROM [Adventure Works]


OUTPUT





Analysis:


In the above MDX Query, We used [Internet Sales amount] on the columns

[Measures].[Internet Sales Amount] ON COLUMNS

Below line of code will return the last sibling member present in the Calender Year from the Date Dimension, Which is Year 2014.


CLOSINGPERIOD ([Date].[Calendar].[Calendar Year])


MDX ClosingPeriod Function Example 2


In this example we are going to find the Last Month present in the Calender Year 2013 using ClosingPeriod function and calculate the Internet Sales Amount of that.


CODE


SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
CLOSINGPERIOD (
[Date].[Calendar].[Month]
,[Date].[Calendar].[Calendar Year].[CY 2013]
) ON ROWS
FROM [Adventure Works]


OUTPUT

Finding Range Using MDX ClosingPeriod Function Example 1

In this example we will show you, How to find the Range using ClosingPeriod function. The following query will return the Internet Sales amount from Calender Year 2010 to 2014


CODE


SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2010]:
CLOSINGPERIOD ([Date].[Calendar].[Calendar Year]) ON ROWS
FROM [Adventure Works]


OUTPUT





Analysis:

In the above MDX Query, We used [Internet Sales amount] on the columns

[Measures].[Internet Sales Amount] ON COLUMNS

Below line of code will return the last sibling member present in the Calender Year from the Date Dimension, Which is 2014.


CLOSINGPERIOD ([Date].[Calendar].[Calendar Year])

It means, We used the range operator between 2010 and 2014 Year so, the output will display the Internet Sales amount of all the Calender Years present in the Date dimension.

[Date].[Calendar].[Calendar Year].[CY 2010] -- 2010

CLOSINGPERIOD ([Date].[Calendar].[Calendar Year]): -- 2014

Finding Range Using MDX ClosingPeriod Function Example 1


In this example we will show you, How to find the Range using ClosingPeriod function. The following query will return the Internet Sales amount from May to December Month in Calender Year 2013


CODE

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
[Date].[Calendar].[Month].[May 2013]:
CLOSINGPERIOD (
[Date].[Calendar].[Month]
,[Date].[Calendar].[Calendar Year].[CY 2013]
) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Children Function

In Multidimensional Expression, Children function will return the associate Child member that belongs to the specified member. For instance, If you are an Amazon developer and If one of your customer is looking for iPod in Amazon.com. As a developer it is your duty to display all the Apple products at the bottom of the page so that user can browse other products and sometimes he may buy? In these situations you can use the Children function by writing something like [Product].[Apple].[iPod].Parent.Children. In this article we will show you, How to write Children function with examples.

MDX Children Function Syntax

The basic syntax of the MDX Children is:

Member_Expression.CHILDREN

Member_Expression: Any Multidimensional Expression that returns valid Member.
MDX Children Function Example

In this example we are going to find the [Reseller Sales amount] of the all the states present in the France.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].CHILDREN ON ROWS
FROM [Adventure Works];


OUTPUT





From the above screenshot you can observe that, It is displaying all the states present in France.


MDX Children Function Example 2

In this example we are going to find the [Reseller Sales amount] of the all the countries present in the Geography attribute.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].CHILDREN ON ROWS
FROM [Adventure Works];


OUTPUT

MDX BottomSum Function

In Multidimensional Expression, BottomSum function will sort the given data in the Ascending order and then selects the required number of records from the sorted data whose total is at least equal to specified Value.

TIP: Please use TOPSUM Function to calculate the Sum of Top N records
MDX BottomSum Function Syntax

The basic syntax of the MDX BottomSum is:

BOTTOMSUM (Set_Expression, Value, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Value: Please provide the Value. BOTTOMSUM function will retrieve the records whose total is at least this Value.
Numeric_Expression: Any Multidimensional Expression or measure. The BottomSum function will use this Numeric_Expression to sort the data in Ascending order and then picks the number of records required to match the Value.

In this article we will show you, How to write BottomSum function to extract the bottom States from Geography Dimension (present in the Adventure Works Cube) whose total is at least equal to the user given value. For this, we are going to use below shown data.

MDX BottomSum Function Example

The following query will return Bottom States present in the Geography Dimension who are contributing Sale of 100000 of total Reseller sales Amount.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
BOTTOMSUM (
[Geography].[Geography].[State-Province]
,100000
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography Dimension on Rows. Next, BottomSum function will sort the data in the Ascending order using Measures.[Reseller Sales Amount] and then add the Sales amount of each state until it reaches 100000.


OUTPUT





If you observe the above screenshot, If you total the Sales amount of above displayed states they approximately equal to 104214.78. It means greater than what we required.
Using NON EMPTY in MDX BottomSum Function

In this example we are going to use Non Empty keyword to remove the NULL values.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
NON EMPTY
BOTTOMSUM (
[Geography].[Geography].[State-Province]
,100000
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works] 

OUTPUT





If you observe the above screenshot, MDX Non Empty keyword we used in the above query removed those Null records. Now, it looks Nice and Easy

NOTE: Please use the appropriate Measure as a third argument in the BOITTOMSUM function otherwise, You will end up with wrong results.

MDX BottomPercent Function

In Multidimensional Expression, BottomPercent function will sort the given data in the Ascending order and then selects the required number of records from the sorted data whose total is greater than or equal to specified Percentage.

TIP: Please use the TopPercent Function to find the Top percentage.
MDX BottomPercent Function Syntax

The basic syntax of the MDX BottomPercent is:

BOTTOMPERCENT (Set_Expression, Percentage, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Percentage: Please provide the percentage to want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. The BottomPercent function will use this Numeric_Expression to sort the data in Ascending order and then picks the number of records required to match the percentage.

In this article we will show you, How to write BottomPercent function to extract the States from Geography Dimension (present in the Adventure Works Cube) who are at bottom 1 percent of total sales in Reseller Sales amount. For this, we are going to use below shown data.

MDX BottomPercent Function Example

The following query will return all the states present in the Geography Dimension who are contributing Bottom 1 Percent of total Reseller sales Amount.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
BOTTOMPERCENT (
[Geography].[Geography].[State-Province], 1
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography Dimension on Rows. Next, BottomPercent function will sort the data in the Ascending order using Measures.[Reseller Sales Amount] and then find the percentage of sales from total sales.


OUTPUT





If you observe the above screenshot, they are not the bottom 1 Percent rows of Total records. If you total the Reseller Sales Amounts present in the above screenshot, it will be greater than or equal to 1 percent of the Total reseller Sales Amount. In short, above mentioned states are the bottom states who are contributing bottom 1 Percent Sales of total sales.
MDX BottomPercent Function Example 2

In this example, We are going to use two measures ([Measures].[Reseller Sales Amount] [Measures].[Reseller Gross Profit]).


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
BOTTOMPERCENT (
[Geography].[Geography].[State-Province], 1
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT





If you observe the above screenshot, We are getting uneven results for [Reseller Gross Profit]. This is because data is sorted according to [Reseller Sales Amount]

NOTE: Please use the appropriate Measure as a third argument in the BOTTOMPERCENT function otherwise, You will end up with wrong results.
Using NON EMPTY along with MDX BottomPercent Function

In this example we are going to use Non Empty keyword to remove the NULL values.


CODE


SELECT {[Measures].[Reseller Sales Amount] } ON COLUMNS,
NON EMPTY
BOTTOMPERCENT (
[Geography].[Geography].[State-Province], 1
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT





Although we asked for bottom 1 Percent records, above query is displaying few records because remaining records are NULLS. MDX Non Empty keyword we used in the above query removed those Null records.

MDX BottomCount Function

In Multidimensional Expression, BottomCount function will sort the given data in the Ascending order and then selects the required number of records from the sorted data.

For example, If you want to find the Bottom 10 performing products or you want to find the 10 regions with Lowest sales or to calculate Lowest Salary we can use this BottomCount function with measure.

TIP: Please use TOPCOUNT Function to get Top 10 Performing States.


MDX BottomCount Function Syntax

The basic syntax of the MDX BottomCount is:

BOTTOMCOUNT (Set_Expression, Count, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Count: Please provide, How many number of records you want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. This is purely optional argument. If we used this argument then BOTTOMCOUNT will sort the data in Ascending order using this Numeric_Expression and then picks the number of records you asked for otherwise, BOTTOMCOUNT will select the required number of records without sorting.

In this article we will show you, How to write BottomCount function to extract Bottom 10 States from Geography Dimension (present in the Adventure Works Cube) whose Reseller Sales amount is less than others with example. For this, we are going to use below shown data.

MDX BottomCount Function with Two arguments

In this example we will show you, What happen when we miss the third argument in the BottomCount function. The following query will return last 10 records from the above source in the order they appear above.


CODE

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
BOTTOMCOUNT (
[Geography].[Geography].[State-Province]
,10
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, BottomCount function will select the Bottom 10 records irrespective of their measure values.


OUTPUT




From the above screenshot you can observe that, they are not the Bottom 10 states whose Reseller Sales Amount is lower than other States. Here BottomCount function just selected bottom 10 records in the order they inserted.
MDX BottomCount Function Example

In this example we will show you, What happen when we add the third argument in the BottomCount function. The following query will sort the records in the Ascending order using [Measures].[Reseller Sales Amount] and then return first Ten records from the sorted data. In short, We will get our Bottom 10 performing states around the world.


CODE

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
BOTTOMCOUNT (
[Geography].[Geography].[State-Province]
,10
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT

Using NON EMPTY along with MDX BottomCount Function

In this example we are going to use Non Empty keyword to remove the NULL values.


CODE


SELECT {[Measures].[Reseller Sales Amount] } ON COLUMNS,
NON EMPTY
BOTTOMCOUNT (
[Geography].[Geography].[State-Province], 10
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT




Although we asked for bottom 10 records, above query is displaying 3 records because remaining 7 records are NULLS. MDX Non Empty keyword we used in the above query removed those 7 records.

NOTE: Please use the appropriate Measure as a third argument in the BOTTOMCOUNT function otherwise, You will end up with wrong results.

MDX AVG Function

In Multidimensional Expression, AVG Function is used to calculate the average of non empty members present in the specified Set. This is similar to SQL AVG Function.

TIP: In MDX, AVG Function will ignore the NULL values while calculating the Average.
MDX AVG Function Syntax

The basic syntax of the MDX AVG Function is:


AVG ( Set_Expression, Numeric_Expression)

Set_Expression: Any Multidimensional Expression that returns valid Set.


Numeric_Expression: Any Multidimensional Expression that returns numeric value. In general, we use the required Measure value here.

In this article we will show you, How to calculate the average using AVG function with examples. For this, we are going to use below shown data.





Following screenshot shows the Countries inside the Geography






MDX AVG Function Example 1

The following query will return the Reseller Sales amount of all the countries present in the Countries and also calculate the average of their Sales Amounts using the MDX AVG Function.


CODE


WITH MEMBER
[Geography].[Geography].[Average] AS
AVG (
[Geography].[Geography].[Country],
[Measures].[Reseller Sales Amount]
)

SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
{
[Geography].[Geography].[Country],
[Geography].[Geography].[Average]
} ON ROWS
FROM [Adventure Works]

OUTPUT




ANALYSIS

Below statement will create the calculated Member [Geography].[Geography].[Average]

WITH MEMBER
[Geography].[Geography].[Average]

In the next line, We used the AVG Function to calculate the Average of [Measures].[Reseller Sales Amount] for the countries present in [Geography].[Geography].[Country]

AVG (
[Geography].[Geography].[Country],
[Measures].[Reseller Sales Amount]
)

In the next statement, We selected the Reseller Sales Amount on columns and then Set of countries and calculated Member on rows


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
{
[Geography].[Geography].[Country],
[Geography].[Geography].[Average]
} ON ROWS


MDX AVG Function Example 2

The following query will return the Internet Sales amount of all the calendar years present in the Date dimension and also calculate the average of their Sales Amounts using the MDX AVG Function.


CODE


WITH MEMBER [Date].[Calendar].[Average] AS
AVG (
[Date].[Calendar].[Calendar Year],
[Measures].[Internet Sales Amount]
)


SELECT [Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY {
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Average]
} ON ROWS
FROM [Adventure Works]


OUTPUT




ANALYSIS

Below statement will create the calculated Member [Date].[Calendar].[Average]


WITH MEMBER
[Date].[Calendar].[Average]


In the next line, We used the AVG Function to calculate the Average of [Measures].[Internet Sales Amount] for the calendar years present in [Date].[Calendar].[Calendar Year]


AVG (
[Date].[Calendar].[Calendar Year],
[Measures].[Internet Sales Amount]
)

In the next statement, We selected the Internet Sales Amount on columns and then Set of calendar Years and calculated Member ([Date].[Calendar].[Average]) on rows. We also used the NON EMPTY Function to avoid the NULL Values.


SELECT [Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY {
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Average]
} ON ROWS
FROM [Adventure Works]


MDX AVG Function Example 3

In this example we will show you, What happen when we have null values. For this, We are using the above MDX Query without NON EMPTY Function.


CODE


WITH MEMBER [Date].[Calendar].[Average] AS
AVG (
[Date].[Calendar].[Calendar Year],
[Measures].[Internet Sales Amount]
)


SELECT [Measures].[Internet Sales Amount] ON COLUMNS,

{


[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Average]
} ON ROWS
FROM [Adventure Works]


OUTPUT





If you observe the result, We are getting the same average for example 2 and 3. This is because, AVG function does not consider the members with NULL Values. It means, AVG Function is calculating average from CY 2010 to CY 2014.

MDX Ascendants Function

In Multidimensional Expression, Ascendant function will return the associate Parent member of the specified member at all levels until it reaches Level 0 OR We can simply say, Ascendant Function will return all the ascendants of a specified Member. For instance, you Know the customer name and if you want to find the Post Code, City, State and Country of that particular customer then you can use this Ascendant function.
MDX Ascendants Function Syntax

The basic syntax of the MDX Ascendants Function is:

ASCENDANTS (Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.


In this article we will show you, How to write Ascendants Function in MDX Query with examples. For this we are going to use below shown hierarchy



MDX Ascendants Function Example


In this example we are going to find the City, State and Country of the Post Code 2055


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
ASCENDANTS ([Geography].[Geography].[Postal Code].[2055]) ON ROWS
FROM [Adventure Works];


OUTPUT

Using Order Function along with MDX Ascendants Function

In this example we are going to use the MDX Order Function to reorder the members written by the Ascendants function in the Descending Order.

CODE

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
ORDER (
ASCENDANTS ([Geography].[Geography].[Postal Code].[2055])
,DESC) ON ROWS
FROM [Adventure Works];


OUTPUT

Using Hierarchize along with MDX Ascendants Function

In this example we are going to use the MDX Hierarchize function to reorder the members written by the Ascendants function from Top to bottom.


CODE


SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
HIERARCHIZE
(
ASCENDANTS ([Geography].[Geography].[Postal Code].[2055])
) ON ROWS
FROM [Adventure Works];


OUTPUT




NOTE: MDX Hierarchize Function is very useful function to order the members written by the Ascendants rather than MDX Order Function

Introduction to MDX query basic terms – SQL vs MDX

MDX stands for Multi-Dimensional eXpressions.  why MDX? Answer is very simple – Performance and Efficiency.

SQL – Structured Query Language

Mostly we run SQL queries to fetch data from Two-Dimensional database (Relational tables).
SQL have capabilities like DML (Data Manipulation Language) and DDL (Data Definition Language).
SQL deals with columns and rows. 

MDX – Multi-Dimensional eXpressions.

MDX queries are executed to fetch data from One, Two, Three or more dimensions (Cube).
MDX does not contain DML and DDL.
MDX plays with rows, columns, multi-dimensions, hierarchies and levels


Analysis – MDX is a standard language designed by Microsoft Corporation to query OLAP cubes(Cube made by SQL Server, Teradata, etc).

Example – Cubes are very intelligent. In Cube, if you want to know the sales for July 25, 2014. It will automatically know the parent for this date is 2014 and aggregated sales data will also be stored at parent level. This gives speed and celebrity treatment to the MDX.

MDX Terminologies

Cube – OLAP cube created in various technologies like Oracle, SSAS, Teradata, etc is the basic Multi-dimensional data storage unit. We can perform analysis functions on the data stored in cube.


Dimensions – These contains textual description about the Line of Business (LOB). Dimensions provide developer to perform primary functions like Grouping, Filtering, Labelling, etc. Clients can view their business by putting on various dimensions like Product categories, Date, Location dimensions, etc.


Measures – These are the numeric data stored in Fact tables. They are used to analyze performance of the business. [Measures] can be like Sales amount, Profit, Tax, etc.


Measure Groups – In simple words, it is collection of related Measures.


Level – In simple way, attributes of dimensions are called as [Level]. Example – Consider Date dimension to learn about levels in MDX. Then, Levels for Date dimension will be as Calendar year, Semester of the year, Quarter of the year and so on. This is also known as Attribute Hierarchy.


.Members and .Children – I bet you will love this keyword once you started working on MDX window. In MDX query, each level contains one or more members. Example – Consider Quarter of the year level then, it will contain members like Q1, Q2, Q3 and Q4.


Only difference between Members and children in MDX is that when you will use members with hierarchy than in result aggregated value will also come in the form of [All]. This aggregation i.e. [All] will not come if you will write children after hierarchy.


Hierarchy (Parent-Child) – In hierarchy, top node is parent and other nodes below the parent arechildren. You can have user defined hierarchies and default level 0 stands for [All] members. Example– Consider Geography dimension to learn about Hierarchy in MDX. Here, Parent node can be Continent and 1st child will be Country, 2nd child will be State, 3rd child can be City and so on.
[Dimension].[Hierarchy].Levels(0).Members/Children


Tuple – It is that value where column cell and row cell intersects. In simple words, when we say C5 in excel then C is column value and 5 is row value and this gives us the result. Now, I will explain this technically. Tuple in MDX is used to identify specific location in the cube with the use of dimension members. Tuples are enclosed within round brackets (). Brackets are optional, if you are using single tuple.
Note – Empty tuple in MDX query is not allowed. We cannot repeat same hierarchy within Tuple.


Set – Combination of zero, one or more tuple makes definition for Set. Set is enclosed within curly braces {}.
Note – We can have empty set in MDX. We can repeat same hierarchies within Set.


How MDX terms are related with SQL terms?
Cube in MDX = Table in SQL.
Level in MDX = Column in SQL.
Dimension in MDX = Several related columns in SQL.
Measures in MDX = Numeric data in SQL.
Tuple in MDX = Cell value (where row and column intersects) in SQL.
Set in MDX = Range in SQL.


Technically speaking MDX is not “faster” than T-SQL, or vice versa. Both are just languages designed for different needs. In our future posts, i will show you the syntax and how MDX query works. I hope you liked this post. Do you remember my deal mentioned above? Share this article with other friends too. Your feedback and queries are welcome as comments below.

SSAS MDX

Most important function of MDX is AXIS. The Axis function returns a set of tuples on the specified axis. PFB syntax of AXIS function.

"Axis(Number)"

Number – is a valid numeric expression which specifies the axis number.

The position starts from 0 and it can be continued as the cube database data is MULTI dimensional. MDX query is capable of returning data in more than two dimensions but our systems doesn’t support it. As our systems support only up to two dimensions, our MDX queries will stick to two AXIS only. For example, Axis(0) returns the COLUMNS axis, Axis(1) returns the ROWS axis, and so on. The Axis function cannot be used on the filter axis. The Axis numbers should be in order. You cannot use AXIS(1) without defining AXIS(0). PFB the sequence -

AXIS(0) or simply 0 – Columns

AXIS(1) or simply 1 – Rows

AXIS(2) or simply 2 – Pages

AXIS(3) or simply 3 – Sections


You can use any format to define AXIS like AXIS(Number) or Simply Number or with name like columns,rows,pages .. PFB sample queries of all three kinds -


Using AXIS -

select [Measures].[Sales Amount] on Axis(0),
[Dim Product].[Products].[Products] on Axis(1)
From [Adventure Works]


Using Number -

select [Measures].[Sales Amount] on 0,
[Dim Product].[Products].[Products] on 1
From [Adventure Works]


Using Name -

select [Measures].[Sales Amount] on Columns,
[Dim Product].[Products].[Products] on Rows
From [Adventure Works]