Understanding Essbase

Now that I’ve got Hyperion Essbase installed and seemingly working OK, I thought it worth taking a look through the Essbase manuals to try and get a feel for how Essbase works. In particular, I’m looking to see what it has in common with Oracle OLAP and Express Server, partly out of curiosity but also so I can start to think about when to recommend one OLAP server or the other. Of course, when you’re used to one particular OLAP server (or database, or language or whatever) there’s a danger that you tend to try and pigeonhole the new tool and end up missing what’s new about it, or what it does differently, and so whilst going through this exercise I’m trying to keep an open mind and recognize when it does something different, or better.

Essbase (or Hyperion Analytic Services, as the manuals now seem to refer to it) is a multi-dimensional database that, like Oracle OLAP, stores data in a format optimized for fast, dimensional access to detail and summary-level data. Unlike Oracle OLAP, whose primary interface out is SQL and OLAP DML, Essbase uses it’s own language called Report Script (I think) for getting data out, analogous to OLAP DML, MaxL for scripting (derived from PERL), MDX and XML/A. Support for MDX and XML/A is particularly interesting to me, as it’s pretty much the standard language these days for accessing multi-dimensional databases through Microsoft’s support for it in Analysis Services, but for one reason or another Oracle have never got Oracle OLAP to support it choosing instead to go down the SQL route.

Essbase, like Oracle OLAP, uses the concept of cubes, dimensions, measures, hierarchies and cell-based storage. Essbase also has this concept of Database Outlines, which contain the metadata about the hierarchies, dimensional relationships, aggregation paths and formulas used by a cube. The diagram below shows the content and structure of a typical database outline, for one of the sample OLAP databases that comes with Essbase.

Oracle OLAP also holds metadata like this but not in such an explicit way – basic relationships, hierarchies and aggregation rules are held in low-level objects such as relations and other data dictionary items, and more recently Oracle OLAP’s Standard Form Metadata stores cube metadata in a form that’s readable from the Java OLAP API, but in general there’s no specific “outline” you create in Oracle OLAP, you just create cube metadata which just gets stored “somewhere”, you don’t separately manage it as a specific object.

One fundamental difference between Essbase and Oracle OLAP is that Essbase comes with two multi-dimensional storage options:

• Block Storage Option, the original storage type for Essbase, which permits the full set of Essbase calculation functionality and stores data accessed by sparse and dense dimensions
• Aggregate Storage Option, a more recent introduction, which stores data in quite a different way to Block Storage and is particularly suited to storing read-only data with high dimensionality.

From my initial scan through the manuals, Block Storage Option (BSO) seems the most Oracle OLAP / Express-like, with Aggregate Storage Option (ASO) looking more like the storage used by Microsoft Analysis Services.

BSO divides dimensions up into sparse and dense dimensions, with dense dimensions storing more cells per block, accessing data faster but requiring lots of RAM for large data sets, whilst sparse dimensions store sparse data more efficiently but at the cost of potentially large indexes and more time required to actually access the data. Whilst Oracle OLAP also has sparse and dense dimensions, it’s storage mechanism doesn’t directly compare – data in Oracle OLAP is stored in pages, with composites doing what Essbase indexes seem to do by only storing the dimension member combinations (“tuples”) that actually contain data.

I need to look at this a bit more, but I suspect there’s more similarities than differences between BSO and Oracle OLAP storage, as dimension sparsity seems to be a key factor together with the number of dimensions and the numbers of members that they contain. ASO in contrast doesn’t seem to suffer from the same restrictions, but from looking in the manual doesn’t support as wide a range of calculations and aggregation methods as BSO. Interestingly, some of the Hyperion apps require ASO, some require BSO, with ASO tending towards read-only, sales analysis-type apps, and BSO being more for financial analysis, write-back and dense cubes.

Hyperion recommends in most cases for BSO implementations a mixed sparse and dense dimension setup (just like Oracle OLAP) which produces a good combination of relatively dense but small cubes that fit into memory, and indexes that aren’t so big that they take ages to traverse, as shown in the diagram above.

In terms of the products you use, Essbase (or as I said, Hyperion Analytic Services as it’s called now) is part of a wider BI framework called Hyperion System 9, which has something called Shared Services at the centre that provides user provisioning and other centralized, general functions. Now I’m not sure whether it’s mandatory to set up and use Shared Services along with Essbase, or whether Essbase can run standalone and just use Shared Services when it needs external authentication against an LDAP server, for instance. I suspect it’s the latter as I’ve got Essbase running but I think Shared Services is misconfigured (see yesterday’s posting and the comment by Vencat for more details). Other than Shared Services, there are a number of Essbase-related tools that you’d generally use when implementing the product.

Essbase uses a three-tier architecture where the data is stored in Analytic Services databases (aka Essbase databases) which either talk directly to a client tier via TCP/IP, using tools such as SmartView (the MS Office add-in), various C and Java APIs and tools using MDX, XML/A and so on.

For developers, there is an Administrative Services client tool which talks to Analyic Administration Services, an mid-tier admin tool used for creating databases, outlines and so on, and Analytic Provider Services, used for clustering, fail-over and so forth. Another tool, Analyic Integration Services, has a mid-tier and client component and is used for integrating Analytic Services with relational databases. I’m sure if Integration Services is just used for hybrid, “HOLAP” implementations where for example the detail-level, transactional data is held in a relational database whilst aggregates are stored in Analytic Services, or whether it’s the main route in for relational data when actually loading and refreshing the cube. I’ll have to work through the examples and try and work this bit out.

Anyway, that’s my brief overview of the Essbase/Analytic Services platform, as gleaned from going through the manuals on the flight over to HK. In summary, I’d say that the conclusions from this were as follows:

• When you define an Essbase/Analytic Services cube, a fairly fundamental decision is whether to go for Block Storage (the traditional storage type, fairly similar to Oracle OLAP and based around blocks and sparse/dim dimensions), or Aggregate Storage, useful for cubes with lots of dimensions and members and typically, sparse data.
• Like Oracle OLAP, once you’re using BSO, you need to pick your sparse and dense dimensions wisely, although like Oracle OLAP, there’s an advisor that helps you do this (not, however, like Oracle OLAP does by sampling the data, but by looking at the dimensional and hierarchy structure instead)
• Essbase uses the concept of dimensions, like Oracle OLAP does, but there are several variations like attribute dimensions, account dimensions (that actually contain the measures you’re using), time dimensions and so on. More on these at a later date.
• Essbase seems to have a fairly well developed set of developer tools, client-server mid-tier integration and clustering tools, plus it has features already set up for hybrid OLAP implementations that retrieve detail-level data from relational databases.

That’s it for now though, just off in a minute to catch the connecting flight from HK to LHR. I’ll see if I can work through a development example on the plane.