DATE OR DATE KEYS IN FACT TABLES

[arat]

I've read different opinions about the usage of dates in fact tables. If a date is NEVER used to filter and only reported, what is the purpose of adding it as a date key instead of a plain date type? Aren't we wasting the resources to get to the date dimension for the actual dates. I am told it doesn't cost much but at an enterprise level with hundreads of such queries.....is it worth.

[BoxesAndLines]

Make the date FK a smart key, e.g. 20100701, and have the best of both worlds. The biggest disadvantage to this is date math. You can no longer add/subtract date keys using a smart key.

[arat]

That's precisely the problem. The overarching question is should we even care about date dimension in the scenario described
Thanks for your reply.

[ngalemmo]

The purpose of a date dimension is to support interpretation of the date. The attributes in the date dimension provide business meaning, and most likely, multiple business meanings of the date. At minimum, a business has both a temporal interpretation and a fiscal interpretation. It can contain standarized labels for reporting, offsets for easily identifying yesterday, last month, same month last year and so on, without the user having to specify dates or the report doing date manipulations.

Different areas of the business will have different views of the calendar. A date dimension is critical in supporting such interpretations. Sure, you can embed code in reports, but such practice is labor intensive, complex and prone to error.

[arat]

thank you for participating in the discussion. However, my date or date key question is strictly concerned with the scenario I described in my original thread. This is all about the dates that are only reported and never filtered. I am in no way questioning the date dimension concept.

[ngalemmo]

Dimensions are for reporting as well as filtering... and neither is a descriminator for wither or not to use a date dimension.

For example, facts or type 2 dimensions may contain effective and expiration dates. It is common to leave them as dates/timestamps for the reason their ONLY use is to filter queries in a temporal context.

Performance or resource issues are NEVER valid reasons to use a dimension or not, use of a dimension must be based on the business purpose and use of the field. For example, there may be a set of business rules that dictate how a date should be presented in reports. Providing text fields in a date dimension formatted to the proper representation greatly simplifies enforcing (as well as changing) such rules.

[arat]

I am not sure if performance can be completely ignored. what if there is start/end date and some comments about the transaction that users want to report on? where should we store those comments. Do we create a seperate table or add them to the existing fact?

[ngalemmo]

Comments should go in a separate table.

If you are using CLOBs, some databases allow you to define them as part of the table but physically store them in a separate structure. This would be preferable over a separate table.

[arat]

I agree. Would that be an extended fact table or a dimension table itself. I am more inclined to placing them in an extended fact as not all reports might need them. Would you agree?

[chrisg2]

I am designing a Data warehouse in the financial sector and dealing with a large number of dates.
Examples: maturity date, interest date, last analysis date, last customer activity date, last deposit date, last interest date, open date, foreclosure date, last rate change date, next rate change date, note entered date, paid date, renewal date and the list goes on...

Should these date attributes/columns be stored as date key from the dimension table in FACT table or just plain date in the FACT table?

Your input is appreciated.

[BoxesAndLines]

Date key unless time is needed.

[chrisg2]

Thank you, however I am certain there is more to it than whether dealing with time or not.
As a matter of fact I found the core issue which is highlighted in design tip #61.

[BoxesAndLines]

How many dates, 15, 20, 30, 50? How are they used in reporting? Are they entry points into the fact or just drill to detail headers? If the date is an entry point, use the date dim, otherwise, place the date in the appropriate dimension. I would add 20 date dimension FK's before looking to push the dates to the dimensions and outrigger to date dimension. Also, any date in a dimension can join to the date dimension just using the date column. You just have to account for NULL in the date column.

[chrisg2]

The point is deciding whether they are facts or dimensions.
They are sometines stored as FACTS and other times as DIMENSIONS. If stored as fact it could be a FK from date dim or just a fact attribute and also if dimension it could be a FK from date dim (outrigger) or just a dimension attribute.
As pointed out it is well described in design tip #61. If you have not read it I suggest reviewing it.
I agree with the outrigger approach in certain and limited cases only since extensive use of outriggers severely impacts browsability performance.
So, I would never add 20 keys pointing to an outrigger dimension, instead store it as a dimension attribute. The same is stated in that design tip.

[Sponsored content]