Role playing dimensions in QlikView

Role playing dimension is used when a dimension table is reused to join with a fact table more than once.

A classic example of a role playing dimension can be a Time Dimension which joins with a Fact table. This Fact table contains OrderDate,ShipDate,ReceiveDate etc. To get these dates you will create just one table for TimeDim in your data warehouse but create copies of TimeDim with different names to join  it with the Fact table to get information about OrderDate,ShipDate and ReceiveDate.

In QlikView creating role playing dimension is easy as long as you understand the concept and the requirement.

To implement role playing dimension in QlikView, i will use the following tables. The idea is to get details of each of the Regions.

TwoTables

In QlikView, if you load these tables, you have to associate them to get the Region Details for the regions. If you associate them by renaming the Region_Key in the Region_Details table then you can get the details of only one region.

In QlikView, association can be done in the following way,

SingleTableAssoc

Creating Association by renaming Region_Key as Atlanta_Key

but this will only give you details about the Atlanta Region. See data model in Table viewer below

Single_TableViewer

To get the details about all the three regions, we have to create role playing of Region_Details table and then associate them with the Region table.This can be done by making multiple copies of the Region_Details table. You can use resident load to create copies of the table already loaded in the memory.

MulitpleCopies_Table

 

If you see this data model in Table Viewer, it will look like the following

Multiple_TableViewer

 

if your datamodel is big and already contains lot of other tables then adding the tables as shown above may make your data model heavy. Above solution is good but you should always try to reduce the number of joins and tables in your data model.You can include your role playing dimension tables into one single table by using Left join.

You can take the same above script and add left join to it.LEftJoin_Roles

Now your data model will look like the following :

LEftJoin__TableViewer

All fields combined into one table

So to create role playing dimension,

  1. Load the main dimension table
  2. Create multiple copies of the dimension by using resident load. Store these tables with different names
  3. Alias the columns on which  join is required
  4. Use Left join to combine role playing dimensions into one table
  5. Drop the main dimension because it is no longer needed

2 thoughts on “Role playing dimensions in QlikView

  1. Hi Chandraish,

    You provide a very good description about role playing dimension.

    Thanks
    Raksha..

  2. Sukumar Padarthi says:

    Excellent post on Role playing dimension.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.