Using the OBIEE Aggregate Persistence Wizard

One of the interesting new features that came with OBIEE 10.1.3.2 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.

"agItems"
 for "Seminar - Complete"."Items"("Quantity")
 at levels ("Seminar - Complete"."Customerdim"."State", "Seminar - Complete"."Productdim"."Category", "Seminar - Complete"."Timedim"."Year")
 using connection pool "custdw"."CUSTDWPool"
 in "custdw".."CUSTDW";

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

create aggregates

"agItems" for "Seminar - Complete"."Items"("Quantity") at levels ("Seminar - Complete"."Customerdim"."State", "Seminar - Complete"."Productdim"."Category", "Seminar - Complete"."Timedim"."Year") using connection pool "custdw"."CUSTDWPool" in "custdw".."CUSTDW";


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:

delete aggregates;

create aggregates

"ag_Items" for "Seminar - Complete"."Items"("Quantity") at levels ("Seminar - Complete"."Customerdim"."State", "Seminar - Complete"."Productdim"."Category", "Seminar - Complete"."Timedim"."Year") using connection pool "aggregates"."Connection Pool" in "aggregates"."E:\product\OracleBI\server\src\aggregates";


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

E:\product\OracleBI\server\Bin>nqcmd.exe -u Administrator -p Administrator -d an
alyticsweb -s c:\aggwizscript.sql


      Oracle BI Server

Copyright (c) 1997-2006 Oracle Corporation, All rights reserved

delete aggregates delete aggregates

Statement execute succeeded

create aggregates

"agItems" for "Seminar - Complete"."Items"("Quantity") at levels ("Seminar - Complete"."Customerdim"."State", "Seminar - Complete"."Pr oductdim"."Category", "Seminar - Complete"."Timedim"."Year") using connection pool "custdw"."CUSTDWPool" in "custdw".."CUSTDW" create aggregates

"agItems" for "Seminar - Complete"."Items"("Quantity") at levels ("Seminar - Complete"."Customerdim"."State", "Seminar - Complete"."Pr oductdim"."Category", "Seminar - Complete"."Timedim"."Year") using connection pool "custdw"."CUSTDWPool" in "custdw".."CUSTDW"

Statement execute succeeded

Processed: 2 queries


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:

E:\product\OracleBI\server\Bin>sqlplus custdw/password@ora10g

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 24 19:03:47 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select tablename from usertables;

TABLE_NAME

SNPPLANTABLE SNPCHECKTAB E$CUSTOMER ADDRESS CUSTOMER CUSTOMERADDRESS ITEMS ORDERS ORDERSALESPERSONALLOC PRODUCT SALESPERSON

TABLE_NAME

SALESPERSONTREE SALESPERSONTREECOPY TIMES AGITEMS SASTATE000062A6 SACATEGOR000062AA SA_YEAR000062AD

18 rows selected.

SQL> select count(*) from ag_items;

COUNT(*)

    14

SQL> select count(*) from items;

COUNT(*)

    19

SQL> select count(*) from address;

COUNT(*)

    10

SQL> select count(*) from sa_state000062A6;

COUNT(*)

     7


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.

-------------------- Logical Request (before navigation):

RqList Quantity:[DAggr(Items.Quantity by [ Product.Category, Times.Yearyyyy, Customer.State] )] as c1 GB, Customer.State as c2 GB, Product.Category as c3 GB, Times.Yearyyyy as c4 GB OrderBy: c2 asc, c3 asc, c4 asc

+++Administrator:460000:46000e:----2007/10/24 19:11:11

-------------------- Sending query to database named custdw (id: <<27483>>):

select distinct T18312.Quantity00006299 as c1, T18293.State00006290 as c2, T18299.Category00006285 as c3, T18304.Yearyyyy0000628D as c4 from CUSTDW.SAYear000062AD T18304, CUSTDW.SACategor000062AA T18299, CUSTDW.SAState000062A6 T18293, CUSTDW.agItems T18312 where ( T18293.State00006290 = T18312.State00006290 and T18299.Category00006285 = T18312.Category00006285 and T18304.Yearyyyy0000628D = T18312.Year_yyyy0000628D ) order by c2, c3, c4


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.

Subscribe to Rittman Mead

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!

Subscribe to Our
Monthly Newsletter!

* indicates required