How to use a slowly changing dimension in Sql Server Analysis Services

Question

In a data warehouse, I want to have a fact table which tracks certain metrics of a university application (average score on a standardized test, for example) and also the status of applications during different times of the year. For simpliciy, let's say a given application progresses through 3 states:

1. New
2. Being Assesssed
3. Assessed

and these states change over time.

I believe I want to use a slowly changing dimension here, but I can't figure out how to get to work properly.

Can someone give me an example of a fact table and dimension table which tracks two applications as they progress through these states?

I'm using SQL Server Analysis Services 2005.

The goal is to be able to do year on year analysis for the number of applications in each state.

Answer 1

Your question mentioned standardized test score and assessment status. Those would be two of your dimensions, along with the omnipresent time, of course. Ralph Kimball has a nice example of a good time dimension. If your test score dimension is SAT it'd have 2400-700 = 1700 rows, because you get 700 points for signing your name and there are three sections with perfect scores of 800 each. Your assessment dimension could be three rows, as you described.

So you'd have one record in your fact table for every time a score or assessment changed, with a key to the time dimension to tell you when the change occurred.

Answer 2

We've got a couple of articles on slowly changing dimensions over on SQLServerPedia:

http://sqlserverpedia.com/wiki/SSIS_-_Slowly_Changing_Dimension_Wizard

http://sqlserverpedia.com/wiki/Data_Warehousing_-_Slowly_Changing_Dimensions

Those may help bring you up to speed.

Answer 3

It sounds like a classic example of where you would use an accumulating snapshot type fact table more than slowly changing dimensions. Accumulating snapshots are the standard way of modeling business processes that have a defined lifecycle when you want to be able to analyze your progress of applications through the pipeline.

Google "accumulating snapshot" fact tables and you will find many good articles on their usage but here is one you may find helpful. http://blog.oaktonsoftware.com/2007/03/accumulating-snapshot-use-accumulating.html