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.
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,
but this will only give you details about the Atlanta Region. See data model in Table viewer below
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.
If you see this data model in Table Viewer, it will look like the following
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.
Now your data model will look like the following :
So to create role playing dimension,
- Load the main dimension table
- Create multiple copies of the dimension by using resident load. Store these tables with different names
- Alias the columns on which join is required
- Use Left join to combine role playing dimensions into one table
- Drop the main dimension because it is no longer needed