Practice Using Conformed Dimensions

In the last post we looked at the benefits of having conformed dimensions. If you wish, feel free to play with this yourself. I have two scripts available that will essentially add a fact_salary table to the star that we used for practice a few weeks ago.

1. If you created that star in your Oracle instance already and want to simply add the fact_salary table, use this script.


2. If you want to create the entire star in your Oracle instance, use this script.

The updated ERD is below (if the image is too small to read, click on it to have it enlarged).

See if you can write some SQL that will allow you to compare the measures between the two fact tables. If you are really into this, try adding a third or fourth fact table of your own (and more dimensions if needed) and doing some additional comparisons. A powerful use of a data warehouse involves comparing measures between multiple business processes to answer questions like…

1. How does his productivity growth compare to his salary growth?


2. How does this year’s increase in revenue (either in dollars or as a percentage) compare to last year’s increase in dollars allocated to new marketing initiatives?


3. How are the number of new work from home opportunities affecting employee turnover?

Using conformed dimensions can help to easily answer questions like these. What was once just a pile of data is now providing valuable information to decision makers.