The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Data Store and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.
In a previous post I have described how to create in Topology Manager a connection to an Oracle Business Intelligence (OBI) Server. Now we will see how we can use Oracle BI Server as a data source in an ODI process.
Creating a Model for Oracle BI Server
The first step is to create a model in Designer.
Click on the Insert Model button
Give it a name, I used OBIEE_EXAMPLE for this post.
Set the Technology to Oracle BI Server and the Logical Schema to the one you created in Topology previously, I used BIEE_LSCHEMA.
Now click on the Reverse tab and set the Context to your default context, I used Development in this post.
Click on the Selective Reverse tab then click on the Selective Reverse checkbox and finally click on the Objects to Reverse checkbox, a list of tables should appear. Pick all the tables you are interested in and then click on the Reverse button to start the reverse engineering process.
Verifying your Oracle BI Server Datastores
You should now see a list of datastores under your OBIEE_EXAMPLE model.
Right-click on one of them and select View Data to display the content of the BI Server artifact and make sure everything is set up correctly.
Prior to using those datastores in your interfaces make sure that the columns datatypes are set correctly. If a datatype is missing, open up the datastore, go to the Columns tab and select the corresponding datatype in the drop-down list.
You can now use those data stores in any ODI interface.
The LKM SQL to Oracle can be used to load data from OBI Server to an Oracle database, for other databases use the appropriate Loading Knowledge Module for that technology.
If you have issues joining columns with spaces, make sure that Object case-sensitive is enabled for the Oracle BI Server technology in Topology Manager.