Business Intelligence – Oracle

Oracle BI EE 10.1.3.4 – Integration between BI EE & Hyperion Financial Reporting(HFR) – Drilling from BI EE to HFR by passing parameters – Part 1

Posted by Venkatakrishnan J on January 28, 2009

In the prior blog entry, i had given an overview of how to go about achieving integration between any two reporting toolsets. As a followup to that, through a series of articles, i would show the integration between the following toolsets

1. BI EE and Hyperion Financial Reporting
2. Hyperion Financial Reporting and Hyperion Web Analysis
3. BI EE and Hyperion Web Analysis

Today we shall see how to integrate BI EE and HFR. The integration between BI EE 10.1.3.4 and HFR 11.1.1 would be seamless in terms of security as SSO is now available between these 2 products. 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. In this case $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 after that you should see the HFR report for the corresponding month.

The above uses the approach 1 that i had blogged about here. Next, lets look at how we can pass dynamic parameters from HFR to BI EE.

7 Responses to “Oracle BI EE 10.1.3.4 – Integration between BI EE & Hyperion Financial Reporting(HFR) – Drilling from BI EE to HFR by passing parameters – Part 1”

  1. CF said

    Thanks Venkat, keep up the great work! Your site is the first site that I visit every day when I go online

  2. […] « Oracle BI EE 10.1.3.4 – Integration between BI EE & Hyperion Financial Reporting(HFR) – Drilling… […]

  3. […] 6, 2009 This is the 3rd and final part of the BI EE and HFR integration. In the prior blog entries here and here, i had shown the integration between a relational BI EE report and an HFR report. But […]

  4. […] by Venkatakrishnan J on February 10, 2009 If you had looked at my blog entry here, i would have shown how to go about drilling from BI EE to HFR. Along the same lines, lets look at […]

  5. Evanescent said

    Impressive Content…keep going !! all the best (already Ctrl+D)

  6. Evanescent said

    Am i currently working on 9.3.1 to 11.1.1.0 upgrade…..i would be glad if some one could throw some light on the upgrade process

  7. Jelle Stas said

    Hello Venkat,

    Interesting blog entry. Currently I’m working on a scenario for Hyperion customers to show them the added value of OBIEE 11g. My big question is: What is the added value for Hyperion users? What will they be able to do more with OBIEE integrated with their Hyperion system?

    I’m hoping to see you in the UK on 14/05 for the seminar which Mark Rittman has organized.

    BR’s,
    Jelle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: