Recently I have participated in a discussion regarding ODI mappings complexity. More specifically reducing mappings complexity. The ODI mappings I am talking about are using datastores built on behalf of bitemporal tables in Oracle databases. Worths mentioning that temporal behavior is implemented using plain "start date" and "end date" fields for both valid time and transaction time periods. Oracle 12c's temporal validity feature is not used.
ETL jobs process data in a snapshot based manner. Each job receives two date parameters (day granularity) used for temporal selection:
These date type values are used to populate VT/TT fields for the inserted and updated data in target tables and to filter source tables data. It is possible to have inside an ODI mapping multiple instances of the same datastore, each instance being filtered on a different valid time. For example, comparing today's data with yesterday's data for a given table.
The necessity to filter source tables on valid time(VT) and transaction time(TT) could lead to a proliferation of Filter components or in the best case increases the complexity and verbosity of existing Filter conditions. It would be nice if we could find a way to eliminate the Filter components created exclusively for VT/TT filtering purposes but preserve the VT/TT conditions in the final SQL code generated by the mapping. Otherwise, if a temporal datastore has to be filtered on some other condition, it would be nice to not have to specify VT/TT conditions (in order to keep the filter condition as simple as possible) yet preserving VT/TT conditions on the final SQL.
It is assumed that temporal source tables are filtered by a reference date so we don't want to complicate the already complex ODI mapping logical and physical layouts with well-known filtering conditions. This is something similar to Oracle application context and fine-grained access control feature. Indeed this was one of the solutions proposed for simplifying the mappings but:
The solution I describe below uses ODI component knowledge module to inject valid time/transaction time conditions directly in mapping's Abstract Syntax Tree. The conditions are specified using XKM options. Because each source datastore instance(or better said the associated physical node) will have an associated XKM, we can specify a filtering condition for each. If necessary we can specify a different reference date for each instance of a given temporal source datastore. Proceeding this way we will have the valid time/transaction time conditions present on the final SQL code without creating dedicated Filter components. We would obtain simpler ODI mappings but we have to create a custom XKM.
Component Knowledge Modules
Component KMs have been introduced with ODI 12c but it was not possible to create/change them until 126.96.36.199.1 release. In ODI documentation we find the following description of Component KMs:
What is an AST? As mentioned above an AST is a bunch of java objects organized in a hierarchic way (tree). These objects are used by the mapping code generator to generate the final generated result code (i.e. INSERT INTO...AS SELECT...). How are the AST objects created? ODI documentation provides a very good explanation:
Back to our initial problem: we want to introduce some filters on the final generated code without creating Filter components and/or without adding extra filter condition on the existing Filter components. How could be this done? My idea was to try to modify the AST object corresponding to temporal datastores and inject there the temporal selection conditions (i.e. start_date <= reference date < end_date) which I want to add. Which type of Component KM is best to implement such change? The XKM. In ODI documentation is specified this:
This is what I want. No extra steps, just an additional condition on the code generated for reading data from a temporal datastore. Let's see how we can create an XKM which adds the required temporal selection conditions.
Creating a custom XKM
As mentioned we want to create an XKM which should offer the possibility to specify the valid time and the transaction time values and create a filter on the source table based on these two values. Considering that column names used for temporal fields are named VT_START_DATE, VT_END_DATE, TT_START_DATE, and TT_END_DATE the XKM should add to generated SQL code conditions like the following one (the effectively SQL code generated by XKM will look differently):
Of course, the reference values for valid time and transaction time needs to be specified using KM options. In the KM which we will build, these options are named VALID_TIME and TRANSACTION_TIME.
We want to have some flexibility regarding the names of the columns used for the fields which store the start and end timestamps of valid and transaction periods. For this purpose, we will introduce in the KM four options which allow the user to specify the names of these fields.
In the version of ODI which I use (188.8.131.52.6) it is possible to create a new Component KM which inherits an existing Component KM. Therefore I have created a new XKM named "XKM Bitemporal Extract" which inherits the existing "XKM SQL Extract" knowledge module. The following step was to create the KM options mentioned before in this article:
The next step is to customize the new XKM behavior: adding the code necessary for the injection of temporal validity filter. Here I have faced two problems:
What has been left is to insert the delegate Groovy script shown above in the XKM. I have done it by editing the XML file obtained from KM export. The script should be introduced in the field with the name "DelegateScript" as shown below:
At this point the new XKM was ready so I have imported it back to ODI. I have to mention that "Import Replace" functionality didn't produce the desired result. The KM was imported without visible error but it didn't function as expected. Therefore I had to first delete the existing "XKM Bitemporal Extract" KM before importing the XML file.
I didn't try to implement the delegate class as an external class (i.e. a Java class in a jar file included in ODI Studio's classpath). The pre-defined component KMs are using this approach so I expect this approach is possible.
The XKM can be downloaded here