Recently I was working with Oracle Business Intelligence 12c on a project where Hyperion Planning was used as a data source.In previous OBI releases, the usual way to connect to Hyperion Planning was to connect to its Essbase cube. In general this worked fine, but as some of readers might know, Essbase cube usually doesn't contain certain information that is a property of Hyperion Planning application.
For example, comments are very important for users of Hyperion Planning, but when you report on Hyperion Planning's Essbase cube, how would you display comments that users enter into planning form? This was theoretically possible, but required a major workaround. To be honest, I haven't done that. But with OBI 12c this is very straight forward. Comments are seen as attributes in OBI's subject area, so it can be included in reports as any other attribute.
This particular customer had specific requirement to show commentaries along side with numbers. Since we were testing OBI 12c as well, we tried this new connectivity. So let's examine what needs to be done to display Hyperion Planning comments in your reports.
On our demo system I have installed EPM 11.1.2.4 (incl. Hyperion Planning) and OBIEE 12c. (We ran into some issues with Planning forms when we tried this integration for the first time. If anybody has similar issues, you can contact me through comments below.)
Setting connection pool and importing metadata for Hyperion Planning
Before anything else, ...
... there is something that needs to be done first on the system side. In Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition you can find quite a lot and useful information about what needs to be done for the integration between OBI and Hyperion Planning to work, but on page 5-26 you can this prerequisite:For Hyperion Planning 11.1.2.4 or later, the installer does not deliver all of the required client driver .jar files. To ensure that you have all of the needed .jar files, go to your instance of Hyperion, locate and copy the adm.jar, ap.jar, and HspAdm.jar files and paste them into MIDDLEWARE_HOME/oracle_common/modules.
Once you've added .jar files ...
... to required location, you can now open OBI Administration Tool and create a new database as you would normally do.
In the first step you have to specify database name, location and password.
Then you specify detail about your table space. Here you need to select Hyperion ADM as a Connection Type and Hyperion Planning as a Provider Type. Simply select options from pull down lists.
In the URL field you have to specify connection string for connection to Hyperion Planning application. In my case, this string is:
adm:thin:com.hyperion.ap.hsp.HspAdmDriver:qubixepm%3A9000:DEV_CAM
Please note that before port, in this case port is 9000, there is a string '%3A'. Normally you would expect that there is '/', but this is a known bug. So use '%3A' instead of '/'.
Enter username and password that you use for connection to your Planning application and continue with wizard.
Move required cubes into repository view and continue.
When you're done, you can investigate what and how was imported. You can see that there are already some attributes created, not just value. For example, TextValue holds comments.
All dimensions are imported as Parent-Child (Value) hierarchies. Parent Key is used to link parent with child records.
To complete your repository, you can simply drag physical model to logical/business and logical model to presentation layer to create Subject Area.
Test your work
When you save and check in changes, OBIEE 12c is now ready for testing. Before doing that, let's just check the comments entered in Planning application.
Now, when we know where comments are stored (at which slices), we can test and view comments entered in Planning application in OBIEE.
In planning form we use Commentary column to store textual comments. You can see two dummy comments as we want to demonstrate functionality of showing comments.
In OBIEE, the following query can be created now:
The query above can now result in the following view:
As you can see, captured commenta are now displayed using TextValue column.