Slowly changing dimensions in MSTR
Slowly changing dimensions (SCDs) are a common characteristic in many business intelligence environments. Usually, dimensional hierarchies are presented as independent of time. For example, a company may annually reorganize their sales organization or recast their product hierarchy for each retail season. “Slowly” typically means after several months or even years. Indeed, if dimensional relationships change more frequently, it may be better to model separate dimensions.
SCDs are well documented in the data warehousing literature. Ralph Kimball has been particularly influential in describing dimensional modeling techniques for SCDs (see The Data Warehouse Toolkit, for instance). Kimball has further coined different distinctions among ways to handle SCDs in a dimensional model. For example, a Type I SCD presents only the current view of a dimensional relationship, a Type II SCD preserves the history of a dimensional relationship, and so forth.
The discussion below is based on an example sales organization that changes slowly in time as the territories are reorganized; for example, sales representatives switch districts in time.
As-is vs. as-was analysis
One of the capabilities available with slowly changing dimensions is the ability to perform either “as-is” analysis or “as-was” analysis:
| • | “As-is” analysis presents a current view of the slowly changing relationships. For example, you can display sales by District according to the way Districts are organized today. |
| • | “As-was” analysis presents a historical view of the slowly changing relationships. For example, you can display sales by District according to the way Districts were organized at the time the sales transactions occurred. |
The techniques described here provide the flexibility to perform either type of analysis. They also provide you an easy way to specify which type of analysis you would like to perform.
Example 1: Compound key with Effective Date and End Date
One way to physically store an SCD is to employ Effective Date and End Date columns that capture the period of time during which each element relationship existed. In the example below, Sales Rep Jones moved from District 37 to District 39 on 1/1/2004, and Kelly moved from District 38 to 39 on 7/1/2004.
For information on compound keys, please refer to Lookup tables: Attribute storage.
LU_SALES_REP
Sales_Rep_ID
|
Sales_Rep_Name
|
District_ID
|
Eff_Dt
|
End_Dt
|
1
|
Jones
|
37
|
1/1/1900
|
12/31/2003
|
2
|
Smith
|
37
|
1/1/1900
|
12/31/2099
|
3
|
Kelly
|
38
|
1/1/1900
|
6/30/2004
|
4
|
Madison
|
38
|
1/1/1900
|
12/31/2099
|
1
|
Jones
|
39
|
1/1/2004
|
12/31/2099
|
3
|
Kelly
|
39
|
7/1/2004
|
12/31/2099
|
When using this type of dimensional lookup table, the fact table must include a date field, such as a transaction date.
FACT_TABLE
Sales_Rep_ID
|
Trans_Dt
|
Sales
|
1
|
9/1/2003
|
100
|
2
|
9/10/2003
|
200
|
3
|
9/15/2003
|
150
|
1
|
3/1/2004
|
200
|
2
|
3/10/2004
|
250
|
3
|
3/15/2004
|
300
|
2
|
9/5/2004
|
125
|
3
|
9/15/2004
|
275
|
4
|
9/20/2004
|
150
|
To specify the MicroStrategy schema
| 1 | Create a logical view to represent just the current District-Sales Rep relationships. |
LVW_CURRENT_ORG
select Sales_Rep_ID, District_ID
from LU_SALES_REP
where End_Dt = '12/31/2099'| 2 | Create another logical view that performs the “as-was” join between the lookup table and fact table, resulting in a fact view at the District level. |
The resulting view is an “as-was” or historical view, which captures the Sales Rep-District relationships that existed at the time the transactions occurred.
LVW_HIST_DISTRICT_SALES
select District_ID, Trans_Dt, sum(sales)
sales
from LU_SALES_REP L
join FACT_TABLE F
on(L.Sales_Rep_ID = F.Sales_Rep_ID)
where F.Trans_Dt between L.Eff_Dt and
L.End_Dt
group by District_ID, Trans_Dt| 3 | Create a table alias LU_CURRENT_DISTRICT for LU_DISTRICT. |
| 4 | Define the following attributes: |
| • | Sales Rep: |
| — | @ID = sales_rep_id; @Desc = sales_rep_name |
| — | Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG, FACT_TABLE |
| • | Current District: |
| — | @ID = district_id; @Desc = district_name |
| — | Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG |
| — | Child: Sales Rep |
| • | Historical District: |
| — | @ID = district_id; @Desc = district_name |
| — | Tables: LU_DISTRICT (lookup), LU_SALES_REP, LVW_HIST_DISTRICT_SALES |
| — | Child: Sales Rep |
| • | Date: |
| — | @ID = date_id, trans_dt |
| — | Tables: LU_TIME (lookup) , FACT_TABLE, LVW_HIST_DISTRICT_SALES |
| • | Month: |
| — | @ID = MONTH_ID |
| — | Tables: LU_TIME (lookup) |
| 5 | Define the Sales fact: |
| • | Expression: sales |
| • | Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALES |
| 6 | Define the metric as required: |
| • | Sales: SUM(sales) |
The result of this is a logical schema that looks like the following:
As-was analysis
Specify the “as-was” analysis by using the Historical District attribute on reports:
| • | Report definition: Historical District, Month, Sales |
| • | Resulting SQL |
Select a11.District_ID District_ID,
max(a13.District_Name) District_Name,
a12.Month_ID Month_ID,
sum(a11.SALES) WJXBFS1
From (select District_ID, Trans_dt,sum(sales) sales
from LU_SALES_REP L
join FACT_TABLE F
on (L.Sales_rep_ID = F.Sales_rep_ID)
where F.trans_dt between L.EFF_DT and
L.END_DT
group by District_ID, Trans_dt)
a11
join LU_TIME a12
on (a11.Trans_dt = a12.Date_ID)
join LU_DISTRICT a13
on (a11.District_ID = a13.District_ID)
group by a11.Distrcit_ID,
a12.Month_ID| • | Report results |
As-is analysis
Specify the “as-is” analysis by using the Current District attribute on reports:
| • | Report definition: Current District, Month, Sales |
| • | Resulting SQL |
select a12.District_ID District_ID,
max (a14.District_Name) District_Name,
a13.Month_ID Month_ID,
sum(a11.SALES) WJXBFS1
from FACT_TABLE a11
join (select Sales_rep_ID, District_ID
from LU_SALES_REP
where END_DT = '12/31/2099')a12on (a11.Sales_Rep_ID =
a12.Sales_Rep_ID)
join LU_TIME a13
on (a11.Trans_dt = a13.Date_ID)
join LU_DISTRICT a14
on (a12.District_ID = a14.District_ID)
group by a12.District_ID,
a13.Month_ID| • | |
Example 2: New surrogate key for each changing element
A more flexible way to physically store a SCD is to employ surrogate keys and introduce new rows in the dimension table whenever a dimensional relationship changes. Another common characteristic is to include an indicator field that identifies the current relationship records. An example set of records is shown below.
LU_SALES_REP
Sales_Rep_CD
|
Sales_Rep_ID
|
Sales_Rep_Name
|
District_ID
|
Current_Flag
|
1
|
1
|
Jones
|
37
|
0
|
2
|
2
|
Smith
|
37
|
1
|
3
|
3
|
Kelly
|
38
|
0
|
4
|
4
|
Madison
|
38
|
1
|
5
|
1
|
Jones
|
39
|
1
|
6
|
3
|
Kelly
|
39
|
1
|
When using this type of dimensional lookup table, the fact table must also include the surrogate key. A transaction date field may or may not exist.
FACT_TABLE
Sale-Rep_CD
|
Sale
|
1
|
100
|
2
|
200
|
3
|
150
|
5
|
200
|
2
|
250
|
3
|
300
|
2
|
125
|
6
|
275
|
4
|
150
|
Specifying the MicroStrategy schema
| 1 | Create a logical view to represent just the current District-Sales Rep relationship. |
LVW_CURRENT_ORG
select Sales_rep_ID, District_ID
from LU_SALES_REP
where Current_flag = 1| 2 | Create a table alias LU_CURRENT_DISTRICT for LU_DISTRICT. |
| 3 | Define the following attributes: |
| • | Sales Rep Surrogate: |
| — | @ID = sales_rep_cd |
| — | Tables: LU_SALES_REP (lookup), FACT_TABLE |
| • | Sales Rep: |
| — | @ID = sales_rep_id; @Desc = sales_rep_name |
| — | Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG |
| — | Child: Sales Rep Surrogate |
| • | Current District: |
| — | @ID = district_id; @Desc = district_name |
| — | Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG |
| — | Child: Sales Rep |
| • | Historical District: |
| — | @ID = district_id; @Desc = district_name |
| — | Tables: LU_DISTRICT (lookup), LU_SALES_REP |
| — | Child: Sales Rep |
| • | Date: |
| — | @ID = date_id, trans_dt |
| — | Tables: LU_TIME (lookup), FACT_TABLE |
| • | Month: |
| — | @ID = MONTH_ID |
| — | Tables: LU_TIME (lookup) |
| — | Child: Date |
| 4 | Define the Sales fact: |
| • | Expression: sales |
| • | Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALES |
| 5 | Define the metric as required: |
| • | Sales: SUM(sales) |
The result is a logical schema as follows:
As-was analysis
Specify the “as-was” analysis by using the Historical District attribute on reports:
| • | Report definition: Historical District, Month, Sales |
| • | Resulting SQL |
select a12.District_ID District_ID,
max(a14.Distrcit_Name) Distrcit_Name,
a13.Month_ID Month_ID,
sum(a11.SALES) WJXBFS1
from FACT_TABLE a11
join LU_SALES_REP a12
on (a11.Sales_Rep_CD =
a12.Sales_Rep_CD)
join LU_TIME a13
on (a11.Trans_dt = a13.Date_ID)
join LU_DISTRICT a14
on (a12.District_ID =
a14.District_ID)
group by a12.District_ID,
a13.Month_ID| • | Report results |
As-is analysis
Specify the “as-is” analysis by using the Current District attribute on reports:
| • | Report definition: Current District, Month, Sales |
| • | Resulting SQL: |
select a13.District_ID District_ID,
max(a15.Distrcit_Name) District_Name,
a14.Month_ID Month_ID,
sum(a11.SALES) WJXBFS1
from FACT_TABLE a11
join LU_SALES_REP a12
on (a11.Sales_Rep_CD =
a12.Sales_Rep_CD)
join (select Sales_rep_ID, District_ID
from LU_SALES_REP
where current_flag = 1)
a13
on (a12.Sales_Rep_ID =
a13.Sales_Rep_ID)
join LU_TIME a14
on (a11.Trans_dt = a14.Date_ID)
join LU_DISTRICT a15
on (a13.District_ID =
a15.District_ID)
group by a13.District_ID,
a14.Month_ID| • | Report result |
Comments
Post a Comment