I have a fact table that refers to my date dimension, both the date of the date and the date of funding. Therefore, measuring a date is a role-playing game of two different roles. This works great, except that I cannot describe the names of the Year or Month column. I would prefer it to be the Form year and Fiscal year columns, respectively, or you could set an attribute description so that my client applications can use this property to display as tooltips / headers.
The problem is that in one summary report the user will have two different years, and it’s not clear what it is (they see 2010 and 2009 and don’t know which is the Year of the Form and which is the Finance Year) if I don’t do any then a hacker code to see what a dimension name is.
In other words, [Date of form]. [Year] and [Date of funding]. [Year] are the attribute "Year" in relation to a set of elements. When you change the description or name of this attribute in SSAS, you change it for both role sizes. You can customize the role name, but unfortunately not the attributes.
My options are:
- In the data warehouse database, 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 to make these copies fit.
- In the data warehouse database, create a view on top of the date dimension table for each use case. The problem here is that I cannot create an FK relationship between the dimension view and the fact table. I am afraid that it will cause me more headaches than its value, because it is like SSAS / SSRS / Powerpivot and other tools that really depend on those FK relationships that can help him determine the structure of the data warehouse.
-Creating copies of the Year column in the same table so that each is implemented as its own attribute in SSAS and, therefore, could have its own name and description properties. They didn’t play with it to see if it would work as I imagine, but I think that I’m just creating several dimensions based on the same table, and in each case will include only one of the Year Year columns, for example Form Year in measuring the form date. (You can also use a calculated column instead of a copy of the column.) The bottom is that it makes the measurement much more confusing. I already have many attributes to support different hierarchies, which is fine, but now I drop in several columns for one attribute only to support the attribute title / description, even if the attribute values are all the same.
In the Kimball Group Data Warehouse Toolkit book, this scenario describes this scenario by saying
These copies of date sizes are declared as semantically different such as “First Purchase Date” [which has attributes such as “Date of the 1st quarter of the purchase” and not just the “Year” table] with unique column labels.
This book is quite understandable and technologically advanced and is not included in the implementation details. The wording in some places involves the use of representations, and in other places the use of physical copies of the table. As mentioned above, both have quite large flaws that I am afraid to take risks on any road, since I can already anticipate headaches.
Do you think I should implement several date measurement roles so that I can customize the description / attribute name properties for each role / use case in which the measurement is involved?
I will also have other fact tables that refer to the same dimension and therefore will have a similar problem, so the problem is not only that one fact table refers to the dimension twice.