Business Intelligence – Oracle

Archive for the ‘Hyperion Shared Services’ Category

Oracle Data Integrator – 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


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


And then choose the JDBC URL.


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 
<the of password root openldap>


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


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


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.


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


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



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.

Posted in All Posts, EPM, Hyperion Shared Services, OBI EE Plus, Oracle Data Integrator | 1 Comment »

Oracle BI EE – Shared Services Integration Part 1 – Connecting to Shared Services OpenLDAP

Posted by Venkatakrishnan J on July 7, 2008

Another interesting question came in our internal forums today wherein a user was trying to authenticate BI EE against shared services. As you might probably know, shared services uses a LDAP called as openLDAP to store all the users, groups and the provisioning details. If you had looked at my blog entry here and here, i would have shown how to go about authenticating against OID using BI EE. Lets look at achieving the same using Shared Services openLDAP. I am not sure whether this is supported, but there is no reason why this would not work. First lets start with the list of users in shared services. In my case, i have 3 users as shown below

Now, openLDAP uses the port 58089 (like 389 for OID). So, lets go into OBI EE and create a new LDAP connection called as shared services.

The toughest part in getting this to work is in identifying the BaseDN and the corresponding BindDN. BaseDN is nothing but the root from which the LDAP will start searching and BindDN is the exact username with which it will bind to the LDAP. So, in order to find the base DN, go to {Hyperion}\SharedServices\9.3.1\openLDAP and open the file openLDAP.log. Here you would find all the DN’s for all the users. Search for “givenName: admin”.

As you see above, the BaseDN for openLDAP is ou=People,dc=css,dc=hyperion,dc=com. And the BindDN would be cn: 911

In the password textbox, enter the password for admin user (password by default). Also, in the advanced tab change the user attribute to givenName.

Now, BI EE would be able to connect to openLDAP successfully.

As a next step, lets try importing the users.

As you see, the connection is pretty straight forward to achieve once we have the BaseDN and the BindDN properly figured out. But the major drawback currently is that if you are using Essbase as a data source and Essbase is using the shared service for authentication, there is no single sign on i.e BI EE cannot authenticate a user into shared services as well as Essbase. One needs to explicitly setup the connection pool properties of Essbase which would be one more layer of un-necessary authentication. But apart from that the integration would work seamlessly.

Posted in All Posts, Hyperion Essbase, Hyperion Shared Services, OBI EE Plus | 3 Comments »