Oracle BI EE 11g - Parent Child Hierarchy Display & Multiple RPD's in one BI Server

In this blog post, we shall see how to overcome two common issues that we would typically face after upgrading to 11g. The solutions for both the issues are straight-forward but i thought it made sense to have a separate posting for this due to the common nature of both the issues.

Parent-Child Hierarchy Display Columns: BI EE 11g provides the ability to report directly on parent-child hierarchies. But if you look at the way in which we have to configure a parent-child hierarchy, we can either have ID columns or Description columns but not both. For example, lets take the example of the EMPLOYEES table in the HR schema. Shown below is a simple parent-child hierarchy closure table on the Employees table.

This closure table can either take Employee ID values for ANCESTOR_KEY and MEMBER_KEY columns or Employee Name. But there is no option to have both i.e. we do not have an ability to use ID as the KEY but use Name as the display column in the BMM layer.

So, if we use the Employee ID & Mgr ID for building the Parent-Child hierarchy, we will only see the IDs in the parent-child hierarchy as shown below

But the common requirement is to use the IDs in the BMM layer but for display we need to use the Employee Name instead. To enable this, BI EE 11g supports display columns for any hierarchy in the Presentation Layer. So, in the presentation layer, change the Display Column from Employee ID to Employee Name.

This will automatically show the hierarchy using Employee Name.

If you look at the underlying query, the Employee ID will still be used for making the hierarchy traversal. But the Employee Name will be used just for display.

Multiple RPD's in one BI Server:

BI EE 10g supported a way of serving multiple RPD's through one BI Server. It was done by updating the NQSConfig.ini file. Though a presentation server could connect to only one online RPD, other client tools can connect to different RPD's and this is still used by some customers. After upgrading to 11g, this way of adding new RPD's to the BI Server does not seem to work anymore. We shall see what changes we need to make to get this to work.

We start off with having 2(or more) repositories in the Repository directory of the BI Server. In my case, i will be hosting WCA.rpd and the SampleApp_BI0006.rpd repositories through the BI Server.

Open up the NQSConfig.ini from {Middleware_Home}\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1 and add the following entry.

WCA = WCA.rpd;

The default repository entry, Star = SampleApp_BI0006.rpd, DEFAULT; is managed by Enterprise Manager. If you want to make a change to this entry, always do the change through Enterprise Manager. But the other entry we added above is not managed by EM. So we can edit this entry. Also, this entry will not change after a server restart as it is not managed by the Enterprise Manager.

In 10g, the above change was sufficient to get the BI Server to host both the RPDs. But in 11g, if we now try to restart the BI Server, we would get an error as shown below.

The reason for this is, in 11g the encryption passwords required to authenticate against the repository need to be accessible from the Credential Framework. So, we need to add the encryption password of the WCA.rpd repository to the Credential Framework. To do that we go to the Credential Framework from Enterprise Manager.

If you notice, under the oracle.bi.enterprise credential map, all the repositories that we deployed from Enterprise Manager already have an entry there.

To add an entry for the new WCA repository, just click on new Create Key and enter tempvalue as the username. Enter the encryption password of the repository as the password. In the key field enter repository.WCA as the value.

Basically the key suffix should have the repository name (without the rpd extension). After adding this, you should be able to restart the BI Server. Also, you should be able to connect to this repository from the DSN.