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.