Mdx Samples
This page was created for people starting to use Microsoft Analysis Services. There were not many examples on the web when I started, so I wanted to put some up. You will see what MDX queries look like and see how to use some of the functions like crossjoin, topcount ... . This is not a tutorial, just samples. Sorry this site is so sloppy, I just don't want to spend time on it. Questions, email me at dan.parker@yahoo.com and check out PMS Programming

C# code examples Best ways to grab data Cellset vs Reader? And Speed issues






WITH
MEMBER [Measures].[SumOfAG] AS
'( Sum({[Time]}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[SuperSum] AS
' (Sum({[Age Group].[13 and 17],[Age Group].[18 and 24],[Age Group].[25 and 34],[Age Group].[35 and 44],[Age Group].[45 and 54],[Age Group].[over 55],[Age Group].[less then 13],[Age Group].[UNKNOWN] },[Measures].[SumOfAG] ))'

MEMBER [Measures].[GMale] AS
'( Sum({([Time],[Gender].[Male])}, [Measures].[RunningTotalSubs]))'
MEMBER [Measures].[GMalePer] AS
' ([Measures].[GMale] / [Measures].[SuperSum])'
MEMBER [Measures].[GFemale] AS
'( Sum({([Time],[Gender].[Female])}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[GFemalePer] AS
' ([Measures].[GFemale] / [Measures].[SuperSum])'

MEMBER [Measures].[GUnknown] AS
'( Sum({([Time],[Gender].[Unknown])}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[GUnknownPer] AS
' ([Measures].[GUnknown] / [Measures].[SuperSum])'

select {[Measures].[SumOfAG], [Measures].[GMale], [Measures].[GMalePer], [Measures].[GFemale], [Measures].[GFemalePer], [Measures].[GUnknown], [Measures].[GUnknownPer] }
on Columns, { ([Age Group].children) } on Rows from [consumers] where ([Zone Id].&[501])


WITH
MEMBER [Measures].[p1] AS '( [Time].[2008].&[7].[31].[30], [Subs Count] ) '
MEMBER [Measures].[p2] AS '( [Time].[2008].&[6].[27].[30], [Net Subs] ) '
MEMBER [Measures].[Variant Percentage] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] ))/( [Time].[2008].&[6].[27].[30], [Net Subs] ) '
MEMBER [Measures].[SubsDiff] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] )) '

select {[Subs Count], [Net Subs],[RunningTotalSubs],[Measures].[p1], [Measures].[p2], [Measures].[SubsDiff], [Measures].[Variant Percentage] } on columns,

TopCount(NonEmptyCrossJoin({Descendants([Source].Currentmember, 3)},{Descendants([Zone Id].&[1178],2)}) ,150, ([Net Subs])) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY on rows

FROM [consumers]



Date Range- This is a good one

WITH SET [MyRange] AS
'{[Time].[2009].&[5].[22].[26] : [Time].[2009].&[6].[26].[24]}'
MEMBER [Time].[Range] AS 'Aggregate([MyRange],[Measures].CurrentMember)'

select {[Measures].[Sends],[Measures].[Views], [Measures].[Clicks]} on columns,
{[Zone Id].&[142]}
on rows
from [All Communication Analysis] where ([Time].[Range])



This is an OR query. I got this query from HERE

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE(
{([Date].[Calendar Year].&[2003],[Customer].[Country].[All Customers])
,
([Date].[Calendar Year].[All Periods],[Customer].[Country].&[United States])})




WITH SET [Top5Camps] AS ' TopCount({Descendants( [Zone Id].&[14],3)},200,([Measures].[Sends])) '

select {[Measures].[Sends],[Measures].[Views],[Measures].[Clicks],[Measures].[Clicks Text],[Measures].[Clicks Html],[Measures].[Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count]} on Columns,

[Top5Camps] DIMENSION PROPERTIES [Zone].[uniq views],[Zone].[uniq soft bounces], [Zone].[uniq hard bounces],[Zone].[uniq clicks html], [Zone].[uniq clicks text],[Zone].[uniq clicks],[Zone].[uniq bounces], [Zone].[Campaign Launch Date],[Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY on rows

from [Communication]


--Some of these below use custom code in reporting services.  The queries are formed in visual basic.  Maybe someday I'll take the time to make these real queries.

="select {[RunningTotalSubs]} on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])),

   {[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows


   from [consumers] where ( [Zone Id].&[" &  Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) &  "])"

WITH

 MEMBER [Measures].[Variant Percentage] AS
 '((  [Time].[2004].&[1].[1].[1], [RunningTotalSubs]  ) -( [Time].[2005].&[1].[1].[1], [RunningTotalSubs]  ))/(  [Time].[All Time], [RunningTotalSubs]  ) '

select {[RunningTotalSubs],[Measures].[Variant Percentage] } on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])),

   {[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows


   from [consumers] where ( [Zone Id].&[14])

WITH SET [Top5Camps] AS

 ' Descendants([Zone Id].&[14].&[74],2) '

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) }

} on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } on Rows from [consumers] where ("&

 iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])"

="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) }

} on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } DIMENSION PROPERTIES [Zone Id].[Campaign Type Id].[Key], [Zone Id].[Campaign Type Id].[Name] on Rows from [consumers] where ("&

 iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])"

ON COLUMNS,

Order([Top5Camps], [Top5Camps].currentmember.properties("Campaign Launch Date") ,desc) DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

 ON ROWS
 from [All communication analysis]

WITH SET [Top5Camps] AS

 ' Descendants([Zone Id].&[14].&[74],2) '

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

 ON ROWS
 from [All communication analysis]

="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "]"")), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) '

="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns,

[Top5Camps] on rows

   from [All Communication Analysis]"

="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) '

="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns,

[Top5Camps] on rows

   from [All Communication Analysis]"

="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "], 3)'

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], MEMBER_CAPTION, MEMBER_KEY

 ON ROWS
 from [All communication analysis]"

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "], 1)'

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

 ON ROWS
 from [communication ]"

 

One link I came accross when using deminsion properties.  Try not to make queries with properties because they are slow.  But if you have to:

http://www.sqlservercentral.com/blogs/mysqllearnings/archive/2009/04/15/key-column-of-a-dimension-in-ssas.aspx

Each product has a weight associated with it. The requirement is to filter all the products whose weight is 5.

To achieve this, weight should be the member property of the product. This time, it cannot be made as a key column.  But, again instead of using ".properties" function, functions like "EXISTS" can be used.

A general way of writing this would be

Filter ([Prod Dim].[Prod Hier].[SKU Lvl].members,[Prod Dim].[Prod Hier].[SKU Lvl].currentmember.properties("Weight") = 5)

In this case, it has to filter each member by getting the property of each member and checking whether the weight is less than 5. Instead of using the ".properties" function, it can be written using "EXISTS" function.

Exists ([Prod Dim].[Prod Hier].[SKU Lvl].members,filter([Prod Dim].[Weight].[Weight].members,[Prod Dim].[Weight].currentmember is [Prod Dim].[Weight].[5]))

Also, it is better to use the function "IS" instead of "=" because IS function eliminates the conversion of the given string to a member.

Because of doing this, there was a considerable improvement in the query performance. These were the scenarios where I felt natural key was useful when specified as the key column.