The purpose of this article is to not only to describe Aggregate Persistence functionality in general, but also to introduce the idea of using Essbase as an aggregate persistence source.
Before embarking on using Essbase as a source for Aggregate Persistence functionality, I highly recommend that you familiarize yourself with the general process by reading the documentation, which can be found in the Fusion Middleware Metadata Repository Builder’s Guide for Oracle Business Intelligence Enterprise Edition 11g (in Chapter 13, “Creating and Persisting Aggregates for Oracle BI Server Queries”).
The Aggregate Persistence general process includes running a wizard in the OBIEE Administration tool that generates a script that you can execute in the command line to generate a persisted store of aggregate information. Oracle recommends that you use such tools as Usage Tracking to understand user query behavior before creating these. If you are a practitioner of data warehousing techniques, there is a very good chance you have created a few aggregate tables in your data mart already. Also, if you are working from good requirements, you will already understand the reporting themes in your data.
In addition to being able to create and persist aggregate database sources, OBIEE can now do the equivalent with Essbase. You follow the same process as Aggregate Persistence, but select an Essbase connection pool that you setup versus a database. A script is then generated that you execute in the command line, and behind the scenes OBIEE will communicate with the Essbase Server and build your cube. What you may find frustrating is that the cube will only be representing one aspect of dimensionality along with your fact, and in that sense, this really is a major underutilization of Essbase itself. You would need to run the Aggregate Persistence utility several times to build out a multi-dimensional cube, and honestly I’m not sure it’s worth it.
As far as I’m concerned, the real value-add is setting up OBIEE to dynamically switch to use an aggregate source based on the user’s query definition. You will find this in the instructions for Aggregate Persistence, but the concept is very universal. You could manually create or leverage an existing Essbase cube to achieve the same results. While there have been many articles on federating Essbase, there has not been a great deal of attention paid to the idea of using Essbase in OBIEE as an aggregate source in combination with a database source, specifically for performance gains.
In general aggregates are sorely overlooked in data warehousing and can offer exponential gains in query performance especially in combination of using a tool like OBIEE to dynamically and intelligently toggle between those sources based on user behavior.
Author: John McGale, Performance Architects