Third-Party Reporting Tools for Oracle OLAP
Over the years, several products have been able to access the data in Oracle OLAP workspaces. Recently, with all the BI software company consolidations and mergers, the landscape has changed. Several of the smaller independent companies that produced some very good tools are no longer with us, but others have emerged to take their place. The three products we address here are the Simba MDX Provider, ClearView, and Escendo Analyzer. The Simba MDX Provider and ClearView both work with Microsoft Excel, but provide different levels of integration and features. Escendo Analyzer is a stand-alone application that works directly with the Oracle OLAP data.
Simba MDX Provider
Simba Technologies has been a leader in relational and multidimensional connectivity tools and drivers for more than 18 years. The company has been providing multidimensional drivers for front-end tools for Microsoft and Hyperion since 2002. In the summer of 2009, Simba came out with another first: a native Microsoft Excel 2007 connector for Oracle OLAP 11g. This product uses Simba's MDX query language technology to access Oracle OLAP. Just about all other multidimensional databases have MDX providers; up until now, Oracle OLAP has been an exception. This will help level the playing field.
The primary purpose of the MDX Provider is to provide the same level of functionality within Excel as Microsoft Analysis Services. As far as Excel is concerned, the Oracle OLAP data looks just like Microsoft Analysis Services data. End users familiar with using the Excel PivotTable feature will view the Oracle OLAP data the same way they would see the Microsoft Analysis Services data. This is not an Excel add-in or bolt-on like other solutions; it works natively with Excel.
The Simba MDX Provider works seamlessly to parse and process multidimensional queries from Excel and integrate with Oracle OLAP 11g via SQL/ODBC. It translates Excel's MDX queries to SQL to retrieve Oracle OLAP cube information. The result is a fast and secure means to use popular, multidimensional tools like Excel to access your Oracle OLAP data directly.
The Excel 2007 PivotTable can now be used with Oracle OLAP to take advantage of all the BI analysis and presentation features that Microsoft provides. Connection to the database is simple. It is set up in the same way as any other MDX multidimensional data store, as you can see in Figure 1.
Figure 1. Connection to Oracle OLAP data via the Simba MDX Provider
As you can see from Figure 2, the OLAP data is presented in the native Excel interface supported by Microsoft. No additional menus or user interfaces are necessary.
Figure 2. Simba MDX Provider accessing OLAP data in Excel
The Simba MDX Provider requires Oracle OLAP 11 g (support for version 10g is currently not planned). This is the first release of the product, and Simba plans for this to eventually become a full MDX provider, compatible with other MDX data consumers. As you can see, this product provides a different layer of compatibility than other tools. While it may be limited by not supporting write-back, it also has the brightest future. If Simba is able to enhance the provider to support integration into other tools that currently support only Microsoft Analysis Services or other MDX multidimensional databases, this will expose Oracle OLAP to a much broader reporting tool base.
ClearView 2.0, by Collaborative Concepts Consulting, Inc., allows Microsoft Excel users a great way to access data in Oracle OLAP cubes. ClearView is a rich Excel add-in that enables users to view data with a powerful query builder tool for ad hoc reporting, boardroom-ready reporting analysis, and what-if analysis. ClearView supports full write-back to Oracle OLAP cubes and can be used for a complete analysis solution.
ClearView 1.0 was originally developed for Oracle Financial Analyzer users. ClearView 2.0 extends support to Oracle OLAP cubes and adds a great number of features. ClearView is developed as an Excel add-in, using the Excel worksheet as a presentation vehicle. This solution is designed for a collaborative, multiuser environment.
It supports a workbook management catalog system, storing all workbooks in the Oracle Database, instead of separate .xls files on users' hard disks.
Cubes in a ClearView solution are still managed by AWM. It uses the same metadata as Oracle OLAP 11 g, using the built-in views to access the data. For Oracle OLAP 10g analytic workspaces, ClearView includes routines that produce 11 g-style metadata and built-in relational views. There are stored procedures and relational tables that are installed in the Oracle Database as part of ClearView setup. This type of architecture allows collaboration and sharing of content between users in a secure way.
On the front end, ClearView is a single DLL installed as an Excel COM add-in. This works with Microsoft Excel versions 2002, 2003, and 2007. The Excel add-in provides all of the interface components for ClearView. ClearView uses Oracle ADO components for communication with the Oracle Database.
ClearView extends the Excel menus to include standard OLAP actions such as ranking, matching, sorting, rotating, selecting, and drilling down, as well as more advanced actions such as spreading amounts from a higher level to lower levels (for allocating forecasts). Figure 3 shows a selection of options available in ClearView.
ClearView also allows users to select the dimension values of interest from within Excel using the Easy Select feature (autocomplete), without needing to pop up another data selection window. The newly inserted row or column inherits all the formatting from the previous row or column. The Easy Select feature, shown in Figure 4, can be used to append or insert records to a range of data without affecting other formats on the worksheet.
Figure 3. ClearView Spread Amount feature
FIGURE 4. Inserting additional rows by using the Easy Select feature
ClearView allows users to create a highly formatted deck of reports that can be periodically refreshed to reflect the latest data. These reports use qualified data reference (QDR) format, which allows users to create customized asymmetric reports with ease. Excel formulas and Excel charts, along with other native Excel features, can be used within the same report, as shown in Figure 5.
Figure 5. Sample boardroom-ready report
Users can enter data directly into Excel to be written back to Oracle OLAP cubes. When this occurs, this data is submitted to a task processor. This allows administrators to have fine control over what is actually written back and to trigger calculation rules that can calculate other data based on what was written back. ClearView's Aggregate function allows users to do a what-if analysis before committing the data to the database. This means that users can type in data, submit data in a read-only instance of the analytic workspace, compute the totals in the database, and redisplay the results in the worksheet. Based on the analysis of the aggregated results, the data can then be permanently submitted, as shown in Figure 6.
ClearView's write-back functionality supports all the AWM data types (text, date, and numeric). Security defined at the cube cell level within AWM is enforced for write-back. The task processor supports multiple queues, which can be configured for different cubes, facilitating multiuser write-back. An intelligent processor optimizes the queue processes to minimize the number of aggregations. It also supports Sarbanes- Oxley compliance with change tracking for reports and all data adjustments.
Figure 6. ClearView submits data tasks to a task processor for processing.
Figure 7. ClearView add-ins provide ways to extend OLAP functionality.
As shown in Figure 7, ClearView can be configured to support custom add-ins to extend the OLAP functionality. For example, a custom add-in could be output coming from a relational query, OLAP DML program, or stored procedure.
While ClearView allows you to place Oracle OLAP data in any cell, by using formulas that access data at the cell level, it also fetches all cells of the same measure in bulk, providing excellent performance even on large data refreshes. Sparse data is handled in the back end to minimize communications between the front end and back end.
We have covered only a sampling of the capabilities of ClearView. Any company that is looking for a complete OLAP analysis solution using Oracle OLAP data with an Excel front end should investigate ClearView. ClearView's advanced write-back functionality makes it an ideal choice for clients who need a low-cost planning and forecasting application solution on top of Oracle OLAP. The task processor provides for orderly submission of data and tracking of multiple changes. The workbook management catalog system gives users the ability to store workbooks directly in the Oracle Database, mitigating one of the major problems with spreadsheet access to OLAP data - the proliferation of data.
Escendo provides an application development environment for developing, deploying, and analyzing Oracle OLAP cubes. Escendo is built by long-time Express and Oracle OLAP consultants for migrating Express applications and developing new analytic applications on top of Oracle OLAP. Rather than using AWM, they wrote their own Escendo Architect tool for building cubes.
Escendo Analytics consists of two related products. Escendo Architect replaces AWM for cube development and deployment. Escendo Analyzer provides a web- enabled front end for reporting and analyzing Oracle OLAP cubes.
Escendo cubes are built with Escendo Architect, a Java application used by Oracle OLAP administrators. Components live on the server as well as on the client side. Escendo works with Oracle OLAP 10g as well as Oracle OLAP 11 g, but uses its own metadata to include features such as version control on OLAP objects, customized SQL for loading data into cubes, a deployment manager, security administration, and migration from Oracle Express. As a result, some of the features available with Oracle OLAP 11 g, such as cube-organized materialized views and query rewrite, are not yet available for cubes built with Escendo.
Escendo Analyzer is a Flash-based front end built on a Java EE framework. Deployments have a hierarchical structure, giving users the ability to control who gets to see what data or approve changes, carrying forward some of the features available with Oracle Financial Analyzer.
Escendo Analytics, shown in Figure 8, includes standard OLAP actions such as rotating, selecting, and drilling down. It also adds a Business Perspectives feature,
Figure 8. Escendo Analytics
which helps to define which users get to see what data in what format. Users can write back data to the cubes.
Escendo allows companies to develop OLAP applications using Oracle OLAP. With Oracle OLAP moving more toward the IT organization, Escendo extends its use back to the user community for classic OLAP applications, developed and managed by line-of-business users.
Escendo should be considered by organizations that are attracted to many of the features of Essbase (but not necessarily the Excel interface), but want to store their data in an Oracle Database in Oracle OLAP cubes. Users can write back data to cubes using Escendo.
Third-Party Reporting Tools for Essbase
Nearly all of the reporting tools that have been used as examples in my blog are Oracle products, from the OBIEE Plus tool set. Given the openness of Essbase and its use of standard MDX for querying, over the years, many companies have introduced great interfaces for Essbase. The software industry has gone through some major changes in the last few years, however, and many independent reporting tool companies were acquired by larger companies. Reporting tools for Essbase that were previously offered by smaller companies such as Temtec and Alphablox are now tools positioned inside larger companies. Their offerings may or may not retain their former level of support for Essbase.
Applied OLAP is one independent company that has developed its product, Dodeca, to meet needs that might not be available out of the box with Oracle's own reporting tools. We know of several Essbase clients using Dodeca very successfully today. This section includes a case study that highlights when the purchase of a third-party tool may be useful.
Dodeca is a web-deployed solution that easily integrates with Microsoft Office.
It addresses the typical range of reporting needs, while also bringing together information from disparate data sources, including OLAP and relational database systems, within a single integrated user interface. Quick and easy customization is one of Dodeca's strengths.
Based on a web services and a metadata-centric architecture, Dodeca is designed to create tailored, customized applications that are targeted for analysts, decision makers, and business users at all levels. It was architected for speed of deployment and ease of use, while centralizing and managing business rules and files.
The two main features of the Dodeca architecture are the modular design and a patent-pending metadata management engine. The modular design provides the flexibility necessary for customers to control an interface that fits their exact needs, while the metadata engine enables rapid, global deployment flexibility and support for best-practice application life-cycle management.
Figure 9. The standard Dodeca user interface.
Typically, companies have a number of disparate data sources that contain related information, both relational and OLAP. Dodeca provides an interface that can bring those multiple sources together into a single custom application. Essbase analytics and underlying data sources, such as general ledger and other transaction systems, become part of a single experience. Figure 9 shows a standard Dodeca window, with user controls on the left, content in the center, and dimensional navigation on the right.
All of the features and functionality in Dodeca are provided by the underlying modular architecture. There are a large number of features that are available out of the box, including the following:
• Dodeca provides essentially the same functionality found in the classic Essbase Spreadsheet Add-in, but also has some unique features. Specific operations can be made available within a given context, thus providing a more structured and guided user experience. For example, the zoom in and out operations can be enabled or disabled on a dimension-by-dimension basis for a specific report.
- Dodeca includes its own Excel-compatible spreadsheet interface that can read and write Excel files that contain one or more sheets, calculate Excel formulas, render Excel formatting and charts, and enforce Excel element protection. It supports familiar editing operations, such as copy and paste special.
- Intelligent navigation makes it easy for users to remain in context as they navigate between different views or reports. This capability allows users to remain within a continuous thought process without the distraction of logging in to multiple systems or reselecting the parameters in the supporting systems. For example, an existing Dodeca customer has both Essbase and PeopleSoft GL, and uses the right-click functionality of intelligent navigation to drill across from Essbase to the details from PeopleSoft.
- Analytic commentary allows users to communicate variance explanations or other textual information related to an analysis in a systematic way. The commentary functionality delivered with Dodeca provides for threaded conversations and allows users to attach relevant documents to data points or partial data points within a view or report.
- Dodeca automatically logs all changes to the Essbase database, including the user information, a timestamp, the old values, and new values.
- All aspects of the Dodeca user environment are controlled centrally by administrators who manage the metadata that drives the application using a form-driven administration environment. Administrators control all aspects of both content generation and behavior - including the spreadsheet templates, available database connections, toolbars, and all user interface elements - to ensure that end users can easily and quickly get the correct information with minimal effort.
These are only a small sampling of the out-of-the-box features of Dodeca.
For those customers who have other needs, Dodeca's object-oriented modular architecture is open, documented, and supported. Customers can create their own modules to satisfy their most demanding user requirements. Now we will look at a case study showing how Dodeca can be used.
Dodeca Case Study
Dodeca is often used when a company has a number of data sources, including a large number of Essbase databases, and wants to enable users to more easily explore and find the information they need.
A large international bank had hundreds of users using an Essbase flash reporting system to view daily results. Although popular with users, the flash reporting database lacked the level of detail necessary to get a complete understanding of the data. In order to access the detailed analytic data, a user had to connect to a different Essbase database that contained significantly more details for two of the dimensions. Next, to further investigate the underlying details stored in an external general ledger system, the user had to log in to the system and create one or more queries. Finally, to review a source document, such as an invoice, a request had to be submitted to the accounts payable department, who provided a copy within five working days.
Due to the cumbersome and time-consuming effort required to complete the analysis process, user productivity suffered. Further investigation was frequently skipped altogether, as the cost to dig into supporting details was often greater than the benefit gained.
The bank managers chose Dodeca for its Essbase functionality and Excel compatibility. They also looked to Dodeca's intelligent navigation feature to make it easier for users to complete more thorough analyses. As noted earlier, intelligent navigation enables users to move interactively between reporting systems in order to explore a particular point of interest, while staying within the same application. The context is automatically retained as a user navigates through and across the various systems.
The value of the intelligent navigation feature is demonstrated in this example during a training session with a corporate controller. He learned how to explore summary information from Essbase, and then, with a single mouse click, navigate to a more detailed analytic view. A report was created that allowed for drill through to details, like the one shown in Figure 10.
The controller was automatically connected to an Essbase database containing more detail in the account and entity dimensions. The view presented at the detailed level maintained the entity, month, scenario, and other dimensions of the selected data point as the starting point for the new report. At this point, he was able
Figure 10. Case study first report with right-click drill-through capability
Figure 11. Drill-through details to CMB.
to navigate through certain dimensions to explore the detailed data further. For example, he could have selected to explore the CMB - Commercial Bank entity rollup instead of viewing data for the entire bank, as shown in Figure 11.
Once the controller found a number that required more detailed exploration, a simple right-click moved the analysis into the company's general ledger system.
When the controller selected the GLTXN link on the context menu, the context for the selected data point was automatically passed to the general ledger and produced a report showing the details. The controller selected a value that was the summary of more than 2,000 entities and over 100 accounts from the context menu shown in Figure 12.
The resulting general ledger report returned posting-level entities and accounts, and prepared a report for all postings that composed the number. Figure 13 shows an example of the GL Details Report.
The controller reviewed the details and found an accounts payable posting that was relevant from this detail, as shown in Figure 14. Once again, intelligent navigation allowed the controller to easily review the invoice image, where he discovered that one of his managers had a signature authority limit significantly higher than he believed appropriate. The continuous analytic thought process enabled by Dodeca gave the controller an insight into his business that led directly to action and to increased oversight within his department.
Figure 12. GLTXN drill-through selection
Figure 13. Miscellaneous expense GL report
Figure 14. Accounts payable posting and invoice
The last few articles of my blog touched on many areas that need to be taken into consideration for OLAP reporting. We showed that customers leverage many common elements - not just functionality and ease of use, but also terminology and common tools to deploy sophisticated OLAP reporting. You saw examples from many of the components in OBIEE Plus, as well as some additional reporting options from third-party companies. Oracle's strategy is to continue to offer functionality in a platform that works with both Oracle OLAP and Oracle Essbase, and extends current capabilities in both the web offerings and Smart View. This will make it easy for a large user base to extend their OLAP projects and leverage these common tools that work with both OLAP offerings.
We discussed determining the types of users and reports, the functionality they desire, and the many deployment options a project team can choose to use. In the next chapter, we look at front-end products that can leverage OLAP data and how they are commonly used in organizations.
- Few, Stephen. Information Dashboard Design: The Effective Visual Communication of Data. O'Reilly Media, 2006.
- Hatch, Toby and Raef Lawson. Oracle's Comprehensive Guide to Realizing EPM Version 2.2. White Paper. Oracle Corporation, October 2007.
- Nader, Michael and Dave Collins. Dare to OLAP. Oracle Corporation, 2008.