Business Intelligence – Oracle

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

Posted by Venkatakrishnan J on January 30, 2009

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

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

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, we were able to do it since we can hardcode the other parts of the workspace URL within the report itself using 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.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ 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 = "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=";

    Map map = request.getParameterMap();
    Iterator iter = map.entrySet().iterator();
    while (iter.hasNext()) {
    Entry n = (Entry)iter.next();
    String key = n.getKey().toString();
    String values&#91;&#93; = (String&#91;&#93;) n.getValue();
    int i = 0;
    while(i < values.length)
    {
    if (values&#91;i&#93;.indexOf("Year.id") == 0 )
    {
    BIEEGoURL = BIEEGoURL + values&#91;i&#93;.substring(8,values&#91;i&#93;.length());
%>
<%
    response.sendRedirect(BIEEGoURL);
    }
     i = i + 1;
     }
        attribs.putValue(key,values&#91;0&#93;.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, once you click on a HFR measure, you would notice that the filters get passed from the HFR report to the target BI EE report.

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

  1. Naveen said

    Venkat,
    Can I use the same approach while integrating HFR to Web Analysis

    I would like to drill to Web Analysis from HFR using parameters provided by HFR

    Please advice

  2. Rich said

    Thanks for the solution. It is exactly what I was looking for.

    I thought that the GO URL had a limit of 5 parameters, is that true? So if you needed to pass more that 5 filters from HFR to OBIEE are you out of luck or are there any work-arounds?

    Thanks again,
    Rich

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: