Slowly Changing (SC) dimensions (type 2)

Some dimensions need to track historical data for a given natural key. This is done by creating multiple records in the dimensional tables with separate surrogate keys for the different versions of the same natural key.

Slowly Changing (SC) is based on Surrogate Keys (SK). 

Editing of dimension has 2 steps/parts:

  • Editing of the SC SK
  • Editing of the Dimension Items using the SC SK:

Following is described here:

  • defining a Slowly Changing column to use in dimension attributes.
  • loading Slowly Changing values from Data Sources.
  • defining and editing Wide and Parent/Child Dimensions.
  • creating Versioned Dimension.

Defining a Slowly Changing column

Slowly Changing Type 2 keeps track of history by adding SCDFromDate and SCDToDate columns. In Addition a definition must include a additional historical column. 

To define this:

  1. Select [Solution Management] in the Navigator and select [Dimension Key Settings]. Select the dimension column to use and tick of 'Use Shared Surrogate Key'.
  2. When you click on [Save], a process dialog pops up. Click [Save and Process New Key Settings] to create a new Surrogate Key definition. If there is tables using this SK, table views will be changed and SK values loaded.
  3. When definition is added. either select this from the Navigator or click on Surrogate Key Settings in the detail pane.
  4. Set it as Slowly Changing by 
    1. Check the 'Track Slowly Changing Attributes'.
    2. Set the' Fact Date Column' to a date column (e.g. default Transdate).
    3. Add Slowly Changing Historical Attribute columns (1 or more).
    4. Save the definition and click Reload in the process dialog that pops up.

E.g. definition of slowly changing OrganizationID:

PS6_SCD_Def.PNG

In this sample OrganizationID is set as Slowly Changing Surrogate Key. LegalEntityID is used as historical column.

On save, a process dialog is shown:

PS6_SCD_Part3_SalesEx.PNG

Click "Reload" to generate table with from and to date columns. This also includes a full reload of all surrogate key data.

If you browse the Surrogate Key table in the data warehouse database, the result has the following columns:

PS6_SCD_Browse.PNG

 

Loading Slowly Changing values from Data Sources

There are 3 ways to get SK SC data:

  • manually edit the surrogate key table
  • using dimension editing
  • load values from sources with a Wide Dimension Source Table. 

To load initial values, define a Wide Dimension Source table. The source can be a regular source for the dimension, or based on distinct values from a fact source table. 

E.g. load from Dimension Source:

 PS6_SCD_WideSourceReg.PNG

Here the Wide Dimension Source Table has mapped in Natural Key and Name. The Natural Key will get a Surrogate Key, but no dates from/to or historical attribute values.

To get more initial data we could add more columns to this dimension source if it was available from source. 

For Fact Source Tables, you map the natural key and the result source view will contain the SC key. The mapping is done with the available columns and the selected date column (e.g. Transdate), If a new item is found it will be added to the SK SC table.

For P/C Dimension Source Tables, the same applies. Natural key is used and the source view will contain surrogate keys.

 

Slowly Changing Parent/Child Dimensions

In order to use a Surrogate Key with/without Slowly Changing, it must be used as the Key (root) Id. E.g.

PS6_SCD_DimDef.PNG

Her a source is added. along with some properties.

When this is processed, you can edit this dimension.

Dimension Editing

From the Browse/Modify tab, dimension data can be edited. 

PS6_SCD_InitialEdit.PNG


For SC dimensions there are some more options:

  • From the edit mode drop down (upper left), there is an option for ‘Slowly Changing Definition’. From her the SC definition (and not dimension) can be edited with dates and historical attributes.
  • From the SC Mode drop down, the following modes can be selected:
    • Mode: Initial - shows all items. Less questions are asked when doing initial editing/construction of the dimension.
    • Mode: All - shows all items. 
    • Mode: By Date - uses date filter to show structure at given date (default, initial set to current date).
  • Click the Filter button to change the date filer.
  • The detail section for a Dimension Item, has read-only fields for SKEY, From/To Dates and Historical Attributes.
  • When clicking New button in details, a SC Edit dialog will show to add/select Surrogate Key for new item.
  • Tree mapping and linking is not available for SC editing.
  • Right clicking a node in the tree will display two SC options in the context menu. Add and Edit SC menu, will display a SC Edit dialog to edit Surrogate Keys.
  • In table mode, double click a surrogate key or the Item ID column for specific row to get the SC Edit dialog. Only the SC SK is edited in this dialog.
  • You can use drag/drop to move nodes. When dropping items to a new position, you are asked if you want to create new SC SK keys ( = Split), or only change (override) parent .
Editing is done on Dimension Items and on the SC SK item that it is based on. Editing of  the SC SK item is done from a dialog:
PS6_SCD_SKDlg.PNG

Editing of Surrogate Keys has following modes

  • New can either be used to pick existing SK from the drop down or to add a new SK.
  • Split creates a new SC item from the existing item using current date. Change date and save, and the existing items will get corresponding start/end dates.
  • Update to edit properties on existing item.

On New and Split, SCDFromDate and SCDEndDate are checked against existing items with same natural key. On Update, check the from/to consistency yourself.

 

Slowly Changing Wide Dimensions

In order to use a Surrogate Key with/without Slowly Changing, it must be used as the Key (root) Id. E.g.

PS6_SCD_DimDef_W.PNG

After a source is added. switch to Attributes-tab to edit the attributes.

PS6_SCD_DimDef_Attr_W.PNG

The Key attribute uses the SKey as Key, the natural key as Value and name/description as Name column.

Check the content under the Relations-tab and add a hierarchy (or more) under the Hierarchies tab.

When this is processed, you can edit this dimension.

Dimension Editing

From the Browse/Modify tab, dimension data can be edited. 

PS6_SCD_InitialEdit_W.PNG

For SC dimensions there are some more options:

  • From the edit mode drop down (upper left), there is an option for ‘Slowly Changing Definition’. From her the SC definition (and not dimension) can be edited with dates and historical attributes.
  • From the SC Mode drop down, the following modes can be selected:
    • Initial - shows all items. Less questions are asked when doing initial editing/construction of the dimension.
    • By Date - uses date filter to show structure at given date (default, initial set to current date).
  • Click the Filter button to change the date filer.
  • Adding of new records can be done when Modifications mode is selected. When you start to edit data, a SC Edit dialog will show to add/select Surrogate Key for new item.
  • Double click a row to get the SC Edit dialog. Only the SC SK is edited in this dialog.
The same SC SK Edit dialog is used as for P/C dimensions.


Versioned Dimensions

When a dimension is processed, all items are included by default. To take a snapshot at e.g. given date for use in Planning, Versioned Dimensions can be used.

From the Navigator, right click a Dimension (P/C or Wide), and select New Versioned Dimension. When added and if the original dimension is SC, a date field is shown:

PS6_SCD_VD.PNG

Right click in Navigator to browse result.

There are some limitations to a Versioned Dimension:

  • the dimension cannot be edited etc.
  • you cannot run Member Scan when used in Modules.