In the third installment of the series on slowly changing dimensions, we’re going to tackle the question of how to manage accumulated fact aggregates in a solution that uses SCD 2 dimensions. While SCD 2 dimensions solve a lot of problems with slowly changing dimensions, accumulated values can still make a mess of the aggregate data.
The problem
When working with fact data that is accumulated over time, such as sales or accounting, along with SCD 2 dimensions, the results of the accumulation will vary if the aggregate is grouped by a property in the SCD 2 dimension that changes.
An accounting example
Here’s an example of a fact table and an SCD 2 dimension for a car manufacturer.
- There are three different car models, C, D and E, classified as small, medium and large respectively.
- As of 2013, a new model, B, is added.
- At the same time, the C model changes class from small to medium.
- In march 2013, the B model also changes size classification from small to medium-sized.
Here is the example setup:
--- SCD 2 dimension for models: CREATE TABLE SCD.model ( [ID] int NOT NULL, modelCode varchar(4) NOT NULL, sizeClass varchar(20) NOT NULL, fromDate date NOT NULL, toDate date NULL, CONSTRAINT PK_models PRIMARY KEY CLUSTERED ([ID]) ); INSERT INTO SCD.model ([ID], modelCode, sizeClass, fromDate, toDate) VALUES (101, 'C', 'Small car', {d '1990-01-01'}, {d '2012-12-31'}), (102, 'D', 'Medium-sized car', {d '1990-01-01'}, {d '2012-12-31'}), (103, 'E', 'Large car', {d '1990-01-01'}, {d '2012-12-31'}), (104, 'B', 'Small car', {d '2013-01-01'}, {d '2013-02-28'}), (105, 'C', 'Medium-sized car', {d '2013-01-01'}, {d '2013-02-28'}), (106, 'D', 'Medium-sized car', {d '2013-01-01'}, {d '2013-02-28'}), (107, 'E', 'Large car', {d '2013-01-01'}, {d '2013-02-28'}), (108, 'B', 'Medium-sized car', {d '2013-03-01'}, NULL), (109, 'C', 'Medium-sized car', {d '2013-03-01'}, NULL), (110, 'D', 'Medium-sized car', {d '2013-03-01'}, NULL), (111, 'E', 'Large car', {d '2013-03-01'}, NULL); --- Fact table for model sales: CREATE TABLE SCD.modelSales ( [ID] int IDENTITY(1, 1) NOT NULL, [date] date NOT NULL, model_ID int NOT NULL, number int NOT NULL, salesAmount numeric(10, 2) NOT NULL, isManual bit NOT NULL, CONSTRAINT PK_modelSales PRIMARY KEY CLUSTERED ([ID]), CONSTRAINT FK_modelSales_model FOREIGN KEY (model_ID) REFERENCES SCD.model ([ID]) ); INSERT INTO SCD.modelSales ([date], model_ID, number, salesAmount, isManual) VALUES ({d '2012-11-01'}, 101, 120, 1200000, 0), ({d '2012-11-01'}, 102, 112, 1120000, 0), ({d '2012-11-01'}, 103, 97, 970000, 0), ({d '2012-12-01'}, 101, 130, 1320000, 0), ({d '2012-12-01'}, 102, 125, 1280000, 0), ({d '2012-12-01'}, 103, 118, 1210000, 0), ({d '2012-01-01'}, 104, 115, 1280000, 0), ({d '2012-01-01'}, 105, 62, 790000, 0), ({d '2012-01-01'}, 106, 122, 1370000, 0), ({d '2012-01-01'}, 107, 127, 1390000, 0), ({d '2012-02-01'}, 104, 99, 1120000, 0), ({d '2012-02-01'}, 105, 87, 980000, 0), ({d '2012-02-01'}, 106, 140, 1940000, 0), ({d '2012-02-01'}, 107, 121, 1810000, 0), ({d '2013-03-01'}, 108, 91, 1090000, 0), ({d '2013-03-01'}, 109, 86, 970000, 0), ({d '2013-03-01'}, 110, 148, 1990000, 0), ({d '2013-03-01'}, 111, 118, 1710000, 0);
With this data, let’s take a look at how many small, medium-sized and large cars have been sold as of february 2013:
SELECT dim.sizeClass, SUM(fact.number) AS number,
SUM(fact.salesAmount) AS salesAmount
FROM SCD.modelSales AS fact
INNER JOIN SCD.model AS dim ON
dim.[ID]=fact.model_ID
WHERE fact.[date]<={d '2013-02-01'}
GROUP BY dim.sizeClass
ORDER BY dim.sizeClass DESC;
By this way to calculate the totals, this aggregate will show the following sales:
- 464 small cars
- 648 medium-sized cars
- 463 large cars
But with the model classifications at the time (february 2013) of the different models, 250 of the 464 small cars are C models, which were classified as small cars at the time of sale, but are now considered medium-sized. So, by the reporting date, only 214 cars should be considered “small cars”.
SCD 1 vs SCD 2?
But if the classifications of the sold cars are determined by the most recent version, aren’t we just talking about a regular SCD 1 dimension? The answer is no, because there are also dimension versions after february 2013. What we’re looking for is how the dimension properties looked on february 2013, but we want to include all the sales up to that date, even if the classification may have been different at the time of the sale.
So, if we used SCD1, we wouldn’t know what the size categories were in february 2013, because it would have been overwritten by newer classifications.
A solution
To fix this problem, we’re can shift the sales balances and “rebrand” them to newer SCD 2 members (with the same natural key, but with a newer date). Here’s how it works:
By eliminating (subtracting) the 250 sold cars on january 1, then re-adding the same number of cars, we can shift the sales from dimension ID 101 to ID 105. This way, the accumulated sales for the C model are the same, but we’ve shifted the sales of model C cars from “small cars” to “mid-sized cars” on january 1.
This way, if you summarize sales as of december 31, you will see 250 small model C cars sold, but as of 2013, they are all medium-sized.
How to create the fact change records
To create these change records, we can use a query that performs the following steps:
- removes any previous change records,
- identify changes in the SCD 2 dimension,
- aggregate the accumulated amount for each dimension member up to the change date,
- create two new bookings on that date.
--- Delete existing change records DELETE FROM SCD.modelSales WHERE isManual=1; WITH versions ([ID], modelCode, sizeClass, ordinal, fromDate) AS ( --- Add version numbers to each version of the SCD 2 model dimension: SELECT [ID], modelCode, sizeClass, ROW_NUMBER() OVER ( PARTITION BY modelCode ORDER BY fromDate) AS ordinal, fromDate FROM SCD.model), versionChanges (fromID, toID, fromDate) AS ( --- Using the versioned view, isolate rows where dimension --- properties change from one version to the next: SELECT a.[ID] AS fromID, b.[ID] AS toID, b.fromDate FROM versions AS a INNER JOIN versions AS b ON a.modelCode=b.modelCode AND a.ordinal+1=b.ordinal WHERE a.sizeClass!=b.sizeClass), factRows ([date], fromID, toID, number, salesAmount) AS ( --- Calculate fact rows to be inserted: SELECT chg.fromDate AS [date], chg.fromID, chg.toID, SUM(fact.number) AS number, SUM(fact.salesAmount) AS salesAmount FROM versionChanges AS chg INNER JOIN SCD.modelSales AS fact ON chg.fromID=fact.model_ID AND chg.fromDate>=fact.[date] GROUP BY chg.fromDate, chg.fromID, chg.toID) --- Add two fact rows, one that eliminates the old balance... INSERT INTO SCD.modelSales ([date], model_ID, number, salesAmount, isManual) SELECT [date], fromID AS model_ID, 0-number, 0.0-salesAmount, 1 AS isManual FROM factRows UNION ALL --- ... and another one that adds the same balance to the new --- SCD 2 dimension key: SELECT [date], toID AS model_ID, number, salesAmount, 1 AS isManual FROM factRows;