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

Lessons Learned from System 9

Question:

Why use Shared Services External Authentication with Essbase?

Answer:

1. Reduced Administration
   
   LDAP Users and Groups flow seamlessly into Essbase via Shared Services. For the Essbase Administrator, maintenance of security can be reduced to provisioning authorities to LDAP groups. Thus, when a new user is to be added he or she will automatically get the proper essbase security simply through the corporate security administrator establishing the user's id in the appropriate LDAP group.
   
2. Automatic Refresh / Synchronization
   
   SHAREDSERVICESREFRESHINTERVAL setting in the Essbase.cfg file can establish an interval for periodic refreshes from Shared Services to Essbase. This setting is in number of minutes. To refresh every 30 minutes, the setting would be SHAREDSERVICESREFRESHINTERVAL 30
   
3. Essbase tasks not related to establishing users and groups remain in EAS Console
   Tasks like the creation of Filters, Calculations, Load Rules, and Substitution Variables continue to be performed in the Essbase Administration Console. This means that the Essbase administrator does not need to learn new tools for managing those features.
   
4. Import / Export utility provides easy backup and restore
   Provisioning information from Shared Services can be easily exported to XML using the utility that is packaged with Shared Services. This file contains all information about the LDAP users, groups, and provisioning. This same file could be used to import the provisioning in the event of a disaster recovery, file corruption or server upgrade.

Question:

What happens to Native Essbase Ids when I Externalize security to Shared Services?

Answer:

Any users that exist as native Essbase users prior to the externalization get converted to LDAP users. Shared Services installs an LDAP utility named OPEN LDAP. Once you externalize to LDAP, everything is LDAP. The term Native User really means and OPEN LDAP user, not an Essbase user that can be controlled via the EAS Console.

Question:

Does Shared Services become a single point of failure if I externalize security?

Answer:

Yes and No.   The LDAP specific information is stored in files on the Essbase server and synchronized with Shared Services.    If Shared Services and OPEN LDAP are not running, "native" users cannot connect to Essbase.   However, if the Essbase service is running, External LDAP users will still be able to function normally as long as Essbase remains active.   If Essbase services are stopped, they will not be able to restart successfully since the service account is a native (i.e.: Open LDAP) id.    At that point, all users would be unable to function until the Shared Services/Open LDAP services are running successfully.

Question: 

Once I externalize Essbase security is there any way to go back to true native Essbase security?

Answer:

Generally, you will hear that the answer to this question is NO.   However, I have found that you can go back to true native mode if you take the following actions.  

1. Delete the Essbase.sec and Essbase.bak files
2. Stop the essbase service
3. Modify Essbase.cfg to remove or inactivate the AuthenticationModule line
4. Start Essbase as a console app
5. Setup the initial user id just as you would during an install
6. Stop Essbase and Restart the service

Questions or comments? info@crownpartners.com

EPM Utilities

 

Author: Abe Combs 

The Crown Partners EPM team has developed several utilities around the Oracle Hyperion EPM toolset to help our team and our clients. Most of these originated because we, as developers, were repeatedly required to do similar tasks across multiple projects. Crown prides itself as a hybrid business (Software & Services) so we thought "Why not? Let's build our own!" Today, we will take a brief look at each of these products.

Smart List Creator

Smart Lists are a great addition to the Oracle Hyperion Planning package. The biggest pain point is the time it takes to create and maintain these lists. The Smart List Creator facilitates Planning administrators to create and maintain Smart Lists with ease. Don't spend hours typing in Smart List entries one at a time! With this utility you can build out your Smart Lists in minutes instead of hours.

Essbase Cube Validation Tool

How many times have you migrated an Essbase cube or created a new, slightly modified cube and then had to go through the arduous process of validating that the data is correct? Do you, like most others, create numerous spreadsheets with an "Old", a "New", and a "Variance" tab to prove the data is the same? It's a time-consuming task and when you are done, you still haven't checked every intersection (heck - there might be 23 billion of them!). The Essbase Cube Validation Tool programmatically checks and validates all the data you can think of. The tool then produces a report defining each cell that differs between the two cubes ... in minutes!

FDM Accelerators

Crown's FDM Accelerators are snippets of code designed to be dropped into a user / developers existing FDM scripts to assist them with their own development efforts.  They are not complete working functions, but rather, examples that Accelerate the development process.  Here's a list of the functionality currently demonstrated in our accelerators.

Get HFM Entities This accelerator shows how to get all descendants of an HFM entity parent as well as how to evaluate those members as base level.

HFM Data Extract This accelerator produces an HFM data extract for a single entity.

Execute Essbase Calc This accelerator launches an Essbase calculation.

POV Driven Essbase Extract This accelerator demonstrates FDMs ability to dynamically build and launch a report script as well as its' ability to call an existing script.

POV Driven Batch Load and Status Check This accelerator performs robust batch processing and error trapping functionality.  It checks the status of each file in a batch and indicates overall batch success and prepares a status email.

Task Flow Security This provides the ability to evaluate security access and grant permission to Task Flows as desired.

Launch External Applications This accelerator demonstrates FDM's ability to call external programs such as a MAXL script.

Custom Table View This accelerator open's a user relational table for input.  This script would typically be launched from task flow menus.  It opens up a table for user input.

Excel Multi-Spreadsheet Retrieve

Never again will you have to retrieve multiple times in an excel workbook.  The Excel retrieval tool allows you to create customized named range prefixes and assign those prefixes to specific Essbase databases.   You can have numerous retrievals on the same sheet and across the workbook, against several different cubes.

Outline Validation Tool

Ever have a successful data load, but still not validate to the source?  Tired of looking through scores of accounts for that needle in a haystack that was supposed to be time balance last but isn't?  Or that upper level account that was supposed to be dynamic but was set to stored?  This Outline Validation tool allows you to set up meta-data parameter checks, then search the outline and make sure it conforms to your cubes specifications, identifying the outliers in seconds.   Make meta-data validation a part of your data processing cycle with this handy utility.

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