Business Intelligence – Oracle

Oracle Data Integrator 10.1.3.5 – Connectivity to Open LDAP of Shared Services

Posted by Venkatakrishnan J on June 9, 2009

One of the features of Oracle Data Integrator is its ability to connect to a lot of disparate data sources using JDBC. One such feature is its ability to expose any LDAP directory as a relational source. If you are on earlier releases of Hyperion EPM like 9.3, where there is no out of the box SSO and authentication/authorization capability to BI EE with open LDAP, one approach is to configure BI EE to authenticate against OpenLDAP and then get the user-group information from some other custom table(or by using the DBMS_LDAP package). I had shown how to configure BI EE to authenticate against OpenLDAP here. Since BI EE cannot automatically pick up the groups directly from OpenLDAP in prior releases, one way is to get the user-group related information from OpenLDAP and then populate that into a set of custom tables. Then BI EE can be used to get these groups from the custom tables. The architecture would look something like this

image

Lets look at what it takes to setup the OpenLDAP connectivity from ODI. As a first step lets first log into Topology Manager and create a new LDAP connection. Choose the “Sunopsis JDBC Driver for LDAP” as the JDBC driver

image

And then choose the JDBC URL.

image

To enable the connectivity to any LDAP directory, the password would have to be passed in an encoded format. To encode the password, run the below command from a command prompt.

 
java -cp {OracleDI}\oracledi\drivers\snpsldapo.jar 
com.sunopsis.ldap.jdbc.driver.SnpsLdapEncoder
<the of password root openldap>

image

Copy the above encoded password. In the JDBC URL, enter the below URL

 
jdbc:snps:ldap?ldap_url=ldap://localhost:28089/ &amp;ldap_password=KILAKMNJKKLHKJJJDDGPGPDB
&amp;ldap_basedn=dc=css,dc=Hyperion,dc=com 

image

The basedn above is what would be used for searching all the users, groups, roles etc. In the Data Server definition, enter the username as root user who has traversing access to the entire OpenLDAP directory

image

You should be able to test the connection to the LDAP from here. The root user of OpenLDAP is different from the admin user. In fact, the admin user’s original cn is not admin. It is 911. admin is the givenName attribute of the 911 user. The root user password is by default root. One behavior that i noticed across the releases, was the fact that in 9.3 release admin user had the traverse directory privilege. But in EPM 11, 911 user does not have the traverse directory privilege. In my case, the default root password did not work. So, i had to reset the root user password from shared services.

image

As a side note, if you feel that shared services web console does not give you the actual LDAP directory structure, i would recommend a free LDAP client like JXplorer. The screenshot of shared services OpenLDAP using this free client is given below

image

Now, if you go to the Designer and reverse engineer this data source using selective reverse.

image

image

This should convert the entire directory structure to a relational format. From this point onwards, its a matter of building the interfaces and loading the custom user-group tables. Though the setup of the above is pretty straight forward, this can come in very handy especially when you are trying to consolidate/report against multiple user sources.

One Response to “Oracle Data Integrator 10.1.3.5 – Connectivity to Open LDAP of Shared Services”

  1. thanks for the post- sure people will find it really useful🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: