Slowly Changing Dimension

[intapiuser]

 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

[DrewGillow]

Thank you for the contribution. I have a different scenario that I am wondering how to handle with Sisense.

I have something that changes state over time.  

for example a  Request goes through several states:  Received, Scheduled, In process, etc. Complete.

There are thousands of Requests per day. They typically go through their status changes over several days.

Want to report on amount of Request by Status and compare the current state/volume to history and forecast trends.  

Granularity might be daily or hourly data.

How do we build the data model and then query it to get aggregate counts at a specific date/time and look at the values preceeding (or following) that point? For example How many requests were "In Process" by hour between 3/1 and 3/5?  How does that compare to prior month or year?

The data doesn't change much so it seems like a mistake to populate / snaphot the Status for each time period (day, hour).  

But without that I don't know how to allow a user to select a date using the built in Dashboard filter and get data using effective dated dimmension. 

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/clarifying-data-warehouse-de...