MDX Tricks

I have started working on MDX queries also. Basic syntax looks like SQL queries but still it is far different than SQL queries. While working I found some difficulties to apply some tricky logic in queries, so I thought to jot down the tricks for others will helpful for some one.

So I am hopping that you should know the basic logic to write MDX queries and below are some defined situation and its logic.

Checking which hierarchy is selected on Axis:

CASE  

when NOT IsError(Extract( Axis(0), [Dim Date].[Calendar_Year]).Count)

then (Formula for Calendar year hierarchy)

when NOT IsError(Extract( Axis(1), [Dim Date].[Calendar_Year]).Count)

then (Formula for Calendar year hierarchy)

when NOT IsError(Extract( Axis(0), [Dim Date].[Fiscal_Year]).Count)

then (Formula for Fiscal hierarchy)

when NOT IsError(Extract( Axis(1), [Dim Date].[Fiscal_Year]).Count)

then (Formula for Fiscal hierarchy)

else '-1'

END

Here we have to check one condition twice for Axis 0 and Axis 1. After that this one MDX expression will work for two different hierarchies.

Checking which hierarchy level from one hierarchy is selected on Axis:

I have situation where I have calendar hierarchy Year- Quarter-Month.

On Month level calculation should act normally summation which is default behaviour of MDX but on higher level which is Quarter it should be the average of all month level calculation. Same for Year, it should be the Average of quarter.

Below is the syntax for it:

Case

when [Dim Date].[Fiscal_Year].Level.Name="Fiscal Month" then

IIF(([Dim Date].[Fiscal_Year].CurrentMember.LEAD(1),[Measures].[Planned Retail]) IS NULL OR

ISEMPTY(([Dim Date].[Fiscal_Year].CurrentMember.LEAD(1),[Measures].[Planned Retail])), NULL ,

[Measures].[Actual INV_TL ]/((SUM({[Dim Date].[Fiscal_Year].CurrentMember.LEAD(5):[Dim Date].[Fiscal_Year].CurrentMember.LEAD(1)},[Measures].[Planned Retail])+[Measures].[Actual Retail])/6))

when [Dim Date].[Fiscal_Year].Level.Name="Fiscal Quarter" THEN 

AVG({DESCENDANTS([Dim Date].[Fiscal_Year].currentmember,[Dim Date].[Fiscal_Year].[Fiscal Month])},[Measures].[Actual IFC])

when [Dim Date].[Fiscal_Year].Level.Name="Fiscal Year" THEN

AVG({DESCENDANTS([Dim Date].[Fiscal_Year].currentmember,[Dim Date].[Fiscal_Year].[Fiscal Quarter])},

AVG({DESCENDANTS([Dim Date].[Fiscal_Year].currentmember,[Dim Date].[Fiscal_Year].[Fiscal Month])},[Measures].[Actual IFC]) )// Here ve have taken avg(avg (month))

END

Average of two measures:

SSAS not provides functionality of calculate average of two measures on set if you are not sure about period or count of values for average. It provides average of one measure for one set. Means you can calculate measure for date year, quarter or date.

If you need average of two measure for one set the you need to follow below steps:

• Create measure for count of non empty values for first measure + same for another measure.
• Create measure for sum of two measures and divide that sum with count of non empty records

To create measure of non empty records use below expression:
Syntax: 

count(NONEMPTY({[Dim Date].[Fiscal_Year].CurrentMember.LEAD(5):[Dim Date].[Fiscal_Year].CurrentMember.LEAD(1)},

[Measures].[Planned Retail]))+Count(NONEMPTY(([Dim Date].[Fiscal_Year].CurrentMember,[Measures].[Actual Retail])))

In that way you can calculate average of two measure for dynamic period or count.

CALCULATED script importance:

I was working on one of the calculated measure and after creating new measures and deleting old one, I deployed cube on server. After I browse the cube I found all Null values in cube.

I am cross checked all the dim and fact for data. All was looking good but I was still messing the data. After searching lots of things I found that, I have deleted CALCULATED Script from the calculated measure group by mistakenly. 

After deleting, Now question for me is how to add it back.

Below steps will tell you about it:

• Open calculate measure tab and open that window in script mode.
• Add below script at the top of all calculations:

Script:

/*

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If deleted or modified, the data within the cube will be affected.

This command should only be edited if you intend on manually specifying how the cube will be aggregated.

*/

CALCULATE;

*/

CALCULATE;

Now redeploy the cub and browse it again. You can see the data in cube.

Note: If you still missing the data  the please check the partition created for fact. Each fact should create one partition for them. If you are missing partition then it can be cause to missing data in cube.