previous article
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.
And a new option is created via Plus.
This is created as type text with the name SCD_END_DATE and filled with the desired date value
Subsequently, move to the tasks tab and open the tree at 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:
into:
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.
