SSAS Time Intelligence Wizard

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:

YEAR TO DATE

view plaincopy to clipboardprint?

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:

view plaincopy to clipboardprint?

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.