Skip to main content

Export & Import in ODI

Exporting Objects

When exporting an Object, an XML file is created.
Objects have dependencies and these will be exported as Object IDs or Object Names in the exported XML file.
The content of this XML file will depend on the export method you will use:
Export With Child Components
Export Without Child Components
When an Object is exported with its child components, all container-dependent Objects IE those which possess a direct parent/child relationship – are also exported.
However, referenced Objects are not exported.
The choice will depend on your goal, if you need to do a partial export then the Export Without Child Components is the one to use.
The Export Multiple Objects feature is useful when you need to regularly export the same set of Objects.
Once the export has been performed, it is very important to choose the import strategy to suite your requirements

Importing Objects

A general definition of import modes can be:
Importing an Object in Duplication Mode: The Object internal ID is reconstructed inside the new Work Repository.
Container dependencies such as parent/child relationships are recalculated to match the new parent IDs. External reference IDs are not recalculated.
Importing an Object in Synonym Mode: The original Object internal ID is retained inside the new Work Repository.
Container dependencies such as parent/child relationships are not recalculated. External references IDs are not recalculated.
The sub mode (INSERT, UPDATE, INSERT_UPDATE) will have the following behavior:
INSERT: Imported Object will be treated as a new Object. If an Object with the same ID exists in the Repository the import operation will not effect the numeric value (it will not be replaced).
UPDATE: Imported Objects will be treated as if the Object already exists. Its properties and child components will be updated accordingly. If an Object with the same ID does not exist in the new Repository, the import operation will have no effect.
INSERT_UPDATE: The imported Object will be either created, or updated to exactly match the content of the XML file. If the Object already exists, this method should be avoided when the export of the initial Object was performed without its child components. (This is to avoid any potential deletions of sub-components of the existing Object.)

Deciding Which Import Mode To Be Used When Importing ODI Objects

ODI import operations function in the following manner:

DUPLICATION mode

Creates a new object in the target Repository and inserts into it all of the elements which are present in the .xml import file currently being used for the import operation.
The ID of this new object will be based on the ID of the Repository in which it is to be created (the target Repository).

Synonym INSERT mode

Inserts a new object into the target Repository with the object ID as created in the original (Source) Repository (ie the one stored in the XML export file being used for the import operation).
In this case, the ID is based on the Repository ID from which the object was exported.
If any error is signaled, the transaction is aborted and an error message is displayed on standard output.

Synonym UPDATE mode

This mode updates the already existing attributes/components of an ODI object in the target Repository with the modifications of these components present in the XML file being used for the import operation.
An import of a Scenario in Synonym UPDATE mode does not mean that ODI will update the Scenario, but will only use UPDATE SQL statements to update the existing records composing the Scenario Object.
Hence, this is potential way to corrupt the Scenario. Please use Synonym INSERT_UPDATE mode instead.
For example, if one of your texts in the Scenario is <= 250 chars, this will be stored as a single record in the SNP_SCEN_TXT table, with internal ID myID1.
If, after the changes on source, the text becomes > 250 chars, there will be two records stored in the SNP_SCEN_TXT table, with internal IDs myID1 and my ID2.
If you import that Scenario in Synonym UPDATE mode, the text record with myID1 will be updated, but the one with myID2 will not be inserted…
If you import the Scenario in Synonym INSERT mode, the text record with myID2 will be inserted, but the one with myID1 will not be updated…
The only way to have myID1 updated and myID2 inserted, is to import the Scenario in Synonym INSERT_UPDATE mode. Or in Duplication mode, but then this will create a new Scenario object, with a new internal ID — and this is may not be what you are attempting to do.
Another example: If in an ODI Package, you have added a new step, this one will not be created with Synonym UPDATE import mode.
So, after the import UPDATE mode, the Package will be corrupted, as it will contain some of the changes, but not all of them….
Same for the INSERT mode: this one will create the new Package step, but will not update any of the existing steps. So again, the Package is corrupted after import
Conclusion: Use Synonym INSERT_UPDATE mode.

Synonym INSERT_UPDATE mode

If no ODI object exists in the target Repository with an identical ID, this import mode will create a NEW object with the ID of the object in the XML import file being used for the import operation.
The already existing attributes/components of the Object will be updated; the new ones, inserted.
REPLACE mode
Replaces the already existing ODI object in the target Repository with the one present in the XML file being used for the import operation.
If the object does NOT already exist – as identified by its ID – the import operation aborts.
What is an existing ODI object?
The notion of an existing object corresponds to a ODI object stored in the Repository (Procedure, Datastore, Scenario …) and possessing a unique object ID. For Knowledge Modules or specific procedures, for example, this corresponds to the I_TRT value (procedure number).
When importing a Knowledge Module in DUPLICATION mode, a new object is created with a new unique identifier (I_TRT) which has no relation whatsoever with the identifier of the object in the import file.
Performing a SYNONYM UPDATE on a KM from the same import file will have no effect on the targeted KM because they are no longer the same objects. This synonym import operation may even result in unexpected updates since there might already exist an object in the target Repository that has the same identical ID as the object stored in the XML file being used for the import operation.
Criteria for deciding when to perform an SYNONYM INSERT import mode
This import mode inserts an object with the SAME attributes as those attached to the object present in the XML file being used for the import operation. If any of the incoming attributes violate any referential constraints, the operation is aborted and an error message is signaled in the standard output.
– When using the synonym INSERT for importing Knowledge Modules, you may modify some procedure or other KM stored in your Repository. This will certainly occur when the Work Repository ID is identical to the one from which the export file has been created.
AVOID using SYNONYM import modes for Knowledge Modules – only use DUPLICATION import mode. To update existing Knowledge Modules, you may prefer to ‘replace’ the existing one (see on-line documentation regarding the import REPLACE mode).
– When using 2 similar Repositories, and a KM is modified in one of them, it is possible to export it and import it into the other Repository with the REPLACE mode.
Conclusion
DUPLICATION mode is used to transfer ODI objects between projects WITHIN the same Repository…
SYNONYM and REPLACE modes are used to exchange ODI objects between 2 different Repositories (such as Projects, Models, Scenarios…) and is particularly useful, for example, when exporting objects from development environments to production environments in order to be able to synchronize updates between Repositories…

Comments

Popular posts from this blog

ODI KM Adding Order by Option

You can add Order by statement to queries by editing KM.I have edited IKM SQL Control Append to provide Order by.  1) Add an option to KM named USE_ORDER_BY, its type is Checkbox and default value is False. This option determines you want an order by statement at your query. 2)Add second option to KM named ORDER_BY, type is Text. You will get order by values to your query by this option. 3) Editing Insert New Rows detail of KM. Adding below three line code after having clause. That's it! <% if (odiRef.getOption("USE_ORDER_ BY").equals("1")) { %> ORDER BY <%=odiRef.getOption("ORDER_BY" )%> <%} %>  If USE_ORDER_BY option is not used, empty value of ORDER_BY option get error. And executions of KM appears as such below; At this execution, I checked the KM to not get errors if ORDER_BY option value is null. There is no prove of ORDER BY I'm glad.  Second execution to get  Ord

Creating Yellow Interface in ODI

Hello everyone! In Oracle data integrator (ODI), an  interface  is an object which populates one datastore, called the  target , with data coming from one or more other datastores, known as  sources . The fields of the source datastore are linked to those in the target datastore using the concept of  Mapping . Temporary interfaces used in ODI are popularly known as  Yellow Interfaces . It is because ODI generates a yellow icon at the time of creation of a yellow interface as opposed to the blue icon of a regular interface. The advantage of using a yellow interface is to avoid the creation of  Models each time you need to use it in an interface. Since they are temporary, they are not a part of the data model and hence don’t need to be in the Model. So let’s begin and start creating our yellow interface! Pre-requisites : Oracle 10g Express Edition with *SQL Plus, Oracle Data Integrator 11g. Open *SQL Plus and create a new table  Sales  in Oracle. You can use any existing ta

Running Count in Talend Open Studio

Most Talend components keep a count of the records processed using variables like NB_LINE or NB_LINE_OK.  But these are only available after all processing is completed.  Define your own counter variable to keep a running count for use in a tMap. Variables like tFilterRow.NB_LINE or tAccessOutput.NB_LINE_INSERTED can be used to report the number of affected lines after a subjob's processing.  However, it may be of use to get the current line index for use in a tMap.  The index variables used to form NB_LINE aren't available during processing; they're only written out the globalMap at the end of processing. In this example, staging records are loaded from Excel to Access.  The order in which the Excel records are read is preserved in a database column called DISPLAY_SEQ_NB.  Note that there is an auto-increment column used for record ID in the Access table.  This could be used to infer a loading order, but this job uses a separate column to keep the ID as a meaningless surr