SSAS MDX Query

Hi 

This post covers entire SSAS MDX Query Interview Q & A

1.How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

  Simply using bottom count will return customers with null sales. 

  You will have to combine it with NONEMPTY or FILTER.

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] ), ( [Measures].[Internet Sales Amount] ) ), 10
, ( [Measures].[Internet Sales Amount] ) ) ON ROWS FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

2. How in MDX query can I get top 3 sales years based on order   quantity?

 

   By default Analysis Services returns members in an order specified during attribute design. 

  Attribute properties that define ordering are "OrderBy" and "OrderByAttribute".

  Lets say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1 FROM [Adventure Works];

Same query using TopCount:
SELECT {[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1 FROM [Adventure Works];

3.How do you extract first tuple from the set?

A: Use could usefunction Set. Item(0)

Example:
SELECT {{[Date].[Calendar].[Calendar Year].Members }.Item(0)} ON 0 FROM [Adventure Works]

4: How do you compare dimension level name to specific value?

A: Best way to compare if specific dimension is at certain level is by using 'IS' operator:
Example:

WITH MEMBER [Measures].[TimeName] AS IIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr') SELECT [Measures].[TimeName] ON 0
FROM [Sales Summary] WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])

5: MDX query to get sales by product line for specific period plus number of months with sales

A: Function Count(, ExcludeEmpty) counts number of non empty set members. So if we crossjoin Month with measure we will get set that we can use to count members.

Query example:

WITH Member [Measures].[Months With Non Zero Sales] AS COUNT(CROSSJOIN([Measures].[Sales Amount] , DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month]))
, ExcludeEmpty )
SELECT {[Measures].[Sales Amount], [Measures].[Months With Non Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1 FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

6. How can I setup default dimension member in Calculation script?

A: You can use ALTER CUBE statement. 

Syntax:

ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';


7.I would like to create MDX calculated measure that instead of summing children amounts,uses last child amount

A: Normally best way to create this in SSAS 2005 is to create real measure with aggregation function LastChild.
If for some reason you still need to create calculated measure, just use fuction .LastChild on current member of Date dimension, and you will allways get value of last period child.

Example:
We want to see last semester value for year level data. Lets first see what data values are at Calendar Semester level:

SELECT {[Measures].[Internet Order Count]} ON 0 , DESCENDANTS([Date].[Calendar].[All Periods],[Date].[Calendar].[Calendar Semester] ) ON 1 FROM [Adventure Works]

8.How to calculate YTD monthly average and compare it over several years for the same selected month?

A: MDX Query:

WITH MEMBER Measures.MyYTD AS SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])

MEMBER Measures.MyMonthCount AS SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))

MEMBER Measures.MyYTDAVG AS Measures.MyYTD / Measures.MyMonthCount

SELECT {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0, [Date].[Calendar].[Month] On 1
FROM [Adventure Works] WHERE ([Date].[Month of Year].&[7])