Refer to Slowly Changing Dimensions and Dimensional Model schema for a context.
There are many aggregate queries done on dimensional tables, like number of
customers using a given product, or number of offices selling through third
party channels, or number of sales agents who have joined in last 12 months.
This is easy if the dimensional attribute over which the query is done is not
a slowly changing dimension. For example, the count of customers who were belonging
to a given location/state, or count of "inactive" customers in a given month,
quarter, date.
One way is:
Build Status_begin_date and status_end_date for a given attribute change in
the dimensional table. For example you can have the begin-date and end-date
for a customer status (active or inactive). If you run a query on the number
of customers who were active on May 31, 2006, the query will filter out all
the records where the "customer status" is "Active" and where the begin_date
<= May 31, 2006 and end_date is >= May 31. With the above logic, you have to
make sure that every time an attribute changes, a new record is created for
the gives dimension..The begin date has to be one day+ status_end_date of the
previous record. This is essentially the slowly changing dimension (method 2).
|
