SSAS Debuging MDX

I've been delving into the world of MDX recently and it is another world. You really have to get your head thinking all sorts of different ways to try and grasp the tuples, slices, hierachies etc.

What writing calculated measures I have often found the result not to be what I expected.

This is often due to me navigating the wrong hierarchy, naming something wrong or navigating to a the wrong place in a dimension. For example if you want the last an average of the last 12 months, then you need to find the period that is 12 months before the current one. Functions like parallel period help in allowing you to specify just that, however I found that for a fiscal calendar of 544 (weeks). It wasn't working, the number where incorrect.

Aggregate({ParallelPeriod([Date].[Date Hierarchy].[Year],1,Date.[Date Hierarchy].CurrentMember):Date.[Date Hierarchy].CurrentMember}

So where do you start in resolving this. The key is to start from the bottom and work up. In this case I need to check that the period returned by ParallelPeriod was the right one. You can't just use the ParallelPeriod as that returns a member and not a value. The trick I started using was to create a calculated measure that returned the name of the member, simple really.

So my calculated measure was something like,

ParallelPeriod([Date].[Date Hierarchy].[Year],1,Date.[Date Hierarchy].CurrentMember).Name

This is also useful if you spell something wrong, because you won't get an error if you reference a level. So if you break down your main calculation and you still get (null) it means you've navigated into a blackhole, where nothing exists. In my case I'd done the cut and paste error in copying between my normal date hierarchy and the fiscal one. The Fiscal one doesn't have a [Year] level it has a [Fiscal Year] level.

 



-
Published Wednesday, November 21, 2007 4:01 PM by simonsabin

Comments

Thursday, November 22, 2007 1:36 PM by Vidas Matelis

# re: SSAS Debuging MDX

Simon,

Check MDXStudio software that is created by Mosha Pasumansky:

http://www.ssas-info.com/forum/MDXStudio

It is an alpha version and free download right now. This will allow you to "debug" your MDX statements.