YTD attributes in fact table

[dennisf]

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?

[rfsalas]

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.

[ngalemmo]

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).

[Sponsored content]