While preparing the ODI lab for KScope12, I came across an issue with the Hyperion Planning IKM. I implemented an interface which loads metadata from a database table to a Planning application. So, as always, I dragged & dropped my source and target DataStores into my interface, mapped the source columns to the target columns and then went on to selecting the KMs on the Flow tab. Those of you familiar with ODI and the Hyperion KMs know that it's important to set the "Staging Area Different From Target" option on the Definition tab. If this option has not be set, the IKM SQL to Hyperion Planning does not appear in the IKM dropdown.
However, in ODI 11.1.1.6 even after setting the Staging area to either the Memory Engine or a custom staging area, I am not able to select the Planning IKM. I haven't seen this in previous versions, so this seems to be new to 11.1.1.6.
I was wondering if this behavior is the same with other KMs which require a Staging Area different from the Target, so I built a quick interface to write data from the database table to a flat file, using IKM SQL to File Append. This was working fine, though, and I was able to select the IKM from the dropdown list. This confirmed that it must be something specific to the Planning IKM (and potentially also to others, but we will discuss this in a moment). If I'm not able to select the Planning IKM although everything has been set up as needed, there must have been a reason why it was not selectable.
The mechanisms which define the KMs which ODI offers in the dropdown are tied to the technologies of the Interface's Source, Staging Area and Target. So I checked the definition of the IKM and found that the Source Technology of was set to Generic SQL.
At a first glance, this seems to make sense because the usability of the IKM should not be limited to a certain database technology like Oracle or SQL Server. However, ODI is very strict about the selected technologies and since I was using a DataStore of technology Oracle, this KM is not available in my interface.
Now there are two things you can do:
- Align the Source Technology with what you are using (in my case Oracle)
- Set the Source Technology to <Undefined>
This little change resolves this bug - unless you have defined your Data Server of technology Generic SQL because then it would have worked fine in the first place. So after I change this setting, I'm able to select the IKM as expected.
The last thing I wanted to know was why this is just the case in 11.1.1.6. Potentially it could have been related to a change in the underlying logic of how ODI determines the available KMs. Since this would be hard to find out, I decided to start going the easy route and compared the IKM Source Technology between version 11.1.1.6 and 11.1.1.5. As you probably already assumed yourself, the IKM in 11.1.1.5 does not define a Source Technology either.
So what did we learn: if you are using the IKM SQL to Hyperion Planning in ODI 11.1.1.6, you can resolve the issue described above by modify the Source Technology. Another thing that we know now is that Oracle is still working on the Hyperion KMs and might come up with some cool new features for us in the future!
