A lot of times in "The Datawarehouse Toolkit" the authors switch their date foreign key name:
- Sometimes they use "Date Key (FK)"
- Other times they name it after the fact e.g. "Invoice Date Key (FK)"
Why and when should this occur? When there are multiple dates within the same Fact? When there are multiple facts within the same process?
When this happens should a role-playing date dimension be used? If so, wouldn't that prevent the BI tool from aggregating between multiple facts? (since they have different date dimensions, roles)
In "The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross, the authors sometimes use different naming conventions for date foreign keys. The variations in naming, such as "Date Key (FK)" or "Invoice Date Key (FK)," are typically employed to provide additional context or clarity about the specific date dimension being referenced in the foreign key column.
If you are dealing with a fact table that captures multiple dates related to different aspects of a business process (for example invoice date, shipping date, payment date), following this best practice helps to identify and differentiate the various date dimensions.
Also, in one other situation if you have multiple fact tables representing different measures of the same business process and each fact table may have its own date foreign key column you may need then to include information specific to that fact in the name, like "Invoice Date Key (FK)" or "Sales Date Key (FK)". It will help you distinguish between the date foreign keys used in different fact tables.
Now regarding, the role-playing date dimensions, they are typically used when you need to analyze the same fact data from different perspectives based on different date contexts. For example, you might need to analyze sales data by both order date and shipping date. In this case, you would create multiple instances of the date dimension, each with a different role, such as "Order Date" and "Shipping Date."
Using role-playing date dimensions as a best practice will guarantee you the association of different date foreign keys with different date dimensions while maintaining the appropriate context for each analysis perspective. However, you are correct that this can potentially limit the ability of some BI tools to aggregate data across different fact tables, as they may treat the dimensions with different roles as separate entities.