When I start a new project, it’s always an exciting time for me (and I’m sure my previous clients are excited to get rid of me too!). In some cases, these new clients are seasoned veterans when it comes to Oracle Essbase, with existing solutions spanning multiple cubes, technologies and environments. At other clients, we are starting with a clean business analytics slate, and an opportunity to try new things. In both cases, if it’s a business analytics initiative using Oracle technologies, I always find myself having a conversation with a variety of different folks answering the same question, “What is Oracle Essbase?”
This is always an interesting conversation, revolving around cubes, architecture, data storage, blocks, ASO, BSO, calc scripts, business rules, hierarchies, Hyperion Planning … the list goes on. I figure there are probably a few readers out there who might be interested in a quick explanation, so read on! Hopefully this provides a nice level set and starting point; if it doesn’t, please comment and ask questions!
Oracle Essbase at its core is a data storage tool, which does a particularly good job organizing data for financial reporting, planning, budgeting, forecasting, and analysis at multiple levels of detail. Oracle Essbase stores and structures the data in a meaningful way to be accessed via Oracle solutions such as Oracle Hyperion Smart View for Office, Oracle Hyperion Planning, Oracle Hyperion Financial Reporting, and Oracle Business Intelligence Enterprise Edition (OBIEE). For those of you who are thinking “so…Essbase is just a database,” you aren’t too far off!
When most folks talk about databases they are referring to relational databases like Oracle Database or Microsoft SQL Server. Oracle Essbase is a multi-dimensional database or OLAP database, which stores data in a completely different way. I’ll get into that in just a second, but before I do here are a few other definitions that I gathered when I asked some of my Performance Architects consulting colleagues how they define Essbase:
Consultant Conversation #1:
“Essbase is an On Line Analytic Processing (OLAP) technology that was created in order to quickly answer business questions.
Simple questions include:
- What is my full year expense budget?
- What are my current year actual expenses?
- How are my actual dollars tracking against my budget dollars?
More complex questions include:
- Which department maintains the largest variance from budget?
- Which product has the highest sales growth for the last five years?
- Which product is most profitable, after allocations?
- Many, many more “What if” questions!”
Consultant Conversation #2:
“A piece of code (or software) that lets one look at data by its various characteristics (dimensions), organized by business models (theoretically).”
Consultant Conversation #3:
“Oracle Essbase is the leading multidimensional database system that provides a platform for complex analytical and custom applications. It can be used by end users to analyze their business and to make rapid decisions while providing complex reporting capabilities out of a common toolset.”
Digging in a little further…data in Essbase is described by dimensions which help describe the data point. Dimensions might be something like Accounts, Cost Centers, or Products. In the screenshot below, you can see two example retrievals in Excel. The first retrieval shows individual balances in January defined by a variety of dimensions: Company, Cost Center, Fiscal Year, Product, Currency, Account and Time Period (Month).
In Retrieval #2, the data has been aggregated across a few dimensions, taking the Company viewpoint from a granular level of detail to a much higher level of detail. The same has been done for Cost Center and Product, giving us a summary view of the business. Make sense?
We can have data for multiple periods and multiple years in an Essbase cube at any one time. But wait! We can layer in even more detail by adding dimensions like scenario or version.
In this expanded example, we layered in Scenario helping us dissect our forecast data from our actuals data. Not only have we done that, but we have also dissected our forecast interactions from one another by leveraging a Version dimension. From here, we can further analyze the data, and have a word with Company 400 about their initial forecast. Grossly overstated?! All of this data can be stored in the underlying Essbase database, and retrieved when required.
As for data storage, Essbase stores data in its own proprietary format, and getting into that is a whole other question, because it depends on which type of Essbase database we are talking about!
That brings us to a second portion of this whole “What is Essbase” question and answer session. There are two types of Essbase databases, which do certain things very, very well. Option number one is the “Block Storage Option” or BSO. The other is “Aggregate Storage Option” or ASO. These two Essbase cube types have their own strengths, which lean heavily into the design decisions that we make with our clients.
BSO applications are great for the manipulation and transformation of data, such as calculating healthcare costs for next year based on a rate, or transforming data based on a series of exchange rates. Data in BSO applications is manipulated using calculation scripts and business rules, which provide the math that Essbase needs to perform.
“Aggregate Storage Option” applications are extremely useful for the rapid aggregation of data on the fly. Data is only stored at the detailed level, and aggregated when users request information. In our old retrieval, an ASO cube would only store Retrieval #1, and then aggregate the data on the fly for Retrieval #2. This is particularly useful when we have a large data set that is updated frequently. A use case might be a reporting cube built on E-Business Suite (EBS) data that is refreshed throughout the month close with more information. ASO might be an excellent option when it comes to summarizing those detailed general ledger balances. Deciding on the database type is always part of a larger design conversation, but certainly one that comes up during the regular “What is Essbase” conversation!
All right…enough for now, plenty more to cover in other posts. Hope this brief definition has been somewhat enlightening. If you have any questions feel free to comment!
Author: Tom Blakeley, Performance Architects