OBIEE Aggregate Persistence Wizard

If you are doing POC on OBIEE or have to do some RAD work, you usually don’t have access to ETL of the database to prep the aggregate tables. If you have access to an DB-schema with some create and drop table rights you can use the OBIEE Aggregate Persistence Wizard to do it Q&D / RAP style. (More info on using aggregate tables you can find here: http://obiee101.blogspot.com/2008/11/obiee-making-it-aggregate-aware.html )

The examples in this article are based on this simplified business model;

Prep the repository:

Open the repository in online mode.
In the physical layer of your repository create a new database, connection pool and schema.

From the tools > utilities menu select the aggregate persistence wizard:

Enter a location for the script:


Select the fact(s) you want to aggregate:
Select the logical levels for the aggregate:

Checking the "Use Surrogate Key ?" will later on map the aggregates using a surrogate key, this can improve performance if you have (complex) keys, consisting of multiple columns.

Select the database, schema and connection pool the script has to use. Be sure to have create and drop table rights in that schema.

Check the "I am done"

Close the online repository .
Open the file, it should look something like this:

create aggregates
"ag_F_FACTS" for "BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at
levels ("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_01" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR";

Before the "create aggregates" add "delete aggregates;" (don’t forget the semicolon!)

delete aggregates;
create aggregates
"ag_F_FACTS" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_01" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR";

Run the script using nqcmd:

{OracleBI}\server\Bin>nqcmd -u Administrator -p Administrator -d AnalyticsWeb
-s c:\agg_wizz.sql

Open the repository again and have a look at the AGGR database in the physical layer:

If you have a close look at the joins you will see that they are using the surrogate key:

Now check your business model:


If you open one of the aggregates in the Sources you will see that OBIEE already did the level mapping:

In the log file you can find the SQL issued to the database:

CREATE TABLE ag_F_FACTS ( LEVEL_0_000000C6SK DOUBLE PRECISION, D_YEAR_000000D6SK
DOUBLE PRECISION, F_FACT_VAL00000086 DOUBLE PRECISION )

Now check with a simple request if OBIEE is actually "picking up" the aggregate:

From the log:

select distinct T1047.LEVEL_0100000074 as c1, T1052.D_YEAR0000007E as c2,
T1059.F_FACT_VAL00000086 as c3 from SA_LEVEL_0000000C6 T1047, SA_D_YEAR000000D6
T1052, ag_F_FACTS T1059 where ( T1047.LEVEL_0_000000C6SK =
T1059.LEVEL_0_000000C6SK and T1052.D_YEAR_000000D6SK = T1059.D_YEAR_000000D6SK )
order by c1, c2

The next step

Based on the basic script we have now with some clever copy and paste actions it’s easy to extend to other aggregates.

delete aggregates;
create aggregates
"ag_F_FACTS01_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_01" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR",
"ag_F_FACTS02_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_02" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR",
"ag_F_FACTS03_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_03" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR",
"ag_F_FACTS04_YEAR" for
"BM_AGG_WIZ"."F_FACTS"("F_FACT_VAL") at levels
("BM_AGG_WIZ"."DIM_LEVELDim"."LEVEL_04" using_surrogate_key ,
"BM_AGG_WIZ"."DIM_DATEDim"."D_YEAR" using_surrogate_key ) using connection pool
"AGGR"."CP_AGGR" in "AGGR".."AGGR";

Before you run the script check your "," and "";" and be sure that each aggregate has a unique name .

When (not) to use this feature:

• This is perfect in a Proof of Concept (POC) or if your have to do some rapid application development (RAP)
• This feature work very crude: A delete aggregates is a "DROP TABLE"
• This feature doesn’t create any indexes, you have to create them yourself!
• This works perfect if you have to do some cross database aggregates, it can really boost the performance there.

Till next time

This article was orginally written for the Ciber knowledge blog: http://knowledge.ciber.nl/weblog/?p=149