After the termination of support for OWB has been officially announced, the Oracle Data Integrator (ODI) is the ETL tool of choice in the Oracle world. The development has progressed to version 12 which brought a few modifications and improvements. The GUI has continued to become more similar to OWB, although there are a few possibilities available which OWB did not offer in this way. In this blog entry, we will deal with the implementation of slowly changing dimensions in ODI.
Table of Contents
The Oracle Data Integrator
The Oracle Data Integrator (ODI) offers an elegant solution to fill slowly changing dimensions. In this process, the basic approach corresponds to the approach in ODI 11, however, the configuration of the target tables has been revised.
At first, the dimension table is created just as any other model via "reverse engineer" in ODI.
Step 1 - Configuring Dimension Table
Firstly, the dimension table has to be set as SCD. In order to do so, one opens the data store and sets "OLAP Type" to slowly changing dimension.
Step 2 - Configuring the Columns of the Dimension Table
The columns of the dimension table can be found in the tab "Attributes":
Here, the value "SCD Behavior" is set for every column.
For an SCD type 2, the columns have to be set as follows:
In the example, it looks as follows:
Step 3 - Importing IKM
The IKM for slowly changing dimensions is no longer available per default in ODI 12. However, it can be imported from the XML which comes with ODI. The knowledge module can be found in the folder $ORACLE_HOME/odi/sdk/xml-reference.
Here, the IKM is to be selected according to the type of the target database. For an Oracle database, that would be e.g. "IKM Oracle Slowly Changing Dimension".
Step 4 - Create Mapping
As soon as the target table is configured, it can be integrated in the mapping in the normal manner.
The integration type "slowly changing dimension" must be selected in the target table.
Now, the IKM should be tested in the physical layer:
A CKM is mandatory for this IKM.
In case no CKM can be selected here, it has to be imported in the same way as the LKM (see step 3).
This completes the implementation of the SCD2 load and it can be tested.
Learn more on how to further adjust the IKM slowly changing dimension to your environment in the next blog article.
Who is b.telligent?
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.
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.