Archive for ODI Expert

ODI 12c First impressions

Posted in EPM, New Features, ODI 12c, ODI Architecture, ODI Architecture, ODI Mapping with tags , , , on October 26, 2013 by RZGiampaoli

When I started working with ODI it was in version 10, and back there we had a few bugs, the UI was good (back there we could change the expressions and we didn’t have to take out the focus to save the changes, for example) , everything worked well, we could write a variable name with upper or lower case, the metadata navigator worked very well and that was one of the things that made the users choose ODI instead of power center Informatica, because they had an easy way to run their interface at will, and some other good things. It was a very stable version of ODI, good times.

Then 11 version came out. Well, the first thing we noticed was the UI, and the huge amount of bugs that came with it, and most of them in the interface screen. In 11 version, if you try to delete a filter, all the other filters disappears, but they are still there, if you close and re-open the interface they’ll come back, if you change a expression and doesn’t remove the focus from the filed it’ll not commit the changes, if you delete a datastore and put a new one (because some model changed for example) you have a good chance to not be able to save the interface for some bizarre error and you need to do this operation over and over, the variables name must be upper case for some odd motive, and other things. Another big loss was the metadata navigator that was replaced by ODI Console, a worse version with so many bugs that we had to stop using it. Some bugs like lack of security (everybody could see everything), all the execution ran as supervisor, we couldn’t see the load plans (only place where the security works), we couldn’t see the variables and lots of other things.

BUT despite of that, the functionalities for the DEV team were almost the same.

Now we have a new version of ODI. The 12c. Ok, this is only our first impressions and we could have been doing something very wrong (and I pray for this). When a software changes its version and two specialists takes more than 30 minutes trying to figure out why and how or what they need to do to sum a column in an interface or should I say mapping (yes, this is the new name, I liked it and this is one of the few things that made sense to me in this new version), something is very wrong with it.

Ok let’s start from the beginning. When I started to work with datamart, the first tool that I used was OWB, and after some time when I started to use ODI to make some integration, I really missed some stuffs from OWB. It makes sense to get this two tools and merge it together. From OWB we had a cool mapping process that makes easy to understand what that transformation is doing, multiple targets, and a few other things that I missed in ODI, BUT, ODI has the Agent that allow us to connect anywhere without the need to create a heterogeneous service or a dblink or other stuff like that, it has more flexibility (and when I say more you can understand infinite more), we don’t need to deploy the mapping to create a procedure in a oracle database to integrate something, what makes the development test super-fast, we have a lot of components, well, ODI is so much better in this aspect that the few things I missed doesn’t bother me at all.

So in this new version they tried to merge the two tools. What looks good in the paper (I mean blogs and documentations) looks terrible in ODI.

We installed it this weekend to see what happened in this new version and we saw a very different workspace for the interface, I mean for the mappings. This simple ODI UI…

ODI 11g Interface UI

ODI 11g Interface UI

Turned into this:

ODI 12c Interface UI

ODI 12c Interface UI

Humm looks good right? Well, yes and no. I’m working in a 60” full screen TV and I need to drag the screen left and right, up and down to make everything visible, poor devs that has a screen smaller than mine.

Ok but this is only layout, everything else should be better right? Well, unless we were doing something very wrong they put a lot of more complexity to solve some issues that we were able to solve very quickly since version 10.

First of all, in all the other versions if you want to sum something in ODI you just need to get the expression in the target datastore and put a SUM() function on it. ODI would do the group by for you and everything is ok.

ODI 11g Sum

ODI 11g Sum

In the new version you need to drag an object called Aggregate, put all the columns that you want to map trough this (like in OWB), change the options of this object, and in the end, put the same SUM() expression in this object instead of in the target datastore.

ODI 12c Sum Part 1

ODI 12c Sum Part 1

ODI 12c Sum Part 2

ODI 12c Sum Part 2

ODI 12c Sum Part 3

ODI 12c Sum Part 3

If you try to put the expression as before (<=11g) it’ll not create the group by and you’ll not be able to run the interface because it will just simply fail ….

Well, at least in OWB when you use the Aggregate object it’ll aggregate the columns that you define without the need to write the SUM() function. Why they put this new complexity? Ok you can execute this SUM() in another place different from the source or the target but still…

We have some other components that we need to use in the interface right now.

ODI 12c Mapping Components

ODI 12c Mapping Components

The dataset is used when you want more than one dataset in the source (we already have it in the 11g, the difference now is that you need more screen to manage it but the bright side is that you’ll not forget to change anything because you missed the datastore tab like in the old version [yes I did it a lot])

ODI 11g Datastore

ODI 11g Datastore

ODI 12c Datastore

ODI 12c Datastore

The distinct component does not need to be explained, the only thing I had to say is that in the old versions you need only to flag it in a simple check box and now we need to add a distinct component in the flow, drag all columns to it and them drag those columns again to the target. A complete waste of time.

ODI 11g distinct

ODI 11g distinct

ODI 12c Distinct

ODI 12c Distinct

The expression… well, almost the same as Aggregate. Now instead of just write any expression in the target datastore you may add this object in the flow, BUT it will work if you just write the expression on the target. So, why do we need to have this additional object???

ODI 11g Expression

ODI 11g Expression

ODI 12c Expression

ODI 12c Expression

For the filter, join and lookup table nothing changed.

ODI 12c Lockup

ODI 12c Lockup

The set is to define the type of union you can have between the datasets, same as before but now it’s in the mapping too.

ODI 12c Set

ODI 12c Set

We now have a sort component, so now we may stop doing “SQL injection” or “KMs changes” for a simple order by component (of course I liked this one).

ODI 12c Sort

ODI 12c Sort

And the Split component. This one is what I missed the most in OWB. This allow us to say something like: if the DIMENSION is Account, all the data goes to DIM_ACCOUNT, DIMENSION =  ENTITY then DIM_ENTITY, the others goes to DIM_OTHERS for example.

This is a cool thing but easily done using our command on source and target in a procedure (See this post 10 Important Things to Improve ODI Integrations With Hyperion Planning Part 2).

ODI 12c Split

ODI 12c Split

As we can see a lot of things were made for this version, but all this things makes it unusable. Really, in the old versions I already tried to not use interface, only if was absolute necessary, because they are time consuming, inflexible, hard to maintain and there’s nothing you can’t do in a procedure that is a lot better and faster to create then interfaces, in fact I only use interface when I want to use the CKM to use some constraints for data quality, nothing I can’t do in a procedure, but this is for sure something easier to achieve using interfaces. Despite of that, everything else I prefer to use a procedure, mainly because I can get rid of the models, that looks good, but for me they are the true villain of ODI. Models are hard coded information, and I hate hard code.

In resume, in this new version things that were relative simple to use, now are a nightmare to create. Of course that things will get more visual but the developers will pay a very high price for that cool looking. Oracle just added foolish complexity on things that were simple and that worked very well. Do you want a final example? On prior versions of ODI, you would import all KMs that you needed for that specific project and you would pick one of them from the combo box on the flow tab. If you needed to change it later, just pick another one from that same list and that’s it:

KMs 11g

ODI 11g KM Selection

On version 12, first you need to be on the Logical tab of the Mapping object, click on the target table to get its focus, expand “Target” properties on the right panel and select its target “Integration Type”. This type will filter which KMs you will be able to see in the Physical tab:


ODI 12c KM Selection 1

In the Physical tab, click again on the target table, expand “Integration Knowledge Module” and select one of the KMs of that type that you filtered in the previous tab:


ODI 12c KM Selection 2

And what happens if you want to change the KM? If it is from a different type, first you need to go to the Logical tab, change its type, go to Physical, and select another KM. Ok, they have categorized the KMs and this is a good thing but why they didn’t add the Integration Type in the same tab of the KM selector??? Now we need to go back and forth without any good aparent reason and if you are in doubt on which KM to select and you want to read their descriptions to see which one best fits your needs, then you are totally screwed.

But there are two really cool things about this 12c version. First one debugger! Finally they added a debugger to ODI! This feature was a long waited one because it was simply terrible to debug things in ODI. Now you can go execute the code step by step, take a look in the variables content for that session and you can even query uncommitted data through the transactions:


ODI 12c Debug

Second cool stuff: Roles in Security Module! Again, another long awaited simple feature that did not exist until now. Roles are similar to Groups where the security added to a Role is replicated to all users that belongs to that Role. This is great, because in the old days, Security configuration was madness with a lot of manual configuration. Finally now we will have a better Security framework to work on it.


ODI 12c Roles

Well, there’s a lot of thing to see in this new version yet, but the first thing wasn’t pretty. I didn’t uninstall it yet. Let’s see if we can find anything good that justify this living hell that the interfaces (mappings) turned out to be.

If someone of you learn something different or get a different idea for this new version please let us know because I still don’t believe that these changes happened and this is the way Oracle wants us to work from now on. (By the way the UI for procedures are different too and for now I’ll not say if I liked it or not because normally we need some time to get used to it [but I didn’t like it J]).

This weekend we’ll try a migration and let our impressions here.

See you guys!


10 Important Things to Improve ODI Integrations with Hyperion Planning Part 7 (Smart Metadata Loading)

Posted in EPM, Hyperion Planning, ODI Architecture, ODI Architecture with tags , , , , , on September 13, 2013 by RZGiampaoli

Hello everybody. The time arrived to put some intelligence behind our metadata load. After some years working with Hyperion Planning, ODI and DRM (or any other metadata repository), I figure out that 90% of the metadata does not change in the month cycle maintenance (in a normal Planning application). That means, 90% of the time that a metadata integration takes is useless. It’s a lot of time if you are maintaining a big client as one of mine that the maintenance cycle took more than 8 hours for all their regions.

Luckily for them I figure out a very effective and easy way to decrease that time and now it takes less than 30 minutes for the entire maintenance cycle. Basically I developed a method that categorizes each metadata row in our tables, and based in this category the interface knows what it need to do with that data. Let’s see how it works.

After we have an inbound and extract tables with all metadata from source and target systems (as we saw in the part 5 of our series), we need to compare them and decide what to do with each metadata member. For this tie out process we created the metadata tie out table that is a merge of both inbound and extract tables containing all source and target columns with a prefix identifying each one of them plus a column called CONDITION. This extra column is used to describe what the metadata load process should do with that particular member. It is important for this table to have all sources and target columns because then we can actually see what has changed from source to target metadata of that member.

Metadata tie out process will be responsible to read both source and extract tables and populate the metadata tie out table with all source, extract and CONDITION information. The tie out process has a built in intelligence that analyzes several different load situations to each member and populates the final result in the CONDITION column. The tie out process always searches for a parent/member/application/dimension combination in the source table and match it to the parent/member/application/dimension on the target table. The process uses this combination because these are the information that represents a unique member in Planning.

Here are the possible CONDITION statuses created by the tie out process:


When it happens


All metadata information from the inbound source table is equal to the extract table information, so no further action is needed.

No Match

Any column from the inbound source table is not equal to the extract table information. This member will need to be updated in the target Planning Application.

Exists only in Source

If it is a new member and exists only in the inbound source metadata table it needs to be loaded to the Planning Application.

Exists only in the Application

If a member was deleted on the source system but still remains in the planning application. For those cases we created a “Deleted Hierarchy” member and move the deleted members under it. The process doesn’t physically delete the member to keep the data associated with it intact.

Moved Member

If a member moves from one parent to the other and needs to be updated in the Planning Application.

Changed Attribute member

When one attribute is moved from his parents to another parent.

Reorder sibling members

When a new member needs to be inserted in the place where other member previously belongs or a member changed place order with one of its siblings.

Deleted Share Members

When one shared member stops to exist in the inbound table and needs to be deleted from the Planning Application.

The first four conditions status are achieved by a “Full Outer Join” between the Inbound and the Extract table and a “Case When” to define the CONDITION column as we can see in the below:

Tieout Query example

Tieout Query example

This query compares all metadata columns in the source and extracts tables to see what has changed and adds to the CONDITION column what the load process should do with that row afterwards. For the other four conditions status we need to work in the data just created by the figure 9 queries.

  • Moved Members: When we execute the query from Figure 9 we get an unexpected behavior regarding moved members. A moved member is a member that changed from one parent to another. Since the query compares the member and parent names to decide if that is a new, modified or deleted member, it will consider that the source member is a new member (because it has a new parent) and the extracted member will be considered as a deleted member (because its parent/member combination does not exist in the source) generating two rows in the tie out table instead of one. To solve this issue the tie out process merge those two rows into a single one. This merge happens for all multiple rows that have the same member name but one with “Existing only in Source” condition and another one with “Exists only in the Application” condition;
  • Changed Attribute Member: Attribute members require a special logic because Hyperion Planning treats them differently. When you want to move an attribute member from one parent to another, you first need to delete the member and then insert it back in the new parent. So this is a two-step operation, instead of the normal move member operation. When the process deletes the attribute first Hyperion Planning automatically removes its value from its associated dimension member. If we don’t load the associated dimension members again their attribute values will be missing in the end of the metadata load process. To solve this issue the metadata tie out process searches for all dimension members that have a moved attribute associated with it and change their condition to NO_MATCH. This will guarantee that after moving the attribute to a new parent the process also loads all the dimension members again with its attribute values. Another particularity with attributes is that if an attribute doesn’t exist anymore in the source system it is deleted from the planning application. It is not moved to a deleted hierarchy because no data is associated directly with the attribute member, thus no data is lost;
  • Reorder sibling members: When a single member is added to an existing parent member and this parent member has other child members, planning adds the new member in the end of the list. This is because Hyperion planning doesn’t have enough information to know in which order to insert this new member as it does not have its sibling’s orders to compare to it. So the tie out process also search for all existing siblings of the new member and mark them as NO_MATCH to indicate that they should be loaded all together. This way Hyperion Planning will have all siblings orders and will load the members in the correct order;
  •  Deleted Share Members: If a share member ceases to exist in the source metadata, it is removed completely from the planning application. There is no reason to move them to a deleted hierarchy member because no data is associated directly with it;

When the tie out process finishes populating the metadata tie out table we will have all information to load only the necessary members to Planning. As this table is centralized and has all applications and dimensions in it, it is just a matter to loop it for every application and dimension needed to be loaded by the generic load component. To accomplish this,the next post will show how to make the KM and the ODI models dynamic enought to handle this.

See you next time.

Dynamic topology configuration in ODI without using contexts

Posted in EPM, ODI Architecture with tags , , , , on August 12, 2013 by Rodrigo Radtke de Souza

— EDITED on Aug 12, 2015 —

Great news! Oracle just released “Patch 21156142: Parametric connections in ODI 12c don’t work anymore” which enables this technic to be used in ODI 12.1.3! Thanks Marius for this great tip!!!!

— End EDIT —

— EDITED on Dec 09, 2020—

5 years later, I had to implement this in ODI 12c and I could really test what some of the comments complained below. It seems that all variables works, but the password variable. However, if you use a GLOBAL variable, it works just fine. So, if you are in ODI 12c, please only use GLOBAL variables for this, otherwise you will have an issue in the password variable. Thanks!

— End EDIT —

— EDITED on May 6, 2015 —

Unfortunately, this technic only works on ODI 11 version. ODI 12c does not support dynamic password settings. We will let you know if we figure out a workaround for it.

— End EDIT —

Hi all, how are you doing? I came across this interesting situation during a project some time ago. Imagine the following scenario: You have a transactional system that was implemented in several places around the word. The system and its tables are exactly the same, but they are distributed across different databases, with different user names, database schemas, passwords, etc. Your task is to load data from this system with all these different database regions into your data warehouse, unifying all this data into one single place. Since all the tables have the same structures, how can you accomplish that with ODI without creating a lot of duplicate interfaces?

Everybody that already worked with ODI will easily answer that it may be accomplished using different contexts and they are totally right as we can see below:


In resume, you create one interface object loading a table from the source system into your data warehouse and each context that you have created will point to a different database. It is just a matter to run that interface for each context and that’s it. However, there are situations where we cannot create that many contexts in a real production environment. In my case I couldn’t create those contexts because production execution repository is used for several other projects where those new contexts would just not make any sense at all. In production, the users have just one context called “Production” and they run any ODI job using that context, independently of the project which that ODI scenario belongs to. We could explain and teach the users to use multiple contexts, but my experience as a developer taught me that we need to let things as simple as possible to the end users, because there will be a day (for sure) that someone will execute a job using a wrong context and a mess may be created.

So, how can we accomplish this task without duplicating one interface per database that you need to load? Here is the technique:

Create one physical data server for each database that you need to load plus a dynamic data server:


ADB_AMERICAS, ADB_APJ, ADB_EMEA and ADB_ZZ are the different databases that you need to connect and load data from. Those data servers are created as you normally create any data server in ODI and they will contain their own information regarding connection, data and work schemas and so on. In ADB_DYNAMIC you will create as the following:

Data Server:

Physical Schema:


image4 image5

As you can see, we have added five ODI variables in the topology information. These variables will be used to receive all the dynamic information from the different databases that we need to load. Now create one logical for each physical schema that you have created and point them accordingly in your context. Notice that we are also creating a logical schema called ADB_DYNAMIC that is going to point to our dynamic physical schema.


Now this is an important step. Your models in ODI need to be pointing to ADB_DYNAMIC logical schema to make this work, but if you did not develop your interfaces yet and you also need to reverse your models and so on, you will not be able to do it pointing to this dynamic topology, since it has just ODI variables and not actual values there. So you will need to point your ADB_DYNAMIC logical schema temporally to a “normal” physical schema, let’s say ADB_AMERICAS to develop your interfaces. If you do this way, you will be able to reverse your models using ADB_DYNAMIC logical schema and you will be able to test all your interfaces pointing to one “valid” database without any additional work. After you complete your entire development, just point ADB_DYNAMIC logical schema back to ADB_DYNAMIC physical schema and proceed to the following step.

Now comes the tricky part. In order to make this technique to work, you will need to have a “control/parent” scenario that will call any “interfaces/child” scenarios passing as parameters all the connection information for each different database that you need to load. Let’s see an example:


This child scenario contains all interfaces that will load your DW and it is receiving as parameters all connection information that will be used in ADB_DYNAMIC data server/physical schema. Remember that at this point, all interfaces are pointing to a physical schema that has only ODI variables on it, not actual values, so if you try to execute this package alone, it will not work. Now it is just a matter to call this scenario multiple times, one for each database that you need to load. First create a parent scenario with a procedure that will call those child scenarios as below:


This procedure will have two steps for each database that you need to load. The first step will get one specific database connection and the second step will call the child scenario passing that connection information. It will look like this:


If we open the first step, we will have the following:


This step is creating Java variables that will temporally hold the connection information. These API commands are getting the connection information from the Command on Source tab, so in that tab we will have the following:


We don’t need any code here. We just need the Schema combo box pointing to the desired database, in this case ADB_AMERICAS. This will allow all Java variables to get ADB_AMERICAS information that will be used in the next step which is the following:image11

This step will call the child scenario passing as parameters all the connection information that is needed to be used in ADB_DYNAMIC connection. When this child scenario runs, all interfaces inside of it will use ADB_AMERICAS connection information and will load data from it dynamically. Cool isn’t it? You are going to do the same configuration for the other steps in this procedure and the only thing that is going to change is the logical schema that you need to point in the “Command on Source” table. So “Get ADB_APJ Connections” will get ADB_APJ connection information and so on.

If you notice, this example is calling the child scenarios using SYNC_MODE=2, which means that they will execute the data load in all different databases in parallel, so if your architecture allows such parallelism, you may gain a great performance boost using this technique as well. We also added a “Wait for child scenarios” step as the last step, so we may control when all of the child scenarios have completed or not:


So here is the end of this post. I hope you all enjoy!

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 6 (Metadata validation when loading data to Hyperion Planning)

Posted in EPM, Hyperion Planning, ODI Architecture, ODI Architecture with tags , , , , , on July 25, 2013 by Rodrigo Radtke de Souza

Hi all! It’s good to be back! As we have seen in our last post, we can easily extract all existing metadata information from any number of planning applications, but what can we do with all this information? Well, we can do a lot of great stuff. One of them, as mentioned in the last post, is to compare the existing metadata information to the new metadata that we will load to planning and load just what have changed. This will be covered in details in a later post. Today I’ll be talking about a simpler but very powerful usage of this existing metadata information: metadata validation when loading data to Hyperion Planning!

Everyone that has loaded data into Hyperion Planning using ODI already passed through this situation: you get some data to load into planning and let’s say that this data load takes five minutes to complete. You are happy, the business team is happy, but for some reason in a random day the data load takes six hours to complete. The end user complains, you go check the logs and you find something like that:

2009-07-30 18:00:52,234 DEBUG [DwgCmdExecutionThread]: Error occurred in sending record chunk…Cannot end data load. Analytic Server Error(1003014): Unknown Member [ACT001] in Data Load, [0] Records Completed
2009-07-30 18:00:52,234 DEBUG [DwgCmdExecutionThread]: Sending data record by record to Essbase

This error happens when you are trying to send data using a member that is not part of your outline. ODI is great to load data into Hyperion Planning, but it has a weird behavior when you have an unknown member in you data load. In a perfect world, ODI reads its source database, gets a big chunk of data and sends straight to Essbase. Essbase process it, sends and OK to ODI and ODI sends another big chunk of data. This works pretty fast because it loads big chunks of data at a time, but if you have an unknown member in the data load, Essbase will send to ODI an error stating that there is one unknown member in that data chunk and ODI will switch to “record by record” mode. In this mode ODI will not send a chunk of data but it will send record by record to Essbase and this may take forever depending on how much data you have to load.

I don’t really know why ODI behaves like this, but this is what happens in reality. To avoid that we have a very powerfull technique that we will always talk about: metadata from planning repository. We already know how to read all existing metadata from a Planning application, so it is just a matter to compare all members that we will be sending to Hyperion Planning against all existing metadata in that application prior to the load. This will guarantee that only existing members of that application will be sent to Essbase, insuring that ODI will not flip to “record by record” mode.

How can we accomplish that? We have a lot of possibilities as it depends on the current database structure and tables that your system has, but I will display one that is generic enough to be applicable to any system.

1) Create a table with the following structure:


You may want to add more columns to this table if you need, but those should be fine for our example. This table is used to store all existing metadata from any number of Hyperion Planning applications that you may have. You may populate it using the techniques seen in our last post.

2) Create an inbound table containing the data that you will send to Hyperion Planning. This table will contain one column for each dimension that you may have plus a column named “DATA” that will contain the value of that intersection and APP_NAME that will contain the name of the Hyperion Planning application which that data will be loaded to. Here is one example:


Why do we need to create this table? As I said, there are many ways to do this verification and maybe you may not need to create it, but I strongly recommend doing so. The reason is that you create a data layer between your source system and Hyperion Planning that is centralized in one single point where you can have data for all applications that you may have (you may partition this table by app for example) and you may add centralized ODI check constraints in one single table as we can see below.

3)  Create ODI check constraints to validate all dimension members. For each dimension column in your INBOUND_GENERIC table, you will create an ODI check constraint that will validate those members against the existing metadata in that application. Let’s use ACCOUNT as an example:


Go to INBOUND_GENERIC model in ODI and add a New Reference constraint. Change the type to “Complex user reference” and select your model that contains the TBL_EXISTING_METADATA table.


Go to Expression tab and add your constraint SQL there as below:


Here we are comparing all members in ACCOUNT column in our INBOUND_GENERIC table against all ACCOUNT members in TBL_EXISTING_METADATA table that has a specific PLAN_TYPE and DATASTORAGE. Again, this is just an example and you may tweak it to your reality. You will do this to all dimensions that you may have and you may also add other constraints as duplicated keys, invalid amounts and so on:


4) The last part is just a matter to select a CKM in your ODI interface that will load the INBOUND_GENERIC table and see the results. You will have INBOUND_GENERIC table with only metadata that exists in your Hyperion Planning application and an E$ table (created by ODI CKM) with all non-existing members in your outline!


Now you may load from INBOUND_GENERIC table to Hyperion Planning application with the guarantee that it will always run fast without worrying about unknown members in your outline. Also as a bonus, you have E$ table with all members that are missing in the outline, so you may use it to warn the end users/support team and so on.

I hope you all enjoy!

Optimization Context in ODI

Posted in ODI Architecture with tags , , , , , on December 19, 2012 by Rodrigo Radtke de Souza

Hi all. I’ve decided to take a little break in our Hyperion Planning series to talk a about a “confusing” (and underestimated) feature in ODI: Optimization Context.

Optimization Context

This is a very important feature but few people know how to use it correctly.  This setting is used in design time to make ODI aware of how it should build its code. Let’s take a look at the following example:

Data Servers

For some reason (budget constraint, laziness, etc) the sales database schema that you want to read is in the same data server then your DW schema that you want to load in development. In production the situation changes and you have two separated data server, one for each schema.

Let’s say that we have two logical schemas, one for Sales (LOGICAL_SALES) and another for DW (LOGICAL_DW). In Development we created the following Physical Topology:

Dev Servers

In Production we have this:

Prod Servers

I will not go through all the small steps to replicate this here, but we need to set up the Physical/Context/Logical information, create the data models and reverse the tables. When we build the interface, things start to get interesting. Let’s take a look at the interface using the “Development” context in Optimization Context:

Flow Tab

In the Flow tab we have only one IKM to select because in the Development context our source and target physical schemas reside in the same data server. ODI uses the Optimization context to figure out how it is going to build its flow tab. Let’s change the Optimization context to Production now:

Optimization Context

Flow Tab

Well, it changed. Now we have one LKM and one IKM. Again ODI checked the Optimization context to build its flow tab. As Production has two separate data servers, ODI added a LKM to be able to load from one server to the other. So the Optimization context is the key parameter to let ODI know how to build its code. And it can be very tricky.

Imagine that we change back to Development Optimization context. Now we have just one IKM in the flow tab. Let’s save it and generate a scenario with that interface. If we run it using the Development context it will work fine because both schemas are in the same data server. But what happens if we run it using the Production context?

Operator Code

Operator Code

It failed. If we check the steps, we have just one “Insert new rows” that tries to insert into DW schema using a select in the Sales schema:

Operator Code

As in Production the schemas are in separated data servers and ODI created its flow tab using the Development Optimization context this code will never work. If we change the Optimization context to Production, regenerate the scenario and run it again, it will create additional LKM steps and it will work fine:

Operator Code

It is worth mentioning that the opposite is also true. Imagine that in Development you have different data servers, but in Production you have only one data server. If you use Development Optimization context ODI will create its code with a LKM and it will add extra steps that would not be necessary in the Production environment. This wouldn’t give you any error, but these extra steps will be redundant and will result in less performance.

So this concludes our post. Prior to select your Optimization context, find out which is your topology in all environments. In this way you will be sure that ODI will create the correct code for all environments without any extra steps or errors.

See ya!