Role Playing Dimensions in SSAS

Friends,

I am backkkk .. Long gap between my last post and this .. Here, in this post we are gonna discuss about ROLE PLAYING DIMENSIONS. I am gonna take same old and our favorite database “Adventure Works” to explain about Role Playing Dimensions. Lets say I have a scenario i.e I wish to see the sales happened based on Due dates,Ship Dates and Order Dates.These three dates MAY BE SAME and also MAY NOT BE as in many cases the ORDER will be given on one date and Shipment will be done on some other date and Dues will be paid on another date and hence this is very important for any Analyst to see the data based on different date types to get better idea about business. In this case I have to maintain THREE DATE KEY columns in my Fact table ONE for EACH date type given above. If you see the Fact table in Adventure Works database, it is designed in the same fashion.  I ran the query to fetch data from FactInternetSales table for the above given columns.

Select Top 100 OrderDateKey,ShipDateKey,DueDateKey,SalesAmount from FactInternetSales

From the above screenshot it is clear that Due Date,Order Date and Ship Date need not to be fall on same dates and hence we maintain different columns for each to track the status. Now, I wish to see the Total Shipment sales year wise. To get this I need to join the Fact table with Time table and the query looks like below.

Select DimTime.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime on DimTime.TimeKey = FactInternetSales.ShipDateKey
Group By DimTime.CalendarYear

In the above query I joined fact table (FactInternetSales) with Time table(DimTime) with SHIPDATEKEY as I wish to see the Sales year wise based on Shipments and the output can be seen in the screenshot given below.

Now, I wish to see the Total sales year wise based on ORDERED date. To get this I need to join the Fact table with Time table and the query looks like below.

Select DimTime.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime on DimTime.TimeKey = FactInternetSales.OrderDateKey
Group By DimTime.CalendarYear

In the above query I joined fact table (FactInternetSales) with Time table(DimTime) with ORDERDATEKEY as I wish to see the Sales year wise based on ORDER Date and the output can be seen in the screenshot given below.

And the final scenario i.e Sales year wise based on Due Date and the query looks like below.

Select DimTime.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime on DimTime.TimeKey = FactInternetSales.DueDateKey
Group By DimTime.CalendarYear

The output of the above query can be seen in the below screenshot.

From the above three results, it is obvious that the SALES VALUES doesn’t match as the transactions happened on different dates for each type. In OLTP database scenario, DIMTIME in the above query is referred as MASTER Table(where we will have master data of each entity) and FACTINTERNETSALES is referred as TRANSACTION table where we capture all transactions. In OLAP, we call DIMTIME as a Dimension and FACTINTERNETSALES as MEASUREGROUP or FACT.

Okie .. If the requirement is either one of the above mentioned three scenarios then NO ISSUES. We can create a Dimension using DimTime table, MeasureGroup using FactInternetSales table and give relationship between these two. What the Case if the user want to see the data based on all the three date types. Then the SQL query looks like some thing like given below.

Select a.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime a on a.TimeKey = FactInternetSales.DueDateKey
Inner Join DimTime b on b.TimeKey = FactInternetSales.OrderDateKey
Inner Join DimTime c on c.TimeKey = FactInternetSales.ShipDateKey
Group By a.CalendarYear

Even though the Date Types are different, the master data for all these are JUST Dates and hence ONLY ONE master table will be maintained. So, we have to use ALIAS of same DIMTIME table to join with different date types as shown above. The Group by and the Select list has a column “a.CalendarYear” and hence the data will be GROUPED yearwise based on DUE DATE as the alias “a” is joined with fact table with DueDateKey. To get the Sales Data year wise based on Order Date we simply need to change the Group By and Select list item a.CalendarYear to b.CalendarYear and to c.CalendarYear to get data based on Ship Date.

The same thing can be handled in SSAS Cubes using ROLE PLAYING DIMENSIONS property. PFB the Steps to define the same.

1) Add the fact table and the Dimension table to DSV as shown below and make sure RELATIONS are given for three date keys in Fact table to DimTime table.

2) Define dimension using DimTime table.

3) Create an Empty cube and add the Measure Group with FactInternetSales table.

4) Now add the dimension to the cube and see the magic. When you add one dimension, it adds THREE one for EACH date type. This is created based on the relationships given in DSV. I have THREE relations between DimTime and FactInterNetSales tables and hence THREE Dimension added. If you remove one relation ship in DSV and when you try to add the same dimension then it will add ONLY TWO dimensions. So, SSAS server will takes the headache of adding ALIAS Dimensions for each relationship given in DSV between these two tables.

Even though THREE ALIAS dimensions are created at Cube level, all the three will be pointing to the same underlying dimension as shown in below screenshot.

In the Dimension Usage tab the relations ships will be automatically set to the corresponding columns i.e ShipdateKey for Ship Date Dimension and so on as shown below.

So, the headache of creating ALIAS dimensions and giving relationships will be taken by SSAS Server if and only if the relationships are given in DSV between these tables. Now, what the case if the relationships are not given in SSAS( a rare case that happens). In this case all the headache will be on Developers who is developing the cube.

The steps remains the same as discussed above until Step 3. PFB the steps to be followed after completing the above given first three steps.

1) Add the dimension to the Cube and rename it to SHIP DATE.

2) Add the same dimension again and rename it to DUE DATE.

3) Add the same dimension again and rename it to ORDER DATE.

4) Go to Dimension Usage tab and click on the cell of each intersecting point and select the relationship type as REGULAR in the pop up wizard and select the columns to link. In the below screenshot I selected DueDateKey under MEasure Group Columns as I am relation DUE DATE Dimension to Fact table. Similarly we have to use OrderDateKey and ShipDateKey for Measure Group Columns to link with ORDER DATE Dimension and SHIP DATE Dimension respectively.

After adding relationships for all the three dimension PROCESS the cube and validate data by browsing cube. In simple Role Playing Dimension is a dimension which will have only ONE in database level and act as many DIMENSIONS in Cube.

That’s it .. Hope you guys understood the concept. Happy Coding !!

Regards,

Roopesh Babu V