Essbase Partitioning: Divide and Conquer Your Processing Woes

 

-Written by Kevin Barth 

A client of ours was having some issues with a completing a complex series of expense and revenue allocations against their budget and actual data (stored in Oracle Hyperion Planning) within a tight calculation window. This data is used to support P&L reporting at a business unit level on a monthly basis. During the close period, drivers are modified and a quick turnaround of results is needed and their current system design was not providing the necessary results fast enough.

Not to go into too much detail but the business is rather large with 1000s of cost centers and accounts. Allocations spread these dollars across three different dimensions: business solutions; products; and markets.  A small change in one business area can have an impact on any of the other dimensions within the application.

During our analysis, we were able to isolate "sets" of allocations that were able to run independently from the other allocations. We determined that trying to accomplish all of this within the Planning application probably would be difficult as there weren't a lot of efficiencies we could take advantage of and still maintain the dimensionality required for Planning. Instead, we decided to take advantage of the "isolation" levels of the data and use the power of Essbase to help solve our problem.

There were two obvious sets of allocations: the Business Solutions/Products and the Markets. We used a series of calc scripts and MaxL to extract the data required for the allocations from Planning and load the data into two smaller Essbase cubes designed specifically to handle each set of allocations. The  two new cubes were given dense/sparse configurations to allow the allocations to execute as quickly as possible. These cubes were able to run the allocations in parallel and complete in the required timeframe because the dimensionality and data sets were limited to those required for the allocations processing.

So the allocations now ran fast enough but we still needed to pull all of the data back together to allow P&L reporting to occur with as little confusion as possible for the user. The solution - partitioning. We created a transparent partition over the three cubes (Planning; Solutions/Products; and Markets). This provided a "one stop shop" for all the users reporting needs. Through the use of a "Source" dimension, this partitioned cube enables the users the flexibility to view the actual/budget data in raw form or after the allocations - or both!

The partitioned solution allows the users to run processes against any of the three source cubes without affecting the other two. Because it is a transparent partition, we are able to map different levels of data granularity to the reporting cube with no impact on processing time.

A little "out of the box" thinking allowed us to provide a solution that exceeded the client's requirements.

Questions or comments? info@crownpartners.com

Missing your Hyperion conference? Why Not Kaleidoscope?

Questions or comments? info@crownpartners.com

An OLAP Primer – Part 2

OLAP Overview continued

Previously, we reviewed some of the history of OLAP databases and touched on the advantages of using OLAP for analytical purposes. This entry will take a slightly deeper dive into the technology behind OLAP databases.

OLAP versus RDBMS - Which is Better?

We have reviewed the advantages of an OLAP database (interactive, ad-hoc, fast, multi-dimensional, hierarchical, etc.) as compared to a traditional RDBMS, so the question in your mind is "Shouldn't I just migrate all my databases to OLAP?" The answer, of course, is no. Relational databases are MUCH better at transactional level detail (although some recent enhancements to OLAP technologies have made text-based data easier to access). Relational databases are also more efficient at storing the "details". The correct answer is for a company to have the best in analytical capabilities, both technologies should be utilized: OLAP to pivot, drill, and isolate the area of analysis and RDBMS to provide the details for that area. A combination of relational and OLAP will provide the best analytical tools for your business.

The key to this is to have a strong RDBMS (i.e. data warehouse) that can feed the OLAP engine. This requires historical data to permit the OLAP engine to derive and project additional data, extending the analytic capabilities.  Remember, OLAP is an analytical engine, not just a data store.

OLAP Components

The OLAP database is really just one big hierarchical structure. The database consists of dimensions; dimensions consist of hierarchies; hierarchies consist of members; all of these describe the measures or the data.

Let's take a look at each of these in a little more detail.

Measures

Measures are the quantitative items that are reported about the business. The numbers that are analyzed are the measures in an OLAP database. When I am teaching this technology to people that have had little exposure to OLAP, I use the Jerry Maguire analogy ... "Show me the money!" Money is the measure. Simply put, the "show me" part of the question is usually asking about the measures in the database.

• Show me the DEPOSITS
• Show me the MARGIN
• Show me the COSTS
• Show me the SALES

If these are the questions being asked then Deposits, Margin, Costs, and Sales are all MEASURES in the OLAP database.

Measures can be classified in two types:

• Standard - data that is loaded into the cube
  • Sales
  • Costs
  • Quantity
• Derived - data that is calculated within the cube after the data is loaded
  • Margin
  • Profit
  • Inventory Turns

Dimensions

Dimensions are most easily described as the identifying characteristic of the data. Dimensions are a classification or attribute of the Measures. Back to the Jerry Maguire reference ... dimensions are the "By" or "For" part of the question.

• Show me the DEPOSITS for BUSINESS CUSTOMERS
• Show me the MARGIN for WIDGETS
• Show me the COSTS for SHIPPING
• Show me the SALES for OHIO

If these are the questions being asked then Customers, Products (Widgets is a member - more later), Cost Type (Shipping is a member), and Geography (Ohio) are DIMENSIONS in the OLAP database. Dimensions represent how a business describes the data.

Common dimensions include:

• Time
• Geography
• Product
• Customer
• Scenario
• Organization
• Channel

These are examples - dimensions can really be anything that describes or classifies the data.

Questions or comments? info@crownpartners.com

An OLAP Primer – Part 1

OLAP Overview
My previous blog entries have discussed various ways to utilize Essbase for a company's analytical processes. At this time, I'd like to step back and provide an OLAP (OnLine Analytical Processing) primer for those readers that may not be as familiar with the technologies. Many readers are familiar with relational databases, even if it is only with casual use of an MS-Access database to perform some simple database queries. While relational databases are better suited for transactional and textual data, OLAP databases and arranged and stored for fast analysis. OLAP databases are inherently designed to avoid the limitations of relational databases which are not well suited for instantaneously retrieving and analyzing large amounts of data.

E. F. Codd is considered the "father of relational databases." In 1994 he introduced his 12 rules for OLAP. These rules included 4 differentiators:

• Multidimensional
  Users analyze numerical values from different dimensions such as Product, Time, Scenario, Customer, and Geography.
• Consistently Fast
  "Speed of thought"
• Varying Levels of Aggregation
  Pre-aggregated summaries of data stored in hierarchies allows users to "drill to detail" rather than "sift through the details"
• Cross-Dimensional Calculations
  Database provides calculations across multiple dimensions

The following table illustrates how OLAP databases overcome some of the limitations of relational databases.

Relational Limitations	OLAP Capabilities
Singular in nature	Interactive / Ad-hoc
Slow to deploy	Fast deployment
Normalized / Denormalized Design	Dimensional/Hierarchical
Speed varies; often slow	Consistent; speed of thought
Sophisticated calculations in query language	Sophisticated calculations part of database engine
Multi-dimensional SQL is not efficient	Multi-dimensional query language is part of the database
Summarized data is labor-intensive and expensive to store	Summarized data is inherent to the database design

The intent of this table is not to infer that OLAP is better than relational - both are very good at what they are intended to provide. A combination of relational and OLAP will provide the best analytical tools for analysis.

OLAP Flavors
OLAP databases are prevalent across the software industry. Each of the OLAP software vendors has taken the same basic rules and developed versions that excel in a particular area. All of these are viable alternatives and have pros and cons as to why they would be valuable in your company. Regardless of which one you choose, your company needs one of these!

DOLAP - Desktop (or Dynamic OLAP)

• All computations are done on the desktop in virtual cubes after data has been extracted from the source
• Advantages:
  • Inexpensive
  • Development is not extremely technical in nature
• Disadvantages:
  • Limited in size
  • Limited capabilities and functionality
  • Typically slow

MOLAP - Multidimensional OLAP

• Traditional OLAP
• Data is stored in a multidimensional cube
• Proprietary database format provides fast response times
• Advantages:
  • Excellent performance - optimized for "slice and dice" and drilling operations
  • Complex calculations inherent
  • Easier to implement
  • Advanced data manipulation capabilities
• Disadvantages:
  • Can be limited by the amount of data (this has changed over time with technology improvements)
  • Requires additional investment of a proprietary database

ROLAP - Relational OLAP

• Uses traditional relational structures but gives the appearance of OLAP "slice and dice" functionality
• Advantages:
  • Can handle extremely large data sets
  • Can leverage functionalities inherent in the relational database
• Disadvantages:
  • Performance can be slow (uses standard SQL on the backend data queries)
  • Limited by SQL functionality
  • Requires database tuning expertise (aggregate tables, temporary tables, etc)

HOLAP - Hybrid OLAP

• Combines the features of ROLAP and MOLAP
• Integrates the relational database (ROLAP) with the proprietary database (MOLAP) to provide seamless integration
• Users can "drill through" from MOLAP to relational data
• Advantages:
  • Eliminates size restrictions inherent with MOLAP
  • Seamless integration
  • Best of both worlds
• Disadvantages:
  • Requires some technical expertise to "join" the two environments
  • ROLAP is inherently slower than MOLAP and does not integrate some of the MOLAP functionality
  • Requires education for user expectations

OLAP Technical Advantages

OLAP technologies have changed the analytical world by allowing analyses to be focused on a subset of a sometimes very large data warehouse or operational data store. Creating data marts (smaller subsets of data for focused analysis) has allowed companies to take advantage of the technology and provide near real-time, speed of thought analysis.

In future posts, we will explore some of the technology behind OLAP and OLAP database design that allow it to provide the speed and functionality. For now, let's review some of the high-level features that give OLAP a technical advantage over traditional relational stores.

OLAP (particular MOLAP) databases are often pre-calculated and pre-aggregated data that provides sub-second query response. Additional features, specific to analysis is built into the database itself, eliminating the need to create special functionality to perform analysis around things like time balance, expense reporting, variance analysis, and time series computations. Complex aggregations like rankings, moving ratios, medians, and various other statistical calculations and functionality are inherent to the database and available "out of the box." OLAP allows analysis to take the next step faster and easier.

Look for the next blog posting that will look closer into the underlying architecture of OLAP and a closer look at some of the architecture, functionality, and operational ability with OLAP.

Questions or comments? info@crownpartners.com

Utilizing Essbase for Hospital Analytics

Essbase was developed with financial reporting in mind. As my last blog post illustrates, the power of Essbase can be expanded and utilized in many areas outside of financial reporting. Today, we'll take a look at one of these areas more in depth. Healthcare is obviously a high profile industry these days. Much of the discussion is around controlling the costs and (hopefully) maintaining the service levels in today's hospitals and healthcare facilities. Our team has performed project work around hospital analytics - financial and operational key indicators reporting to support hospital management. Through this work, we have developed a list of questions we like to review with hospital management and administrators to determine how effectively their hospitals are being managed. This blog entry will share some of the areas Crown Partners sees as key understandings to managing an operationally efficient and profitable hospital or medical facility.

Accounts Receivable

• Are you able to track your outstanding AR and the length of time that it takes to collect? 
• Are you able to qualify the outstanding AR and the impact that additional time to collect has the operation of your hospital(s)? 
• Is your outstanding AR better or worse than it was last year? Last quarter? Last month?

Bad Debt Analysis 

• Are you able to track your Bad Debt in relation to other financial indicators?
• What impact does Bad Debt have on your outstanding AR? 
• Are you able to predict Bad Debt for current or future patients based on analysis of insurance providers and self-pay trends? 

Patient Analysis 

• Are you able to analyze your patient volume based on the type(s) of insurance the patient is covered by?
• Are you able to analyze your key operating indicators (do you have key operating indicators?) by insurance type (Medicare, Medicaid, Self Pay, Government, Non-government, etc)?
• Are you able to accurately forecast and plan based on patient volumes based on historically trending of these volumes and the patient mix (DRG codes, ICD-9 codes, inpatient/outpatient mix, patient age, etc)?

Insurance (Payor) Analysis 

• Which insurance carriers provide the least/most amounts of denials and discrepancies? 
• How does the self-pay portion of your business affect the overall business? 
• Are certain carriers better/worse when they are secondary/tertiary versus the primary company? 
• How does the volume mix across these payors affect your overall performance? 
• Within a particular group (i.e. Cigna, Aetna, BCBS), do certain regions and branches provide better/worse service and response to your company? 
• Can you measure the impact of this?

Charge Analysis 

• Are you able to analyze insurance denials and discrepancies?
• What is the trend of denials/discrepancies being resolved, overturned, corrected, etc? 
• Is the trend showing that denials/discrepancies are better or worse than last year? Last quarter? Last month? 
• Are denials/discrepancies more prevalent on particular payors?
• Are you able to classify these based on the total amount charged to determine impact on the overall process? 
• Which has more impact on your business - charge variances or payment variances? Are these variance trends better or worse than last year? Last quarter? Last month?

Operations Analysis 

• Do you have metrics to analyze the patient "experience" when they enter your facility? 
• For example, in an Emergency Department: Are you able to measure the time from the patient's arrival to when they initially talk with hospital staff? 
  • From that time to when they are assigned a bed/room? 
    
  • From then to when the patient disposition is completed? 
    
  • From that time to departure? 
• Are you able to measure these against the patient severity (DRG, ICD-9, etc)? 
• Are you treating the most severe dispositions in the most expedient manner? 
• What peak times does care take the longest? 
• Are staffing levels designed to incorporate these peak times? 
• Are you able to measure whether care is better today than yesterday? Than last week? Than last month?

In all of these examples, are you able to start your analysis at a very macro level (total organization if more than one hospital)? Are you able to drill to lower levels of the organization (region, state, hospital, department)? Are you able to drill all the way down to the actual patient level detail (demographics, charges, etc) when you get to the lowest level of your analysis? 
Essbase can provide the necessary drill paths to allow analysis to provide the answers to all of the questions above. The OLAP capabilities provide the engine opening hospital administrators to better manage their institutions. By combining dashboarding and reporting solutions on top of the Essbase database, healthcare facilities can focus efforts and profitability, operational efficiencies, or other key performance indicators, strengthening their position as leaders in their industry.Questions or comments? info@crownpartners.com

Manufacturing and Distribution Network Applications for Essbase

Essbase development has traditionally focused around financial applications. The Essbase calculation engine and the function libraries are loaded with numerous financial-based functions and calculations. In the early years of Essbase, this was the easiest place to sell Essbase, as financial managers were used to working in a spreadsheet model and understood pivoting data. Some early adopters of Essbase (myself included) implemented Essbase solutions outside of the financial spectrum yet, the majority of Essbase applications were inside this arena.

Over time, the scope of Essbase usage has increased to include numerous applications outside of the financial arena. The application of Essbase in an analytical space is truly immeasurable. Multidimensional models can be built around basically any facts (data) that can be described by other dimensional attributes. The purpose of this blog entry is to introduce some of the non-financial applications that we have implemented with Essbase across our vertical markets.

Distribution Network Performance
We have created an Essbase model that captures shipment performance metrics across a company's distribution network. The application captures ordering and shipment information from the company's ERP system and calculates performance metrics around the distribution network's performance.

Key performance indicators of this system include:

• In-region shipping performance
• Out-of-region cost of delivery
• Replenishment rates for warehouse inventory
• Order fulfillment rates
• Back order analysis including back order percentages by region, market, product, and brand
• Order / delivery cycle times
• Shipment errors
• Order / Line item fill rates

With this system, the company has the ability to monitor how well their distribution network is performing, the effects of good and bad performance trended over time, and focus improvement efforts on specific areas where improvements are required.

Product Pricing and Profitability
In many companies, product pricing is often contracted across the customer, products, brands or some other dimensional attribute. Most companies still retain a standard price book but the bulk of the companies' sales are unique to a customer contract (sometimes nearing 100%). Understanding the impact on these contracts becomes increasingly difficult as the quantity of sales, number of products, and number of customers increases. Analysis of this data is often seen as too difficult simply because of the amount of data that must be examined. Typically, a company will look more at a macro level (Are overall sales and revenues increasing?  Is total product margin being sacrificed?) because the capability to dive deeper into the data doesn't exist.

Our solution allows a company to organize the sales and contract data based on hierarchies of product, brand, and billing/shipping customers. A number of metrics were utilized, including customer groups, product price brackets, standard price book impact, regional/district geographies, shipping locations, and shipping costs.  Examining these metrics with a hierarchical view of the attribute dimensions allow the company to focus on areas that will provide the most margin or revenue gain.

Sales & Marketing
While somewhat closer to the traditional financial metrics, sales and marketing metrics solutions focus more on the impact of the general marketing strategy of a company. In simplistic terms, the goal of marketing activities is to increase sales of the company's products. Like any other business investment, it is important to understand the return on investment (ROI) of any marketing activity. There are a number of marketing channels and each channel must be analyzed separately and in total with the other channels. Basic metrics of interest include quantities, revenues and margins.

The key to the sales and marketing Essbase analysis is to surround this standard sales-related data with dimensionality that represents the marketing efforts. The analysis benefit comes from the company's ability to recognize sales, revenue, and margin performance in concert with the marketing campaigns in play at the time. Standard sales revenue dimensions include customers, brands, products, geography, and salesforce. Additional, marketing-specific dimensionality is layered on top, providing trend analysis and marketing impact based on campaign goals and campaign types (print, media, web, etc.).

Bookings and Shipments
Bookings and Shipments analysis, similar in nature to some of the sales, marketing, and distribution channel solutions already discussed, is closely aligned with the performance of the customer service and distribution areas of a business. The key questions being answered: how many orders did we take yesterday and how much product went out the door?

This application takes the traditional "booking" a step further. Through the power of the Essbase engine, a true measure of the company's bookings is calculated through examination of order changes, order cancellations, back orders, and other items normally omitted from the bookings calculation. High level shipping analysis is available, showing the quantities shipped each day. Additional performance analysis can be found by joining the Distribution Performance application through partitioning or advanced analytical reporting.

Dimensionality available for analysis includes products, brands, production facilities, distribution network attributes, and customer demographics (including billing and shipping geography).

A Partner for your Essbase Needs
Whether your company's Essbase requirements involve financial or non-financial applications, a Crown Partners expert can help.  To learn about more Hyperion solutions that we've developed, visit our website at http://www.crownpartners.com 

Questions or comments? info@crownpartners.com