ODI Series Part 3 - The configuration continues

Part 3 has arrived and I can go through the remaining configuration elements inside the topology manager. The goal is to be able to load/extract hierarchy details from files and csv files and database tables into Planning and essbase, to do this we need to set up connection details to essbase , planning, sql database and a file store. It is going to be screen shot heaven today as sometimes it explains much than words can.

Fire up the topology manager and straight into the Physical architecture tab. I will go through each technology in alphabetical order so we start with the file set up.
If you going to write to or load from files in ODI you need a define a physical location, for this exercise the files are going to be in E:\FileStore

If you expand File you will see that there is already Data server created called FILE_GENERIC, I am going to ignore that and set up a new one.

Right click and “Insert Data Server”
(A physical component that stores and returns data is defined as a Data Server . A data server is always linked to a single technology.)



Give the Data Server a name



Added 28/09/08. For the JDBC tab you need to have the following driver settings :-



Once you hit apply the physical schema window will appear, this is where you enter details for the location of the files and set up the context details

An ODI Physical Schema corresponds to a pair of schemas:
• A (Data) Schema, in which Oracle Data Integrator will look for the source and target data
structures for the interfaces.
• A Work Schema in which Oracle Data Integrator can create and manipulate temporary work
data structures associated to the sources and targets contained in the Data Schema.

You need to manually enter the location into the Directory (Schema) and the Directory (Work Schema), most screens in ODI are default screens so no matter what technology you are using you will be presented with the same options, so don’t worry if you keep asking the question Why?

I have not found a requirement yet to use different values between the data and work schema.

There is no such functionality as browsing to the file location so make sure you enter the details correctly.



Next you need to point it to a context, setting up a context was covered in the last part. Add a new context, you will notice that the context that was set up earlier is populated as we made it default, if you have a number of contexts you can manually select a different one. Finally we need to provide a schema name where information will be stored in the repository.



Onto Essbase, I am going to set up a connection to Sample/Basic, it is pretty much the same as before only when you insert the data server you will have to enter the essbase server connection details.



One thing to be aware is not to hit the test button; this is only for use with JDBC connections so will fail for products like essbase/planning.

Filling in the physical schema is all manual, so don’t expect the drop downs to populate with applications and databases. This is one of the problems with ODI you never really know if you have got things right until later on because you can’t test and there is no auto populating.





Setting up the planning details is pretty much similar but you must put the RMI port next to the server details, the default port is 11333. I am setting up a connection to my sample application (plansamp).







The next technology to set up is a connection to a database so I can access and write to tables, for me it will be the Microsoft SQL Server technology but it is very similar for Oracle set up. I have set up a database called HypData. Once again it is pretty much the same sort of setup but this time you enter the JDBC connection details and yes you can even hit the test button.





As this time we are using a JDBC connection when you set up the physical schema you can don’t have to manually enter information the drop down boxes are auto populated, the only difference is you have to select the database name and the owner.



Create a name for the logical schema



Ok, the final technology we have to make changes to is the “Sunopsis Engine”, this is the technology that can be used when you are transforming data, so for example from a fields from a flat file and mapping to a planning fields.
The technology is already set up and you just have to point it the context to it



Well the configuration is done for what we trying to achieve, depending on how you are going to use ODI will no doubt increase the number of technologies you will use, it is a pretty powerful tool and we are just touching on the surface that may suffice for most users.

That concludes today’s session, next we will move on to the real centre of the product the designer, where we create the interfaces to move the data to and from planning/essbase.