All, It's been a while I built a DWH star schema, too much data vault nowadays :) Quick question. Can we use dates as an attributes in some dimensions? Such as in Product dimension, if we have product effective_date and retired_date, these can be included as attributes in product dimension right?
Have a separate date dimension as well.
Thanks.
Maybe the correct question is Should dates in the dimension tables be joined to dimDate? First point : These attributes can be considered facts because in general anything refering to a date is a fact since it is time-sensitive. So if it is possible, we can add these into a fact table. This could be as simple as Fact.Product or Fact.ProductHistory for example, but it really depends on how you will be analysing the data. Sometimes you may even want to add them directly into the main fact table such as Fact.Sales or something similar.
Second point :
You must be aware that adding relationships will take time in term of maintenance of your data warehouse. Since the foreign keys may cause additional overhead during the ETL process, even when disabling them while loading the data, you need to recheck them once they are enabled again.
Third point : You can create a dedicated-seperated date dimensions for each one of the date attributes
if you do wish to link the dimensional attributes to a date dimension, then I find it is better to create separate date dimension tables for each one of your attributes. I will let you discover the inconvenience of this if your data model becomes complex.
You can link all date attributes to the same date dimension table, then you will discover that it will be difficult when applying multiple independent filters.
What if you only want to see products added in X month but you also want to aggregate the sales by the month they were purchased?
Using a single date dimension means that this query is a lot harder than it needs to be. If you have separate date dimensions it is pretty easy to do.
You can read more about role-playing dimensions.
