A table of time in a universe

Hello All

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 ?

Thank you

You’re mixing dates and times in your post. Do you mean date values or do you mean time values?

In the data warehouses that I’ve designed, we almost always have a date dimension. Sometimes we have a time dimension. For my current client, rarely do they care about the time, just the date within the warehouse. If time is important, I store it in a separate “time” column and strip the time from the “date” column.

The date dimension is handy as it has columns that break down a date into more detail so that a report doesn’t have to make conversions at runtime. This would be things like month name (short, long), month number, day name (short, long), day of week number, day of year number and Year (YY and YYYY). Indicators exist to indicate if the date is a weekday or a weekend.

Then we get into application specific columns like day of fiscal year number, fiscal month number and fiscal year long name. We have a column that defines the current day. A value of zero indicates the current day, -1 prior day, +1 next day, etc. This field is handy for the report developers to find all rows within the past 30 days.

There is a lot you can do with a date dimension. In Oracle, our date columns are indexed using a bitmap index type, so the overhead is quite low.

1 Like

One of the things you can do with a date column is partitioning. Use a formula (involving the modulus operator) to convert YYYYMM to some NN so that you can have more than one set of months. That makes all of the subsequent searches faster by instantly excluding all but the target month.

But certainly separate your time from your date fields.

What are you trying to achieve with your “table of time”? There may be other ways to do it.

1 Like

maybe there is already a time/date_dimension_table in one of your source-DBs.
sometimes there is a built-in table or some application/DB-function to create such a table within a range of dates.