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