Thursday, April 17, 2008

Slowly Changing Dimensions

I learn something new every time I go to work. Today, the concept of slowly changing dimensions. Typically when I created a database and then a website application to go on top of the database, I had the typical select, update, and insert. The update function would overwrite the current values with the new values. So what about history of the data? It's gone obviously.

Let's say we are looking to analyze the data in our database. The database is tracking sales from users. One user buys items, then moves to another state (and updates their account information) and buys more items. If you want to know the sales by state, all the sales for that user will be linked to their current location. Maybe this is what you want, however if you want to compare sales in each state over time, history will keep changing. Solution? Slowly changing dimensions: keep a history of these dimensions that change over time (typically slowly, hence the name slowly changing dimensions).

Time to adjust my databases, as Yuri once said: "The less you know, the better you sleep".