Introducing new reverse-engineering capability

In my previous blog post, I introduced our new Odi2Package tool that allows you to auto-generate ODI Packages. Our ODI development teams were quick to adopt the tool for their project needs. Soon, however, we  noticed a significant weakness: the manual creation of templates for new Package generation was tedious and time-consuming, and it was difficult to get all the Step and Link JSON definitions right.

The way we ODI developers prove a concept is: we create a Mapping or, in this case, a Package in ODI Studio and run it to ensure it works. Only once we are happy with the prototype do we start worrying about how to replicate it in tens or hundreds of times.

To the Odi2Package tool, we have now added Odi Package analysis and reverse-engineering capability: now the tool can encode any Package in the JSON format. This JSON can be copy-pasted to create a new Template. Typically you will need to make some minor edits to the reverse-engineered prototype JSON to replace instance-specific parts with <TAGS>.

Multiply Odi Package in 3 Easy Steps

Before we start

Please note that the Odi2Package tool needs to be set up - its code needs to be deployed, metadata schema created and configuration edited - only then are we ready to run the tool itself.

Step 1: Analyse, Reverse-engineer

In the O2P_ANALYSE_SOURCE metadata table we can specify the Odi Project Code, Folder Path and Package Name that we want analysed. (We can also provide only the Project code or Folder Path, in which case all Packages that are found in the given location will be analysed.)

Once we have added all the Packages and Package locations to this table, we run the Odi2Package tool's Analysis script from ODI Studio.

Analysis results are written into 3 tables:

• O2P_ANALYSE_PACKAGE - one record per analysed Package. Contains: Package name and location (Project and Path), Last Updated Date and Last Updated by User, Package's latest Scenario (if available), package reverse engineered in JSON format.

In addition to the core Package Analysis table, there are two additional tables to provide extra detail about the Scenarios that the Package calls and Variables that the Package declares, refreshes, sets and evaluates:

• O2P_ANALYSE_PACKAGE_CALL_SCENARIO - in M:1 relationship with the above Package table. Contains: Scenario Name and Scenario Version.

• O2P_ANALYSE_PACKAGE_USE_VARIABLE - in M:1 relationship with the above Package table. Contains: Variable Project Code (GLOBAL for Global Variables), Variable Name and 4 flags to indicate whether the Variable is declared, refreshed, evaluated and set.

In our use case, we just want to reverse-engineer one Package to get its JSON definition. However, the above analysis functionality can be useful to a development lead to keep an eye on the Packages in Work Repository.

Step 2: Create Template Based on Analysis

The next step for us is to create a Template record in the O2P_PACKAGE_TEMPLATE table. However, this time we do not have to create the template JSON from scratch - we copy it across from the O2P_ANALYSE_PACKAGE table.

After that, all that is left is to name the Template (TEMPLATE_2 in my example) and replace the hardcoded values in the JSON that are going to be different for each Package instance with tags. In my example there is only one tag: <DEMO2__SCENARIO_NAME> - the name of the Mapping Scenario to be called.

When the Template is created, we specify the Package instances to be created in the O2P_CREATE_PACKAGE table...

... and specify Tag values for each instance in the O2P_CREATE_PACKAGE_TAGS table. We are keeping it simple here, having just one tag in the Template.

That is it - our setup is done!

Step 3: Generate Packages

We run the Generate script from ODI Studio and refresh the output folder.

We can see that 4 new Packages have been auto-generated based on our template and instance-specific customisations were successfully applied. That really was easy!

As is the case with auto-generated repository content, if we now realise our template was incomplete, we can quickly update it and re-generate all these Packages in a couple of minutes.