SSAS Time Intelligence Wizard

1. WITH MEMBER [YTD Internet Sales Amount] AS
2. AGGREGATE(
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]

WITH MEMBER [YTD Internet Sales Amount] AS
  AGGREGATE(
           YTD([Date].[Calendar].CURRENTMEMBER),[Measures].[Internet Sales Amount]
           )
SELECT {  
          [YTD Internet Sales Amount]
         ,[Measures].[Internet Sales Amount]
       } on 0
      ,EXISTING([Date].[Calendar].[Month].members,[Date].[Calendar Year].&[2004]) on rows
  FROM [Adventure Works] 

Comparison between two quarters:

1. WITH MEMBER [QTD Internet Sales Amount] AS
2. AGGREGATE(
3. QTD([Date].[Calendar].CURRENTMEMBER),[Measures].[Internet Sales Amount]
4. )
5. MEMBER [Last QTD Internet Sales Amount] AS
6. AGGREGATE(
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]

WITH MEMBER [QTD Internet Sales Amount] AS
  AGGREGATE(
           QTD([Date].[Calendar].CURRENTMEMBER),[Measures].[Internet Sales Amount]
           )
MEMBER [Last QTD Internet Sales Amount] AS
  AGGREGATE(
           QTD(PARALLELPERIOD([Date].[Calendar].[Calendar Quarter]
                              ,1
                              ,[Date].[Calendar].CURRENTMEMBER))
                              ,[Measures].[Internet Sales Amount]
           )  
SELECT {  
          [Measures].[Internet Sales Amount]
         ,[QTD Internet Sales Amount]
         ,[Last QTD Internet Sales Amount]
       } on 0
      ,EXISTING([Date].[Calendar].[Month].members,[Date].[Calendar Year].&[2004]) on rows
  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 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 http://www.msbiconcepts.com/.