What is Essbase? A question I often get from my friends and family when I am enthusiastically sharing my adventures with this software. And, although a simple question, it is not always so easy to explain in layman’s terms. That’s why I created this blog posts. It is targeted to all persons that have stumbled upon “Essbase” and want to know what the hack it means. And if you thought it was written as “S base”, you are welcome too.
There is a little bit of IT foreknowledge required, a basic understanding of Excel and a level of conceptual thinking. So let’s start where most people start on a topic they do not know of.
what is Essbase, a google search
If you do not know the topic, google it! So, let’s do that. Google on “Essbase” and if you are visually oriented like me, you will probably start on the images tab. This is what you get:
First impression: very technical stuff and it has something to do with a cube.
Somehow, the shape of cube (sometimes even Rubik’s cube), appeals when talking about Essbase. And there is a connection, but more on that later. If you would have searched on “All” you would have gotten the following definition by Oracle:
Oracle Essbase is an OLAP (Online Analytical Processing) Server that provides an environment for deploying pre-packaged applications or developing custom analytic and enterprise performance management applications. Oracle Essbase, along with Oracle BI Suite Enterprise Edition Plus, is part of the Oracle BI Foundation.
Now, this is only for illustration and not for explanation. So please do not run away … I am trying to bring you into my “world” of Essbase and I promise to keep it simple. But let me tell you a little story first about my encounter with Essbase and then try to find the relation with a “cube”.
Essbase, my first encounter
Back in 2001 when I got introduced to Essbase, I was really confused about its concepts. I was told from day one that Essbase is a database that works closely with Excel; as you could find in the name (it was not obvious to me) for “Extended Spreadsheet database“. At that time, I was specialized in relational databases and was king in working with tables, columns and rows including the programming language SQL. Spreadsheets were yucky and needed to be replaced with solid applications. My world-view on data was completely defined by databases and I valued all solutions against these concepts.
So, when I first learned about Essbase, that it was a kind of Excel-like database, I translated its concepts to the tables and columns “world” I was familiar with. My first impression about Essbase at that time? Get rid of it and rebuilt it as a relational database! Nothing better than that. Step-by-step I realized that such a thing was impossible and that Essbase had a view on data that was completely new to the view I had on data. A collision of world-views in IT land, including all “opinions” 😉.
Over time, I discovered that both “views” on data coexist with each other. More precise, Essbase solved business problems I was not aware that they could be solved at all.
So, what do we know so far? What is Essbase?
- is a database, but not one with tables and columns;
- does something with Excel spreadsheets;
- supports a different view on data than the regular view;
- can solve business problems that can not so easily be solved with standard tables and columns.
Let’s now dive a little bit deeper … hang on.
what is Essbase, a deep dive
What is so different about Essbase? Let’s take an analogy from Excel, which I assume you are familiar with. In the following picture, you will see an Excel list, formatted as a table and you will see an Excel pivot table. You can compare the Excel list with a classic table with columns and the pivot table is the perfect analogy for Essbase. Both have the same data, but completely different presentation and serving a different user audience:
The pivot-way of working with data might be new. Have a look at the right table. It is the same information, but the “city” data is grouped into countries and continents and the “date” values are grouped in years. The values in the table are summaries (or aggregates) of the “values” in the Excel list. (I recommend you view a couple of YouTube videos on the Excel pivot table to grasp the idea of presenting information this way.)
Essbase is like the Excel pivot table. And as you can see, a pivot table has a horizontal and a vertical axis; it is a matrix like structure. If you play around more with a pivot table (using other data) you will notice that you can also add a third axes as a page value or as different tabs. And – here – the comparison with a “cube” is created.
Let’s do that and add two additional columns “service” and “scenario” and change the Excel table:
The pivot table now has as “page item” the “service” with selected value (filter) “training”. The value “250” is graphically plotted in a xyz coordinate system with the fixed values for country, year and service on the respective axes.
As you will now see, the form of a “cube” appears with 3 dimensions.
If we add the “scenario” value as well, you will see you can not plot it anymore as a whole. Only if you divide the data into a subset with just one value for scenario, you can plot it in 3d again. And, in this particular example it is even possible to show all data, as there is only “actual”. But when you use Essbase to generate “budget” and/or “forecast”, of course it is not possible anymore.
Hence, the name “cube” for the Essbase database, or more generally spoken, “multidimensional” database.
So, what concepts have been added to Essbase? What is Essbase?
- allows to view data in a coordinate system, like an Excel pivot;
- is a multidimensional database.
what is Essbase, dive deeper
But there is more to Essbase than just being a multidimensional database. Another feature that makes Essbase standout is the ability to write data from the “pivot” view. So consider you would like to change the 250 to 350 in the image above, you can change that and store the data in the database. Anyone opening the database in the pivot view will then see the new value 350. This “data write” functionality makes Essbase very suitable for “planning and budgeting”, allowing to view the actual (real life) data and make plans for the future (write data back to create a budget or a forecast).
You can even go a step further and have Essbase create an automated forecast based on certain driver values. This is supported by the unique Essbase feature to create complex logic. You can do this in the so called “business rules” or “calculation scripts”. Although more databases can do this, the uniqueness is in the fact that you can store the results of the logic back in the database and build new logic on top of the results of the previous calculation.
Take for example the following common business calculation:
- calculate the revenue using the product volumes, the price lists and the deductions, translate the result to the companies base currency and calculate the total, but deduct inter company sales.
A pretty complex calculation, but when you break it down into sub components that run in sequence, it will become relatively easy.
- first calculate the revenue and margin using the formula “product price” times “volume per product” for each product
- convert the result to the company’s base currency “EUR” (remember I am based in The Netherlands)
- deduct the inter-company sales between the countries and business units
- aggregate the revenue and profit
By breaking business logic down to single steps that need to be run in sequence you can solve almost any business problem, making Essbase suitable for driver based modeling, strategic forecasting and planning & budgeting.
The unique feature of implementing complex logic is also its Achilles’ heel; if you do not design Essbase models in the right way you will run into performance issues. Hence the reason why we created this web site; namely to provide the Essbase best practices for building solutions that run really fast. If you ignore these “golden rules” of design you will run into performance issues sooner than later.
what is Essbase, the summary
We covered the most fundamental aspects. So, what is Essbase?
- is a multidimensional database (comparable to Excel pivot table);
- can solve business problems that can not so easily be solved with standard tables and columns;
- allows to store data from within Excel;
- has the unique ability to implement complex business logic in a step-by-step way;
- could run into performance issues if you do not really understand how Essbase works.
I hope that with the information in this post you can continue to learn more about Essbase.
Now we are at it, this might be the time to introduce some “parts” of the Essbase cube, which are:
- dimension – one of the axes in the Essbase cube with predetermined set of items (e.g. “country”);
- member – one of the items in the set of predetermined items in the dimension (e.g. “Paris”)
- hierarchy – the placement of members in totals and sub-totals in a dimension (e.g. “continent” – “country” – “city”)
- value – the value in the multidimensional space with a selected item for each dimension.
Also in the “cube” view of the “data world” the following terminology is used:
- slicing and dicing – moving dimensions in the “pivot table” report
- drilling – zooming in or out on the aggregates, e.g. from “Europe” to the “countries”
- drill down – zooming in
- drill up – zooming out
- drill through – show the underlying table data for an aggregate
May be I will detail these concepts in a future blog post.-----
share this post