Business Intelligence – Oracle

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.

3 Responses to “Integration of all Oracle and Non-Oracle Reporting Toolsets – URL Parameters – An introduction”

  1. Tom said

    Hey

    Venkat I really like your blog and your work is really serving the community.

    Venkat can you blog or publish an article on integrating OBIEE with other data sources custom data sources or data coming from 3rd party sources such as call center data etc

    and my client wants to connect to different data instances based on the time of the day as he has global instances so he would want to connect to different instances based on the time in that country or part of the country can you share your ideas on how to accomplish this

    Looking forward

    Tom

  2. […] Oracle Homes for OCI DriversOracle BI EE 10.1.3.3 and Mapviewer – Step by Step integration – Phase1Integration of all Oracle and Non-Oracle Reporting Toolsets – URL Parameters – An introductionOracle BI EE 10.1.3.3/2 – Top 10 Common ErrorsYour QuestionsCustomizing OBI EE – GO URL […]

  3. Viral said

    I am trying to connect SQL server database into the Oracle Publisher but it gives me an error(Could not establish connection.). I am following these steps.
    1. Logged on to Oracle Publisher using User Name:Administrator & Password:Administrator
    2. Selected Admin tab & then selected JDBC connection. Add data source. i.e.(Admin>JDBC>Add Data Source)
    3. Provided all the parameters…
    Data Source Name: GIS
    Driver Type: Microsoft SQL Server 2005
    Database Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
    Database String: jdbc:sqlserver://119-053:1433;DatabaseName=GIS
    Username: sa
    Password: sa2005

    Could you please tell me, Where am I doing wrong?
    Or can anybody send me the correct procedure to connect SQL database into the Oracle Publisher?

    Thanks,
    Viral…

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: