DATE OR DATE KEYS IN FACT TABLES

View previous topic View next topic Go down

DATE OR DATE KEYS IN FACT TABLES

Post  arat on Tue Jul 13, 2010 12:26 am

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.


arat

Posts : 5
Join date : 2010-07-12

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  BoxesAndLines on Tue Jul 13, 2010 8:40 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  arat on Tue Jul 13, 2010 10:16 am

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

arat

Posts : 5
Join date : 2010-07-12

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  ngalemmo on Tue Jul 13, 2010 12:29 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  arat on Tue Jul 13, 2010 4:14 pm

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.

arat

Posts : 5
Join date : 2010-07-12

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  ngalemmo on Tue Jul 13, 2010 6:25 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  arat on Tue Jul 13, 2010 6:54 pm

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?

arat

Posts : 5
Join date : 2010-07-12

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  ngalemmo on Wed Jul 14, 2010 1:27 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  arat on Thu Jul 15, 2010 8:59 am

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?

arat

Posts : 5
Join date : 2010-07-12

View user profile

Back to top Go down

Date or Date Key in Fact table

Post  chrisg2 on Wed Apr 20, 2011 5:27 pm

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.

chrisg2

Posts : 6
Join date : 2011-04-18

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  BoxesAndLines on Wed Apr 20, 2011 6:12 pm

Date key unless time is needed.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Date or Date Key in Fact table

Post  chrisg2 on Wed Apr 20, 2011 6:46 pm

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.

chrisg2

Posts : 6
Join date : 2011-04-18

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  BoxesAndLines on Wed Apr 20, 2011 6:59 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  chrisg2 on Wed Apr 20, 2011 7:47 pm

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.

chrisg2

Posts : 6
Join date : 2011-04-18

View user profile

Back to top Go down

Re: DATE OR DATE KEYS IN FACT TABLES

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum