XKM: Extract Knowledge Modules

I haven’t managed to find any information around XKMs apart of a basic description from Oracle and I have decided to share what I have found so far.

They are part of the new Component-style Knowledge Modules and therefore they are neither editable nor visible from ODI.

 

  • XKM Oracle Extract.

This knowledge module is used on most of the scenarios (source is a normal table).

CUSTOM_TEMPLATE: Use this option to customise the SQL statement generated on source.

 

For more information on this topic, please refer to: http://dwteam.in/query-as-source-in-12c/

 

  • XKM Oracle External Table.

This knowledge module is used when the source table is an external table. These are the extract options that can be setup:

cap

Advertisements

Temporary tables naming issue

ODI 12c works in a different way compared to ODI 11g in terms of naming convention for temporary tables (C$_, I$_, etc). We need to take extra care when running processes in parallel, mainly in Load Plan parallel steps and even with ODI projects running in parallel.

 

The generated name of the C$ temporary table is usually based on the alias of the object directly connected to the target table.

For instance, if we have a filter before the target table, ODI will run the following statement:

“create table ODI_TEMP.C$_0FILTER …”

This will create a non-unique table name and it can be easily messed up by any other ODI process running in parallel.

 

There are different approaches to work around the issue, I suggest one of these two:

  1. To rename the aliases of the objects directly connected to the target tables to a unique and meaningful name.
  2. To use the “Use Unique Temporary Object Names” and “Remove Temporary Objects on Error” properties of the “Physical” tab from mappings:cap

 

The name of the temporary table won’t be meaningful (alphanumeric string randomly generated) but it will be 100% unique.

 

 

For more information on this topic, please refer to:

https://www.rittmanmead.com/blog/2016/05/ditips-odi12c-table-names/ https://blogs.oracle.com/dataintegration/entry/odi_12c_parallel_target_table

Database Schema to hold ODI temporary objects

This might be quite trivial for most ODI developers but key for ODI newbies.

 

It is a best practice to use a separate schema for holding ODI temporary objects (such as C$_ or I$ tables).

This schema is named “WORK SCHEMA” in ODI and it can be setup in the “Physical Schema” within the Topology tab. For instance, “ODI_TEMP” is a quite intuitive name for this purpose:

cap10

 

For more information on this topic, please refer to: http://odiexperts.com/why-creating-a-separate-schema-for-temporary-tables-in-odi/

Load Plan Auto-Restartability through JEE Agent Web Services

This set-up will allow to restart ODI Load Plans running under a JEE agent.

1) Create a package to be used within the Exception Step of the Load Plan.

There are two mandatory steps within the package:

  • The first mandatory step is to refresh a variable (I have named it “VN_LP_ID”) which captures the Load Plan ID . This variable will get the numeric Load Plan Instance ID by querying the repository table “SNP_LP_INST” and using the odiRef API method to get the Load Plan Global ID of the parent Load Plan.
SELECT I_LP_INST
FROM ODI_REPO.SNP_LP_INST
WHERE GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>'
  • The second mandatory step will execute the scenario of a package (I have named it “PKG_LP_RESTART_UTILITY”) in “Asynchronous Mode”.

 

2) PKG_LP_RESTART_UTILITY package description.

* Please note that the creation of this package is a one-off task. Once created, it can be directly used within the Exception package and it shouldn’t require any additional set-up.

cap7

– VN_LP_ID: this global variable has been declared to keep the ID of the Load Plan instance on failure. The value is coming from the “Exception Step” package.

– Sleep for 10 sec.: OdiSleep step to wait 10 seconds before calling the web services to make sure that the Load Plan has been set to “Error” status.

– Invoke Restart Load Plan: OdiInvokeWebService step to call the web services of the JEE agent.

 

Steps to set-up the OdiInvokeWebService:

  • Click on “HTTP Analyzer” option.
  • Click on “Select WSDL …” to input the WSDL URL of the agent:

http://host:port/oraclediagent/OdiInvoke?WSDL

  • From the “Operations” option, select “invokeRestartLoadPlan(,)”.
  • On the “Request HTTP Headers” side and “part1” section there are 5 parameters that need to be specified (OdiUser, OdiPassword, WorkRepository, LoadPlanInstanceId and LogLevel):

cap8

It will raise an error for “LoadPlanInstanceId” but it is actually an ODI bug because this variable will be substituted afterwards by a valid value. It can be ignored.

On the “Content Editor” tab we can see the actual XML code generated for the request.

 

3) Add the scenario of PKG_LP_RESTART_UTILITY to the Exception Package.

It has to be called in “Asynchronous Mode”.

cap6

On the tab “Additional Variables” we define how the variable “VN_LP_ID” refreshed in the first step will be passed to the “PKG_LP_RESTART_UTILITY” scenario.

cap9

 

In order to make things as generic and reusable as possible, I recommend to create the PLP_LP_RESTART_UTILITY package within a project named “UTILITIES” so you can reuse it in any project. The variable “VN_LP_ID” was created as a Global Variable for the same purpose.

 

  • Optional steps:

– The ODI/Oracle error message can be captured and sent by email (for instance).

– Depending on the error message, it can be restarted or not by evaluating the error message against a pattern.

– The number of retry attempts can be controlled by using the odiRef API method to get the current attempt number.

– It can wait for X time before being restarted.

Querying Work Repository to identify SCD behaviour

The access to the Slowly Changing Dimension Behaviour is a bit uncomfortable in ODI 11g because it requires to go to each attribute (column) of the data store and then to the “Description” tab.

There is another option going through the background and querying the repository tables to get the behaviour of hundreds of columns in a second.

 

Query:

SELECT
  sm.mod_name AS MODEL_NAME,
  st.table_name    AS TABLE_NAME,
  sc.col_name      AS COLUMN_NAME,

  CASE
    WHEN sc.SCD_COL_TYPE IS NULL
    THEN 'Undefined'
    WHEN sc.SCD_COL_TYPE = 'OC'
    THEN 'Overwrite on Change'
    WHEN sc.SCD_COL_TYPE = 'SK'
    THEN 'Surrogate Key'
    WHEN sc.SCD_COL_TYPE = 'IR'
    THEN 'Add Row on Change'
    WHEN sc.SCD_COL_TYPE = 'NK'
    THEN 'Natural Key'
    WHEN sc.SCD_COL_TYPE = 'ST'
    THEN 'Starting Timestamp'
    WHEN sc.SCD_COL_TYPE = 'ET'
    THEN 'Ending Timestamp'
    WHEN sc.SCD_COL_TYPE = 'CR'
    THEN 'Current Record Flag'
    ELSE 'UNKNOWN'
  END             AS COLUMN_TYPE,

  sc.scd_col_type AS COLUMN_TYPE_CODE

FROM [ODI_WORK_REPOSITORY].snp_model sm,
 [ODI_WORK_REPOSITORY].snp_table st,
 [ODI_WORK_REPOSITORY].snp_col sc

WHERE st.i_mod   =sm.i_mod
AND st.i_table   =sc.i_table
AND sm.mod_name  ='[MODEL_NAME]'       -- Please specify model name
AND st.table_name='[TABLE_NAME]' -- Please specify table name

ORDER BY sc.POS;

 

User Interface Slowdown issue

After a fresh installation of ODI 12c, some default settings should be modified to avoid a slowdown / unresponsive issue with the IU of the client.

For instance, it’s quite easy to get the following error message while working with Load Plans:

UI_slowdown

 

It’s possible to tweak the memory settings by changing some details on the client configuration files. There are two different files to be modified:

  • odi.conf

File location: <ORACLE_HOME>\Middleware\Oracle_Home\odi\studio\bin

  1. Create a backup of the “odi.conf” file.
  2. Open the “odi.conf” file and add (or change) “AddVMOption -XX:MaxPermSize=1024M” and save it. It depends on your system but 1024M works fine for me.

cap1

  • product.conf

File location: C:\Users\<Your Username>\AppData\Roaming\odi\<ODI Version Number>

  1. Create a backup of the “product.conf” file.
  2. Open the “product.conf” file, add the following two lines to the file, underneath the Java Home specification and save it:
    • AddVMOption -Xms128m
    • AddVMOption -Xmx<YourNumber>m (I used 10240)

cap2

Reference: https://medium.com/red-pill-analytics/having-odi-12c-studio-performance-memory-issues-e4103304d5dc

Working with Synonyms

By default, on the ODI models, you can select “Synonym” as a type of object to reverse-engineer. They will appear in the “Selective Reverse-Engineering” tab and you can ‘partially’ reverse engineer them, however the “Attributes” tab of the data store for each synonym will be empty.

To resolve the issue, a couple of key/value needs to be added to the required data server.

 

Topology -> Physical Architecture -> Technologies -> Oracle -> [Data Server] -> JDBC tab -> Properties section

 

A new property needs to be added with the following values:

Key = includeSynonyms

Value = true

 

It will look like this:

cap4

After that setup, please disconnect & connect again to the ODI repository to be able to reverse engineer synonyms properly.