Why transactional databases don't make good data warehouses

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.

About Wes Trochlil

For a quarter century, Wes has worked in and with dozens of associations and membership organizations throughout the US, ranging in size from zero staff (all-volunteer) to over 700. In that time Wes has provided a range of consulting services, from general consulting on data management issues to full-scale, association-wide selection and implementation of association management systems.

Scroll to Top