So far, we have discussed a general approach for determining what may be needed in a reporting solution. However, types of users and styles of reports are not the only factors that need to be taken into consideration. The project team needs to know which deployment options are feasible within the existing IT infrastructure. Sometimes, the ideal reporting solution is simply not possible based on security requirements or previous investments in technology. The team needs to manage expectations and be wary of letting pie-in-the-sky notions become requirements.
The project team works with the user committee to provide the best possible solution for their environment - one that adheres to enterprise standards while also providing the required functionality both for web-based and desktop-based reporting.
Fitting in with Enterprise Standards
Organizations may have strict policies about what can be delivered to end users.
To follow best practices, many different interfaces need to be used to meet the requirements of different user types, but all of them need to comply with corporate standards for technology, security, and single sign-on end-user restrictions. Many companies do not allow employees administrator-level control over their desktop or laptop, may have data restrictions to different databases, and may assign different access to different reports.
Most likely, the project team members who selected the software are already aware of the possible options for deployment. If not, they can poll the user committee or user community for requirements based on what they know will fit in with their standards.
Web-Based Deployment Options
The majority of examples shown so far in this chapter have been from web-based reporting tools, so let's first look at the different ways you can deliver reports over the Web:
- Interactive reporting A good way to look at web-based interactive reporting is that it provides all the functionality for doing detailed analysis - functionality that is as close as possible to that available in a desktop application - yet delivered securely through the Web for ease of maintenance. There is always a need to deliver ad hoc analytics and what- if capabilities through the Web, and this style of reporting comes closest to satisfying the needs of users who are used to desktop analytic tools. Examples of deployment options include Hyperion Web Analysis, Oracle Business Intelligence Answers, and custom web portals.
- Dashboard reporting Web-delivered dashboards have been a buzz topic for many years now, because they provide a great overview into a particular subject area on one or just a few screens. Ideally, a dashboard is a starting point, containing high-level information, which can then be drilled into for additional detail, investigation of a certain problem, and links to additional areas that may be of interest. Examples of deployment options include Oracle Business Intelligence Interactive Dashboards and Hyperion Interactive Reporting.
- Production reporting Also known as pixel-perfect reporting, production reporting delivers a report that must look a certain way to meet requirements, but has little or no interactivity. Statements, checks, formatted management reports like income statements, balance sheets, and cash flow reports fall into this category. Examples of deployment options include Oracle Business Intelligence Publisher, Hyperion Financial Reporting, and Hyperion SQR Production Reporting.
Desktop-Based Deployment Options
Desktop-based reporting is generally delivered via Microsoft Office products - in particular, Excel. However, some third-party tools exist that offer other spreadsheet- based interfaces with built-in Essbase or Oracle OLAP features.
Ad Hoc Spreadsheet Reporting
As previously mentioned, ad hoc spreadsheet reports are most often created in Excel by power users. OLAP features are integrated into the Excel interface. Analysts can connect to and retrieve data from either an Essbase database or an Oracle OLAP analytic workspace. They can also set the POV, navigate the data, calculate the data, and format the report - all from within the spreadsheet. With the appropriate permissions, they can even write back to the database. End users can modify the report to create what-if scenarios.
Essbase Tools for Ad Hoc Reporting You can add Essbase features to Excel using either the classic Spreadsheet Add-in or the next-generation Smart View, which both come standard with Essbase. Smart View has the added advantages of being able to connect to and retrieve content from other EPM applications, such as Planning and Financial Management, as well as offering the ability to create custom Microsoft Office reports (as described shortly).
Some third-party tools, such as Dodeca from Applied OLAP, offer an Excel- compatible spreadsheet interface and integrated Essbase features. Dodeca is described in more detail later in this chapter.
Oracle OLAP Tools for Ad Hoc Reporting For Oracle OLAP, several desktop options are available for displaying and reporting data. These tools include those provided by Oracle and some third-party tools that support Oracle OLAP 11g features.
We have already discussed OBIEE Plus and its support for Essbase and Oracle OLAP, but there is also Oracle Business Intelligence Standard Edition, which is based on the Oracle Business Intelligence Discoverer tool set.
BI Discoverer has two components that can be used with OLAP data.
- Oracle Business Intelligence Discoverer Plus BI Discoverer Plus is a Java- based tool delivered via a browser. It provides a rich report development and viewing environment. Because BI Discoverer Plus is a Java application, it can run on any operating system capable of running the Java environment, including Windows, Linux, and Mac OS X. BI Discoverer Plus has been available for quite some time for relational data. Several years ago, an OLAP component was released, called Discoverer Plus OLAP. Discoverer Plus OLAP directly accesses the data in Oracle OLAP cubes. Unlike earlier versions of BI Discoverer Plus, there is no need to use an administrator tool to create the metadata; in this case, an end user layer (EUL) can directly access the OLAP data stored in an Oracle database.
- Oracle Business Intelligence Discoverer Viewer BI Discoverer Viewer is a web-based, thin client viewer that allows for viewing of reports and workbooks created with BI Discoverer Plus.
To view OLAP data in Discoverer Plus OLAP, start Discoverer Plus OLAP and select the data you want to view using the Workbook Wizard. Figure 1 shows a sample report. The feature-rich multidimensional query builder contained in this tool allows users to build very powerful presentations and briefing books.
Figure 1. Discoverer Plus OLAP report
After you have selected all the data and filtered the dimensions, you will see your new Discoverer Plus OLAP workbook. You can now create reports. You have the option of viewing the reports in BI Discoverer Plus or allowing authorized web users to view the reports as well using BI Discoverer Viewer, as shown in Figure 2.
You can also use the BI Spreadsheet Add-in to display OLAP data in Excel. The BI Spreadsheet Add-in can be downloaded from the Oracle Technology Network (OTN) site or installed as part of the Oracle Business Intelligence Standard Edition Client bundle.
The BI Spreadsheet Add-in adds the following enhancements to Excel:
- Common business rules and definitions on the OLAP catalog
- Leverage of the scalability of Oracle OLAP
- Access to the advanced calculations and power of the OLAP engine
- Storage of data in a central repository - one version of the data!
FIGURE 2. BI Discoverer Viewer in a browser
After the BI Spreadsheet Add-in is installed, you start up Excel, navigate to the OracleBI menu item, and create a new query. After creating a connection to the database schema that has access to the OLAP cubes, the Query wizard is displayed. The wizard uses the same set of dialog boxes used to create a query in Discoverer Plus OLAP. Figure 3 shows an example of an Excel spreadsheet with the same data we showed in Discoverer Plus OLAP.
The BI Spreadsheet Add-in cannot use the same presentations that were created in Discoverer Plus OLAP, but they can share calculated measures and calculated members. One excellent offering of the BI Spreadsheet Add-in is a write-back feature that allows authorized users to write data directly back to the OLAP cubes. While this is only single user, the BI Spreadsheet Add-in does a good job of managing contention and opens the data in read-write mode only when data is actually being written.
As of this writing, the BI Spreadsheet Add-in supports only Oracle OLAP 10g.
A couple third-party products support Oracle OLAP 11g in Excel, as discussed in the "Third-Party Reporting Tools for Oracle OLAP" section later in the chapter.
FIGURE 3. Excel spreadsheet with the Oracle BI menu
Custom Reporting with Microsoft Office
Recall that custom Microsoft Office reports enable analysts to create documents and slides with live data points. Analysts can create the report once and reuse it month after month, simply by updating the content of the data points. The data points can be presented in the default grid format and in a variety of other visualizations, such as a scatter plot, bar chart, heat map, and so forth.
OBIEE includes the Oracle Business Intelligence Add-in for Microsoft Office which allows data from the BI Server to be used in Office products such as Excel and PowerPoint. Because Oracle OLAP and Oracle Essbase data can be used with OBIEE Plus and the BI Server, this same data can be used in the Oracle Business Intelligence Add-in as well. Oracle also offers the ability to create custom Microsoft Office reports out of the box with Smart View and its integrated visualization tool, Visual Explorer. Smart View offers the ability to view Essbase data directly or use data from the BI Server, which includes Oracle OLAP data.