We are currently having a discussion about the relevance of having a “table of time” (you may call it “Time dimension”) in a BO universe.
Today, we have a table of time linked to the fact table through a time_id.
For me, it forces us to put 3 indexes
- on the foreign key in the fact table
- on the primary key in the time table
- on the date itself located in the time table.
when only 1 index and no relation would be necessary with the date directly in the fact table.
For each date, we need a maximum of 6, maybe 7 derivatives such as week, month, calendar year, fiscal year…
I think that it would be way easier to have the date in the fact table and build these objects from it.
On top of this, not all the dates are linked with this table. Only 1 “special” date per fact table is linked to this time table…
So our customers need to know what the date in the time table represents before using it. To my opinion, aliases would have been the right choice (we have 6 fact tables… so 6 contexts)
What is your experience with this situation ?
Have you ever experienced a “useful” time table in a universe ?