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. 

SSAS Key Performance Indicator (KPI)

KPI (Key Performance indicators) in analysis services represents data to measure business success at very high level(mostly at organization ...

KPI (Key Performance indicators) in analysis services represents data to measure business success at very high level(mostly at organization level/regional level). 

Consider sales team want to know: 

 1) How are we doing in comparison of last year/month/week sales?

2) What is the trend of revenue in last one year? 

3) Are we matching sales targets set by higher management?


These questions can be answered by KPIs in well structured and graphical manner. Here is the example of KPIs when you are using Excel 2007 as client tool to view Analysis Services data:

Key Performance Indicators in Excel 2007

Before we start on "How to implement SSAS KPIs". Let me explain my cube data. I have taken only two dimension Product and Time and only one measure group "Fact Internet Sales" from "Adventure Works" sample project. I have added one more measure group which has target revenue  for each year. We have scripts below to load data into new "Target" table:

view plaincopy to clipboardprint?

1. CREATE TABLE Targets (CalendarYear VARCHAR(4), TargetInternetSalesRevenue BIGINT)   
2.   
3. INSERT INTO Targets values (2001,3000000)   
4. INSERT INTO Targets values (2002,7593423)   
5. INSERT INTO Targets values (2003,9000000)   
6. INSERT INTO Targets values (2004,11770000)   
7. INSERT INTO Targets values (2005,10500000)  

CREATE TABLE Targets (CalendarYear VARCHAR(4), TargetInternetSalesRevenue BIGINT)

INSERT INTO Targets values (2001,3000000)
INSERT INTO Targets values (2002,7593423)
INSERT INTO Targets values (2003,9000000)
INSERT INTO Targets values (2004,11770000)
INSERT INTO Targets values (2005,10500000)

My cube will look like:

Cube Data Source View

Now we are ready to create KPIs in the cube. Use the following steps to create KPIs in cube:

1) Open Solution and click to "KPIs" tab.Click on "New KPI" and it will open one template for filling the desired information.

2) Name: Provide name to new KPI. For example, we have used "Internet Sales Revenue" for KPI.

3) Associated Measure Group: KPI belongs to one particular measure group. Here you can specify particular measure group name like "Fact Internet Sales"(in our example).

4) Value Expression: This is actual value of KPI. This may be raw measure or MDX numeric expression.

[Measures].[Sales Amount]

5) Goal Expression: Here our new created measure group will come in picture. We will get "Value Expression" from "Fact Internet Sales" measure group's measure "Sales Amount" but goal value will be from "Target" measure group's measure "Target Internet Sales Revenue". 

[Measures].[Target Internet Sales Revenue]

6) Status:This checks state of KPI at any point. Status MDX expression should return value between -1 and 1. 

In this example, if KPI Value is meeting 90% target then it is positive status and status is average for range 85-90%. If it is less then 85% then it is alarm to senior management and they should take some actions to make it positive.

KPI Status Expression

7)Status Indicator: Status can be represented by multiple ways. check below figure for available options in SQL Server 2005 and SQL Server 2008:

Status Indicators

8) Trend:Trend evaluate value of KPI over the period of time. You can specify values between -1 and 1 for trends. For example, You want to compare current year and last year's revenue for checking the trend. if revenue growth is greater than 10% then it is positive trend and revenue drop more then 10% then it is negative trend.

KPI Trend Expression

7)Trend Indicator: Status can be represented by multiple ways. check below figure for available options in SQL Server 2005 and SQL Server 2008:

Trend Indicators

There are some additional properties which we can configure like Display folder, parent KPI and weight which i will try to cover separately. 

Now we are done with all the changes. Once we process the cube, KPIs will be available for use and we can view them in "Browser view" under KPIs tab. 

Lets see cube data so we will be able to check if KPIs are behaving same as expected:

Cube Browser data

Calender year 2003 has achieved its own target and shows positive growth from last year. Lets put filter on calendar year for 2003 in "Browser View" of KPI tab and view data. 

Cube KPI Browser Tab data

DIMENSION WRITEBACK

 MSBI: DIMENSION WRITEBACK.:

MSBI Cube pulls data from relational database on the basis of defined processing strategy (Process full, Process Incremental) and storage options (MOLAP, ROLAP and HOLAP).  

In few situations, we would like to write back directly to cube. This can be achieved by Writeback Functionality in SSAS. In SSAS, you can directly write data to cube dimension and measures which will be reflected in relation data source as well. There are two types of Writeback in SSAS.

1. Dimension Writeback
2. Cell Writeback

When dimension data is updated then it is called Dimension Writeback and when measure/ fact data is updated, it is called Cell Writeback.

Situations where Writeback will be useful?

1. (Dimension Writeback): For example, In Product dimension case: you want to add new product then it can be added by using Writeback feature or you are going to start new campaign in coming Christmas or want to promotional cost related to existing campaign. Writeback feature is good only for small number of updates because for each DML operation one request will be sent to relational database. It is better to change relation DB and then reprocess cube data. Writeback feature provides great flexibility to users who don’t have direct access to relation DB. 
2. (Cell Writeback): Tuples value can be changed while viewing result set. Most prevailing example is budget or forecasting data. By cell Writeback, change budget or forecasting data and see effect on different matrices to analyze business. You can option to commit the change data or rollback it in cube.

 Dimension Writeback


Dimension can be changed in SQL Server management studio as well as in business intelligence development studio.  Dimension Writeback has some limitations:

1. Dimension table should be from single table. Snowflake dimension are not supported.
2. Named query and DB Views are not supported.
3. In Cube Dimension, Dimension key and name columns properties should be same.

for using in the post , I am creating DimProduct_Writeback table by the help of Adventure Works DW table Dim Product. I can not directly Dim Product because of mentioned limitations.

view plaincopy to clipboardprint?

1. CREATE TABLE [dbo].[DimProduct_Writeback](   
2.  [ProductKey] [int] NOT NULL,   
3.  [Color] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,   
4.  [Size] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,   
5.  [SizeRange] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL  
6.     )  

CREATE TABLE [dbo].[DimProduct_Writeback](
 [ProductKey] [int] NOT NULL,
 [Color] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,
 [Size] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,
 [SizeRange] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL
    )

view plaincopy to clipboardprint?

1. Insert into [dbo].[DimProduct_Writeback]   
2.  ([ProductKey],   
3.  [Color],   
4.  [Size] ,   
5.  [SizeRange])    
6. select [ProductKey],   
7.  [Color],   
8.  [Size] ,   
9.  [SizeRange]   
10.   from [dbo].[DimProduct]  

Insert into [dbo].[DimProduct_Writeback]
 ([ProductKey],
 [Color],
 [Size] ,
 [SizeRange]) 
select [ProductKey],
 [Color],
 [Size] ,
 [SizeRange]
  from [dbo].[DimProduct]

Dimension Writeback cannot write back to identity column so I have created PRODUCTKEY[dbo].[DimProduct_Writeback] table into regular integer column. Let’s create dimension using [FactInternetSales] table will be used as Measure Group.

Cube Snapshot

How will you enable Writeback and How will you write back to cube as well as to  relation database?

In Dimension Properties, set “WriteEnabled” property to True.

Once you make "WriteEnabled" Property to TRUE then in browser tab, “Writeback” icon will be enabled.

 

Now Dimension can be updated in Dimension Browser tab or by using SQL Server Management Studio Dimension Browser tab.  In this post, We will use BIDS Dimension Browser tab.

Now take key attribute in Hierarchy Menu (“Dim Product Writeback” key attribute in example) and click on Member properties and then on “”Show All)”: 

Result will show all attributes related to key attribute:

Now we are ready to Writeback in Cube as well as in relation database. Lets click on “Writeback” icon in menu bar near “Member Properties” and right click on any row in data:

1. CREATE SIBLING: Now We can insert record in dimension. In the example, New product can be inserted by this option.
2. CREATE CHILD: For my example, this option is disabled but if you use “Parent Child Relationship” in your dimension then this will be enabled to insert new child for the parent.
3. DELETE: Record can be deleted from dimension as well as underlying relation table.
4. UPDATE: You can directly modify value of any cell by just selecting the cell and entering new data.

Example of New row insertion:

It will create new row in the dimension as well as in the [DimProduct_Writeback] table. 

Let’s understand how events have happened at server level:

1. MDX INSERT/DELETE/Update statement to analysis server in form to XMLA
2. SQL DML Statement to relational DB
3. Processing Dimension and incremental Cube Processing

MDX INSERT/DELETE/Update statement to analysis server in form to XMLA

SQL DML Statement to relational DB 

It sends DML operation to relational database in parametrized query:

view plaincopy to clipboardprint?

1. INSERT INTO [dbo].[DimProduct_Writeback]   
2.         ([ProductKey],[Color],[Size],[SizeRange])   
3.     VALUES (?,?,?,?)  

INSERT INTO [dbo].[DimProduct_Writeback]
        ([ProductKey],[Color],[Size],[SizeRange])
    VALUES (?,?,?,?)

Processing Dimension and incremental Cube Processing

 

Once data has been written back to relational table and cube dimension then it process cube to bring in consistent form and reflects changed data. These events can be observed by SQL Server profiler.

CELL WRITEBACK

Learn MSBI: DIMENSION WRITEBACK and CELL WRITEBACK in Analysis...: Like Dimension Writeback, there are limitations with Cell Writeback also. For enabling Cell Writeback, all measures should have aggregation as SUM in measure group. If measure group contains other aggregations like count, distinct count, max etc. then move them to separate measure group.

Cell Writeback is not possible using graphical user interface like we have for dimension Writeback using SQL Server Management Studio (SSMS) or Business intelligence studio (BIDS). It can only be done by writing MDX queries through SSMS. However you can built your own front end application and pass right XMLA script to analysis server.

Let’s understand “how can we implement cell Writeback?”

There are two major steps:

1. Enable Cell Writeback for measure group/partition
2. Execute MDX queries for changing cell/tuple values for same measure group/partition

1. Enable Cell Writeback for measure group/partition

Go to cube’ Partition tab, right click on measure group partition and select “Writeback Settings…”.

It will open new pop up window, where your changes will be recorded. By default, table name will be WriteTable_.  You can change table name and data source if you want to record in some other table or data source.

Once you click "OK", it will create one extra partition (Writeback Partition) for recording changes.  Once you deploy solution, same SQL Server table will be created in Specified data source.

So now Cell Writeback has been enabled. Now let’s change some data in cube.

2. Execute MDX queries for changing cell/tuple values values for same measure group/partition

MSDN Document provides syntax definition for updating cube data.

In example, we have data from 2001 to 2004 in our cube:

view plaincopy to clipboardprint?

1. select  [Dim Product Writeback].[Dim Product Writeback].MEMBERS on 1   
2.       ,[Order Date].[Calendar Year].members on 0   
3.   from  [Adventure Writeback Cube]   
4.    where ([Measures].[Sales Amount])  

select  [Dim Product Writeback].[Dim Product Writeback].MEMBERS on 1
      ,[Order Date].[Calendar Year].members on 0
  from  [Adventure Writeback Cube]
   where ([Measures].[Sales Amount])

If business user has to enter data for 2005 then it can be done by writing UPDATE CUBE statement in SSMS:

Update [Adventure Writeback Cube]      

   set ([Order Date].[Hierarchy].[Calendar Year].&[2005]       ,[Measures].[Sales Amount]) = 10,000,000  

Update [Adventure Writeback Cube]   
   set ([Order Date].[Hierarchy].[Calendar Year].&[2005]
        ,[Measures].[Sales Amount]) = 10,000,000

One more important aspect, whenever user execute UPDATE statement server internally start new session for user with New BEGIN TRANSACTION. These changes will be visible to same user. Only when user COMMIT TRANSACTION by executing COMMIT Statement, it will be visible to others. User can ROLLBACK transactions using ROLLBACK.