Thursday 7 February 2008

Restating history or "What was the number on this date?"

Interesting article from Joy Mundy here about meeting the need to 'restate' history or do point-in-time analysis from a data warehouse. It involves joining on a business key which might raise a few eyebrows. Not sure how you'd go about doing this in an OLAP tool - a difficutly she highlights herself.

In a recent project I solved a similar problem by loading a snapshot of all relevant facts every day, and doing distinct counts in Analysis Services. A 'snapshot date' dimension allowed for analysis on any given date. This approach worked well because the data-sets were small (only amounting to 2000 rows a day) - Joy's might be a more elegant solution if you have millions of rows to contend with.

No comments: