As described in the previous blog entry, the Oracle Data Integrator (ODI) offers an integrated solution for keeping a history of data with the SCD (slowly changing dimension) methodology. Upon closer consideration and when an integration quantity is loaded practically into a target table using the integration knowledge module (IKM) SCD, it is noticeable that the ODI uses certain default values for the end of the validity period of the dataset.
This can be illustrated in the following scenario.
Data is written in the dimension table DIM_TEST_SCD from the staging table STAGE_TEST_SCD.
In order to comprehend the initial situation more easily, in the following test cases it is described how the functions can be presented.
Three datasets have arrived in the staging table that are to be written into the empty table DIM_TEST_SCD by means of IKM SCD.
As is seen in the result, the end of the validity period of the dataset is set as standard by the IKM at 01/01/2400 to depict unending validity.
The staging table now provides changes. The dataset with NK = 1 remains unchanged (ATTR); for NK = 2, ATTR was changed to “C”; NK = 3 is no longer provided; and NK = 4 was newly added.
In the case of a change, the original set is terminated in its validity and a new dataset with unending validity is inserted. As can be seen in this example, in the event of termination the validity of the dataset is set at the same value as the start of the validity period upon insertion of a dataset.
As has become clear from the examples, the IKM SCD inserts 01/01/2400 as standard for the unending validity and the loading date for the termination of a dataset. However, in many situations it can be desirable to insert a specially defined value for the unending validity or to set the validity of the dataset to “loading date – 1” in the case of termination.
For the validity of the dataset to be set at the desired values, it is necessary to perform minor adjustments in the IKM SCD. For our example, we assume that the date for unending validity is set at 31/12/9999 and the date for a set to be terminated is set at “loading date – 1”. In the ODI, the IKMs can be opened and edited by double-clicking on them in the designer tab under the corresponding project folder. Under the “Tasks” tab, the following tasks can be identified, which need to be adjusted for the desired changes:
For the tasks in the green boxes, the validity of the dataset is adjusted. To this end, the dots beside the code snippet presented in the “Target Command” area are clicked on.
An adjustment was performed on the task “Flag rows for update” as an example above.
For the validity of the dataset to be set to the desired value in the case of a termination as well, the validity must be reduced by one day in the task “Historize old rows”:
Following the adjustments, the IKM SCD can be saved and the test cases repeated. After the execution of the ODI mapping in test case 2, the following combinations now result:
As desired, the validity of the dataset was set to 31/12/9999 and, in the case of a termination, at “loading date – 1”.
Do you want to replace the IoT core with a multi-cloud solution and utilise the benefits of other IoT services from Azure or Amazon Web Services? Then get in touch with us and we will support you in the implementation with our expertise and the b.telligent partner network.
Domain Lead Data Platform & Data Management
Domain Lead Data Platform & Data Management
Exasol is a leading manufacturer of analytical database systems. Its core product is a high-performance, in-memory, parallel processing software specifically designed for the rapid analysis of data. It normally processes SQL statements sequentially in an SQL script. But how can you execute several statements simultaneously? Using the simple script contained in this blog post, we show you how.
Many companies with SAP source systems are familiar with this challenge: They want to integrate their data into an Azure data lake in order to process them there with data from other source systems and applications for reporting and advanced analytics. The new SAP notice on use of the SAP ODP framework has also raised questions among b.telligent's customers. This blog post presents three good approaches to data integration (into Microsoft's Azure cloud) which we recommend at b.telligent and which are supported by SAP.
First of all, let us summarize the customers' requirements. In most cases, enterprises want to integrate their SAP data into a data lake in order to process them further in big-data scenarios and for advanced analytics (usually also in combination with data from other source systems).
As part of their current modernization and digitization initiatives, many companies are deciding to move their data warehouse (DWH) or data platform to the cloud. This article discusses from a technical/organizational perspective which aspects areof particularly important for this and which strategies help to minimize anyrisks. Migration should not be seen as a purely technical exercise. "Soft" factors and business use-cases have a much higher impact.
No previous post
No next post
