In this article we present some real-world examples of how Oracle Essbase have been used to solve analytic challenges. These examples are assembled from a variety of sources, including published stories and our own experiences. In some cases, we exclude the name of the company to protect its privacy; as you might expect, these companies have gained a competitive advantage and want to preserve this advantage.
Each example describes a typical challenge that can be addressed using OLAP technology, and then shows how Oracle Essbase was used to solve that problem. We do not cover every type of usage, but rather provide a sample of what has been done to give you an idea of the possibilities.
In the preceding section, the Oracle OLAP examples show how a database-centric approach to OLAP effectively solves the challenges faced by these organizations, while at the same time leveraging the features of the Oracle Database. In this section, we present three examples of how Essbase has been used to solve business problems.
You will notice that the Essbase examples take a different approach than the Oracle OLAP examples. An Essbase implementation tends to start with a pilot completed during the discovery process. For a pilot, an Essbase analyst builds an Essbase database using the client's data, and then presents the results to the line-of- business users. The purpose of the pilot is twofold: it shows how Essbase can solve the organization's problem and it demonstrates how quickly Essbase can be implemented.
The examples show Essbase being used for the following purposes:
- Replacing the Excel workbook
- Enhancing an enterprise resource planning (ERP) system
- Replacing custom SQL reports generated by IT
Replacing the Excel Workbook
Late we introduced a phenomenon known as "spreadsheet hell," in which hundreds of manually produced spreadsheets may exist, with no central source for data or calculations. In this scenario, we examine a real-world example of spreadsheet hell and review the Oracle Essbase solution.
Challenge: Eliminate Manually Created Spreadsheets
A rental car conglomerate controlled two brand names, operating in several countries.To power business analyses, Excel workbooks were prepared monthly and distributed throughout the organization. The business analysts used spreadsheets for analysis because the software was already available in the organization, they had early success with spreadsheets as analysis tools, and they were already familiar with spreadsheets.
As their use of spreadsheets expanded, the workbooks became more complex and time-consuming to assemble. On a monthly basis, analysts would extract data from disparate sources, including operational systems and the data warehouse, and paste the data into several spreadsheets. These data sheets were then used as the source for report sheets. The report sheets relied on a very complex network of reference-based cell formulas to pull data from the data sheets. All told, it took several business analysts an average of eight business days to perform the data extractions, load the workbooks, and perform some rudimentary tests. This situation caused concern for both the chief operating officer (COO) and chief financial officer (CFO).
The CFO and COO wanted to have access to the data sooner, as well as improve the reliability of the data and calculations. It was also important to them that, whatever solution was implemented, business users would continue to create, maintain, and monitor the application, rather than having IT control administration. The COO and CFO saw IT providing value through data integration and project mentoring.
Solution: Move Data into an Essbase Database
The COO and CFO identified resources from IT and the business community to act as project sponsors. It is a best practice to have representation from both IT and the line of business right from the start. IT has a lot of experience in building systems
and managing data, while people from the business side know the business use cases and can identify analytic and reporting requirements. The role of the project sponsors was to assist the Essbase analyst in creating an Essbase database as a pilot project.
The project team created the following plan:
- Schedule a meeting to discuss the business needs and review the existing solution.
- Present a basic technology demonstration to provide a baseline of knowledge for the project sponsors.
- Gather data and reports from which to create the pilot.
- An Essbase analyst from Oracle would create the database and example reports.
- The Essbase analyst would review the pilot with the project sponsors.
- Sponsors would present the final pilot to the COO and CFO for project approval.
The entire pilot process was scheduled over a two-week period. The team selected this time frame to demonstrate that the review process could be completed in less time than was needed to maintain the application monthly.
The first step in proving the proposed solution involved moving the data from the workbooks to an Essbase database. The Essbase analyst built and demonstrated this process. Essbase dimension build rules were used to create the database outline, and data-load rules populated the database with data from the original sources.
From there, some ad hoc spreadsheet retrievals validated the dimension structures, calculated values, and demonstrated typical query usage.
After the pilot was complete, the Essbase analyst reviewed the pilot with the project sponsors. The demonstration was executed first with the business cases in mind. Then the methods used to create the database were presented. Most impressive to the sponsors was the speed with which the pilot had been created. From needs analysis to first review took just a few days, while the pilot database itself took the Essbase analyst a mere two hours to build and load with data. And to the amazement of the sponsors, the pilot data matched the production system and the workbooks.
The COO and CFO wanted reliable data available sooner, but they also wanted a system that could be managed by the business users. On the strength and accuracy of the pilot, and with the assurance of the sponsors that the system could be managed by the business analysts, the company adopted and implemented the Essbase solution. It took three weeks to complete the production-ready system, including the Essbase database and the end-user reports. Now it takes just one hour a month to load the data from the sources systems and generate end-user reports from the Essbase database.
Enhancing an ERP System
In the next scenario, we look at how Essbase can be used in conjunction with an ERP system. ERP systems use an online transactional processing (OLTP) schema to store data efficiently in a relational database. However, to answer typical analytic questions requires many joins between tables, and the more joins, the slower the query response time. In contrast, OLAP technology stores and aggregates data multidimensionally, which translates into extremely fast query response times and the ability to do ad hoc analyses. Adding Essbase on top of an ERP system enables an organization to take advantage of the strengths of both OLTP and OLAP technologies.
In the following example, the company has a single ERP system. However, because companies often acquire other companies, they can find themselves attempting to manage multiple ERP systems. This is a clear case for a tool like Essbase, which can consolidate data from disparate sources.
Challenge: Create a More Flexible Analytic Environment
One very large consumer packaged goods (CPG) company implemented an ERP system to integrate the data and processes of the organization into a single system. The advantages of an ERP system are many, but for this customer's IT department, the big draw was the ability to provide a single source for all transaction data.
For business analysts, however, an ERP system can present challenges. This particular company's business analysts discovered their limitations very quickly:
- Running analyses was very slow. The ERP application was retrieving detailed data from a relational database management system (traditionally a slow process when done on a production system during business hours) and calculating dynamic values at the time of the query (also time-consuming).
- The analysts could not create custom dimensions. The ERP system came with predefined dimensions and offered little flexibility to modify them to meet analytic needs.
To work around these limitations, the business users needed to analyze the data offline from the ERP, and ended up building very large workbooks using Excel. As in the previous example, updates often took days or weeks, which limited the impact of analyses on the current month and resulted in decisions that were not always based on facts.
Management had been looking for alternatives when a newly hired, senior-level employee mentioned Essbase. He had used Essbase before, and he encouraged management to look at it.
Solution: Add Speed and Flexibility to ERP with an Essbase Database
The pilot process was accomplished very quickly. First, a senior employee was excited about the opportunity to incorporate Essbase into the organization. Second, the Microsoft Office integration available using Oracle Hyperion Smart View for Office was very attractive, as the company's business analysts often used Excel, Word, and PowerPoint to provide information to executives. Once again, a demonstration database was created from an Excel workbook.
In this case, the presentation caused some consternation. When the Essbase analyst presented some reports validating the data, one attendee called into question the revenue for a business unit that had been sold recently. First, the Essbase analyst checked the Essbase outline for accuracy and found that the consolidation used was indeed correct. Then the Essbase analyst checked the formulas in the original Excel workbook for that business unit and discovered the error: Someone had not updated the consolidations from child to parent after the previous quarter's reorganization. The result? The revenues expressed in the workbook were 13 percent understated. Because the workbook had been the only source used to assess the worth of the business unit, the hard truth was that the business unit had been undervalued when it was sold, representing a loss of millions of dollars to the shareholders.
For the managers, the news of the error was enough to convince them to implement Essbase. For the business analysts, Essbase solved their main challenges with the ERP system. Essbase gave them the flexibility to model dimensions that reflect the way the company is managed, rather than the way the ERP system is configured.
Queries to the Essbase database are now fast - speed-of-thought fast - and the data is reliable. Instead of taking days to weeks before the data is available for reports, it now takes about an hour to load and consolidate the Essbase database.
Replacing Custom SQL Reports
Companies succeed in analytics due, in large part, to a strong working relationship between IT and the rest of the business. A good relationship, however, does not mean that IT should expend valuable resources doing things for business users that, with the right tools, the business users could do for themselves. For example, analysts who understand their business should be able to model the business, build scenarios on the fly, and create their own reports.
The organization in this example is a very large utility conglomerate. As in the previous example, an ERP system was in place, providing a single source of detailed data. Once again, the business users were facing challenges when performing analyses, in this case due to the technical nature of the analytic and reporting tools provided by IT.
Challenge: Empower Business Users to Create Their Own Reports
The IT department members of the utility conglomerate knew business users needed analytics. To meet this need, they had acquired and deployed an add-on analytical application for the ERP system, as well as a SQL-based query and reporting tool. Most of the required content was available through the relational data mart and some 200 standard reports created by IT. However, several ad hoc needs were either ignored or just very difficult to achieve. Because the query and reporting tool required knowledge of SQL, users began to lean on IT resources to create custom reports. This reliance on IT resulted in significant delays in the analysis process, as the analysts were required to wait hours or days for reports to be generated.
Custom reports also contributed to higher workloads for IT personnel. Soon, the IT department was faced with about 3,000 of these custom reports! In many cases, the custom report differed very little from the standard version. However, because the tools provided were technically challenging to the business users, the notion of self-service reporting was merely a dream.
IT management grew concerned about how to handle the thousands of new reports and how to manage the increased demand on their time. A new OLAP reporting solution was required.
Solution: Use Smart View to Create Reports
The Essbase pilot focused on showing how Smart View installed in Excel enabled business users to run their own queries and create their own reports - without using SQL.
Solutions to several previously unidentified reporting issues surfaced during the pilot. For example, simple variance reporting using favorable/unfavorable scenarios meant custom coding in each SQL-based report. The Essbase analyst was able to demonstrate how, with Essbase, the expense reporting property was stored in the database, and so provided this functionality to each report and ad hoc query, eliminating the need for custom coding within the reports themselves. Similarly, balance sheet reporting was modeled in the database, instead of at the report level. Tremendous advantages can be gained when modeling complexities like favorability and balances in the database. Users no longer need to remember to maintain these calculations in every report or query.
Essbase became part of the solution to the challenges experienced by both the business users and IT. Business users began relying on Smart View for ad hoc reporting, rather than a formal reporting tool. Because ad hoc analysis is driven via business terminology, rather than by SQL, IT was off the hook for supporting all the custom report requests.
The six examples (three for Oracle Olap) in my blog were selected to represent typical ways in which Oracle OLAP and Oracle Essbase can solve real-world problems. We demonstrated how Oracle OLAP has been used by organizations to accelerate a data warehouse, analyze projection data from multiple sources, and analyze financial data. The Essbase examples showed how Essbase has been used to replace data stored in Excel workbooks, enhance an ERP system with improved analytics, and replace custom SQL reports generated by IT with self-service reporting. These are just a few of the ways that OLAP systems can be used within an organization. For more examples, see the success stories on the Oracle Technology Network (OTN) web site or contact your Oracle sales consultant.
While the examples in this blogare specific to each product, it does not mean that Oracle OLAP or Essbase would be unable to meet the analytic requirements presented in the other product's examples. That said, it should be clear that each example plays to the strengths of the Oracle product used in the example. If IT wants to speed up response times for analytic SQL-based queries against an Oracle database, Oracle OLAP is the obvious choice, both for its cube-organized materialized views and for its support of SQL. In contrast, when the line of business wants to retain control of the OLAP technology outside the Oracle Database, Essbase is the obvious choice. This is a key point. Both Oracle products are full-featured OLAP offerings; however, each has different strengths that arise from their histories and different development goals. Indeed, the products' differing strengths speak to why Oracle continues to support the development of both products.