Business Intelligence – Oracle

Archive for December 30th, 2008

Oracle Data Integrator – Configuring Metadata Navigator & Lightweight Designer

Posted by Venkatakrishnan J on December 30, 2008

I had the opportunity to dedicate some of my time on Oracle Data Integrator today. Oracle Data Integrator is a very interesting ETL tool since it is based on a ELT architecture like Oracle Warehouse Builder. But apart from that, the similarity between these 2 tools end, both in terms of product usage and also in terms of the product related jargons. Now that there are plans to introduce ODI as the back end ETL tool for loading data into Hyperion Financial Management, Hyperion Planning, Hyperion Essbase(these 3 already exist now), BI Applications, my interest in this tool grew a bit further. I have worked with both Informatica and Oracle Warehouse Builder in the past and hence it was very interesting prospect to analyze the similarities and differences across these toolsets. My intention is to primarily cover the connectivity to HFM, Hyperion Planning and Hyperion Essbase. I would be covering these in the future blog entries. For an introduction to this tool, i would recommend Mark’s series of blog entries here. But in today’s blog entry we shall be covering the new feature(web based metadata navigator & Designer) that was introduced in the version of Oracle Data Integrator. Though the documentation is technically correct, certain details were missing. In this blog entry, i would briefly explain how to go about configuring these 2 pieces as part of Oracle Data Integrator installation.

Pre-Requisites(for the below set of steps to work):

1. Oracle Data Integrator should have been installed
2. The master repository should be created on an Oracle Database
3. A sample Work Repository called WORK REPOSITORY should have been created.
4. A sample OC4J instance. If you have BI EE installed, then just use that instance itself. These navigators are pretty lightweight.

Configuring Metadata Navigator:

Configuring metadata navigator is straightforward. All we need to do is to deploy the corresponding War File in the OC4J instance.

1. Open the OC4J console and deploy the oracledimn.war(from the installation setup/manual folder) in the OC4J container.
2. Use oracledimn as the application context and click on next.

3. Just deploy the war file with the default configurations.

4. Now we should be able to open up the Metadata Navigator console. The link to be used is http://<hostname&gt;:<port>/oracledimn

5. Ensure that you have configured your work repository. Also, ensure that you are able to login to the work repository using the Designer java client. In order for the online web app to use the same connect repository details, just copy the {OracleDI_HOME}\oracledi\bin\snps_login_work.xml file to {OC4J_HOME}\j2ee\home\applications\oracledimn\oracledimn\WEB-INF directory. This will ensure seamless login of the metadata navigator into the repository.

6. Restart OC4J. Now, we should be able to browse the metadata navigator.

As you see, we have the entire metadata repository(Topology) in the web based console. One very good thing is that this is very lightweight and is much easier to configure than the OWB metadata repository. Currently this is just a navigator. Online updates are not supported. This i believe is a big difference between Informatica and the 2 ELT based tools. Informatica currently supports online updates to the repository, Integration Service etc.

Configuring Lightweight Designer:

Configuring Lightweight Designer involves more detailed configuration than the Metadata Navigator. Some of these steps are not explained in detail in the configuration guide. Lets go through the steps one by one.

1. Deploy the oracledilwd.war in the same OC4J container as above. Use oracledilwd as the application context. Ensure that you are able to access the login page of the online designer using the URL http://<hostname&gt;:<port>/oracledilwd

2. Since we are using Oracle as the Repository database, we need to copy the jdbc jar file of Oracle to this application. So, copy the ojdbc14.jar from {OC4J_HOME}\j2ee\home\applications\oracledimn\oracledimn\WEB-INF\lib to {OC4J_HOME}\j2ee\home\applib. The ojdbc14.jar would be deployed automatically when we installed the metadata navigator above. If you do not have this navigator, then you can get the jar file from the ODI or Oracle DB installation directory.

3. Restart OC4J and open up the OC4J enterprise manager. Go to the Administration tab and go to the JDBC Resources configuration screen.

4. We need to create 1 connection pool and 2 data sources. Let us start with creating a data source first. Give the name for this connection pool as ORACLE_MASTER though it does not matter in this case.

Ensure that you are also providing the schema details of the master repository and test the connection. It should be successful. This will validate the jar file copy that we did above.

5. Now, we need to create 2 data sources. One with the name ORACLE_MASTER and the other with the name ORACLE_WORK. Ensure that the details for these data sources are as shown below. The JNDI path is very important. If this is wrong then the connection would not happen.

Do not worry about the name Lightweight Designer Application for the connection pool in the above screenshots. The name of connection pool can be anything. In you case, it would be ORACLE_MASTER.

6. Go to {OC4J_HOME}\j2ee\home\applications\oracledilwd\oracledilwd\WEB-INF and edit the Web.xml file. In that XML file add the below XML directives.

				OracleDI Local Repository
				OracleDI Local Work Repository

7. The above directives basically point to the JNDI path that we specified in step 5 above.

8. Go to {OC4J_HOME}\j2ee\home\applications\oracledilwd\oracledilwd and edit the Repositories.xml file. Add the below XML directive to the file.

	<login name="OracleDI Local Repository">
		<master name="jdbc/ORACLE_MASTER" masterDriver="oracle.jdbc.OracleDriver"/>
		<work name="jdbc/ORACLE_WORK" workName="WORK REPOSITORY"/>

Ensure that the Work Repository name provided in the above directive matches with your Work Repository name.

9. Restart OC4J. Now you should be able to login to the Lightweight Designer application.


Posted in All Posts, Oracle Data Integrator | 4 Comments »