Friday, April 20, 2012

Slowly Changing Dimensions Solutions To Practice

In the last post I provided a script that allowed you to practice interacting with slowly changing dimensions.  The answers to the three questions that I proposed are...


1.) John Farmer earned $62,000 in salary (wages, benefits, and bonus) from Information Systems and $12,200 from Human Resources.  A SQL statement like this (although variations of it will answer the question as well) will answer this question...



select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid),
sum(b.actual_benefits_paid),
sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.employee_number = 2546
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

2.) James Couch's salary dropped 36.6% from the first to the second position.  It dropped 36.4% from the second to the third position.  From a business perspective this seems odd, but the point is to see there there was a change of some sort.  A SQL statement like this will answer this question...

select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid) + sum(b.actual_benefits_paid) + sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.employee_number = 2547
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

3.) Assuming that a current appointment is defined as a record with a null value for the appointment_end_date (in reality, a developer would need to verify this with a subject matter expert) the following SQL statement will display salary dollars for active positions only...

select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid),
sum(b.actual_benefits_paid),
sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.appointment_end_date is null
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

As the dimensions (descriptors of measures) move through time, a type 2 slowly changing dimension allows the measures to be grouped by each of those changes.  For example, the salary dollars can not only be displayed per person.  As the person progresses through the organization by moving to new positions, the salary dollars can be grouped by each stop along the way. 

From a technical perspective this amounts to the dimension's natural key expanding from the employee number to the employee number, position title, department, and appointment begin date (or whatever defines an appointment in your organization).  From a business perspective this example allows an analyst to view an individual's effect on the organization as she progresses through that organization.

Image: digitalart / FreeDigitalPhotos.net

No comments:

Post a Comment