YTD attributes in fact table

View previous topic View next topic Go down

YTD attributes in fact table

Post  dennisf on Sat Aug 22, 2009 7:25 pm

The DATAWAREHOUSE TOOLKIT book by Ralph Kimball recommends NOT physicalizing YTD type attributes but rather calculate them in the BI tool layer(Cognos, Business Objects etc). But it does not explain the reason for the recommendation. Can any body shed any light on this subject please?

dennisf

Posts : 9
Join date : 2009-04-14

View user profile

Back to top Go down

Re: YTD attributes in fact table

Post  rfsalas on Wed Aug 26, 2009 10:52 am

One thing I see it's the overhead the ETL will incur. You could potentially be updating too many rows each time the fact table is lodaded.
avatar
rfsalas

Posts : 3
Join date : 2009-02-04

View user profile

Back to top Go down

Re: YTD attributes in fact table

Post  ngalemmo on Wed Aug 26, 2009 11:58 am

Nothing to do with ETL. Besides, one could argue that creating a YTD summary once each night is better than doing it each time someone runs a YTD query.

The basic reason is that there is no clear definition of what YTD means. YTD to one user may be as of the close of business yesterday while to another user it may mean as of the end of last month. How do you build a summary of that short of a complete snapshot for every day? Also, when does the year begin? Is it a calendar year, fiscal year, sales season? They could be the same, but, then again, they may not.

What a YTD query boils down to is a date range summary. Given some arbitrary start and end date, sum the data. If you design things so they can query for ANY date range, then implementing YTD queries is a piece of cake... plus it gives users the flexibility to do any other date range summary. Something they cannot do with a YTD summary table. (This is a good example where less is more when designing a DW).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: YTD attributes in fact table

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