Role playing Dimensions: When is it appropriate?

[RSMaxwell]

This issue has come up and I was curious what you would say. This stems out of differing interpretations of role playing a dimension in the Data Warehouse Toolkit. There are basically two faction where I work concerning when it is appropriate to role play a key field name: those who say role play every key in every field, and those who say only role play when the dimension is used twice in the same fact.

As a simplified example, the fact table contains a store store identifier (StoreKey), a date identifier (DateKey), and gross dollars (GrossDollars) sold for the day.

Here are the dimension tables:

DateDim
DateKey INT
CalendarDate DATETIME

StoreDim
StoreKey INT
StoreName VARCHAR

Here are the two options for fact tables:

Fact table Version 1 ---
StoreSalesFact
StoreKey INT
DateKey INT
GrossDollars

Fact Table Version 2 --
StoreSalesFact
StoreSalesStoreKey
StoreSalesDateKey
GrossDollars


So in this situation, which would be the “best” to use?

Fact tables that contained two or more DateKey columns would definitely require the use of role playing. In which case, would both DateKeys be role played? Or just the “secondary” date field? For example, in an OrderFact, if the fact contained the order date and a shipment date, would the fact contain OrderDateKey and ShipmentDateKey or would it be wise/acceptable to just have DateKey and ShipmentDateKey?

[Devendra Naik]

I recommend OrderDateKey and ShipmentDateKey , it is makes more sense . that is the standard we follow.

[Type2]

My preference is to roleplay all dates and anywhere else that makes the key reference easier for ETL/BI to understand.

[BrianJarrett]

The school of thought where I work now is that we have a "common" date field (called something like "Activity Date") and then any other dates in a fact table are aliased.

The argument for having a common date field is that the reporting tool (in this case Business Objects) will automatically merge that common date field across multipass queries. It saves the users from having to manually merge the two different date fields. I can see how this is beneficial, particularly with less experienced end users and with data that lends itself to using a common date.

Right now that date field is directly joined to the dimension table. The other date fields are aliased in the BO universe (semantic layer). Aliasing all the dates and then choosing one of the aliases as a common date would probably provide the most flexibility.

I do, however, like the concept of role playing all dates and not choosing a common date for the fact table. That could be an arbitrary process we could get wrong if we don't gather thorough requirements or the business changes its mind. In reality though determining a common date does make things easier for the end user and ultimately for the developer as well so I'd probably go that route in the end.

[Devendra Naik]

There are three recommended levels of fact tables.

Transaction level, Accumulating , Periodic Snapshot.

If you really want to design a transaction level fact which is generic in nature then you can consider a "common" date field, as suggested before.

Accumulating snapshot you have to live with the role play, since it will going to summarize the history and same with the periodic snapshot.

Please remember that the ETL to populate the accumulating snapshot from transaction level fact table will become relatively difficult if you are using generic date fields, All your users should be relatively advanced.

[juz_b]

If memory serves me right, according to the Kimball philosophy, the goal of the presentation layer (what the end user sees) is to make the fields names as specific as possible. The reason is so it minimizes the need for users to try to guess what the data truly represents. It should be as user friendly as possible.

One can argue that the presentation layer should be implemented at the database level, or (for BO developers) at the universe level. My preference is to implement it at the database level, so the users are not limited to a single query tool.

Having said that, I would choose the role playing option at the database level. It is also recommended that for every role, create a view of the base dimension and rename the fields in the view to reflect it's new role.

I am also not in favor of a common date field in the presentation layer because, once again, it is too generic and they can have different meanings in different fact table.

[BrianJarrett]

juz_b,

You make a good point about not having a generic field because it can create confusion. I agree with that form a purist perspective and that's actually how we did things at my previous employer.

The flip side to this, as I mentioned before, is that end users are often confused about how to merge dimensions. They don't always know that an invoice date and a purchase order date field should be merged nor do they know how to do it in the reporting software. It's an education issue, I know, but it's also a support issue when they open a ticket on it or call a report developer up and ask how to do it.

I guess the question in the end is which way will create less confusion and require less time from the developer. I'm not sure I have the answer to that question; maybe it depends on the culture and experience level of the users. I have to say though that after reading your post maybe the generic field is a compromise. I'm not so sure that I'd fight for that solution now.

Doing things at the database level does provide more flexibility. Role playing dimension are done easily there; create the view and you're done. What's more difficult is to implement something like aggregate navigation at the database level. If you can convince management to only use a single piece of software to do the reporting you're pretty safe doing it all in the semantic layer. If that ever changes though you've got the task of having to implement the same functionality in multiple applications. That can really eat up some time and can introduce problems. Maybe a good middle ground is do the easy stuff at the database level and leave aggregate navigation in the BI tool. So much of this also depends on the environment and culture of the business as well; each situation is a little bit different.

Great post; I wondered how long it would take you to get on this board!

[warrent]

RSMaxwell's original post also asked about changing the names of the non-date keys, such as changing the Store_Key to Sales_Store_Key. This application of the idea of role playing is a bit too rigorous for my tastes. It introduces more complexity in the model, and implies there might be other kinds of stores involved in other business events. If there is only one possible Store entity that could be involved in a sale, I would just use the original key name. If you could have multiple stores involved in a single fact table, like in an accumulating snapshot, where you might have a Selling_Store and a Return_Store, then you need to distinguish the two.

Otherwise, the only time you will get ambiguities is when multiple fact tables are involved in a single query. In this case, the tool should be able to resolve the ambiguity for the user via its drill-across capabilities. Depending on the front end tools you use, most users won't ever see the key names anyway.

--Warren

[Purushothaman.VS]

Hi,
Actually the concept of Role playing dimensions are used primarily for avoiding the confusion due to the presence of the similar dimensions with different role, which in the example given by RSMaxwell does not hold true. Hence it is not necessary to have role playing dimension for all the dimensions.

Moreover, Date dimension is mostly used as Role playing dimension as is the case in the second example suggested by him involving Order Date and Shipping Date. We normally dont use role playing dimension in every case but in specific scenario. The Dimension modeller would have to first identify the dimensions which could have same structure but different interpretations for example the Date, which would have the same structure but would mean two completely different dates when used to signify Order Date and Shipment Date. Once such Dimensions are identified, only those tables would have be used as a role playing dimension.

Besides it is not necessary to have two physical tables, in our case as Order Date and Shipment Date tables. Instead in the fact table, we can have two columns signifying Order Date key and Shipment Date key joined to a single Date table. At the reporting layer, infact at the Reporting tool level these tables could be aliased to different table instances, in our case the Date table could be aliased to Order Dates and Shipment Dates at the Reporting Tool level.

I was fortunate to have a chance to work with different Reporting tools like Cognos, Business Objects, SQL Server 2005 BI Suite and Proclarity , Microstrategy etc and all these tools have the capability to handle the Role playing dimensions at the Reporting tool level by the use of Aliases. What the tool does at the time of query generation is that it aliases the tables accordingly and hence handles the confusion. The concept these tools use is the basic sql concept of aliasing when the same table has to be used in two different context.

Hence at the Dimensional Model level, the only consideration to be given to the Role playing dimensions is to name the keys in the Fact table accordingly for identification. The rest can be handled at the Reporting tool level.

[Jeff Smith]

Some dimension tables are more generic than others. The Date_Key should always be given an alias in the fact table to reflect the actual date field it is representing. But there are other occassions as well. I worked for a Bank and Branch was somewhat generic. The branch could be the Transacting Branch or it could be the location of where the account was opened (Account Branch). Geography is another example - a Zip Code can represent the geography of so many different types of entities.

[Sponsored content]