Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

MDX Utility Belt of Calculations Part 1

Developing MDX calculations is one of the most confusing and time consuming pieces of building an Analysis Services cube.  That is why I have accumulated what I like to call a Utility Belt of calculation that I can pull out at any moment to solve a problem.  I consider this a Utility Belt because on just about every Analysis Services project that I have worked on these calculations have come in handy and I hope they will be helpful for you.

When building calculations you may find the templates that are available in Business Intelligence Development Studio (BIDS) as a helpful starting point.  Unfortunately, I have found even using these templates can make calculations more confusing than they really need to be.

In this blog series I will give you the business problem, solution, and then calculation that satisfies the business problem. 

Problem

You need to return sales for one year prior (Ex. one year ago, four quarters ago, twelve months ago).

Solution

Use the ParrallelPeriod time series function to look back a set number of values back in a date hierarchy and return the sales at that point.

Calculations

Looking back one year at the Year level

(ParallelPeriod([Date].[Date Hierarchy].[Year],
1,
[Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])

---------------------------------------------------------------

Looking back four quarters at the Quarter level

(ParallelPeriod([Invoice Date].[Date Hierarchy].[Quarter],
4,
[Invoice Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])

---------------------------------------------------------------

Looking back twelve months at the Month level

(ParallelPeriod([Invoice Date].[Date Hierarchy].[Month],
12,
[Invoice Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])

You can find me regularly blogging at http://www.bidn.com/blogs/DevinKnight

Comments

Posted by Wes W. on 4 December 2009

Hi Devin,

Have you ever been successful getting a SSAS dynamic named set calculation to work in Excel based on ParallelPeriod?

For example, I have the following dynamic named set calculation to show the previous quarter, where 'Today' is a top-level named set and works fine.

ParallelPeriod

(

 [Date].[Fiscal].[FiscalQtr]

,1

,Exists

 (

   [Date].[Fiscal].[FiscalQtr].MEMBERS

  ,[Today]

 ).Item(0).Item(0)

)

The above code oddly works fine everywhere except in Excel where no result set is shown.  This one has me stumped and I'm not sure if it's an Excel issue or not yet.

Posted by knight_devin@hotmail.com on 7 December 2009

That's strange.  I haven't had the problem where my calculations will not work in Excel.  I'll try and replicate the problem.

Posted by raj more on 7 December 2009

Should this be a "-1" for the number of years as in:

(ParallelPeriod([Date].[Date Hierarchy].[Year],

-1,

[Date].[Date Hierarchy].CurrentMember),

[Measures].[Sales Amount])

We are going a year back in time.

Posted by knight_devin@hotmail.com on 7 December 2009

Actually positive 1 is correct in this calculation.

Posted by Zenatti on 10 January 2010

Any news aboutcalculations not working in Excel?

Leave a Comment

Please register or log in to leave a comment.