In this blog, we present some real-world examples of how Oracle OLAP have been used to solve analytic challenges. These examples are assembled from a variety of sources, jjl 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.
We start by presenting three Oracle OLAP examples. Each example describes a typical challenge that can be addressed using OLAP technology, and then shows how Oracle OLAP or 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.
Oracle OLAP is often a part of a larger implementation, but in these examples, we focus on the specific portion involving Oracle OLAP. In this section, we describe how Oracle OLAP has been used for the following purposes:
- Accelerating a data warehouse
- Analyzing projections
- Analyzing financial data
Accelerating a Data Warehouse
As mentioned late in blogs, data warehousing with Oracle OLAP can provide the best of both worlds: a single source of analytic data coupled with a multidimensional analysis of the data. The following example comes from a customer story first referenced in an Oracle Magazine article titled "Measure. Analyze. Perform." and then published as a follow-up success story in the February 2009 Oracle OLAP Newsletter. The company is R.L. Polk & Co. (Polk), and we focus on how adding Oracle OLAP to an existing Oracle Database data warehouse boosted the performance of both the warehouse and the company's BI tools.
Challenge: Improve the Performance of the Data Warehouse for a BI Application
Polk was running an Oracle Database 10g data warehouse and using materialized views to create aggregate totals for its BI application. The managers contacted Oracle for assistance in improving data loads, data formatting, and query response times. They were also interested in reducing time spent on maintenance.
Solution: Introduce Oracle OLAP Cube-Organized Materialized Views
Before making any changes to the data warehouse, Oracle consultants created a baseline by running benchmark tests for user performance and query rewrite using Oracle Real Application Testing and a set of data from the warehouse. Using good experimental design techniques, they ran the same tests with the same data after each major change to the data warehouse, so that they could quantify and isolate any improvements.
In this scenario, the company first upgraded its version of Oracle Database from 10g to 11 g. After the upgrade, they reran the benchmarks. The time to load and aggregate the materialized views decreased from 452 minutes to 385 minutes with version 11 g.
Then they created an Oracle OLAP cube with four dimensions, based on the design of the original relational materialized views. Unlike the relational materialized views, which were only partially aggregated, the cube-organized materialized views contained fully aggregated data, as well as views for the dimensions themselves. Cube-organized materialized views were also joined to relational tables as necessary at query execution time, which meant that the cube could be integrated into the existing snowflake schema without the need to store all dimension attribute data in the cube itself. When the benchmark tests were rerun using the Oracle OLAP cube- organized materialized views, the results were dramatic. The load and aggregation were computed in just 38 minutes - an improvement of 92 percent over Oracle Database 11 g alone. Query response times also improved significantly from 552 seconds down to 12 seconds for one sample query.
NOTE
As discussed in this blog, cube-organized materialized views can dramatically improve performance on normal queries against fact tables. Analytic applications do not need to be modified.
The applications simply query the fact tables as usual via SQL, and the Oracle Database tig optimizer redirects the queries to the cube-organized materialized views for faster performance and access to data.
Summary
The benefits of Oracle OLAP as a data warehouse accelerator are clear from this example: improved build times and query times. In addition, as a team member noted, one or a few cube-organized materialized views can replace tens or hundreds of relational materialized views, reducing IT workload and maintenance tasks.
Analyzing Projections
Many of the examples in this book have involved financial data. OLAP technology can be used for other purposes as well. As our next example shows, Oracle Database with Oracle OLAP can be useful for consolidating and analyzing data along multiple dimensions. In this example, the National Petroleum Council (NPC) uses Oracle OLAP to analyze projections for world energy consumption and supply.
Challenge: Analyze Projections for Energy Consumption and Supply
In October 2005, then Energy Secretary of the United States, Samuel W. Bodman, wrote an open letter to Lee Raymond, Chairman of the NPC, an advisory group representing the oil industry. In his letter, Secretary Bodman asked the following questions:
- What does the future hold for global oil and natural gas supply?
- Can incremental oil and natural gas supply be brought online, on time, and at a reasonable price to meet future demand without jeopardizing economic growth?
- What oil and gas supply strategies and/or demand-side strategies does the Council recommend the United States pursue to ensure greater economic stability and prosperity?
His questions precipitated a multiyear study of the problem, pulling information from many experts and organizations, including academic experts, research centers, government agencies, environmental groups, and energy companies. The results were published in 2007 under the title Facing the Hard Truths about Energy, available at this link.
For the study, the organization needed to collect projections from multiple sources and aggregate them in a meaningful way. One of the challenges of the project was privacy. To meet antitrust guidelines set forth by the government, and to ensure no competitive information was exchanged during the study, all information that could identify the source of a projection had to be removed. Indeed, with individual projections being considered very proprietary, technical consultants working on the project were not allowed to access the raw data.
Solution: Implement an Oracle OLAP Analytic Workspace for Survey Data
The Oracle Database data warehouse was designed to be the main analytical tool for the task groups, accepting all data collected from the survey questionnaire and other data sources. To meet privacy concerns, a bonded third party ran Oracle
database routines that computed averages, medians, minimums, and maximums, which were used for further analysis.
The multidimensional survey data was then loaded into Oracle OLAP cubes for analysis. The OLAP data was organized into seven dimensions:
- Time (year)
- Geography (country or geographic region)
- Energy type (such as oil, gas, coal, nuclear, or renewable)
- Energy sector (such as commercial or residential)
- Case type (such as business as usual or alternative energy policy)
- Units (applicable unit of measure)
- Source (such as public or proprietary)
The data presented its own issues, such as nonadditive data, skip-level hierarchies, and nonstandard aggregation rules. For example, analyses were conducted at multiple geographic levels. Sometimes the detail data for a given country needed to be obscured because revealing that data would reveal the source of the data. Summary data at a continent level, however, could be revealed, because there were more than three sources for the continent-level data. This led to the need to load data at multiple levels, with special aggregation logic to support higher-level data that was not necessarily the sum of the children.
To analyze the data and draw conclusions, industry experts used Discoverer Plus OLAP. The data was also exported from Oracle BI Discoverer and loaded into Microsoft Excel and PowerPoint for presentations to members of various analysis teams. Study teams were able to investigate relationships in the data during working sessions. The multidimensional nature of the data allowed them to drill down in multiple ways to investigate relationships that otherwise would not have been obvious.
Summary
By storing and analyzing multidimensional survey data using Oracle OLAP, the NPC was able to develop a comprehensive study of the global energy supply and demand through 2030. The privacy of the study contributors was protected because the analysts never had access to the source data, only the aggregated data stored in Oracle OLAP cubes. This project shows that OLAP technology can be used for a wide variety of purposes beyond handling marketing and financial data.
Analyzing Financial Data
Our next Oracle OLAP example is a more typical BI scenario. A company (name withheld for privacy) wanted to help its clients improve their business operations by analyzing their financial data.
Challenge: Provide an Environment for Clients to Analyze Financial Data
In this case, the application was a traditional financial analysis application for reporting financial general ledger data and key business metrics in the healthcare industry. Some of the challenges for this project included the following:
- About 25,000 Account dimension values, with the need to flip the sign of certain accounts for reporting purposes
- Calculated equations for some accounts with interdimension references
- Calculated measures with rules that vary depending on account value
- Need to use Excel in addition to a web-based viewer of reports
Solution: Leverage Oracle OLAP Financial Calculations
While other OLAP products could provide the necessary calculations, this company chose Oracle OLAP specifically so that it could leverage the scalability, reliability, and backup facilities in the Oracle Database that already housed the company's data. The client already had extensive knowledge of the Oracle Database. The reporting requirements would have been difficult to meet using SQL queries to access the relational tables.
By moving the data into a properly designed Oracle OLAP cube, they were able to quickly build reports to include time-series analysis and interdimensional calculations. Because the data is stored at all levels in the cube, the performance of run-time reports improved. By joining the Oracle OLAP data to relational tables, the client was able to easily build reports that drilled to the detail in the relational tables.
The following sections highlight a few of the calculations that were created to meet each challenge and the front-end tools that were used.
Flipping the Sign for Reporting Purposes Financial reporting has unique requirements. The general ledger stores revenues, liability, and equity accounts as credits, and expenses as debits. The credits are saved as negative numbers, while debits are positive numbers. Using a different sign for credits and debits allows the income statement to be easily derived by aggregating the numbers. For external reporting purposes, however, it is necessary to report all the numbers as positive numbers, changing the sign for credit accounts by multiplying by negative 1 (-1).
For example, if a company has a profit of 1 million, this should be reported as +1,000,000 even though it is stored as -1,000,000.
The consultants fulfilled this external reporting need by multiplying all credit accounts and their ancestors by -1 as the data was reported. Moving the responsibility for this data requirement to the Oracle Database ensured that this rule was applied consistently to all reports. For this project, the consultants defined and loaded a one-dimensional measure dimensioned by account called ACCT_REP_SIGN_VAR, which contained a 1 or -1 from a preexisting relational table. The reporting measure ACTUAL was then modified to use the following formula:
ACT * ACCT_REP_SIGN_VARBy multiplying the data by a one-dimensional measure that contains a 1 or -1, they were able to "flip the sign" as appropriate for each account.
Calculated Accounts Simple aggregation handled most of the calculations for the Account dimension, but some accounts needed to be calculated using custom equations. These accounts were calculated using models.
The business needed to report the cost per day for inpatient hospital care.
Oracle OLAP was able to meet this business need by using the following formula, defined in the model:
OPEREXP_PER_DAY = OPEREXP / STAY_DAYSThis divides the operating expenses by the number of inpatient days to achieve the operating expenses per inpatient day. By defining this key metric in the database, application designers could ensure that this metric was calculated in a consistent way across all reports and analyses.
Calculated Accounts with Interdimensional References Some more interesting account calculations needed to refer to specific data from two months ago. The business had a cash collection goal for each month. The goal was to collect all net revenue and any bad debt over 60 days. They were able to report the cash collection goal by creating the following model calculation:
CASH_COL_GOAL = PATIENT_REV + LAG(BAD_DEBT, 2, PERIOD)Calculated Measures with Rules That Vary Revenue and expense type accounts can be summed up when calculating a year-to-date measure. Summing balance sheet numbers over periods would grossly overstate the balance sheet. Balance sheet accounts (assets, liability, and equity) are reported as balances as of the end of the period (current month = prior period balance + changes in balance sheet for the current month), requiring no aggregation for year-to-date numbers. In addition, some accounts, like square feet (actual square feet of a building), do not change over time. For both of these examples, a year-to-date measure should report the same as the current month value. The consultants accomplished this requirement by giving these accounts an account type of B and changing the year-to-date formula as follows:
EQ if ACCT_TYPE eq 'B' then ACTUAL else {normal YTD formula}End-User Tools For most of the reporting, Discoverer OLAP was used. BI Discoverer Plus with a Java applet was used to generate reports. BI Discoverer Viewer was used by casual users who wanted to view reports created previously in Discoverer Plus OLAP. Because the cube was built using AWM, the business can take advantage of BI Discoverer, as well as other Oracle reporting tools such as OBIEE and the BI Spreadsheet Add-in to access the same analytic workspace for different customer needs.
Summary
This story highlights how Oracle OLAP can be used for traditional financial analysis applications. Oracle OLAP eases time-series calculations and allows for flexible reporting of the data in a consistent fashion. OLAP models allow for complex models, and insulate the reporting and analysis application from the complexities of data calculations. The business is now able to quickly report on its financials, and easily create formulas and models to meet its needs. The ability to employ models and custom formulas allows Oracle OLAP to be used in a variety of analytical situations.
