Welcome Guest! Log in


The Slowly Changing Dimensions (SCD) are used in DataWarehouses to track the changes in the databases.

Tracking the updates of customers' addresses to keep a trace of it, for example.

With SCD there are different ways of doing it:

  • Insert a new row every time the address is changed (historize)
  • Only update the row
  • Use a start date, end date, etc.

For further information on what is SCD, there are good articles you can find on the internet.

In Stambia, SCD can be used with the Oracle Metadata and Template. We will see in this article how to do that with some examples.

 

Prerequisites

1) The oracle templates are needed, and can be found in the specific templates here: 

http://www.stambia.org/download/data-integration/stambia-di-templates

2) An oracle metadata in which creation of tables and insertion of data is allowed.

 

Goal

We will use SCD to track the changes of the cities' names of the T_ADDRESS demo tables.

 

Creating the dimension table

The first step consists of the creation of a new table in oracle, the dimension table, in which we will track the updates.

We put in this table the informations about addresses.

Here is the code for our example:

CREATE TABLE STAMBIA_TEMPLATE_ORACLE_DEMO.dim_geo
(    
      id_geo INTEGER,
      ADR_id INTEGER,
      city VARCHAR2(200) ,
      zipcode CHAR(5) ,
      dim_version INTEGER ,
      start_date TIMESTAMP,
      end_date TIMESTAMP ,
      dim_comment VARCHAR2(200)
)

We will use a sequence for the id_geo column:

CREATE SEQUENCE STAMBIA_TEMPLATE_ORACLE_DEMO.seq_dim_geo minvalue 0;

Then, the table can be reversed on the oracle metadata.

Note:

SCD will be managed by Stambia and the Oracle Integration Template, this table is nothing more than a usual oracle table.

 

Configuring the Metadata

The next step is the configuration of the columns of the dimension table, directly in the metadata. The purpose is to indicate the SCD behaviour for each column.

metadataSCD

The different modes are:

historizedIfModified If a change is detected on this column a new row will be inserted with the new value and this row will become the current one.
updatedIfModified If a change is detected on this column the existing record will be updated with the new value.
startDate The date or timestamp when the record was inserted.
endDate The date or timestamp when the record was outdated for a newer version.
recordVersion A flag indicating the status of the record (0=history, 1=current)
surrogateKey A unique specific key to each record. This column will typically be loaded with a Sequence.
naturalKey The key which identifies logically the record, regardless of the version that have been historized (typically the primary key of source table)

 

And here is the modes used for our table:

ID_GEO  surrogateKey
ADR_ID  naturalKey
CITY  historizedIfModified
ZIPCODE  historizedIfModified
DIM_VERSION  recordVersion
START_DATE  startDate
END_DATE  endDate
DIM_COMMENT  updateIfModified

 

Creating a mapping to load the table

Mapping Overview

mappingOverview

 

Note:

  • CUS_START_DATE, CUS_END_DATE , CUS_VERSION are left empty. These columns are used for historization and will be managed automatically by the template.
  • The Integration Strategy has to be set to SlowlyChangingDimension and the Useless Update Detection Method to notExists.
  • We are using a sequence for the ID_GEO column. This expression must be executed on Target.

 

After executing the mapping, the dimension is loaded with data.

Exemple:

mappingResult

The DIM_VERSION is 1 and the END_DATE is null because it is the current version so it does not have yet an end date.

 

Updating an historized field

Our mapping is ready, we are now going to update a city, which is set to historizedIfModified mode in the dimension table.

For that, we update the city in the in the T_ADDRESS table with an update statement in the SQL Editor for example:

UPDATE HOTEL_MANAGEMENT.T_ADDRESS SET ADR_CITY='BOSTONnew' WHERE ADR_ID=1;

Be careful, the dimension table is just used to track changes and is mostly used for historization.

So the updates must still be done in T_ADDRESS in our example.

Then we execute the mapping again, which will historize the changes of the source table.

Here is an example of the result, after updating the city and executing the mapping:

mappingUpdatedADRResult

A new record has been added with the DIM_VERSION 1 and the old record has been updated with and END_DATE and its DIM_VERSION is set to 0.

 

Updating an updateIfModified field

We will now try to update the DIM_comment column, which is set to updateIfModified mode in the dimension table.

This column is filled manually in mapping in this exemple so we have to update our mapping

mappingUpdatedComment

Example of result:

 mappingUpdatedCommentResult

The comment has been directly updated in the current version of the record and no new record has been added.

Conclusion

We have seen in this article:

  • How to configure a dimension table in the metadata
  • How to use SCD in mappings with the oracle template
  • Examples of use

It should help you to begin with SCD in Stambia with Oracle.

 

You have no rights to post comments

Articles

Suggest a new Article!