Hi Guys,

Another really usefull tip about data types!

There are some time it happens we don’t have the required data type in respective Technology in ODI say for example DATETIME YEAR TO DAY ,DATETIME YEAR TO SECOND etc and so when we do a Selective reverse or RKM we don’t get the correct data type listed causing which we get the Null data type or get assigned to String .

The other simple solution if you are particular in getting the data type listed in Model . Here are the steps to Manually add the data type in Topology and create the respective conversation syntax.

Step1 . Go to the Respective data type and right click and Insert Data type or Duplicate an already existing datatype

Step2. Provide the required code and syntax

Step 3 . Provide the required converted To and Converted From detail for the Technology you are going to Work with . Lets say my target will be Oracle in that case , I have assigned to Timestamp .

Easy and Simple solution in dealing with Data types missing.

Any comments???

It’s our pleasure to have you here, at  www.odiexperts.com

This post is all about explaining how the internal Id in ODI plays and acts and what is the best practice and what actually happens when most of the error occurs.

Internal Id is an important element in ODI when its comes to Import Export of Object  and so a good understanding of it can help us save time and above all errors related to Export Import . Internal ID apart being an incrementing code also have relationship to Work and Master Repository.

If you are keen on knowing how the internal Id objects have relation to Work (WR)and Master Repository. Here is the key , when you define your 2 or 3 digit number as the Internal ID to Repository,  this number(WR Internal ID )  acts like a base in all the object.

Lets say my internal id for Work Repository is 200 .
My project will have the Internal Id – 1200
My Folder will have Internal Id of -2200
Interface1 – 2200
Interface2- 3200
Interface3- 1200
Variable1- 2200.

If you have notice all the  different objects have suffix 200( WR Internal ID) to their incrementing number.

Note : ODI can have same internal Id for different objects but never for the same object , since while storing into the SNP work repository tables it increments accordingly by 1000 or other number as defined in its code.

Similarly its go for the Master Repository.

Lets say my Master Repository internal id 50 .
Data Server 1- 2050
Data Server 2 – 3050
Physical Schema1 – 1050
Physical Schema2 – 2050
Agent1-1050

[Please note some of the Default Data server created while Initial ODI installation( Ex - XML_GEO_DIM , File Generic etc ) will have different Internal ID not matching to your Master Repository IDs. ]

We hope till now , you would have got an good understanding of ODI objects , Repository Internal ID and their relationship .

Now let us cover the importance and the simple reason why Repositories needs to have different Internal ID and which is the highly suggested and the best practise to do with few simple scenarios.

Scenario 1. Two DWR having the same internal ID.

Let  us call them DWR1 and DWR2. They both have the same Internal Id of 200 connected to different Master Repository.

Let say the development is being carried on both the repository or even the Development and Testing environment are both DWR .

Let say we  have reveresed engineered datastore
Source Data Store – ABC (121200)
Target Data Store – TRG (321200)
and created the interface
Interface – XMT_Interface (143200)

The  interface is ready and some body in the DWR2  needs the same Interface for their development build, but unfortunately some one have already reverse engineered the same in the DWR and their internal id as follows .
Source Data Store – ABC (122200)
Target Data Store – TRG (322200)

When the Developer brings in the Interface XMT_Interface thinking that already the Data store exists in the DWR2 , it will fail because of improper links , since  Interface will look for  Datastore ABC (121200) and TRG (321200) and KM and when they are different it error out.
To solve this  either the developer have to bring in Duplicate mode and also change the Source Data stores. In short he pretty much needs to rebuild the whole interface again.

The other sub scenario can be Interface with the same ID can exists with different name and code. Again the Developer need to import in duplicate and assign the required datastore , in short rework .

Scenario 2. Two DWR having different internal ID.

Let me call them DWR1 and DWR2. They both have the different internal id of 200 and 201 respectively.

Now let say the developer is moving the Interface from the DWR1 to DWR 2. Well even now the interface will fail , not becuase of  Interface id but becuase of the datastore can be different in the DWR2.

To solve this we need to import the data store too but that’s like having two different data stores for single purpose.

Well here the steps to handle in such a scenarios.

Step1. Sync Global Variable and other Global ODI objects , Model and Datastore when imported in One DWR to another DWR immediately or at the End of the Day.
Step2. Import the Codes in this order Project, KM, Local Variable, Procedure ,Interface and Packages for sync [ if required ]

Well with these above conditions in place, the Developer can easily migrate the code since
Source Data Store – ABC (121200)
Target Data Store – TRG (321200)
Will be present and also being a different internal id we can use Insert_Update and import the codes.

Above all, what ever is the case, have different internal Id for Work Repository.

Scenario -3 Importing from DWR to EWR having the same internal ID. (100)

Lets take an Example where a Developer in the EWR makes a duplicate of the present scenario ( 12100) and ODI starts to define a new Internal ID but here comes the issue  , that

scenario internal id can be already present  or in future when trying to import makes an Error ,since we have created a duplicate interface with that ID.

Scenario -4 Importing from DWR to EWR having the different internal ID. (200 and 201 )

If in case the EWR would have had a different internal id DWR (200) and EWR (201) , so when the Developer make a duplicate the new id will be with some thing like this (12201) and so its does not updates or modify or error out because of an existing object or even when importing scenarios in the future ,since the base ODI have scenarios with numbers ending in 200 .

Similarly the Internal ID can impact for different ODI objects in Topology and Security Manager, so use different Internal ID.

I hope this post have given you an understanding and the relationship of Internal Id , its generation method and why the objects gets error out.

Moral of the Post – Create Different internal Id for both Work Repository and Master Repository always .

Look for odiexperts.com for more tips, tricks and best practices.

This post explain the process of Exporting Scenarios from DWR to EWR .  As Cezar have suggested in previous post of how it is important to have one single DWR ( Developer Work Repository) and multiple EWR [ Execution Work Repository ] , As Multiple  DWR can lead to improper code maintains and improper synchronization between the multiple DWR.    Read more this post here

In this present example i am moving from my DWR to EWR [ Testing Environment ] .

Generally before the UAT , its important to have an Testing environment  where Scenarios functionality, code Testing , Data Validity  is tested. I strongly feel that it is import to have such an environment before releasing to UAT so that less codes fail at the UAT and multiple minor issues can be fixed at this stage thus avoiding more failure at the next stages.

In this example i am creating the EWR in the same Master Repository and using a different context and agent but its is not necesarry to do so,  if both are in the same box ,  the only draw back i see is that using the same agent for  Development and Testing can lead to performance degradation , but again its the architecture and the requirement which matter requiring to understand in  how to handle the resources.

Step 1

  -   Creating the Execution Work Repository  schema, tablespace and other Database permission for users.

Step 2

  -  Creating the EWR in ODI Topology Manager..

Right click – > Insert Work Repository .

Provide the required Meaningful name ,  EWR  schema user/password   created in step 1

Provide the  JDBC Driver, URL.

Click Test  and Once Successful Click Apply and OK.

Provide the new ID , make sure the ID different from the  other DWR created .  Select ‘ Execution ‘ type  and provide a meaningful Name as that will be your  Execution Work Repository Name.  click Ok once done.

We have our  EWR created

As you can see that in EWR , mostly tables related to Scenarios , Sessions  , Sequence , Variables tables are created and the work tables in  EWR are less than the DWR since in EWR mostly information related to scenarios, sessions are mattered a lot.

Also  EWR  , access to  Operator , Topology  and Security Manager are allowed . As the name suggestion Execution Work Repository so only  Scenarios are allowed to be imported into this environment and so designer are not created in EWR .

Step 3

  -  Logging into EWR Operator.

Now we will move our Scenarios from DWR to EWR

Step 4

-  Transfer of Scenarios from DWR to EWR

Log into Operator  of DWR .

Right Click on the Scenario to be Exported and selected the Folder.

Always use the insert_update mode so the in the future when you try to insert or update ODI can automatically identify the scenario and make the necessary insertion and updation and less code corruption.

Using Solutions

Solution is great when the client or architecture demans to track each code flow from one environment and to the other ,as solution creates version for insertion and for every transfer so at the end there can be multiple version of the same objects . As i have said before its the requirement that matter , in case if solution have to be used use Description to provide as much details as possible

say for example

Description .

1. Fixed the Invalid_number issue

2. Logic for Data validation is changed by this example and so on.

Doing so can provide more help and information for the administrator or the user in  getting older and newer codes  into the other environment as the demand requires.

Go the DWR  operator  – > Insert Solution

Provide the Solution Name  and drag in the scenario  into Principal Elements.  Solution creates Version for every Insert you do .  Solution are great when you would like to track each scenario and roll back to previous versions.

Click yes on the above  warning and keep adding as many scenarios to the Solution.

Since  Solution are created in Master Repository[ SNP_VERSION] , you can find that in other Work Repository.

We are going to extract the scenarios from the Solution created above. To do so , Right click – > Restore All Elements and click yes for the below warnings

Once restored , all the scenarios are imported into the other environment.

Step 5

- create a new Context ‘ XMT_TEST’ 

[ NOTE : – Make sure you create a new Work Schema for Testing as it will be easy to Testing and Debugging the $ tables ]

step 6

– Creating Data Servers  and Physical Schema . [ Being a testing environment i am using the same development source box  and so only the target structure is replicated for the newer envrionment, thus creating the new Dataserver and Physical schema and link them to the respective Logical Schema  ]

Create the Physical schema and Link the to the already created Logical Schema using the new Context

Keep repeating the same steps for other physical schemas.

Step 7

– Create Agents for the new Testing environment.

Step 8

- Lets test the scenario using the newly created context.

This query will show the interface  mapping  in the following order

PROJECT_NAME  , FOLDER_NAME  ,  INTERFACE_NAME  , INTERFACE_TYPE , TARGET_SCHEMA , TARGET_TABLE , TARGET_COLUMN , TARGET_DATATYPE , SOURCE_SCHEMA , SOURCE_TABLE , SOURCE_COLUMN , SOURCE_DATATYPE , MAPPING  filtered by Interface , Folder  or  Project Name. I have commented out other option of Interface & Folder..

SELECT DISTINCT
SNP_PROJECT.PROJECT_NAME    AS PROJECT_NAME,
SNP_FOLDER.FOLDER_NAME      AS FOLDER_NAME,
SNP_POP.POP_NAME            AS INTERFACE_NAME,
CASE
WHEN SNP_POP.WSTAGE='E' THEN 'TABLE_TO_TABLE_INF'
ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE,
SNP_POP.LSCHEMA_NAME        AS TARGET_SCHEMA,
SNP_POP.TABLE_NAME          AS TARGET_TABLE,
SNP_POP_COL.COL_NAME        AS TARGET_COLUMN,
CASE
WHEN SNP_POP.WSTAGE='E' THEN T_COL.SOURCE_DT||' ('||T_COL.LONGC||')'
ELSE SNP_POP_COL.SOURCE_DT||' ('||SNP_POP_COL.LONGC||')'
END AS TRG_DATATYPE,
SNP_SOURCE_TAB.LSCHEMA_NAME AS SOURCE_SCHEMA,
SNP_TABLE.TABLE_NAME        AS SOURCE_TABLE,
SNP_COL.COL_NAME            AS SOURCE_COLUMN ,
SNP_COL.SOURCE_DT||' ('||SNP_COL.LONGC||')' AS SRC_DATATYPE,
rowtocol('SELECT DISTINCT TXT FROM SNP_TXT WHERE I_TXT='||i_txt ) AS COLUMN_MAPPING
-- to use without rowtocol use SNP_TXT.TXT AS COLUMN_MAPPING
FROM SNP_PROJECT
LEFT OUTER JOIN SNP_FOLDER ON SNP_FOLDER.I_PROJECT=SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN SNP_POP ON SNP_POP.I_FOLDER=SNP_FOLDER.I_FOLDER
LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_POP_CLAUSE.I_TXT_SQL
LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT= SNP_POP_COL.I_TXT_MAP
LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_TXT_CROSSR ON SNP_TXT_CROSSR.I_TXT=SNP_TXT.I_TXT
LEFT OUTER JOIN SNP_COL ON SNP_COL.I_COL=SNP_TXT_CROSSR.I_COL
LEFT OUTER JOIN SNP_TABLE ON SNP_TABLE.I_TABLE= SNP_COL.I_TABLE
LEFT OUTER JOIN SNP_COL T_COL ON T_COL.I_COL=SNP_POP_COL.I_COL
WHERE
-- SNP_POP.POP_NAME='&TAB'
-- SNP_FOLDER.FOLDER_NAME ='&TAB'
SNP_PROJECT.PROJECT_NAME='DEVELOPMENT'
ORDER BY SNP_POP.POP_NAME

I have used the rowtocol function  for SNP_TXT.TXT  ,as when  mapping length  is more than  256 character ODI stores in separate line with different i_txt number causing  mapping to come into two or multiple rows . In order to avoid such scenario and in order to get all the Mapping field information into a single column , i have used this function rowtocol.

Source code for  rowtocol function is available here – > http://www.oracle.com/technology/oramag/code/tips2004/050304.html 

For this particular Example I am exporting Project, Models and all the other objects under it from ODI_DEV [ DWR ] environment to ODI_TEST [ DWR ] where DWR is Development Work Repository

Step 1 – Export the Project

Specify the directory to be exported

Step 2 – Export the models as shown above and you will find all the exported objects are created as XML Files as shown below.

Lets now move the objects to the other ODI_TEST [ DWR ] environment.

Step 1 – Export the Models

Login into ODI_TEST [DWR] , Right click on the Import Model folder option under the Model Tab.

Go the Folder where the objects are created.

Use only ‘ INSERT_UPDATE’ option as this way it would maintain the internal ID of the objects.

Don’t worry about this warning and click ok.

The models are imported . The next step is to import the project

Repeat the above step to import using the INSERT_UPDATE mode and select the project to be import and as you can see all the packages, interface , KM are also imported .

Now lets test the interface for its validity.

Voila it works

1.  

For this example I have not used any variable in that case I would suggest this order for Import.

2. Import Models
3. Import Global Variables , Sequences , Functions
4. Import Project

Lets look at the Internal Id of both the environment, they are same , you can compare other objects too and you will find that their internal ID are the same.

Using ODI Solution

Right click on the Solution and click ‘ Insert Solution ‘

As You can see that , the Project and the Model are created with the required versions.

Now Log into the other DWR environment and since the solution is stored in the Master Repository and we have the same Master Repository . The solution will be visible in the other DWR.

Right click on ” Restore All Elements ” to restore all the objects of the solution.

Click ok on all the warnings

Do so for all the other objects and finally all the objects will be restored to the required version.

I ran the package again and it worked.

Please find my future post on more best practices and method of Export and Import of ODI Objects.