A few weeks ago we looked at slowly changing dimensions. I've provided a script that will create a table called dim_employee_scd and fact_salary_scd. These are two slightly altered versions of the same tables in the star that we have been using (if you are just joining us, don't worry, they can stand alone too).
In this case, the dim_employee_scd acts as a slowly changing dimension. As always, don't read too much into the dates and/or salary amounts. This data is entirely fictitious. Run the script (here are instructions if needed), play with the data and see if you can answer these questions...
1.) How much did John Farmer receive in salary from Information Systems as opposed to Human Resources?
2.) As a percentage, how many more (or fewer) dollars did James Couch receive in salary when he moved from the first to the second and then from the second to the third position? Remember, even if the numbers decrease although it appears that he was promoted, that's ok. This is fictitious data and the point is to see there there was a change of some sort.
3.) Try to display salary dollars for active only positions, which may require that you make an assumption about the business rule.
We'll look at some solutions in the future.