How to Query Data from SSAS Cube using MDX .

Problem: How to Query Data from SSAS Cube.

Solution: MDX Query - The Basic Query

After we build the cube, its time extract the data for reporting purposes. In SSAS to build a query we must right multi-dimensional expressions to extract the data from the cube.

 Also, MDX can be used to few aggregations and special functions like group by, lead lag and other TSQL functions.

There are few co-ordinate terminologies here in MDX called members, tuples and sets.

To query the data in MDX we need to specify measure.measure name and specify whether you want to display those as rows or columns. Alternatively, you can use 0 for columns and 1 for rows.

Example:

In this example, I would like to show we can query the Adventure work sales cube we have built in the earlier articles. , for more information go through this article How to build a cube..

Step 1: launch ssms and from the connection options select analysis server. And right click on the cube to get a new query or hit ctrl+N

 

Step 2: Now go to metadata of the object explorer and drag drop the measures you want.

Step 3: build a query to assign rows and columns for the measures and dimensions and in this example I want internet sales count by year so I select measures of internet sales count on columns and the year dimension on rows. And to group by the children I have to specify children on each rows.

Query:

SELECT [Measures].[Fact Internet Sales Count] ON COLUMNS,

[Order Date].[year].CHILDREN ON ROWS

FROM [Adventure Works Cube]

Results:

	Fact Internet Sales Count
2005	(null)
2006	(null)
2007	(null)
2008	(null)
2009	(null)
2010	14
2011	2216
2012	3397
2013	52801
2014	1970

Example 2:  to eliminate the null values I am using a function to filter out the null values by specifying the NONEMPTY on rows.

SELECT [Measures].[Fact Internet Sales Count] ON COLUMNS,

NONEMPTY([Order Date].[year].CHILDREN) ON ROWS

FROM [Adventure Works Cube]

	Fact Internet Sales Count
2010	14
2011	2216
2012	3397
2013	52801
2014	1970