Very often I’m asked by my clients: “Why can’t I get a report out of my database comparing year-to-year data on membership?” And the simple fact is, transactional databases do not make good data warehouses.
Transactional databases, by their nature, are dealing with dynamic data. This means the data is changing frequently. Whether it’s something as simple as an address change, or more complex data like purchases, a transactional database is meant to be changing all the time.
And that’s why they don’t work as a data warehouse. A data warehouse is meant to hold a very discrete set of data and the data itself does not change daily.
Take this example: Say I want to compare my current event registration against last year’s registration, looking at how members are registering this year vs. last. If I use my transactional system to check this, I may not get accurate numbers because individuals who were members last year may have since dropped membership, thus throwing off my numbers.
With a data warehouse, a snapshot of that data would be taken at the conclusion of the event and then would be available for comparison at later dates, with no corresponding risk of changed data altering the results. This is why many of my clients take “membership snapshots” every month (or every year, depending on membership period) and store it in a separate area. These snapshots won’t change because they’re no longer in a dynamic transactional database, and thus will allow for more accurate data comparisons over time.
So if you’re trying to do data comparisons over time, you may want to consider developing a simple data warehouse to address this.
Update: Check out Rob Kaighn at TMA Resources for a little more on this subject.