Report Portal

SSAS Time Intelligence Wizard

Reposted from Amit Gupta's blog with the author's permission.

In any organization, typical questions to know organizational health are:
1) What is the growth of my company from last year?
2) What is particular product sale in last 3 months?
3) What is revenue for new promotion strategy till date ? etc...
All such questions needs comparison among different time slices. We can create such calculations using Time MDX Functions.
Here are few examples:

  1. WITH MEMBER [YTD Internet Sales Amount] AS
  3. YTD([Date].[Calendar].CURRENTMEMBER),[Measures].[Internet Sales Amount]
  4. )
  5. SELECT {
  6. [YTD Internet Sales Amount]
  7. ,[Measures].[Internet Sales Amount]
  8. } on 0
  9. ,EXISTING([Date].[Calendar].[Month].members,[Date].[Calendar Year].&[2004]) on rows
  10. FROM [Adventure Works]

Comparison between two quarters:

  1. WITH MEMBER [QTD Internet Sales Amount] AS
  3. QTD([Date].[Calendar].CURRENTMEMBER),[Measures].[Internet Sales Amount]
  4. )
  5. MEMBER [Last QTD Internet Sales Amount] AS
  7. QTD(PARALLELPERIOD([Date].[Calendar].[Calendar Quarter]
  8. ,1
  9. ,[Date].[Calendar].CURRENTMEMBER))
  10. ,[Measures].[Internet Sales Amount]
  11. )
  12. SELECT {
  13. [Measures].[Internet Sales Amount]
  14. ,[QTD Internet Sales Amount]
  15. ,[Last QTD Internet Sales Amount]
  16. } on 0
  17. ,EXISTING([Date].[Calendar].[Month].members,[Date].[Calendar Year].&[2004]) on rows
  18. FROM [Adventure Works]


Well now again if we need similar calculations for "Internet Reseller Amount" or "Internet Order Count" then developer need to write lot of almost redundant MDX code.

Here is the good news ... SSAS Provides feature called "Time Intelligence Wizard". This feature will provide neat GUI to achieve the same purpose which we were trying by MDX code. 

Lets explore the "Time Intelligence Wizard":

1) In BIDS, Click "Cube" in menu bar and select "Add business Intelligence"

2) Click "Time Intelligence Wizard" on next screen.

3) "Choose Target Hierarchy and Calculations" screen: If you have multiple hierarchy in time dimension then choose one targeted hierarchy on which you want to implement the calculations. For Adventure works example, time dimension has two hierarchies Calendar and Fiscal so desired one can be specified. SSAS doesn't provide option to implement same calculation set to multiple hierarchies simultaneously. You need to run through this wizard multiple times for each hierarchy.

You can choose multiple calculation based on your requirement.

4) "Define Scope of calculations" Screen: Calculations defined in previous screen can be applied to all measures or only to selected members.

5) "Completing the Wizard" screen: Once you come to this screen, Analysis Services generate MDX script and applies to cube as soon as we hit the OK button.

You need to create date dimension as time dimension to use this feature. Wizard will be disabled until date dimension is set as Time. 

This covers the scope of this post. I will cover few customization and optimization aspect in coming post.


Amit Gupta

Amit Gupta is working in BI space from last 7 years and he has started working in MSBI from Jun 2006. He specializes in business intelligence services and solutions and holds Microsoft certifications in BI space. His personal blog site can be found at


Tags: design, mdx


2007-2015 VidasSoft Systems Inc.