In this post, I describe how the ODI Generator tool is used in a large datawarehouse project. In this project, we are using ODI Generator to create not only ODI objects like datastores, mappings, packages, and scenarios but also other types of artifacts like Oozie workflows and DDL statements. Follow the link below to discover more.

Using ODI Generator in a large project

As the name suggests, "SQL Structured Substitution API" provides a more straightforward and structured way of generating code using ODI knowledge modules. In the documentation and examples provided by Oracle, the following pattern is used to generate code:

1. Variables are created and initialized using Groovy code in Groovy Task-local and/or Groovy KM-local sections of KM steps. In the Groovy code present on the examples, I have found that only read functions are used. I didn't find any example where the AST (i.e. the various objects exported using INSERT, QUERY and so on variables) is manipulated.
2. The variables mentioned above are then used on the Command Text section of KM steps to generate the code.

Therefore when we need to change the statement generated by an IKM we can do it by writing our custom code in some Groovy variables and then use these variables in the Command Text section. It is also possible to write text directly in the Command Text section. There is also a third possibility which I want to illustrate in this blog: manipulating the AST structure. This means that we do not necessarily touch the Command Text section or create Groovy variables but make our changes directly in AST. Example of such changes could be:

• create  predefined mapping expression for technical fields
• add some tables in the FROM clause (i.e. adding a join with the table containing the surrogate keys)

Follow the link below to see how you can create a new component-style IKM on which the AST used to generate the code is modified:

Altering AST in component-style IKM

As largely expected, starting with ODI 12.2.1.2.6, Oracle suggests that newly developed Knowledge Modules should be component-style KMs:

Component KM is a new, improved style of KM development, which is applicable to IKMs, LKMs, XKMs. KMs for mappings have two different types of implementation styles: The legacy 11g-style and component-style. 11g-style KMs are designed to use the monolithic odiRef substitution API object and syntax in their template commands. Component-style KMs are designed to use the newer object-oriented substitution API objects and newer template flow control syntax in their template commands. Both styles of KM can be seen in the project and global KM tree, as IKMs, LKMs, and CKMs with a predefined set of component-style KMs called XKMs. All new LKMs, IKMs, and XKMs should be designed and coded using the new component KM style. Component KMs are new since ODI 12c and are first exposed in the ODI Studio from 12.2.1.2.1 release. A component KM has the same functionality as any other KM, which includes tasks and options.

Follow the link below to see how you can create a new component KM:

Injecting WHERE conditions using XKM