cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
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

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request