Business Intelligence – Oracle

Archive for July, 2008

Hyperion Financial Reporting 11.1 – Annotations and Cell level Commentary

Posted by Venkatakrishnan J on July 22, 2008

One excellent feature of the new EPM 11.1 release is that one can now add cell level/report level comments in Hyperion Financial Reporting. Since Hyperion Financial Reporting(HFR) is primarily used for creating financial reports out of Essbase, this feature makes a lot of sense wherein users can add attachments/comments to a report which other users can see & comment/reply upon. For example, consider a scenario wherein there are 2 users admin1 and admin2 who are analyzing the same HFR report. Consider admin2 to be a financial analyst and admin1 to be an Essbase/HFR Administrator. Now, lets assume that the HFR administrator has created the below shown report based on the DMDemo data which one would get by default after installing EPM 11.1.



As you see, the above is a very simple report wherein users can analyze the product sales across different years for different scenarios. Now, once this report has been created admin1 would then publish the report out to admin2 so that he/she can do the analysis on the report. Admin2 user, after doing an analysis realizes that there is a huge mismatch in the data. There are some negative numbers which is not possible since sales can only be positive or zero. In a normal scenario, if admin2 wanted to raise a flag to the Report Administrator (admin1), he would have to send an email to admin1 stating that the report data is wrong. Now with the introduction of Annotations, one can add comments to a report at the report level, cell level, grid level and individual member levels. So, admin2 would basically come inside the HFR report and would enable the Annotations window.


The annotation window would look similar to the screenshot shown below.


Now, the admin2 user would click on New to create an annotation.


One can assign different categories to the annotation. Also, one can assign contexts and also attach an URL or a custom document to the annotation.




In our example, admin2 realizes that 2003 numbers are totally wrong (since they are all negative). Hence he would be creating 2003 Year member level annotation indicating to the admin1 user that 2003 data is wrong.


He would also assign a 2003 member context to the above annotation.


Now, the annotation would automatically come on the 2003 member as a cell document (shown in the below screenshot)


The admin2 user would then set a permission on this annotation so that admin1 can see this annotation.


Now if we log in as admin1, we should be seeing an annotation on the 2003 member. Now, admin1 user can reply back to this annotation, informing admin2 that he would look into the reporting issue.


A very interesting feature. I believe this was part of the Hyperion Planning product before and this has now been incorporated into Hyperion Financial Reporting. It would be very interesting to see how this feature comes over to BI EE since dashboard commentary is something that is lacking currently in BI EE. Though i have blogged about the method of implementing the dashboard commentary for BI EE here, the major problem there is it maintains all the commentary in the database which should typically not be the case. Report and member level commentaries should be maintained only in the report metadata.

Posted in All Posts, EPM, Hyperion Essbase, Hyperion Financial Reporting | 3 Comments »

Oracle BI EE – Importing XOLAP Cubes

Posted by Venkatakrishnan J on July 21, 2008

If you had read my blog entry here, i would have shown you the new XOLAP feature of Essbase 9.5 (EPM 11.1). Though XOLAP is a new terminology, the idea has been there for quite some time. I basically wanted to test out this new Essbase feature of XOLAP directly from BI EE to see whether the latest BI EE version can infact import and report on the XOLAP cubes, without any issue. We shall use the same cube that we created using Essbase Studio in the last blog entry.


Lets import this cube using BI EE Administrator.


So far so good. Looks like the XOLAP has been designed to be compliant with the XMLA standards. Now, lets try creating a report on the imported Essbase Cube.



To be honest, i was expecting the import to error out. But it has not. I am not sure whether Essbase 9.5 is actually certified for BI EE but it does seem to work without any issues. Now, my next test is on Typed Measures in Essbase. Lets see how the current release of BI EE handles Typed Measures.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 2 Comments »

EPM 11.1.1 – Essbase Studio, XOLAP – First Impressions

Posted by Venkatakrishnan J on July 18, 2008

I just did a complete install of the latest EPM release 11.1 on my laptop. One of the first things that i noticed was the naming of this release. This is a fusion release. So, you would notice a uniform look and feel across all the products. Also, the installation procedure itself has changed quite a bit. There is no more setup.exe that we need to click for each of the products. The entire EPM package can be installed at one shot. There is a seperate installer download. This installer would provide the UI for installing all the components. The screenshot below gives you a sample of the installer.


It would be interesting to see whether all the other oracle softwares like BI EE etc would follow the same installation procedure. There is also one more change that i noticed. There is no need for having seperate schemas for storing EAS and Shared Services related metadata. All of them can use the same database schema which simplifies the installation. Be warned if you are doing this installation on an XP machine. Shared Services would not start from the services console(i believe this would be a problem in all XP machines since i have noticed this on 3 different machines with XP as OS. Let me know if that is not the case). One would have to manually start it. The first thing that i did after the installation was to check out the Essbase Studio and the newly introduced XOLAP.


I was expecting some glitches here and there since this is its first release. But so far, all the connections, metadata creation etc have been pretty seamless. Also, there are some very helpful custom tool tips that pop up which provide you with proper direction whilst you are navigating within the studio. It looks like a lot of thinking has gone into this Studio version and so far it is looking really good. There is a very good lineage editor which shows the lineage of the metadata within Essbase Studio.


The other very good feature is that it now recognizes BI EE metadata, Oracle, Essbase as data sources.


Unfortunately, it does not understand BI EE hierarchy yet. It does not use the ODBC driver directly. Instead it directly connects to the 9703 port of the BI Server to extract the metadata. This i believe, is to bypass issues in using the ODBC driver. Now, lets see how we can create a simple XOLAP cube and see what XOLAP actually means. As a first step, lets import the tables in the default SH Schema.


We shall then create a new folder in the Metadata Navigator which would hold all the Multi Dimensional elements.



Once this is done, let us drag and drop relevant/needed columns from the source into the folder that we created above.


Now, create simple hierarchies on Channel, Time & Customer containing one column each (the hierarchies would be flat containing the ids to keep it very simple. This is not the best of examples).


Also, create a measure hierarchy to include AMOUNT_SOLD and QUANTITY_SOLD.


Now, lets create a new Cube Schema that would contain all cube related objects like Hierarchies and measure hierarchies. This is similar to the metaoutline creation step that one does in Essbase Integration Services.


After the creation of a cube schema, lets create a deployment model for that cube schema. So, basically the idea is to create all the possible hierarchies first in the root folder and then determine the type of hierarchies to load in different cube schemas.


Now, in the properties of the Model, choose the deployment option as XOLAP.


Now, right click on the model and click on the deployment wizard. As you see this is similar to the screen that we have in EIS for cube deployment. Lets choose a new application and database name to do the deployment.


As you see, since this is a XOLAP model, it will only build the outline. And the data would be retrieved at run time using SQL. So, this is very similar to ROLAP in OWB. But the difference here is that the outline resides outside of the database and SQL Queries would be fired on the fly while retrieving the data. Once deployed, lets look at the new cube from the Essbase Administration Console.


The XOLAP application also has a set of rule files and also the corresponding outline with the ids. Now, lets go into Visual Explorer and see whether the cube actually fires queries to get the data back for us.


Well it does work. But somehow it errors out whenever i try to do a pivot. On the whole, the Essbase Studio has a pretty intuitive user interface and looks very neat. Next, we shall see the new features of HFR and workspace.

Posted in All Posts, EPM, Hyperion Essbase | 6 Comments »

EPM 11.1.0 – Its out & available for download – Fusion Edition of Essbase, Hyperion BI Plus

Posted by Venkatakrishnan J on July 15, 2008

I picked this up from Mark’s blog here. Actually, i was expecting this to be released only by the end of this week. But yes, always sooner the better. The new release of the entire EPM is available for download in edelivery. There are loads of new features in this release. I will list some of the interesting new features one by one for the BI Components in this release.


1. A new UI called Essbase studio has been release which supersedes the Integration Console & the Administration Console of Essbase. But both of them are still available in this release.
2. Support for JDBC connection to extract data.
3. Support for a concept called as XOLAP(Extended Online Analytical Processing). One can now completely model Multi Dimensional cubes with Outlines sitting in Essbase and the data coming in from relational sources.
4. Supports typed measures. Measures need not be numeric anymore.
5. Inbuilt Lifecycle management for migration.
6. Support for use of Environment Variables in Calculation scripts. Now, one can have system level parameters in Calculation Scripts.
7. Parallel load of SQL data into Essbase Databases. In this version one can run upto 8 rule files in parallel using the same authentication.
8. New Report level Calculation functions have been added.
9. New MDX Functions – This has more relevance since BI EE using MDX over an XMLA connection.
10. New MaxL statements.

Hyperion Workspace:

1. The most important addition here is that one can now add BI EE reports, dashboards, Delivers & BI Publisher within the workspace out of the box.
2. Performance Scorecards is now integrated into Workspace
3. A new searching feature that can now search for all Workspace related documents.
4. The search feature now can integrate with Google OneBox as well as Oracle Secure Enterprise Search.
5. Support of new high definition pixel perfect themes. This, i believe, is to bring a common Fusion UI across all the components.
6. Copy pasting of documents is now supported.
7. Portlets for the Hyperion BI Plus components to Oracle Portal, Web Center Suite & Sharepoint are now available.
8. Support for Oracle Proxy user authentication.

Hyperion Financial Reporting:

1. One can do annotations/comments from HFR now. An excellent feature which is currently lacking in BI EE. I believe this feature will also see its way in some form to BI EE in future.
2. Rank function enhancement – From a performance standpoint and also from a functionality standpoint.
3. External files can now be added to Books.

Hyperion Interactive Reporting:

1. Support for Oracle Proxy user authentication
2. New connectivity to Oracle BI Server. One can now create reports directly using OBI EE Server ODBC connection.
3. New Dashboard Gauges have been added.
4. Enhanced drill through from a multi-dimensional data source to a relational data source.
5. Dashboard specific enhancements

Hyperion Production Reporting:

1. Support for Oracle Proxy user authentication
2. BI EE Semantic layer integration – This is something i need to test out as it says that the semantic layer for production reporting is now integrated with BI EE.

Web Analysis:

1. One can do dynamic row and column references.
2. New calculation functions have been added. Some changes in the calculation dialog box.
3. Multiple report objects import into Smartview

Now, that i have worked on almost all the above reporting toolsets, one thing that i am noticing is that all the tools are converging in terms of functionality. I can see new features that were planned for BI EE also getting added to these product sets. So, no tool is left behind which is very good for existing customers. Also, each of these toolsets are getting integrated. For example, now we have connectivity to BI EE, BIP from Workspace. Also, we have portlets for the hyperion components to connect to Oracle Portal, Webcenter suite etc. The probable next integration is to enable the out of the box drilling from one reporting tool to another. Lots of things to test in the coming weeks!!!

Posted in All Posts, EPM, General, Hyperion Essbase, Hyperion Financial Reporting, Hyperion Workspace | 3 Comments »

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 »

Oracle BI EE – Integration into Hyperion Workspace

Posted by Venkatakrishnan J on July 7, 2008

One of the common challenges in working with the current release of BI EE plus is that there is no out of the box integration available between BI E and the plus(Hyperion) components. I believe the coming releases would address that. But if you are working on the current release and if you want to integrate both Hyperion and BI EE components then the coming blog entries should provide you with some methodologies to achieve that. The plus part of our BI EE plus bundle has the following components.

1. Hyperion Workspace
2. Hyperion Interactive Reporting
3. Hyperion Production Reporting
4. Hyperion Financial Reporting
5. Hyperion Web Analysis

The integration methodology of BI EE into each of the above components is different since all of them are different reporting toolsets and each work differently. But the idea is to use BI EE GO URL to enable integration into BI EE. Lets start with Hyperion Workspace and BI EE integration. This kind of integration is pretty straightforward to achieve. This is achieved using a simple HTML page. Workspace can load HTML documents and can open them in a seperate tab within the workspace. So, the first step is to open up workspace and then import a URL.


Then enter the dashboard GO URL (http://localhost:9704/analytics/saw.dll?Dashboard&nquser=Administrator&nqpassword=Administrator). For now, passwords would have to be embedded in the URL. If you do not like the passwords to be embedded, use the Public pages approach that i have used here.


Now, you would have dashbords page link in your workspace.


If you try opening this HTML page, you would notice that the entire BI EE Dashboards page would be embedded within the workspace.


The other advantage is that, the above BI EE Dashboards tab in workspace also has its own URL.


Once SSO release of BI EE and Plus products come out, there is no need to even pass the passwords into the URL.

Posted in All Posts, Hyperion Financial Reporting, Hyperion Workspace, OBI EE Plus | 3 Comments »