Databases Reference
In-Depth Information
A solo exercise
By now, you are well armed, so what about a little challenge?
We've already added the fields for Monthly Headcount Change. How would we go
about adding new fields for Quarterly Headcount Change and Annual Headcount
Change? What information can you get from the resulting data?
Dealing with slowly changing dimensions
A slow changing dimension is one whose values vary across undefined time periods,
that is, it can have different meanings depending on the time period context.
To illustrate the concept, consider the evolution of Joey, a support technician
employee in a given company, over a certain period of time. When Joey joined
the company, he had the Junior Support Technician position. Then, after one year,
he gets promoted to Senior Technician. And now, one year later, has become the
Support Manager.
Now, imagine you want to visualize the number of cases resolved by the entire
support team over a three-year period and find out how many of those cases were
resolved by junior technicians, how many were resolved by senior technicians, and
how many were resolved by the support manager. If, for reporting purposes, we take
Joey's current status in the company, all cases he has resolved in the last three years
will be logged as if they were resolved by the Support Manager, which is not quite
accurate. We should, instead, identify which positions Joey has had and the specific
time frame for each of them. Then, count the corresponding number of cases he
resolved on each support role and report it. Quite a task if we are dealing with tables
of a respectable size.
To tackle challenges like these, we can make use of the
IntervalMatch
script function.
We will adapt our example to the
Airline Operations
data we've been
working with, so make sure you have the
Carrier Decode.qvd
file in the
3.QVD\Source
folder.
