Using the OBIEE Aggregate Persistence Wizard

October 26, 2007 Oracle BI Suite EE

One of the interesting new features that came with OBIEE was the Aggregate Persistence Wizard. This utility automates the creation and initial population of aggregates, persists them in a back-end database and configures the BI Server metadata layer so that they’re used when appropriate. What’s particularly interesting about this feature is that you can create aggregates for, say, an Oracle database and store them in an SQL Server, DB2 or Teradata database. It’s one of those OBIEE “rapid application development” features that lets you tackle underlying database performance issues in a pragmatic way – you can’t create summaries in the backend database, so find any old database and create them in there, the BI Server will remember where it’s stored them – and it’s also a useful feature when your metadata layer maps to multiple back-end databases, and there isn’t an obvious single place to create your aggregates as your facts span multiple databases.

So, how you you use this feature? Well, it’s accessed from the BI Administrator tool, and relies on you first having set up dimensions in your logical model to describe the hierarchies and levels within your data. In this example, I’m looking at a simple dataset with three hierarchies; a customer hierarchy that goes from All Customers to Countries, then State, then City and then Customer, a product hierarchy that goes from All Products through Categories to Product, and a time hierarchy that goes from All Times through Years, Quarters, Months and Days.

In this scenario, I’m interested in aggregating the Quantity measure within the Items fact table by Product Category, Year and State. To kick things off, I select Tools > Utilities from the BI Administrator application menu, and then select Aggregate Persistance Wizard from the list of available utilities.

The way the wizard works is to let you select one or more fact table measures, then select the dimension hierarchy levels on which you’ll aggregate, wherapon it then generates a script that tells the BI Server to create the aggregates. You can run this script either through the scheduler (the method the docs recommend) or through the nqcmd.exe command-line utility found in the /OracleBI/server/bin directory (my recommended method, as this seem to be the only way I can get it to work).

To start off then, the first screen displayed by the wizard prompts you to enter a filename for the script it’s going to generate. Note the option underneath to generate a DDL file as well – this is a bit of a red herring, as the main script that it generates already tells the BI Server to generate back-end database tables anyway, I’m not quite sure what the DDL file is for but it’s definately not needed for what we’re doing.

You then select the business model, then the fact table and finally the measure(s) that you wish to aggregate. To keep things simple, I’m just aggregating the quantity measure in my Items fact table.

Then you select the dimension hierarchy levels on which you’ll aggregate the data.

Now you choose the database in which you’ll store the aggregates. In this initial example, I’ll stored the aggregate tables in the same Oracle schema as the source data, but I could just as well store them in an Access database, or an SQL Server database, if I had one handy.

The wizard then shows me a preview of the script it’s going to generate, and asks me if I want to create any more summaries. Here’s how the preview script looks – note that it’s not SQL, it’s a script language that the BI Server understands and uses as the specification for an aggregation job, which it then turns into separate CREATE TABLE, INSERT … SELECT and so on SQL statements, “optimized” for the particular back-end database platform.

I then close the wizard down and take a look at the script that it’s generated.

Before I run the script, I insert another command into the script before the “create aggregates” command, to clear down any aggregates that might already exist:

Now the script is ready, I run it using the “nqcmd.exe” utility in the /OracleBI/server/bin directory.

If I take a look in the schema in which it’s created the aggregates, I see that some system-generated tables are now there:

Looking at the Physical Layer in the BI Administration tool, I can see the new aggregate tables created and registered, and shown in red to show they’re aggregates.

Similarly, new Logical Table Sources have been created in the business model to link these aggregate tables to the existing logical model.

Now, if I run a query through Answers that selects the Quantity measure at this level of aggregation, and then check the NQQuery.log file to check that the new aggregate tables are being used, I see it’s all working ok.

Not bad. Of course, if you’re running solely against an Oracle database, creating and maintaining your aggregates using materialized views probably makes more sense, but if you’re running in a mixed environment, or for whatever reason you either can’t create materialized views or you only have an DB/2, SQL Server or other database lying around to create aggregates in, it’s a useful feature to be aware of.

Thanks to Christopher Bridge for reminding me of this feature, and for a few tips on getting it working. Cheers Chris.