Adapting the Knowledge Modules IKM Oracle Slowly Changing Dimension – Part 1

The first part of the OBI blog showed how one can implement a slowly changing dimension in ODI by means of the knowledge module IKM Oracle Slowly Changing Dimension.

The knowledge module IKM Oracle Slowly Changing Dimension already covers many use cases. Thus, for example, new data sets are checked against existing ones. In case of amendments, a new data set can be created automatically (SCD2) or the existing data set can be overwritten (SCD1).

However: in case technical fields, such as the ID of the loading job or the insert and/or update time are to be updated, or if one wants to use a different SCD_END standard value, KM reaches its limits.

Here, the benefit of ODI of being able to freely customize the knowledge modules comes to bear. 

Customizing the VALID_TO Date

If an indefinitely valid data set is written, the SCD_END date is set to 01-01-2400. However, a different standard value (e.g. 31.12.9999) is defined in many environments.

In order to stay as flexible as possible, we do not simply set a new SCD_END date, but introduce a new option into the knowledge module. 

In order to do so, the knowledge module is opened and the tab `Options´ is selected. 

 

knowledge-module-ikm-reiter-options

 

And a new option is created via Plus.

 

knowledge-module-ikm-neue-option

 

This is created as type text with the name SCD_END_DATE and filled with the desired date value

 

knowledge-module-ikm-datumswert

 

Subsequently, move to the tasks tab and open the tree at Execution Unit Main. 

 

knowledge-module-ikm-execution-unit-main

 

The following tasks need to be amended:

  • Update existing rows       
  •  Historize old rows
  • Insert changing and new dimensions

In each case, the value

01-01-2004

is replaced

by:

<%=odiRef.getOption("SCD_END_DATE")%>

 For example, change:

 

knowledge-module-ikm-code-aenderung

 

into:

 

knowledge-module-ikm-code-aenderung-ergebnis

 

By means of this amendment, the SCD_END date can be chosen freely and adapted to the requirements of an existing architecture.
It should be taken care, however, that the date is displayed in the correct format. I.e. in this case 'mm-dd-yyyy'.

Read in the next part of this blog “Adapting the Knowledge Modules IKM Oracle Slowly Changing Dimension – Part 2” how you can correctly treat technical fields, such as load operation ID or insert and/or update timestamps, in IKM Slowly Changing Dimension. 

Views: 0
clear
Oliver Gräfe

Contact

Oliver Gräfe
Team Lead
DE +49 (89) 122 281 110
CH +41 (44) 585 39 80
marketing@remove-this.btelligent.com