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

September 16th, 2012 by

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:

NewImage

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.

NewImage

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.

NewImage

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.

NewImage

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.

NewImage

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.

NewImage

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.

Sshot 3

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).

Sshot 4

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.

Sshot 5

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).

NewImage

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:

NewImage

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.

Sshot 6

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

Sshot 7

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.

Sshot 8

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.

NewImage

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.

Comments

  1. Sandy Says:

    Mark,

    Great update. Seems like it’s a step in the right direction. I heard Oracle was moving in this direction three years ago, nice to see they are finally heading there. Something is better than nothing right?

  2. vyke Says:

    Mark,

    Even with all the negatives that we can think of, what makes this an exciting proposition is that now we can create aggregates without encroaching data warehouse teams realm of responsibilities. I would like to move the aggregation layer closer to BI layer and have better control to serve my customers better.

    As Sandy pointed out, this was announced couple of years ago and its good to see that finally happening.

  3. khan Says:

    What about using inside-db OLAP option to create aggregates for BI Aggregation persistance?

    Will Oracle ever use its powerful, simpler, scalable and more efficient inside-db OLAP engine in its BI products?

    Data compression and query performance from Oracle OLAP is superb in 11.2.0.2+ versions. Plus everything is inside database.

  4. Victor Says:

    Do we require an Esbbase license to install this cutdown version?

  5. Mark Rittman Says:

    Victor,

    Yes, but the expectation is that you would have an Essbase license through Oracle BI Foundation, which includes OBIEE, Essbase, Scorecard etc.

    Mark

  6. Mick O Says:

    Mark,

    Last year you wrote an article on Exalytics which included this statement about the Summary Advisor:

    “There are also some general restrictions on what repository features can’t be used with the Summary Advisor (this applies to the Aggregate Persistance Wizard, as well). For example, you can’t use logical dimensions with skip-level or ragged hierarchies, or with value-based hierarchies, and there are restrictions around degenerate dimensions, non-additive dimensions and the like.”

    Does that restriction plus this latest development mean that Essbase is now preferable to TimesTen as a vehicle for cacheing aggregates on Exalytics?

  7. Mark Rittman Says:

    Hi Mick,

    The same restrictions that apply to TimesTen aggregates through the Summary Advisor would also apply to Essbase, in this scenario. Also, I don’t believe this configuration is supported for Exalytics – whilst what you install and how you configure the software on the Exalytics server is ultimately down to the customer, there’s a recommended install and patching process that doesn’t, I think, include this one-off configuration of all the products together.

    To answer your question more widely – in general, if you’re doing a lot of aggregation across multiple dimensions and levels, at a certain point a dedicated MOLAP cube such as Essbase will make more sense than dozens of separate aggregate slices stored either in TimesTen, or in Essbase as per the setup described in this article. However if you skipped the summary advisor altogether, you’d lose the benefit of aggregate recommendations and their automatic build, refresh and adaptation over time, which is still useful. So whilst TimesTen may not be the optimal place to store star schema aggregation across multiple levels etc, it’s all we’ve got for now, so you’re balancing manageability and adaptivity (TimesTen + Summary Advisor) against a more obvious multidimensional OLAP store (Essbase).

    Mark

  8. Mick O Says:

    Thanks Mark.

    Sounds like it ideally needs something in the mix like the old Express AGGMAPs to keep track of what dimension + level combinations have been pre-calculated.

  9. Mark Rittman Says:

    Mick,

    Ahh, AGGMAPS. Of course the irony is, with all these Exalytics server, Exadata databases, in-memory caches and so forth, none of this is as fast as blue-screen Express, running on an old 512MB RAM laptop with a Pentium III chip ;-)

    Mark

  10. Mick O Says:

    Yes it’s all been downhill on the productivity front since v5.0.3 and the demise of the ccxapi. I used to live in a paper bag in the middle of the road you know.
    ;)

Website Design & Build: tymedia.co.uk