Improvements to Essbase Integration in OBIEE 11.1.1.6.2 BP1 : ASO Aggregate Persistence, Combined Install & Systems Management

The recent 11.1.1.6.2 BP1 patchset for OBIEE 11g provided a bunch of new features including trellis charts, Oracle BI Mobile and the repository Model Checker, but it also sneaked in another set of new features that will be of particular interest to Essbase users: closer integration between Essbase Server and OBIEE, and the ability to persist OBIEE repository aggregates in an Essbase database. So how does this work?

As far as I can tell, nothing's been officially announce but Oracle Support Doc. ID 1471661.1 details how this feature is set up and enabled. Whilst the ability to persist repository aggregates in an Essbase database is of course interesting, to my opinion it's how Oracle enabled this feature, and the integration it's required between OBIEE and Essbase, that's even more interesting. For anyone who's new to OBIEE, or OBIEE's Aggregate Persistence Wizard feature the idea is that the BI Administration tool provides a wizard that lets you select one or more measures from the logical business dimensional model, select a level from each hierarchy that the measures are dimensioned by, and then specify a physical database that the aggregates will be created in, like this:

The output of the process is a script for use with the nqcmd utility, which acts as a command-line interface into the BI Server's ODCB interface. The script contains logical SQL queries that create the required aggregate tables and indexes, and then run SQL commands to populate the tables. Finally, the script then registers these aggregates back in the BI Server repository, mapped in as new logical table sources and registered at the correct levels of granularity.

Then, when users query the business model from the dashboard, if a query comes through that could make use of the aggregate tables created in the previous steps, the BI Server automatically redirects the query to these new aggregates, in most cases significantly speeding up query response time as the results have already been pre-computed and stored ready for use.

As the feature previously stood, you could create these aggregate tables in either Oracle Database, Microsoft SQL Server or IBM DB/2, with Oracle TimesTen for Exalytics also an option if you're running on the Exalytics platform. What this new feature does though is make it possible to store these aggregates in an Essbase ASO (Aggregate Storage Option) database, a multi-dimensional data store that potentially offers faster response times than relationally-stored aggregates.

So far so good; the really interesting thing though is how this new feature has been enabled. To make it possible to quickly spin-up Essbase databases to hold these aggregates, the installer for OBIEE 11.1.1.6 has a "hidden" option to install various Essbase components as well, something that's only available when you initially install OBIEE and enabled through using a response file with some additional options selected. Once you've performed the install, you have to immediately patch OBIEE up to the 11.1.1.6.2 BP1 release and enable Essbase Studio integration through the NQSConfig.INI file, but once you do so and then log into Fusion Middleware Control, you'll now see Essbase added to the list of targets, and Essbase elements in the coreapplication module as well.

What you've got now as part of this setup is the following Essbase products, installed along with OBIEE as part of a single Oracle BI Domain:

• Essbase Server 11.1.2.2.0
• Essbase Administration Services 11.1.2.2.0
• Essbase Studio 11.1.2.2.0

Essbase and EAS are now stopped, started and monitored through the same Fusion Middleware Control installation as OBIEE, with OPMN starting up Essbase and the WebLogic Admin Server controlling EAS. Essbase Studio is also part of the install, but it's managed separately with no integration (yet) with Fusion Middleware Control. There are some significant limitations and restrictions with this setup though:

• It's only available for new OBIEE 11.1.1.6.2 BP1 installations - if OBIEE is already installed you'll need to de-install it, re-install OBIEE 11.1.1.6 and then patch it all up again to 11.1.1.6.2 BP1, enabling the Essbase features en-route
• It only works with the Essbase binaries shipped as part of this release, with no other versions supported
• Scale-out of Essbase isn't permitted, and you can only use the Essbase server for aggregate persistence, nothing else
• There's no upgrade path for Essbase databases created using this feature to later versions of Essbase, and
• Releases beyond 11.1.1.6.2 BP1 may require you to re-install the whole stack to get the upgrade.

In other words, this is for early adopters only and it's just there to support aggregate persistence into Essbase ASO databases. With all of that in-mind, let's go through the aggregate persistence process now, using the SampleAppLite repository and data set as the source. Here's a quick look at SampleAppLite's repository before we do any aggregate persistence, and in the Physical layer you can see the stub entry for the Essbase database that's going to hold our aggregates, which corresponds to some new entries that go into the NQSConfig.INI file to enable integration with Essbase Studio.

Let's step through the Aggregate Persistence Wizard now, and then we'll look at how the aggregates get created in the Essbase database. We'll start by checking that the repository is open online, then we'll select Tools > Utilities > Aggregate Persistence. After specifying a name and location for the script that the process is going to create, I then select the measures that I want to aggregate using the wizard. These measures will then be aggregated using the default aggregation method specified for them in the repository's business model and mapping layer.

I then select the dimension hierarchies and levels that I want to aggregate these measures by. All measures have to be aggregated by the same levels, and only a single aggregation can be specified by each run of the wizard (I'll come back to the implications of this later on).

Next I select the connection pool, and therefore target physical database, for the aggregates. In this case I can now select an Essbase database as my target, rather than the usual Oracle Database, SQL Server or IBM DB/2 targets.

Finally, to close the process I press Next, Next and then Finish to exit the wizard and generate the script. Let's take a look at what it's produced.

The output from the Aggregate Persistence Wizard is a script that contains a logical SQL statement to create a specific aggregation, like this:

create aggregates
"ag_F0_Re"

for "SampleApp Lite"."F0 Revenue Base Measures"("Revenue","Billed Quantity","Discount Amount")

at levels ("SampleApp Lite"."H0 Time"."Quarter", "SampleApp Lite"."H1 Products"."Products Brand", "SampleApp Lite"."H3 Orders"."Order Type", "SampleApp Lite"."H2 Offices"."Offices Organization")

using connection pool "DMA_DB"."localhost"

in "DMA

This script is obviously (a kind of) SQL script rather than an MDX or MaxL script, so how does it product an Essbase outline and a populated Essbase database? The script itself is executed using the nqcmd BI Server ODBC command-line utility, with a syntax like this:

cd c:\oracle\Middleware\Oracle_BI1\bifoundation\server\bin
nqcmd -d coreapplication_OH1272047220 -u weblogic -p welcome1 -s c:\TEMP\create_ess_aggs.txt

Under the covers, the BI Server uses a special version of Essbase Studio to create the Essbase objects. With the 11.1.1.6.2 BP1 release of OBIEE, assuming you've performed the special install three Essbase components get installed along with the regular OBIEE 11g ones, giving you the updated architecture diagram below (with the new Essbase components highlighted).

As you might have spotted, Essbase Studio sits within the architecture and is managed via OPMN, but by default it's not in the standard Oracle Instance that contains the BI Server, BI Presentation Server and now in this case, Essbase Server. Essbase Studio in this version has a slightly modified catalog that doesn't persist metadata in a database schema, and is called on an "as-needed" basis from a servlet specially created to provide datamart automation for the BI Server. Requests come in from the BI Server in XML format via HTTP, and Essbase Studio then takes the aggregate definition, checks back with the BI Server repository to get the details, creates an internal "cube model" and then uses it to generate an outline for the Essbase database. Once this is done, it then generates an Essbase rules file contain SQL statements against the BI Repository's database model, and then uses it to populate the Essbase database, like this:

Once the Essbase database is created, you can switch over to the Essbase Administration Services console that also comes as part of this setup, where you'll see that it's an Aggregate Storage Option database that's been created, with an outline and rules files generated by Essbase Studio from the script definition.

You can also see the Essbase database within Enterprise Manager, listed as an application under the main Essbase server entry.

Whilst back over in the BI Administration Tool, you can see the new Essbase database mapped in to the Physical layer, and as logical table sources in the Business Model and Mapping Layer.

Making use of the new Essbase aggregates is fairly automatic; you just run queries through the dashboard or analysis editor as normal, and in the background where appropriate the BI Server will direct queries to the Essbase database, rather than the detail-level relational data source, something you can check by turning on query logging and then checking the nqquery.log file.

So, overall, what's the verdict? Well, it all works as advertised, but … it's kind-of missing the point when it comes to using Essbase as a data source. The Aggregate Persistence Wizard only creates single aggregations whereas of course an Essbase cube contains potentially many, many aggregation points, so if you use the Aggregate Persistence Wizard to create your Essbase data, you'll (a) spend ages doing it as you'll need to create every aggregation separately, and (b) you'll end up with lots and lots of separate Essbase databases. That said, for the task in-hand (aggregating specific slices of a logical business model) it does the job and potentially stores the data more efficiently, and more effectively, than in a relational data store.

For me though what's more interesting is the increased integration between Essbase and OBIEE. Essbase Server along with Essbase Administration Services and a cut-down version of Essbase Studio all get installed alongside OBIEE, and are managed using a single instance of Fusion Middleware Control. What's not there yet is integrated security, and of course there's no EPM Workspace, SSO between EPM and OBIEE applications and so on, but hopefully it's one more step towards complete integration between the EPM and OBIEE toolsets.