Collection of some of the important type of MDX queries which you should be prepared with. The queries refer to sample SSAS database that comes with SSAS installation. Some of the queries used here link back to examples mentioned in Microsoft msn forums.
________________________________________
Q: How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?
A: Simply using bottomcount 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] ) ;
________________________________________
Q: How in MDX query can I get top 3 sales years based on order quantity?
A: 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];
________________________________________
Q: 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]
________________________________________
Q: 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])
________________________________________
Q: 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])
________________________________________
Q: 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='';
________________________________________
Q: 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]
________________________________________
Q: 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])
________________________________________
Q: MDX query to get sales by product line for specific period plus number of months with non empty sales.
A: You can use COUNT() function with ExcludeEmpty option. For count function you specify set that is corssjoin of Date members at the month level and measure that you are interested in.
WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above 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])
________________________________________
Q: How do I group dimension members dynamically in MDX? Source: MSDN SSAS Newsgroup.
A: You can create calculated members for dimension and then use them in the query. Example below will create 3 calculated members based on filter condition:
WITH MEMBER [Product].[Category].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))'
MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))'
MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))'
SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS
, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWS
FROM [Adventure Works]
________________________________________
Q: How can I compare members from different dimensions that have the same key values?
Lets say I have dimensions [Delivery Date] and [Ship Date]. How can I select just records that were Delivered and Shipped the same day?
A: You can use FILTER function and compare member keys using Properties function:
SELECT {[Measures].[Internet Order Count]} ON 0
, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children
)
, [Ship Date].[Date].CurrentMember.Properties('Key')
= [Delivery Date].[Date].Properties('Key')
) ON 1
FROM [Adventure Works]
________________________________________
Q: How can I get attribute key with MDX
A:
To do so, use Member_Key function:
WITH
MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key
SELECT {Measures.ProductKey} ON axis(0),
[Product].[Product Categories].Members on axis(1)
FROM [Adventure Works]
________________________________________
Q: How do I create a Rolling 12 Months Accumulated Sum (InternetSalesAmtR12Acc) that can show a trend without seasonal variations?
A: Here is query example
WITH MEMBER [Measures].[InternetSalesAmtYTD] AS SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"
MEMBER [Measures].[InternetSalesAmtPPYTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)),
[Measures].[Internet Sales Amount]), Format_String = "### ### ###"
MEMBER [Measures].[InternetSalesAmtPY] AS SUM(Ancestor(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Date].[Calendar].[Calendar Year]),
[Measures].[Internet Sales Amount]),Format_String = "### ### ###"
MEMBER [Measures].[InternetSalesAmtR12Acc] AS ([Measures].[InternetSalesAmtYTD]+[Measures].[InternetSalesAmtPY] )- [Measures].[InternetSalesAmtPPYTD]
Select {[Measures].[Internet Sales Amount], Measures.[InternetSalesAmtYTD], [Measures].[InternetSalesAmtPPYTD],[Measures].[InternetSalesAmtR12Acc]} On 0,
[Date].[Calendar].[Month].Members On 1
From [Adventure Works]
Where ([Date].[Calendar Year].&[2004]);
________________________________________
Q: How to setup calculated measure as default measure for a cube?
A: Use ALTER Cube statement on measures dimension. Example:
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]
________________________________________
Q: How can I write MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category
A: Example of such query:
WITH SET [FirstSales] AS
FILTER(NONEMPTY( [Customer].[Customer Geography].[Customer].MEMBERS
* [Date].[Date].[Date].MEMBERS
, [Measures].[Internet Sales Amount])
AS MYSET,
MYSET.CURRENTORDINAL = 1 or
NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(MYSET.CURRENTORDINAL-2).ITEM(0)))
MEMBER [Measures].[CustomersW/FirstSales] AS
COUNT(NonEmpty([FirstSales], [Measures].[Internet Sales Amount])),
FORMAT_STRING = '#,#'
SELECT {[Measures].[Internet Sales Amount],[Measures].[CustomersW/FirstSales]} ON 0,
[Product].[Product Categories].[Category] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2003]}, [Customer].[Customer Geography].[City].&[Calgary]&[AB]);
________________________________________
Q: How do you write MDX query that returns measure ratio to parent value?
A: Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is independant on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:
WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"
SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]
} ON 1
FROM [Adventure Works]
