Venkat Akrishnan

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.

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 10.1.3.3.3 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 10.1.3.3.3/2 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.

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.

If you had read my blog entry here, i would have shown you how to drill from BI EE to HFR by passing the parameters from BI EE to HFR. I had written that blog entry first since that integration was a bit easier than what we would be doing today. Today, our aim is to achieve drilling from HFR to BI EE by passing parameters from HFR to BI EE. We shall be using the same reports that we used in the last blog entry. So, our HFR and BI EE reports over which we would achieving the drills is given in the below screenshots

So, our aim is, when we drill from HFR to BI EE, the target BI EE report should automatically get the “Feb” filter since we are looking at Feb data in HFR. The major challenge in getting this integration to work is that HFR does not allow direct HTML formatted dynamic links in the report layout. One can only pass dynamic parameters. For HFR the URL parameters would look like the one shown below

<a href="http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%20Report&elementType=2&repository_name=Drill%20Through%20Report&repository_format_id=html&run=1&sso_token=$SSO_TOKEN$&attribute=Product.id.Product&attribute=Year.id.Qtr2&attribute=Measures.id.Measures&attribute=Market.id.Market&attribute=Scenario.id.Scenario"><strong>http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%
20Report&elementType=2&repository_name=Drill%20Through%
20Report&repository_format_id=html&run=1
&sso_token=$SSO_TOKEN$&attribute=Product.id.Product
&attribute=Year.id.Qtr2&attribute=Measures.id.Measures&attribute=Market.id.Market&attribute=Scenario.id.Scenario</strong></a>

For BI EE the URL parameters(GO URL) would look as shown below

http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/HFR%20BI%20EE%20Integration/Drill%20Report&Action=Navigate&P0=1&P1=eq&P2=TIMES.MONTH_NAME&P3=Feb

As you see, the parameters above are very different. In the previous case in BI EE, we were able to do it since we can hardcode the other parts of the workspace URL within the report itself using BI EE HTML formatters. But this is not available in HFR. So, in order to achieve the integration we would need an external jsp page which would accept the CONTEXT parameters from HFR and will dynamically generate a BI EE URL. Once the URL is generated, the jsp page will automatically do the redirection. So, the integration can be summarized as shown below

So, lets start with creating the jsp page in JDeveloper. The below code is a sample jsp code that i created for passing the month from HFR to the BI EE report.

<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.Map.Entry" %>
<%@ page import="java.util.jar.Attributes" %>
<%@ page import="java.util.Iterator" %>
<%

    Attributes attribs = new Attributes();
    String BIEEGoURL = "<a href="http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/HFR%20BI%20EE%20Integration/Drill%20Report&Action=Navigate&P0=1&P1=eq&P2=TIMES.MONTH_NAME&P3=">http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/HFR%20BI%20EE%20Integration/Drill%20Report&Action=Navigate&P0=1&P1=eq&P2=TIMES.MONTH_NAME&P3=</a>";
   
    Map map = request.getParameterMap();
    Iterator iter = map.entrySet().iterator();
    while (iter.hasNext()) {
    Entry n = (Entry)iter.next();
    String key = n.getKey().toString();
    String values[] = (String[]) n.getValue();     
    int i = 0;
    while(i < values.length)
    {
    if (values[i].indexOf( "Year.id" ) == 0 )
    {
    BIEEGoURL = BIEEGoURL + values[i].substring(8,values[i].length());
%>
<%
    response.sendRedirect(BIEEGoURL);
    }
     i = i + 1;
     }
        attribs.putValue(key,values[0].toString());}
   
%>

The jsp above basically does the following

1. Accepts the CONTEXT parameter from jsp which would be of the form &attribute=<dimension name>.id.<dimension value>
2. Parses the CONTEXT parameter and extracts the dimension value for the Year dimension.
3. Then it appends the extracted dimension value to a string which in turn is a BI EE Go URL.
4. Once the Go URL is completely generated, the jsp redirects the page to the BI EE Go URL.

Now, copy the above jsp over to the {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics directory so that we can access the jsp over the OC4J BI EE URL. This jsp can basically be deployed on any java app server. Test this jsp using the below URL

http://localhost:9704/analytics/HFR%20-%20BIEE%20Integration.jsp?&attribute=Product.id.Product&attribute=Year.id.Feb&attribute=Measures.id.Measures&attribute=Market.id.Market&attribute=Scenario.id.Scenario

Now, the above URL should automatically filter our target BI EE report for the Feb month (it is assumed that the BI EE report has the proper is prompted filters).

Once the above check is completed, the next step is to include the below link in the related content link in HFR.

http://localhost:9704/analytics/HFR%20-%20BIEE%20Integration.jsp?$CONTEXT$

Now, one you click on a HFR measure, you would notice that the filters get passed from the HFR report to the target BI EE report.

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.

Essbase:

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!!!

As you had seen in this blog entry, i would have shown you how to go about integrating BI EE into Hyperion Workspace. Today we shall see how to integrate BI EE and HFR. The credit for this blog entry actually goes to Toufic, an Architect in Oracle who was kind enough to share the integration technique that he had used for a client. The only gap right now between the 2 products from an integration point of view is the SSO which should hopefully be addressed in the coming release. So, our aim today is to drill from a BI EE report to an HFR report by passing parameters from BI EE to HFR. So, lets start with a simple HFR report as shown below.

As you see, this is a very simple report showing the Total Sales for a Year and the corresponding products. Now, assume that the sales transaction details are reported through BI EE. For example, consider the BI EE report shown below.

Now, our aim is to enable drill on the Month column of the BI EE report i.e when anyone clicks on, say the Month January, the HFR report should also provide the data for January. In order to achieve this, we need to understand how HFR accepts parameters in the URL. To know that, go to any data cell in HFR and click on related content.

In the related content, just include the same report as the related content report.

Then click on properties. You would get the below URL.

http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%20Report&elementType=2&repository_name=Drill%20Through%20Report&repository_format_id=html&run=1&sso_token=$SSO_TOKEN$&$CONTEXT$&rcp_version=$RCP_VERSION$&rcp_alias=$RCP_ALIAS$&mimetype=application/hyperion-reports-report

As you see, in the above URL there are certain dollar prefixed and suffixed names that pass the values from the base report to the target report. This is where Toufic pointed out that $CONTEXT$ is the actual keyword which passes the actual parameter POV values to the target report. In order to see the actual value of the CONTEXT parameter, lets go back to the related content again and enter the below link instead of the above report

http://localhost?$CONTEXT$

Now, lets click on the URL from the HFR report and see what happens to the target URL.

http://localhost/?attribute=Product.id.Product&attribute=Year.id.Year&attribute=Market.id.Market&attribute=Accounts.id.Accounts&attribute=Scenario.id.Scenario

As you see, all the POV values are passed to the target report in the form of URL parameters. The parameter is of the form

attribute=Market.id.Market

i.e we can pass dynamic parameters to a HFR report by passing the parameters in the form shown below

attribute=<dimension>.id.<dimension value>

Now, lets test this out using the below URL.

http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%20Report&elementType=2&repository_name=Drill%20Through%20Report&repository_format_id=html&attribute=Product.id.Product&attribute=Year.id.Jan&attribute=Market.id.Market&attribute=Accounts.id.Accounts&attribute=Scenario.id.Scenario

As you see, in the above URL we are passing Jan as a POV to the year dimension.

Now, once we are sure that the parameters are getting passed to the HFR report, lets go to the BI EE report that we created earlier. In the column formula, enter the below formula

'<a href="http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%20Report&elementType=2&repository_name=Drill%20Through%20Report&repository_format_id=html&attribute=Product.id.Product&attribute=Year.id.'||LEFT(TIMES.FISCAL_MONTH_NAME, 3)||'&attribute=Market.id.Market&attribute=Accounts.id.Accounts&attribute=Scenario.id.Scenario">'||TIMES.FISCAL_MONTH_NAME||'</a>'

Now, convert this column format to HTML. We would get the below report with the drills enabled for all the months.

Now, if we drill on any month, it would take us the HFR report. But since SSO is not enabled yet for these products, it would first show a login screen. But after that you should see the HFR report for the corresponding month.

This is an excellent starting point for anyone who wants to do the integration between these products. As you see, it provides the necessary steps and of course the necessary starting point (URL parameters for HFR). Next, lets look at how we can pass dynamic parameters from HFR to BI EE.

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.

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.

Another common question that keeps circulating is, “how do we bypass login to certain BI EE Dashboards?”. The most obvious answer to this question is to pass the username and password through the url. But certain security requirements/standards in some companies do not allow passing of passwords through the URL. So, lets look at a simple approach wherein some dashboards can be seen without passing the passwords through the url. The idea is pretty simple. Identify a user, for example PUBLIC, that would get direct access to certain dashboards. This user should not exist in the BI EE repository. All the other users would exist in the BI EE repository. So, the first step to achieve this is to create a simple init block with the below shown sql.

select ':USER' from dual where upper( ':USER' ) = upper( 'Public' )

Make the above init block to set the USER system session variable. Also, check the “Required for Authentication” check box. The init block can connect to any oracle database connection pool.

Now, if you use the below shown go url, the dashboards would be shown. As you see we are not passing the passwords through the go url.

http://localhost:9704/analytics/saw.dll?Dashboard&nquser=Public

The above approach uses the concept of external table authentication. Basically, the init block checks for the PUBLIC user and ensures that the user is authenticated without even checking for the password. All the reports/dashboards that can be viewed by the “Everyone” privilege can be accessed by this PUBLIC user. Very simple but can be used in certain situations.

As you would probably know, BI EE provides the capability to do row-level security from the Business Model layer. There was a question the other day wherein a user wanted to know how to do row-level security, wherein more than 1 filter value come from some other table. For example, lets consider the standard SH schema that comes with Oracle Database. The BM for this schema would look like the one shown below

Consider another table PROD_SECURITY containing the columns USERNAME and PROD_CATEGORY.

So, basically, the above table provides the list of users and their corresponding product category for which the users have access to. Now, in our BM, the sales and the product table are joined at the Product level. Each product category can have multiple products and hence we would have to use row-wise initialized session variables to achieve this security. So, start with creating an init block which would populate a session variable PROD_FILTER with all the prod ids belonging to the product category for which the user has access to. The init block would use the sql below

SELECT 'PROD_FILTER',PROD_ID
FROM PRODUCTS A, PROD_SECURITY B
WHERE A.PROD_CATEGORY = B.PROD_CATEGORY

Once this done, use the below statement in the where clause of the content tab of the Sales data table.

ORCL."".SH.SALES.PROD_ID IN ( VALUEOF(NQ_SESSION."PROD_FILTER"))

So, basically the row wise initialized variable would be initialized to (100,200,….) etc. Lets check whether the security is getting properly applied from answers. Since, we have not secured on the dimension table, lets first include the products column in our report and see what happens to our report

As you see, it would list down all the product categories. Now lets include the AMOUNT_SOLD column from the fact table into our report.

As you see, the security has been applied and the user Administrator would be able to see only 2 categories for which he has access to.

The last couple of weeks have been really very hectic for me as i am currently leading a very interesting project for a client involving EPF, Essbase, BI EE, Informatica and HFR. Though the project details would never see the light of the day, there have been lots of key take aways both from a technology standpoint as well as from an architecture standpoint. As part of the project, i had an opportunity to interact with one of the PM’s for a product called as CPM Analytics. I am not sure how many of you have actually heard of this product. For those who have not heard about this product, it is very similar to the Analytic Applications (OBIA) like HR Analytics, SCM analytics etc. But the major difference between this and the other applications is that this is a pure reporting analytics i.e there is no warehouse or ETL part to this application. CPM analytics is nothing but a repository and a set of reports bundled by the Oracle Development team for direct reporting on Oracle Enterprise Performance Foundation. Now, why am i blogging about this? As i said earlier, i had a very fruitful discussion with the PM of this application and there were 3 key take aways from that meeting which i shall be sharing here. As you might probably know, EPF or Enterprise Performance Foundation provides an out of the box capability to consolidate financial as well as non-financial data from Oracle E-Business Suite. One of the major advantages of EPF is that it structures the data in such a fashion that it is very easy to report on. It structures the data in the form of dimensions and facts(though not a direct star since the grain can vary). Our discussion centered around 3 main topics. They were

1. Handling Ragged and value based hierarchies in EPF
2. Performance considerations
3. Drill through to Oracle GL using Action links

I will explain each of the above in detail.

Handling Ragged and Value based Hierarchies:

This was my first question to the PM since BI EE does not have the capability to handle value based hierarchies out of the box. Most of the hierarchies in EPF are value based (Parent-Child). So, in order to circumvent that drawback, the CPM analytics uses a two way flattening. Lets understand this in detail. For example, lets take a simple hierarchy as shown below

In EPF, this hierarchy would exist in the Parent-Child form as shown below

Now, in order to leverage this hierarchy in BI EE, the first step is to flatten the above hierarchy as shown below

Now, if you notice, though we have flattened the hierarchy, we still have raggedness in the hierarchy. Hence while making the join with the fact table, we would be losing out on all the values that are null in the lowermost level. In order to circumvent this, we would have to do additional flattening which will replicate the lowest child to all the levels (where it is null) as shown below

So, if you look at the CPM analytics repository, you would notice 20 levels for all the dimensions. The reason for having that is to handle the value based hierarchy and also to ensure that we are handling the raggedness in the hierarchy.

This is a very nice way of handling ragged hierarchies. But the major drawback with this approach is that you would have a lot of unnecessary duplicates when you drill down and also we need to ensure that the hierarchy depth does not cross the limit specified. Still a very handy approach.

Performance Considerations:

The second part of our discussion was towards the performance of CPM analytics. EPF contains huge amounts of data even after consolidation from Oracle GL. So, one would have to ensure optimal performance for all the users irrespective of the kind of queries that they fire. In order to achieve that, CPM analytics uses 2 approaches

1. Enable caching for all queries - The usual caching approach. But the major drawback with this is that one cannot determine what kind of queries an end user will fire and hence it would not be possible to cache everything. Also, there is a physical limitation to caching.
2. Intelligent use of MV’s - This is an approach very unique to CPM analytics. In order to ensure optimal performance, they have created Materialized views for each dimension at its granularity. For example, Natural Accounts is one of the important dimensions in EPF. So, you would find a MV called as FEM_NAT_ACCTS_MV which will store the aggregated data at the natural account level. Also, in order to ensure that this MV is always used by the optimizer you would find the below statement being fired for all the sql’s.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = stale_tolerated

The above ensures that the optimizer would pick up the data from the MV even if the statistics are stale.

Drill through to Oracle GL using Action Links:

This is again one of the very unique features of CPM analytics. It provides out of the box capability to drill through to Oracle GL. Lets first try to understand what Action links are. Action Links, but for its name, are nothing but columns exposed as URL’s. There is nothing extra that one would have to do on the BI EE side to enable this. Action links enable end users to drill from a BI EE report to an Oracle GL form. Typically these are called as Action Links since the links are dynamic and they are obtained directly from Ebusiness Suite. In CPM analytics (the same concept was used in Fusion Intelligence before), the action links have been implemented using a simple sql shown below (just an example)

SELECT B.*
,OBJ.OBJECT_TYPE_CODE AS CREATED_BY_OBJECT_TYPE_CODE
,CASE WHEN (
  OBJ.OBJECT_TYPE_CODE IN ('OGL_INTG_BAL_RULE&#039  OR
  B.CREATED_BY_OBJECT_ID IN (1702,1703,1704,1705)
) THEN
  <b>FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL (
    CASE WHEN OBJ.OBJECT_TYPE_CODE IN ('OGL_INTG_BAL_RULE&#039  THEN
      FND_FUNCTION.GET_FUNCTION_ID('FEM_INTG_DRILLDOWN&#039 
    WHEN B.CREATED_BY_OBJECT_ID IN (1702,1703,1704,1705) THEN
      FND_FUNCTION.GET_FUNCTION_ID('FCH_DATASUB_DD_FORWARD&#039 
    END
    ,FND_GLOBAL.RESP_APPL_ID
    ,FND_GLOBAL.RESP_ID
    ,FND_GLOBAL.SECURITY_GROUP_ID
    ,'&DatasetCode='||B.DATASET_CODE||
    '&CalPeriodId='||B.CAL_PERIOD_ID||
    '&LedgerId='||B.LEDGER_ID||
    '&CurrencyCode='||B.CURRENCY_CODE||
--    '&CreationRowSequence='||B.CREATION_ROW_SEQUENCE||
     DECODE(DATASET_ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,NULL,NULL,'&DataTypeCode='||DATASET_ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER)||
     DECODE(B.COMPANY_COST_CENTER_ORG_ID,NULL,NULL,'&CompanyCostCenterOrgId='||B.COMPANY_COST_CENTER_ORG_ID)||
     DECODE(B.PRODUCT_ID,NULL,NULL,'&ProductId='||B.PRODUCT_ID)||
     DECODE(B.LINE_ITEM_ID,NULL,NULL,'&LineItemId='||B.LINE_ITEM_ID)||
     DECODE(B.NATURAL_ACCOUNT_ID,NULL,NULL,'&NaturalAccountId='||B.NATURAL_ACCOUNT_ID)||
     DECODE(B.ENTITY_ID,NULL,NULL,'&EntityId='||B.ENTITY_ID)||
     DECODE(B.FINANCIAL_ELEM_ID,NULL,NULL,'&FinancialElemId='||B.FINANCIAL_ELEM_ID)||
     DECODE(B.CHANNEL_ID,NULL,NULL,'&ChannelId='||B.CHANNEL_ID)||
     DECODE(B.CUSTOMER_ID,NULL,NULL,'&CustomerId='||B.CUSTOMER_ID)||
     DECODE(B.PROJECT_ID,NULL,NULL,'&ProjectId='||B.PROJECT_ID)||
     DECODE(B.INTERCOMPANY_ID,NULL,NULL,'&IntercompanyId='||B.INTERCOMPANY_ID)||
     DECODE(B.TASK_ID,NULL,NULL,'&TaskId='||B.TASK_ID)||
     DECODE(B.USER_DIM1_ID,NULL,NULL,'&UserDim1Id='||B.USER_DIM1_ID)||
     DECODE(B.USER_DIM2_ID,NULL,NULL,'&UserDim2Id='||B.USER_DIM2_ID)||
     DECODE(B.USER_DIM3_ID,NULL,NULL,'&UserDim3Id='||B.USER_DIM3_ID)||
     DECODE(B.USER_DIM4_ID,NULL,NULL,'&UserDim4Id='||B.USER_DIM4_ID)||
     DECODE(B.USER_DIM5_ID,NULL,NULL,'&UserDim5Id='||B.USER_DIM5_ID)||
     DECODE(B.USER_DIM6_ID,NULL,NULL,'&UserDim6Id='||B.USER_DIM6_ID)||
     DECODE(B.USER_DIM7_ID,NULL,NULL,'&UserDim7Id='||B.USER_DIM7_ID)||
     DECODE(B.USER_DIM8_ID,NULL,NULL,'&UserDim8Id='||B.USER_DIM8_ID)||
     DECODE(B.USER_DIM9_ID,NULL,NULL,'&UserDim9Id='||B.USER_DIM9_ID)||
     DECODE(B.USER_DIM10_ID,NULL,NULL,'&UserDim10Id='||B.USER_DIM10_ID)||
    '&PerformDrilldownQuery=Y&addBreadCrumb=Y&retainAM=Y'
  )
END AS ENTERED_BALANCE_URL
</b>,(SELECT ENTITY_ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER
  FROM FEM_DIMENSIONS_B DIM
  ,FEM_DIM_ATTRIBUTES_VL ATTR
  ,FEM_DIM_ATTR_VERSIONS_VL VER
  ,FEM_ENTITIES_ATTR ENTITY_ATTR
  WHERE DIM.DIMENSION_VARCHAR_LABEL = 'ENTITY'
  AND ATTR.DIMENSION_ID = DIM.DIMENSION_ID
  AND ATTR.ATTRIBUTE_VARCHAR_LABEL = 'ENTITY_TYPE_CODE'
  AND VER.ATTRIBUTE_ID = ATTR.ATTRIBUTE_ID
  AND VER.DEFAULT_VERSION_FLAG = 'Y'
  AND ENTITY_ATTR.ATTRIBUTE_ID = ATTR.ATTRIBUTE_ID
  AND ENTITY_ATTR.VERSION_ID = VER.VERSION_ID
  AND ENTITY_ATTR.ENTITY_ID = B.ENTITY_ID
  AND ENTITY_ATTR.VALUE_SET_ID = B.ENTITY_VS_ID
) AS ENTITY_TYPE_CODE
,(SELECT COUNT(*)
  FROM FEM_CAL_PERIODS_HIER HIER
  WHERE HIER.HIERARCHY_OBJ_DEF_ID = LEDGER_ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER
  AND HIER.PARENT_ID = B.CAL_PERIOD_ID
) AS PARENT_CAL_PERIOD_COUNT
FROM FEM_BALANCES_V B
,FEM_OBJECT_CATALOG_B OBJ
,FEM_DIMENSIONS_B DIM_LEDGER
,FEM_DIM_ATTRIBUTES_VL ATTR_LEDGER
,FEM_DIM_ATTR_VERSIONS_VL VER_LEDGER
,FEM_LEDGERS_ATTR LEDGER_ATTR
,FEM_DIMENSIONS_B DIM_DATASET
,FEM_DIM_ATTRIBUTES_VL ATTR_DATASET
,FEM_DIM_ATTR_VERSIONS_VL VER_DATASET
,FEM_DATASETS_ATTR DATASET_ATTR
WHERE OBJ.OBJECT_ID = B.CREATED_BY_OBJECT_ID
AND DIM_LEDGER.DIMENSION_VARCHAR_LABEL = 'LEDGER'
AND ATTR_LEDGER.DIMENSION_ID = DIM_LEDGER.DIMENSION_ID
AND ATTR_LEDGER.ATTRIBUTE_VARCHAR_LABEL = 'CAL_PERIOD_HIER_OBJ_DEF_ID'
AND VER_LEDGER.ATTRIBUTE_ID = ATTR_LEDGER.ATTRIBUTE_ID
AND VER_LEDGER.DEFAULT_VERSION_FLAG = 'Y'
AND LEDGER_ATTR.ATTRIBUTE_ID = ATTR_LEDGER.ATTRIBUTE_ID
AND LEDGER_ATTR.VERSION_ID = VER_LEDGER.VERSION_ID
AND LEDGER_ATTR.LEDGER_ID = B.LEDGER_ID
AND DIM_DATASET.DIMENSION_VARCHAR_LABEL = 'DATASET'
AND ATTR_DATASET.DIMENSION_ID = DIM_DATASET.DIMENSION_ID
AND ATTR_DATASET.ATTRIBUTE_VARCHAR_LABEL = 'DATASET_BALANCE_TYPE_CODE'
AND VER_DATASET.ATTRIBUTE_ID = ATTR_DATASET.ATTRIBUTE_ID
AND VER_DATASET.DEFAULT_VERSION_FLAG = 'Y'
AND DATASET_ATTR.ATTRIBUTE_ID = ATTR_DATASET.ATTRIBUTE_ID
AND DATASET_ATTR.VERSION_ID = VER_DATASET.VERSION_ID
AND DATASET_ATTR.DATASET_CODE = B.DATASET_CODE

As you see above in the sample sql, the part highlighted in bold would provide us the URL. So, typically when we are in an Answers report the idea is to pick up that column and make it as a URL. Hence, whenever someone clicks on that link it will automatically take them to the GL page by passing the appropriate parameters. The above was just to give an idea. There can be lots of different pages in EBS that we can redirect to and hence it is pretty tedious and complex to implement. But on the whole the idea is the same. But the major drawback with this approach is that this has a specific granularity. So, whenever someone creates a report with this link, it will open up a report at the URL grain rather than the report grain. This is something one would have to be aware of while implementing action links.

On the whole, the 3 approaches above can be re-used in relevant implementations.

Another common question that anyone might get while working on the BI EE - Essbase connectivity is to know a way for displaying UDA’s of Essbase dimension members. Essbase supports this concept called UDA’s wherein one can assign the same attribute to multiple dimension members so that one can do cross dimensional attribute analysis. For example, if you take the default Sample-> Basic database you would notice that the Market Dimension members would have some UDA’s assigned to it like Major Market, Small Market etc.

But if you import the database/cube to BI EE, you would notice that there is no provision for explicitly displaying UDA’s. So, in order to display all the members to a specific UDA, we would have to use the below EVALUATE function from within Answers.

CAST(EVALUATE('UDA(%1.Dimension,"Major Market")',Market.Region) as character(30))

Lets try drilling down on say East and see what happens.

It looks like BI Server is not drill happy whenever you use UDA’s. Also, one cannot have a report like the one shown below in BI EE(atleast in 10.1.3.3.3)

The major reason for the above errors that you see is due to the fact that BI EE is more of a relational reporting tool. It still is not Multi-Dimensional aware per se and it still tries to treat a multi dimensional data source in the form of rows and columns. But one good thing is that it is getting there and hopefully we should have full blown multi dimensional query capability in the coming releases. Now, lets try to achieve the same in Hyperion Financial Reporting.

As a first step lets try to create the above shown excel report. In order to do that we would need to have the query as shown below

Once this is done, choose the members for the Region and Accounts.

Now, insert a column just after the Region column. Make sure that it is a column of type formula.

And in the custom Text area enter the below formula (increment the row numbers as needed).

<<MemberProperty(current, 1, Market, UDA)>>

Now, if you look at the report one can get UDA’s as well as the member names. Now, what if we want all the member names for a specific UDA. In order to do that, just have sales and Market columns in your report. Then instead of adding the members add a function as shown below

The above will list all the members to a specific UDA (Major Market) similar to what BI EE did above. What if we want drill downs? Just enable auto expansion and we would get the drills automatically as shown below

There are quite a few member specific formatting that one can do with HFR. BI EE and BI Publisher should hopefully uptake these features in the coming releases.

I wanted to blog about something very simple today which came to me as a question from two different users. If you had noticed my blog entry here, i would have shown how to go about creating image prompts. Lets look at another approach today that will give more control to reporting users. I will be using the same image as used in the previous blog entry (shown below)

So, our idea is to filter a report based on the year values displayed above i.e for example when a user clicks on 2000, then our aim is to open up a report with a year=2000 filter. So, lets start with a simple report as shown below.

Ensure that you have the prompted clause filter on Year. Then go to the narrative view of this report and enter the below HTML.

<body>
<img src="<a href="http://lh4.ggpht.com/krisvenky83/RysmfbTfQiI/AAAAAAAAAzQ/jcvF5eclvXc/Snap4.jpg">http://lh4.ggpht.com/krisvenky83/RysmfbTfQiI/AAAAAAAAAzQ/jcvF5eclvXc/Snap4.jpg</a>" usemap="#YearPrompt" border="0">
<map id ="YearPrompt"
name="YearPrompt">
<area shape ="rect" coords ="120,7,173,28" href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2000" target=_new/>
<area shape ="rect" coords ="119,65,172,89"  href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2001" target=_new/>
<area shape ="rect" coords ="118,124,174,145" href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2002" target=_new />
</map>
</body>

As you see, what this above html does is it produces an image map and assigns the GO URL of the different reports to different map coordinates. So, when you click on Year 2000, the 1st url would pop up. Same would be the case for other years. Or the other option is to put the above HTML map directly in the dashboard Text Object as shown below.

Now, this Image Map would work without even using the Image Prompts. One good thing about this approach is that you can control the positioning of your target report.

Simple, but a better option than image prompts since you have more control.

As i have mentioned before BI EE uses the C-API of Essbase to fire queries against Essbase. The queries fired are standard MDX queries. Today, lets look at the MDX queries fired by BI EE and see what these queries actually return in Essbase Administration Services. Also, lets see what kind of MDX queries does BI EE accept. In order to do that, we would have to first enable the logging level of the user to 2 or 3.

Now, lets create a simple report out of Sample->Basic cube.

Now, lets check the MDX query which is being fired back by BI EE to Essbase.

With
  set [Market3]  as '[Market].Generations(3).members'
  set [Population2]  as '[Population].Generations(2).members'
  set [Q] as 'crossjoin ({[Market3]},{[Population2]})'
select
  {} on columns,
  {[Q]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sample.Basic]

Lets copy this query and fire this in Essbase Administration Services

Now, lets enable Direct Database Request and lets fire the above query from there.

So far so good. It looks like the query is correct and it gets the columns out properly. But lets see what happens when we view the results.

Ouch. Looks like direct database requests do not work with Essbase. Strange. Since the reports work i was under the impression this would also work. I am pretty sure that i am doing something wrong here. One other strange thing is that this is not a BI EE specific error but rather a C-API specific error. I am not sure what to conclude here since i cannot continue my testing here untill i get this to work. My idea was to test all the possible MDX queries that BI EE would accept. I cannot do that since it looks like direct database requests do not work on Essbase.

Though BI EE has connectivity to Essbase, there are still quite a few gaps that would have to be addressed before it can be used for creating financial reports out of Essbase. I hope 11g can bridge that gap. So, lets look at another tool today called Hyperion Financial Reporting. Hyperion Financial Reporting (HFR) is a component similar to BI Publisher. The major difference between these 2 products is that, BI Publisher can create financial reports only out of relational databases. But HFR can report out of Multi-dimensional data sources like Essbase, MSAS., SAP-BW and also on Hyperion Financial Management (HFM). Also, HFR provides drill down, drill backs etc out of the box which BI Publisher currently does not. I hope BI Publisher roadmap covers integrating features of HFR in the long run. In order to install and configure HFR there are 4 components that one would have to download. They are

1. Shared Services
2. BI Plus Services
3. BI Plus UI Services
4. BI Plus Client

HFR reports are generally created out of a client component called as Reporting Studio. All the reports created out of this studio are then published into the Hyperion BI Plus Workspace so that users can access the reports through a URL. Once the installation is done, we would have to create an user in the shared services and provision it to access BI Plus services.

The reporting studio connects to the Financial Reporting Server through the user created above.

Now, lets start with creating a simple report. As you see below, the reporting studio interface looks very simple and uncomplicated. But the features of this product are so good that you cannot think of using any other tool for doing Multi Dimensional financial reporting.

Lets start with creating a simple report on the Basic cube of the Sample application. We start with creating a grid in the report which would in turn ask us to provide the connection details.

Once this is done, lets choose Year in the Rows and Market in the Columns and see what happens.

Now, lets choose some dimension members for the Year and market dimension.

As you see we can choose any specific level for reporting (which is a major drawback currently in BI EE).

In order to enable drills, choose the member for which you want to drill and enable “Allow Expansion”.

Now, save this report and view the report from workspace (Since in my case there is no data in the cube you would see only #Missing in the report. )

As you see you can also drill down/drill back on all the levels.

In a future blog entry we shall be looking at some complex reports and how those can be created in HFR.

For the past couple of months i have been primarily working with Hyperion Essbase and the BI EE connector to Essbase. There are a lots of things that i want to write about which i would be doing in the coming week or so. As i had blogged here, there are many ways of loading data into Hyperion Essbase. They are

1.   Essbase Administration Services
2.   Essbase Integration Services
3.   Hyperion Data Integration Management
4.   Oracle Data Integrator

The most commonly/widely used ones are the first 2. I had already shown you here on how to go about loading Value based hierarchies using EIS(Integration Services). Lets see how to go about doing the same using Administration Services(EAS). EAS uses the concept of Rules Files to load data into an Essbase database. In our example, lets load the simple EMP - MGR parent child hierarchy from the EMP table of the SCOTT schema into a sample Essbase database. So as a first step, log into the Essbase database using EAS. And then open the outline.

Now open the Data Prep Editor from File -> Editors menu. You should be getting something like the screenshot below

Basically data prep editor is the editor that we would be using to create a rules file. Just like EIS, this editor also allows data load directly from a relational database. In our case, go to File -> Open SQL. Ensure that you are assigning the database to the SQL. Once that is done, enter the SQL given below.

SELECT
NVL(to_CHAR(MGR), 'EMP' ) MGR,
EMPNO,
ENAME, '+' PROPERTY
FROM
EMP

In the above sql, we are doing an NVL in order to make Essbase understand that we are creating child members for EMP dimension. So, basically all our members would roll up to EMP (which would be same as the EMP dimension). If you closely look at the second screenshot the root is actually in the middle of the result set. We shall see what impact this has on the final dimension later. Once this is imported, go to View and set this rules file as a file with Dimension build fields.

Then assign an outline to the rules file.

Then go to the Dimension Build Settings and set the 2 properties as shown below. The Allow Property changes will allow us to set the aggregation (’+’ in our sql above) of the members and the Parent Child references will automatically create the hierarchy based on the parent child relationship in the sql.

Then go to the field properties and assign the dimension, Parent, child, alias and property properties for the each of the fields.

Now validate the rules file. Then save it. Lets load the dimension using this rules file.

Now if you see, the data is not loaded properly. 7902 and 7698 should actually come under KING (7839). But that has not happened here. So, what this means is that Essbase rules file expects all the parents to be loaded first and then the children.  But in the case above, since we did not have a proper ordering of the data, the 2 children 7902 and 7698 got loaded before their parent KING (7839). Now, in order to rectify this go the SQL of the rules file and change it to the one shown below

SELECT
NVL(to_char(MGR), 'EMP' ) MGR,
EMPNO,
ENAME,
'+' PROPERTY
FROM
EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH MGR IS NULL

Now the above will give us the data in proper format (parents will get loaded first since the connect by clause internally starts with the root).

I have been receiving quite a few emails recently asking for a list