Tag Archives: odi

Flipping an ODI model to a different technology and kicking the interfaces

One of my recent ODI projects is a relatively complex transformation job. I am effectively building up a master/detail set of records from a single table. The single table isn’t really a single table in the source, it’s multiple tables. Within ODI I make several passes on it, dialing in the fields with interfaces and procedures. I opted to use the in-memory engine (MEMORY_ENGINE) because I thought the architecture would be a little cleaner, and the amounts of data being pushed through are not huge.

Everything was fine, until I hit a legitimate ODI bug. I actually found a relevant case in Oracle support for it: ODI-1228 “statement is not in batch mode”. There was even a patch! Unfortunately, the patch required a version of ODI higher than what I had available. So on a tight deadline my choices were to push through an ODI upgrade or to find some workaround.

I decided to see if I had a low-cost option of switching from using the memory engine to just using an Oracle schema as a stage (note that the package and interfaces themselves are all just moving data between various Oracle servers, nothing Hyperion related even). So I went into my model for the staging table that I was using, and just switched it from the In-memory Engine to Oracle (using the drop down). No complaints from ODI there.

Next I went into one of the interfaces that was previously setup to have source/staging/target in terms of its whole process. I went straight to the Flow tab but had some issues and fun little NullPointerException errors which is always a fun time. The thing is, I changed a technology on a model being used in various interfaces but it’s not like any part of ODI went into those interfaces to say “Hey, this changed…” – in fact, when you change the technology of a model, ODI helpfully and plainly just says “Hey, this is likely to break stuff. Proceed at your own risk… THAR BE DRAGONS.” Or something like that.

Anyway, I found that I could sort of ‘kick’ the interface when I opened it, by checking the “Staging Area Other Than Target” option, then turning it off again (it was off in most of my interfaces). This forced the interface to sort of recalculate and reset the flow, which took into account the updated technology of the model. There might be a better way to do this rather than this “fuzzy” method, but it worked and I didn’t have to redo the plethora of interfaces in this package.

Hopefully this helps someone else out someday!

Using ODI error tables (and tables with dollar signs in their names) as models for transformations

I setup a solution awhile ago where ODI facilitates data movement from an ODI error table into another data store. As you may know, ODI has a naming convention where its temporary and work tables are prefixed with some combination of a letter (or two), a dollar sign, and perhaps an underscore. So if my original table in a relational data store is CUSTOMERS, then the error table for this table would be E$_CUSTOMERS.

So of course I fire up my RKM to build out the model metadata for these new tables, just like I normally would. Everything goes fine but when I go to build the interface and run it, things don’t work so well. A dollar sign ($) is a very common character for scripts and programming languages to indicate that variable interpolation should occur (in other words, to fill in a placeholder). For example, if the variable $color = “orange” and you interpolate a string such as echo “My favorite color is $color!” then the result is “My favorite color is orange!” Nothing too fancy here.

That being said, sometimes a dollar sign can wreak havoc when used where some code doesn’t expect it. I’m not sure if it ever got fixed, but I developed at a place once that would dole out Active Directory IDs based on letters from the first and last name – three letters from each, to be precise. But people with very short last names would have dollar signs appended to them. There was a bug in Hyperion where you couldn’t login with one of these user names, likely because it just never got tested (oops). I’ll assume that’s fixed now.

In any case, back to ODI. In a given ODI datastore, you have the name, resource name, and alias name. Name is typically how you see it in the ODI design context. Resource name is the actual resource in the schema/database. Alias is used by ODI in interfaces as, well, an alias. I didn’t record the exact error message but given that my interface was failing and the output in Operator was a bit odd, I immediately suspected the dollar sign throwing things off either for ODI’s generated code, on the target system (and Oracle database server), or both.

My quick solution? Just replace the dollar sign with an underscore in the alias in the datastore. You can delete the dollar sign entirely but I decided to just go with an underscore. By making the change in the datastore, subsequent interfaces leveraging this datastore will also benefit from the fix, as opposed to trying to adjust just the one interface. Of course, leave the resource name alone (and the name) since they don’t need to be changed (and in the case of the resource name, shouldn’t be changed).

Another approach to all of this would be to change the physical topologie’s naming convention so that error tables don’t have a dollar sign (or other entities that ODI names) but I quite like ODI’s preferred naming strategy (for the most part).