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
