Sunday, November 9, 2008

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

10 comments:

Sreekar Suri said...

Hi,

When I try to use the same procedure mentioned above, I am getting this error when I execute the following command.

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s “D:\AggregateCountry.sql” -o “D:\Outputfile1.txt”

I am getting the below error in the output file. I am copying the entire output file..

create aggregates

“ag_Sales_time”
for “SALES”.”Sales”(”total_quantity”,”total_amount”)
at levels (”SALES”.”Year_Dimension”.”Month” using_surrogate_key )
using connection pool “SALES_DWH”.”Sales_Data_Warehouse”
in “SALES_DWH”..”SALES”
create aggregates

“ag_Sales_time”
for “SALES”.”Sales”(”total_quantity”,”total_amount”)
at levels (”SALES”.”Year_Dimension”.”Month” using_surrogate_key )
using connection pool “SALES_DWH”.”Sales_Data_Warehouse”
in “SALES_DWH”..”SALES”
[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.

[nQSError: 84008] [Aggregate Persistence] Error while processing aggregates (refer previous errors in log).
Statement preparation failed

Processed: 1 queries
Encountered 1 errors

can you please help me on this…

Regards
Sreekar Suri

John Minkjan said...

@Sreekar Suri

Does the user in the RPD connection pool have create / drop table rigths on your Database?

regards

John

Sreekar Suri said...

@John Minkjan

Yes John.... the user has privilages....

Regards
Sreekar Suri.

John Minkjan said...

@ Sreekar Suri

Dit you delete any old aggregate tables first?

regards

John

Sreekar Suri said...

I did not delete any aggregate tables...

This was the first time i am using aggregate table concept.

I just did what is posted in the blog..

Regards
Sreekar Suri

eejimkos said...

hi,
as you mentioned this utility is for fast development.
Some thoughts and a question..
we have a fact table and 6 dimensions,3 of them have their suppplements one.(join with differnet sk with fact but in real they have the same values...).If we create these 3 as logical one(duplicate physicals) the aggregate utility does not recognize them or 'forget' one of these 3.
So we lead to make procedures,make views from the query generated from the 'bi sql query' in order to fill the tables(using partioning bcause of large data-20 mil records per month).The question is,
which is the best approach??make the aggragates from urselves?how???
use the same dimensions as we use for the general fact or create new aggregate dimensions based on 'bi query'??and for aggregate facts,create the first from the general fact and the others(above levels) aggregated facts from the previus??or again from the fact...
tnks on advance....

John Minkjan said...

@eejimkos,

OBIEE isn't designed for large data set aggregation. Best things is to design your DWH with daily, weekly and monthly aggregates and model your BM to pick them up. from my own experience it works best to start with only aggregate on the time dimesion and leave the others on there lowest level. If you are on oracle try using the QUERY rewrite option agiant MVIEWS.

regards

John

Anonymous said...

Hi, John,

I tried to create the Aggregate tables using Aggregate Persistence Wizard, everything looks good, the aggregate tables were created and populated, the rpd was updated with the right LTS and mapping, however when I tried to test in Answers, BI server will not pick the aggregate tables, instead it still use the detailed tables. Any idea or suggestions are greatly appreciated.
Thanks,

eejimkos said...

November 8, 2009 2:34 PM
Anonymous said...
/////
your answer is in your eyes.
obviously,you have selected and other columns which are not in the aggregates ones.
consider again which columns from dimesions tables you want to be reported and make the appropriate hieracly.Meaning,let's say product_dim.
1.category
2.group
3.item
(in your fact you have the detail in bottom level-item-),so you make an aggregate at group.
Now,in your question,if you choose any other column from your product_dim ,it will go from the detail one and not from the aggregate one.if you choose -let's say- 'group' and 'shape' , it will go from the detail one.
The same for the other dimensions and the fact one.
Check one more the columns that you have choose for aggregates.

i hope i helped..

Dan O'Brien said...

The wizard is especially useful, as noted, for data federation.

My entries on the Wizard here:

http://www.obieeabc.com/2011/04/how-to-use-aggregate-persistence-wizard.html

http://www.obieeabc.com/2011/04/how-to-use-aggregate-persistence-wizard_13.html