Business Intelligence – Oracle

Archive for the ‘Hyperion Financial Reporting’ Category

Oracle BI EE 10.1.3.4 – Integration between BI EE & Hyperion Financial Reporting(HFR) – BI EE Essbase Reports to HFR Drills – Part 3

Posted by Venkatakrishnan J on February 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 there are cases wherein one would need integration between a BI EE Essbase Report and an HFR report. In such cases, it is not possible from BI EE directly to generate the dynamic URL as specified before. One of the main reasons for that is, the concatenation of custom HTML strings are not supported in MDX queries. So, in order to achieve such integration we would have to bring in a relational source somehow. Lets understand this through an example. Consider a sample report in BI EE shown below

       

As you see, its a very simple report based on the Demo->Basic cube. Now our intention is to have a custom hyperlink column called as Drill which would be used for drilling to a HFR report. And while drilling, the corresponding row member names for the Gen3, Year and Gen3, Market need to be passed to the target report. The target HFR report is given below

       

Now, what we had done here was, we used the capability of BI EE to generate a dynamic URL from answers itself. But if we do that on a pure Essbase sourced answers report, it would throw an error as normal string functions are not supported on a MOLAP source. So, in order to overcome this, we shall use the horizontal federation approach. For this we would need two conforming dimension tables as shown below (these dimension tables is where we would pass the values to the URL)

       

       

Now, lets setup horizontal federation for the 2 conforming Year and Market dimensions by importing these 2 tables into the physical layer. Also create a custom physical alias table in the physical layer as shown below

SELECT
YEAR,
QUARTER,
MONTH,
MARKET,
REGION,
CITY,
'<a href=http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/BIEE%20Integration&elementType=2&repository_name=BIEE%20Integration&repository_format_id=html&run=1&sso_username=admin1&sso_password=welcome1&attribute=Product.id.Product&attribute=Market.id.'||City||'&attribute=Year.id.'||Month||'&attribute=Accounts.id.Profit&attribute=Scenario.id.Actual >HFR Drill</a>' CustomURL
FROM
MARKET,
YEAR

       

As you see, what this alias does is, it creates a cartestian join between the Year and Market tables. This would be used to produce our dynamic URL. Also, this would be used as a fact placeholder to generate the URL. Be careful while using this cartesian join approach on large dimensions. If you have very big dimensions, i would recommend using a complete fact view with only the necessary dimension values. The below screenshots shows how to setup the federation. The first step is to add another provider LTS for the Year dimension for all its columns.

       

Similarly set the same for the Market dimension. Once that is done, include the cartesian alias that we created into the BMM. Our idea is to use the CustomURL (in the above query) column as the column that generates the dynamic URL. Since we are including this as a fact column and since we are using the conforming dimensions approach, we need to ensure that the measures of Essbase and this CustomURL are of the same grain. So, make sure to set First or Last as the aggregation property of the CustomURL column.

       

       

       

       

       

Now, lets create the answers report as shown below

       

As you see, we get the URL in our report along with the Actual and Budget measures. Now, lets convert the URL column type into HTML. This will convert it into a drillable column as shown below

       

       

Now, if you click on any one of the links, it would take you to the HFR report. Also, it would pass Year and the Market dimension values as a parameter as well.

       

There are other possible approaches as well. But i wanted to use this approach in order to demonstrate the kind of flexibility BI EE offers from an integration standpoint. To make this even more flexibile instead of hardcoding the values, just create a function in the database and use EVALUATE to generate the URL.

Posted in All Posts, EPM, Hyperion Essbase, Hyperion Financial Reporting, OBI EE Plus | Leave a Comment »

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.

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

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.

Posted in All Posts, EPM, Hyperion Financial Reporting, OBI EE Plus | 7 Comments »

Integration of all Oracle and Non-Oracle Reporting Toolsets – URL Parameters – An introduction

Posted by Venkatakrishnan J on January 26, 2009

Last week i was interacting with one customer who had 2 different reporting toolsets across 2 different departments(they had 2 HR departments as the company recently went through a merger). Both the departments had a good data warehouse. One residing on Oracle and the other on SQL Server. Due to the current global economic climate and for operational efficiency purposes, they wanted to merge both the departments into a single one(both the departments were functioning seperately due to the recent acquisition). Again, the client was under very stringent budgetary constraints and hence was not in a position to spend more on data consolidation of the warehouses. Hence, the customer wanted to take the integration in a two phased approach.

1. Achieve management level reporting by integrating the reporting toolsets of both the departments.
2. Achieve actual data consolidation by merging both the data warehouses once budget becomes available in 2010.

Though they wanted to take the 2nd approach as that would solve their long term goal, due to the budgetary constraints, they just could not proceed with this approach. They wanted to know whether it was possible to integrate their existing reporting toolsets to achieve their immediate reporting requirements. Though this was a unique request, i wanted to understand what exactly they meant by integration as there could be multiple possibilities and approaches to integration. Their scenario was that they had 2 different reporting toolsets and both of them provided more or less a similar set of reports. As part of their consolidation effort, they wanted to merge both the reporting tools into a single one or atleast provide a capability which would provide a seamless drill down from one tool to another. For example, one of their key reports was Employee count breakdown by Year. Since they do not have a consolidated warehouse, they were fine with having 2 reports with the same employee breakdown by year for both the departments seperately. But they wanted a capability wherein one can drill down from one report to another. Basically, what they wanted was a capability to pass parameters from one report to another. And the major challenge was that the reports were built out of 2 different toolsets.

Though the requirements listed by the customer above are pretty reasonable, one of the reasons why i blogged about it here is to give an overview of how such integrations can be achieved. More importantly, the effort required to achieve these integrations are always on the lower side considering the fact that considerable investments have been made on the toolsets already. This blog entry would give a brief on how in general integrations like the one listed above can be achieved. We would go into details of the integration in the coming articles.

In general, all reporting tools that are web based accept parameters in 2 forms.

1. Through the URL
2. Through backend javascript post method parameters

Parameters are nothing but certain report/tool specific values which apply fiters, set configuration settings etc through the URL itself. I would list down some of the major vendors/reporting toolsets that support URL parameters

1. Oracle BI EE – BI EE has excellent parameter support through the URL. There are quite a few examples that i have covered in this blog before. Supports use of username/password in the URL itself.
2. BI Publisher – BI Publisher has good URL parameter support. One issue is that one cannot pass username/passwords through the URL itself as BIP does not support it as of now.
3. Discoverer – Both Discoverer Plus and Viewer support URL parameters. 9i release of discoverer supported passing of username and passwords through the URL. In 10g, this was removed due to security reasons. But Post method of login (2nd method above) is supported.
4. Hyperion Financial Reporting – Has excellent support for URL parameters. Passing of Username/Password through the URL is not supported.
5. Hyperion Web Analysis – Has excellent support for URL parameters. Passing of Username/Password through the URL is not supported.
6. Microstrategy – Supports an extensive API for parameter calls through the URL
7. Cognos – Supports parameter passing through the URL
8. Business Objects – I could not find an URL API for BOBJ. Not sure whether it is supported(i believe it should be). But i am pretty sure the 2nd method above, is supported.

As you see, almost all the tools above support passing parameters through the URL. What would vary is the extensiveness of this API across toolsets and across releases in each of these toolsets. For example, almost all features of BI EE is exposed through the Go URL and Dashboard URL. But in the case of BI Publisher or Discoverer not every functionality is exposed through the URL. So, to do the integration, it is necessary to understand the toolsets involved in detail. The integration which i would be covering below would not involve a SSO across the products. To achieve SSO across the products would require extensive coding effort as it is necessary to understand how each tool handles the security.

Integration of reporting toolsets can be achieved by 2 methods. They are shown below

       

       

1. The first approach assumes that the source reporting tool allows dynamic URLs to be built within a report itself. For example, in BI EE one can pass dynamic values from a report column to any other URL.
2. The second approach assumes that the source reporting tool only passes the URLs dynamically. The tool itself does not have the capability to build dynamic URLs. For example, Hyperion Financial Reporting & Web Analysis, does not allow to build dynamic URLs. In such cases, the parameters are passed to a custom JSP/ASP page which in turn generates the target URL and redirects accordingly.

This should give you an idea of how the integration would work. In the coming blog entries, i would elucidate this with examples of integration between HFR & BI EE, Web Analysis & BI EE, HFR & Web Analysis etc.

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

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 »

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.

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

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

Oracle BI EE 10.1.3.3.3/2 – 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 »

Oracle BI EE 10.1.3.3.3/2 – Displaying UDA’s of Essbase Dimension Members – A comparison with Hyperion Financial Reporting

Posted by Venkatakrishnan J on June 15, 2008

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.

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

Hyperion Financial Reporting – Getting Started

Posted by Venkatakrishnan J on June 7, 2008

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.

Posted in All Posts, Hyperion Financial Reporting | 36 Comments »