I have a fact table that references my date dimension as both a Form Date and Finances Date. Hence the date dimension is roleplaying two different roles. This works fine, except that I can't roleplay the names of the Year or Month column. I would rather it be the Form Year and Finance Year column respectively, or be able to set the attribute description such that my client applications can use that property to display as tooltips/headers.
The problem is, in a single pivot report, the user will have two different years and it isn't clear which is which(they see 2010 and 2009 and don't know which is the Form Year and which is the Finance Year) without me doing some hackish code to look at what the dimension name is.
In other words, [Form Date].[Year] and [Finance Date].[Year] are both a "Year" attribute as far as the cellset is concerned. When you change the description or name of that attribute in SSAS, you are changing it for both roleplaying dimensions. You can customize the name of the roleplaying dimension, but unfortunately not the attributes.
My options so far:
-In the data warehouse DB, create copies of the date dimension table for each use case so that I can customize the attribute names/descriptions for the columns. This creates more maintenance/work for me in keeping these copies conformed.
-In the data warehouse DB, create a view on top of the date dimension table for each use case. The problem here is that I can't create FK relationships between a dimension view and a fact table. I'm afraid this is going to cause me more headaches than its worth because it seems alot of SSAS/SSRS/Powerpivot and other tools really depend on those FK relationships being there to help it determine the structure of the data warehouse.
-Create copies of the Year column in the same table so that each is materialized as its own attribute in SSAS and hence can have its own name and description properties. Haven't played with this to see if it would work the way I imagine, but I am thinking I would just create multiple dimensions based on the same table, and in each case only include one of the Year columns, such as the Form Year in the Form Date dimension. (Could also use a computed column instead of a copy of the column.) Downside of this is that it makes the dimension alot more confusing. I already have lots of attributes to support various hierarchies, which is normal, but now I'm throwing in the mix multiple columns for a single attribute just to support the header/description of the attribute being different even though the attribute values are all the same.
In the Kimball Group Data Warehouse Toolkit book, it describes this very scenario by saying
These date dimension copies are declared as semantically distinct views, such as "First Purchase Date"[which has attributes like "Date of 1st Purchase Year" rather than just "Year"] dimension table with unique column labels.
This book is pretty conceptual and technology agnostic and doesn't go into implementation details. The wording in some places implies using views, and in other places implies using physical table copies. As mentioned above, both has big enough disadvantages that I'm afraid to venture down either road since I can already foresee headaches.
How do you think I should implement multiple roles of a date dimension, such that I can customize the description/name properties of the attributes for each role/use case that the dimension participates in?
I also will have other fact tables that reference the same dimension and hence will have a similar issue, so it is not just the issue of having a single fact table referencing a dimension twice.
