Essbase Implied Shares

Recently, one of our clients was experiencing an issue where they thought Essbase was returning some incorrect results. They were running a calculation script they thought would clear some upper level blocks but it didn’t appear the blocks were clearing correctly. After some analysis, we found that they were experiencing the inherent efficiency of the Essbase cube when it comes to something called “implied shares.”

When Essbase encounters one of the following situations in out an outline:

1. Parent with only one child
   ProductGroupA (+)

                        Product123 (+)

1. Parent with only one child that consolidates

ProductGroupA (+)
                        Product123 (+)

Product456 (~)

Product789 (~)

1. Parent tagged “Label Only” (first child’s value)

ProductGroupA (~)   (Label Only)
                        Product123 (~)

Product456 (~)

Product789 (~)

Essbase will store the data the most efficient way by implicitly sharing the value of the child with the parent. In essence, the parent member doesn’t even exist within the stored data. When it is queried, the Essbase engine knows of the implied share and returns the value of the child.

 

As a developer, it is important to understand this concept when designing your outline. If you may perform calculations specifically on a parent that is an implied share, you need to understand what is really happening under the covers.

 

To help with this, a best practice to follow is to tag a parent with the “Label Only” tag if you really want it to take on a single (or first) child’s value. We often see outlines where the parent isn’t tagged label only and only has one child (implied share), or only the first child consolidates (implied share). While this may work, it is not efficient and a change to the outline could result in unexpected changes to the data when retrieved.

 

Essbase also allows a member to be tagged with the “Never Share” tag. “Never Share” will not allow Essbase to create an implied share during data loads or calculations. The parent is created as a separate member and exists in the Essbase cube along with the child. This obviously has some effect on the efficiency of the database and could result in a difference in performance. Additionally, the opposite effect of the “implied share” could occur – you think you should see the same data but you might not depending on the calculation executed.

 

Long story short … test the effects of these various tags and see which ones you require. Utilize best practices in your design and Essbase cube should return exactly what you thought it would!

Questions or comments? info@crownpartners.com

Are You Ready?

We’re guessing that some of you will be attending Oracle OpenWorld September 19-23 in beautiful downtown San Francisco. It’s the world’s largest conference for Oracle technologists, business users, and partners. And because we’re an Oracle Platinum Partner, you can count on members of our EPM team making the September trip to the City by the Bay. They’re also going because they know how to have fun, and it sounds like OpenWorld won’t let them down.

 

 

Still not decided on whether to attend? Here’s what you’ll be missing if you decide to take the road more traveled and hang out at the office that week.

 

The Big Picture

• 1,800 sessions, 400 partner exhibits, almost 400 Oracle demos, and keynotes from the world's technology leaders

 

Networking Events

• Oracle Welcome Reception – Sunday, 7:45 p.m. - 9:45 p.m. Join the Oktoberfest celebration and raise a stein of German ale with other attendees
• Oracle Appreciation Event – Wednesday, 7:30 p.m. - 12:00 a.m. If you’ve attended before you know it’s the best part of OpenWorld, and this year it brings

o   Black Eyed Peas

o   Don Henley

o   Steve Miller Band

o   Montgomery Gentry

• It’s a Wrap! – Thursday, 4:00 p.m. - 6:00 p.m.

 

Keynotes

See what's on the technology horizon by attending keynote speeches Sunday through Wednesday, featuring:

• Technology leaders
• Oracle top executives and key partners
• Technology sneak previews
• New product demos

 

General Sessions

Countless sessions will be offered, including many Hyperion-related and industry-specific sessions. Here are some sessions that might interest you:

• Oracle's EPM and BI Strategy and Roadmap – S318278
• What's New and What's Coming: Oracle Hyperion Planning – S316891
• Failure to Success: Best Practices for Driving Oracle Hyperion Planning Success – S319127
• Oracle Hyperion Applications Strategy and Roadmap – S317486
• Strategy Maps and Scorecards for Getting onto and Staying on the Same Page – S318319
• Using Oracle Essbase for Enhanced Financial Reporting – S315635

 

Things to do in San Francisco

When you’re not at the conference and still have per diem to burn:

• Fisherman’s Wharf
• Alcatraz
• Golden Gate Bridge
• San Francisco 49ers game Monday night
• Cable Cars
• Some of the best eateries in the country

 

Ready to register? You can still register for Oracle OpenWorld, with discounts offered for pre-registration.

 

Interested in learning about Crown’s Hyperion services and solutions? Email us at info@crownpartners.com or check out our Oracle Hyperion platform solutions.

 

By Kevin Bath 

Questions or comments? info@crownpartners.com

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

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

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