Logical Tables » Logical view examples » Business case 3: Slowly changing dimensions

Business case 3: Slowly changing dimensions

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')a12
on (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

Report result

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