2

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)

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Peterson Davis
  • 119
  • 1
  • 7
  • 1
    Not sure what you are asking, but a *fact* record can have more *time* columns, i.e. FK to the *time dimension*. A typical *transaction* has an *entry date* and a *booking date*. This does not *prevent* aggregation, but you must decide based on which time dimension you want to aggregate. – Marmite Bomber Oct 08 '21 at 14:07
  • @MarmiteBomber thank you for your reply. Maybe I wasn't clear: imagine you have fact a Fact_Order with a Order_Date_ID (which links to a Dim_OrderDate) and a Fact_Request with a Request_Date_ID (which links to a Dim_RequestDate). Now say you want to have a tabular vision in your BI application (say PowerBI) with the Orders and Requests # per Date, since they are 2 different dates (different dimensions), you can't. Unless you use a snapshot table. – Peterson Davis Oct 08 '21 at 14:28
  • 1
    If you have multiple date columns in your fact then you have no choice. But even with one date column in your fact it's usually still a good idea to give it a descriptive name. – Wouter Oct 08 '21 at 14:37
  • The date dimension should be Dim_Date in your data warehouse. This way you can do the role playing in Power BI. – Wouter Oct 08 '21 at 14:38
  • Well, you typically have *only one* time dimension and you use native `date` columns in you fact table. Alternative setups are more in the realm of the *theory*. – Marmite Bomber Oct 08 '21 at 14:43
  • Hi @Wouter, I agree, although this would be a tremendous violation of Kimball's DW Toolkit, since it clearly specifies that FK names should be descriptive and each with its own role-playing date dimension (ofc same names/concepts same role, conformed that is). My question was: assuming a perfect Kimball model, without just having a generic "Dim Date" in the presentation layer, can my question be answered? Again, doing so would ignore the idea of the role-playing for each conformed fact. – Peterson Davis Oct 08 '21 at 14:46

1 Answers1

0

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.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60