SAP Data Warehouse Cloud - How to Create a Slowly Changing Dimension

[RichT]

*updated - new roadmap dates

My name is Richard Thomas, I have been an EIM (Enterprise Information Management) Consultant at NTT DATA Business Solutions for over 10 years and I have been using SAP DWC (Data Warehouse Cloud) for the last 12 months.

Recently, I was involved on a DWC project for which there was a requirement to use an SCD (Slowly Changing Dimension) to capture changes in data over time, and to provide historical reporting based on how the data would’ve appeared in the past.

Other SAP tools such as Data Services have specific transforms to handle this type of requirement but at present DWC has no such inbuilt functionality.  There are different ways you can go about solving this problem and meeting the requirements but which one to choose will depend on a number of factors.

To help, this blog is separated into two sections – the first section describes the three different methods and their pro’s and con’s, and the second section contains the details of each design pattern with step by step guidance on how to implement this.

Method 1 – Traditional Type 2 SCD

This method is the traditional Type 2 SCD approach that creates a new entry in the dimension table whenever a change has been identified.  Valid from and to dates will be updated on the old and new record to reflect the record history.

Pro’s

• Most development effort required

This method will require the most amount of development effort and complexity as it is necessary to compare the source data with the existing data in the dimension table to identify the record status as ether new, existing or deleted.  Once this has been determined, existing records must be evaluated to establish whether a change has occurred.  The entire dataset must then be recreated with the appropriate valid to/from dates and flags.

• Most storage-efficient method

As this method only captures when there is a change, it requires the least of amount storage.

• Greater reporting flexibility

With the use of valid from/to dates and deleted dates this method provides you with the most reporting flexibility

Con’s

• 2 Dataflows are required -Time staggered

As historical entries require updating, and currently there is only the method to append\truncate when using a dataflow, the entire dataset will need to be recreated using truncate.  Due to the table being truncated, it is necessary to use a temporary table to load the data into first, before truncating the main dimension table.  This means that the use of 2 dataflows is required.  Currently it is not possible to link Dataflow tasks to run in sequence so they will need to be scheduled with a sufficient time delay between each job.

Method 2 – Daily Snapshot (Append)

This method captures a snapshot of the dimension on a daily basis by adding a Snapshot Date to the Dimension.  The data is appended to the Dimension table; so if the load was run multiple times on a day, only the first run would be captured.

Pro’s

• Minimal development effort required

This method only requires one extra column to be populated with the snapshot/load date.

• Only 1 Dataflow required

As only one table is required for this method there is only a need for one dataflow

 

Con’s 

• Data will be duplicated / Dataset size needs to be considered

Creating Snapshots will mean duplication of data within the Dimension table so we also need to consider the size of the dataset as the table could become extremely large if being appended every day.

• Snapshot date usage

You need to consider how to use the Snapshot date within your data model to ensure you don’t get duplicated records when joining to the dimension.

 

Method 3 – Daily Snapshot (Truncate)

This method is the same as Solution 2 as it captures a daily snapshot of the dimension, the main difference being that the entire dimension is recreated resulting in the most recent values being retained.

Pro’s

• 2 Dataflows are required – Time Staggered

As the dataset is truncated but also used as a source, this method requires the use of two dataflows that will need to be scheduled with a time delay between them.

 

Conclusion

Hopefully these three methods will help you if you have a similar modelling requirement in Data Warehouse Cloud.  Each has its own advantages and disadvantages so you will obviously need to decide which is most appropriate for you.

Method 1 (Traditional Type 2 SCD) was the chosen method to implement for the DWC project I was working on due to the large volume of data and low volume data changes.

There are a number of upcoming items in the Data Warehouse Cloud Roadmap which may well help achieve the same objective but it’s not yet clear when they will arrive but please monitor as they might be beneficial.

 

Expected - Q2 2022

Roadmap item - Dependent serial scheduling tasks

Anticipated Impact - Expected to provide the ability to chain task\dataflows together

URL - https://roadmaps.sap.com/board?PRODUCT=73555000100800002141&range=FIRST-LAST#;INNO=000D3A4855C81EDB9...

 

Expected - 2023

Roadmap item - Time-dependency support added to dimensions

Anticipated Impact - Expected to provide the support for a Type 2 SCD with Valid From\To

URL - https://roadmaps.sap.com/board?PRODUCT=73555000100800002141&range=FIRST-LAST#;INNO=000D3ABE772D1EDBB...

 

If you got this far then thanks for reading my first blog and I hope you have found it useful, please post any questions, comments and feedback that you may have below and don't forget to follow me!

If you are interested in implementing one of the methods then please take a look at the Solution Walkthroughs below for a more detailed step-by-step guide and I will try to answer any questions or comments.

 

Solution Walkthroughs

This section details the individual steps needed to implement each of the solutions

1      Solution 1 – Traditional Type 2 SCD

The first solution is a traditional Type 2 Slowly Changing Dimension where any change in a record will create a new entry and the valid from\to dates updated accordingly.

Below is a high-level overview of all the objects used in the solution with a short description of the object usage.

In the Data Builder there are five different object types available and currently no way to distinguish if a View is Graphical\SQL once created.  Applying a naming convention such as the one below helps aid clarity.

1.1      Naming Convention

• Tables – Prefixed with “TB_”


• Views (Graphical) – Prefixed with “VG_”


• Views (SQL) – Prefixed with “VS_”


• Dataflow – “DF_”


• Entity Relationship Model – “ER_”

 

	Object Name	Object Type	Description
	“TB_Source_CSV”	Table	Source File
	“TB_Dim_SCD_Temp”	Table	Table to temporarily hold Dimension Data
	“TB_Dim_SCD”	Table	Table to store Dimension Data
	“VG_Dim_SCD_1”	View	Compares source and Dimension data to Identify if the record is new, updated, deleted or no change
	“VG_Dim_SCD_2”	View	Recreate dimension with valid to\from dates and is current\deleted flag
	“DF_Dim_Temp_Load”	Dataflow	Loads data from “VG_Dim_SCD_2” to “TB_Dim_SCD_Temp”
	DF_Dim_Load	Dataflow	Loads data from “TB_Dim_SCD_Temp” to “TB_Dim_SCD”

 

1.2      Step 1 - Create Source

Create a Source Table\View to use as the source for the Dimension (“TB_Source_CSV”).

1.3      Step 2 - Create Dimension tables

Create two tables (“TB_Dim_SCD_Temp” and “TB_Dim_SCD”).  These tables we be identical and will match the source plus five additional fields:

• Valid From


• Valid To


• Is Current


• Is Deleted


• Deleted Date

 

1.4      Step 3 - Create VG_Dim_SCD_1  - Identify Change Type

Create a new Graphical View to identify data changes

Add the Source Table\View twice (“TB_Source_CSV”) and alias them as:

• Source Data


• Source Current

Now add the table “TB_Dim_SCD” three Times and alias them as:

• Dim Current


• Dim Not Current


• Dim Deleted

Add a Filter to Dim Current that checks:

• Is_Current = 1 and Is_Deleted = 0

Add a Filter to Dim Deleted that checks:

• Is_Current = 1 and Is_Deleted = 0

Add a Filter to Dim Not Current that checks:

• Is_Current = 0 or Is_Deleted = 1

Left Join Source Data and Dim Current ensuring the Source Data is the left\driving table and the tables are joined on the primary key.

 

A Projection should be automatically added after the join, if it is not, add a projection.

Enable all fields in the Projection.

Rename all duplicated fields from Dim_Current

 

Add a calculated column.

Add a new field called SCD (String (10)).

Create a Case Statement:

If key Dim field is null Then New

If any Non-key fields has changed value Then Updated

Else No Change

 

Go back to Dim Not Current.

Add a Calculated column after the filter.

Add a new field called SCD (String (10)).

Hardcode the value “No Change”.

Left Join Source Deleted and Dim Deleted ensuring the Dim Deleted is the left\driving table and the tables ae joined on the primary key.

 

A Projection should be automatically added after the join, if it is not, add a projection.

Disable all fields from Source Deleted except the Key field.

Rename the Key field from Source Deleted.

Add a filter that returns where the Source Deleted Key field is null.

Add a calculated column.

Add a new field called SCD (String (10)).

Hardcode the value No Change.

The three datasets are now ready to be combined with a Union operator.

Start with Current and Not Current then Add Deleted.

Ensure all fields have been correctly mapped in the union.

The first view is now completed and it can be identified if the Dimension data is new, changed, deleted or unchanged.

1.5      Step 4 - Create VG_Dim_SCD_2 - Recreate Dimension

Create a New Graphical View

Add “VG_Dim_SCD_1” 5 times to the view and alias as followed:

• New


• Updated Historic


• Updated New


• Deleted


• No Change

Add a filter to each flow using SCD based on the alias name:

• New = New


• Updated Historic = Updated


• Updated New = Updated


• Deleted = Deleted


• No Change = No Change

Add a Calculated Column transform to each flow and derive the values to be used for:

• Valid From


• Valid To


• Is Current


• Is Deleted


• Deleted Date

Use the values below as the derived values for each alias

New

• Valid From = '1900-01-01' - As your history only starts from the point that the load starts we use 1900 instead of the run date to cater for historic reporting


• Valid To = '9999-12-31'


• Is Current = 1


• Is Deleted = 0


• Deleted Date = NULL

No Change

• Valid From = Dim Valid From


• Valid To = Dim Valid To


• Is Current = Dim Is Current


• Is Deleted = Dim Is Deleted


• Deleted Date = Dim Deleted Date

Deleted

• Valid From = Dim Valid From


• Valid To = Dim Valid To


• Is Current = Dim Is Current


• Is Deleted = 1


• Deleted Date = Current\Load Date

Updated New

• Valid From = Current\Load Date


• Valid To = '9999-12-31'


• Is Current = 1


• Is Deleted = 0


• Deleted Date = NULL

Updated Historic

• Valid From = Dim Valid From


• Valid To = Current\Load Date


• Is Current = 0


• Is Deleted = 0


• Deleted Date = NULL

Add a Projection to each flow.

At this point it is important to ensure the correct fields are being used for all attributes.

Exclude all columns.

Enable Valid From, Valid To, Is Current, Is Deleted and Deleted Date

New - Enable all Fields from Source Data

No Change - Enable all Fields from Source Data\Dimension Data

Deleted - Enable all Fields from Dimension Data

Updated New - Enable all Fields from Source Data

Updated Historic - Enable all Fields from Dimension Data

Union all datasets

 

1.6      Step 5 - Create DF_Dim_Temp_Load - Load into Temp Table

The reason for using Dataflows\Tables is because the target table is also used as the source and the target is truncated when loaded.

When a dataflow is executed, the first step is to truncate the target table, this results in all records falling into the SCD category new and all history being lost.

To avoid this issue, first load the data into a temporary table.

Use “VG_Dim_SCD_2” as a Source and “TB_Dim_SCD_Temp” as the target set the table-loading mode to TRUNCATE

  1.7      Step 6 - Create DF_Dim_Load - Load from Temp Table to Dimension

Use ”TB_Dim_SCD_Temp” as a Source and “TB_Dim_SCD” as the target set the table-loading mode to TRUNCATE

1.8      Step 7 - Schedule Dataflows

The Data Integration Monitor doesn’t currently have the ability to link/chain Dataflow executions, you only have the ability to schedule based on time\frequency.

Ensure that you give ”"DF_Dim_Temp_Load” enough time to complete the execution prior to the “DF_Dim_Load” schedule starting.

 

2      Solution 2 – Daily Snapshot (Append)

Instead of comparing the data, this solution takes a daily snapshot of the entire dataset and appends this to the Dimension table with a Snapshot Date.

2.1      Naming Convention

• Tables – Prefixed with “TB_”


• Views (Graphical) – Prefixed with “VG_”


• Views (SQL) – Prefixed with “VS_”


• Dataflow – “DF_”


• Entity Relationship Model – “ER_”

 

	Object Name	Object Type	Description
	“TB_Source_CSV”	Table	Source File
	“TB_Dim_SCD”	Table	Table to store Dimension Data
	“VG_Dim_SCD_1”	View	Compares current Snapshot Date with Max Dim Snapshot Date and filter data
	DF_Dim_Load	Dataflow	Append data from “VG_Dim_SCD_1” to “TB_Dim_SCD”

2.2      Step 1 - Create Source

Create a Source Table\View that you would like to use as your source for the Dimension “TB_Source_CSV”

2.3      Step 2 - Create Dimension table

Create the table “TB_Dim_SCD” this will be identical to the source plus one additional field

• Snapshot Date

2.4      Step 3 – Create VG_Dim_SCD_1  – Check Dimension Max Snapshot Date

Create a new Graphical View

Add “TB_Source_CSV” to the design pane add alias as Source

Add “TB_Dim_SCD” to the design pane add alias as Dim

The aggregation function only aggregates on Integer data types, so we will create an integer representation of the date

Add a calculated column transform to both flows and add the following fields

• Source

• 
  
  
  
  • Snapshot Date – Integer – CURRENT_DATE())
  
  
  • Snapshot Date INT – Integer - DAYS_BETWEEN('1900-01-01',CURRENT_DATE())
  
  
  
  

• Dim

• 
  
  
  
  • Snapshot Date INT – Integer - DAYS_BETWEEN('1900-01-01', "Snapshot Date")
  
  
  
  

Add a Projection to the Dim flow

Disable all fields excluding Snapshot Date INT

Add an aggregation transform

Set Snapshot Date INT to MAX aggregation type

Join the two flows together

Set the Join Type to Cross Join and remove any mappings

 

In the projection rename Snapshot Date INT (Dim) to Max Snapshot Date and ensure all fields are enabled

Add a calculated column transform

Update the formula in Max Snapshot date to replace null with 0 to cater for loading into an empty Dimension table

Add a filter where Snapshot Date INT > Max Snapshot Date INT

 

2.5      Step 4 – Create\Schedule Dataflow

Create a dataflow to load the data from your view to the Dimension, ensure the table load type is set to APPEND and schedule the Dataflow.

3      Solution 3 – Daily Snapshot (Truncate)

This solution is a replica of Solution 2, the difference here is that rather than checking the Max Snapshot Date in the Dimension, all the data is extracted from the Dimension except where the Snapshot Date equals the Load Date.

This allows the load to run multiple times in a period and the most recent run in the period will be the version held in the Dimension.

As the target table is truncated, two Dataflows and a temporary table will be required in this version of the solution.

 

3.1      Naming Convention

	Object Name	Object Type	Description
	“TB_Source_CSV”	Table	Source File
	“TB_Dim_SCD_Temp”	Table	Table to Temporarily store Dimension Data
	“TB_Dim_SCD”	Table	Table to store Dimension Data
	“VG_Dim_SCD_1”	View	Retrieves all records from “TB_Dim_SCD” where Snapshot Date < Run Date and combines with “TB_Source_CSV”
	“DF_Dim_Temp_Load”	Dataflow	Truncate and load data from “VG_Dim_SCD_1” into “TB_Dim_SCD_Temp”
	DF_Dim_Load	Dataflow	Truncate and load data from “TB_Dim_SCD_Temp” to “TB_Dim_SCD”

 

3.2      Step 1 - Create Source

Create a Source Table\View that you would like to use as your source for the Dimension “TB_Source_CSV”

3.3      Step 2 - Create Dimension tables

Create the tables “TB_Dim_SCD” and “TB_Dim_SCD_Temp”, they will be identical to the source plus one additional field

• Snapshot Date

3.4      Step 3 - Create VG_Dim_SCD_1 – Combine Historic and Current Dimension

Create a new Graphical View

Add “TB_Source_CSV” to the design pane add alias as Source

Add “TB_Dim_SCD” to the design pane add alias as Dim

Add a calculated column transform to the source flow and add the following fields

• Source

• 
  
  
  
  • Snapshot Date – Integer – CURRENT_DATE())
  
  
  
  

Add a Filter to the Dim flow where Snapshot Date < CURRENT_DATE())

UNION the two flows together

Set the Join Type to Cross Join and remove any mappings

3.5      Step 4 – Create DF_Dim_Temp_Load

Create a dataflow to load the data from your view to the temporary table, ensure the table load type is set to TRUNCATE.

3.6      Step 5 – Create DF_Dim_Load

Create a dataflow to load the data from your temporary table to the Dimension table, ensure the table load type is set to TRUNCATE.

3.7      Step 6 - Schedule Dataflows

Ensure that you give ”DF_Dim_Temp_Load” enough time to complete the execution prior to the “DF_Dim_Load” schedule starting.