cancel
Showing results for
Did you mean:

# Slowly Changing Dimension

Community Team Member

## Analytical Need

This dimension table keeps every change that has been made. The user wishes to see historical data per the dimension and not just the current state.

Example: Transactions per organizational hierarchies, price list, historical currency rates

## Modeling Challenge

We need to associate each record of the fact to the correct (within the time frame or specific date) record in the dimension – this is our connection between the fact & the dim.

We will display 2 example cases : Organizational Hierarchy & Currency Conversion over time

## Solution

Example 1 : Organizational Hierarchy

We have a fact table with transactions per date and a dim table that records every change in the organizational hierarchy.

Logic:

We'll add a rank to each of the dimension's records - running serial number. We'll then create a custom query for the fact, where we add the serial to each record according to the date of the transaction that corresponds to the time frame in the dimension.

SQL :

Step 1 : Add a serial number to the dim table as a custom column:

rankasc([ID])

SELECT f.ID, f.Value, d.Serial, f.Date FROM

[Fact] f LEFT JOIN [Dim] d

ON f.Date >= d.[start date] AND (f.Date <=d.[end date] OR IsNull(d.[end date]))

AND f.ID = d.ID

Step 3: Connect the dim & fact table based on the Serial field. In case you have a dimension with a record for each date (and not a range) the connecting key will need to be a concatenation of Date & ID (instead of creating the serial).

Example 2 : Currency Conversion

We need to join the fact table with the dimension containing of date range – this way we'll receive a table containing a value for the date the transaction was made.

We'll get a value in \$s for each day (conversion table is weekly).

SELECT Distinct O.*, C.CurrencyRate,

O.SubTotal_OriginalCurrency*C.CurrencyRate

SubTotal_BaseCurrency

FROM SalesOrderHeader O LEFT JOIN WeeklyCurrencyRates C

ON O.OriginalCurrency=C.FromCurrencyCode AND

O.BaseCurrency=C.ToCurrencyCode AND O.OrderDate>=C.StartDate AND

(O.OrderDate<=C.EndDate OR IsNull(C.EndDate))

- 1.7 MB - Slowly Changing Dimension.ecdata

- 25 KB - Slowly Changing Dimension.dash

Version history
Last update:
‎10-17-2021 06:45 AM
Updated by:
Contributors
Community Toolbox

Developers Group:

Product Feedback Forum: