[OBIEE 10g] How to Use the Aggregate Persistence Wizard

 

How to Use the Aggregate Persistence Wizard

       The Aggregate Persistence Wizard is a nifty utility for creating, modelling and populating aggregate tables available in OBIEE 10g and 11g. This utility is especially useful to quickly build a single aggregation layer upon federated heterogeneous physical sources.  OBIEE will create, model and populate aggregate dimensions and facts based upon the intelligence you have built into the RPD. This avoids re-writing the same logic into your ETL.

For this walk through, you will need a physical database connection where you can create and populate tables and the OBIEE Scheduler to schedule the automatic creation of aggregates. 

A) Generate script with Wizard

B) Test script with nqcmd

C) Schedule script with job manager

Pros/Cons of Aggregate Persistence WIZARD

Pros:

• Simple setup
• Leverages RPD defined dimensional model, avoids redefinition in ETL
• Combines well with federated data sources
• Can be scheduled in post load processing of ETL

Cons:

• Production ready?
• RPD and physical objects are recreated on each run and limits on object naming
• Physical table metadata maintained in OBIEE (as opposed to say the Data Warehouse Administration Console or other tool)
• Cannot define aggregate table indices (though there may be a work-around)

A) Generate script with Wizard

1. In the Admin Tool, Tools->Utilities->Aggregate Persistence Wizard

2. Enter a location to save scripts, ex: c:\temp\aggwiz.sql (leave Generate DDL File unticked), Next

1. Note: The Generate DDL script will create a second script with the suffix _DDL which can be used for the create and model the aggregate(s) without population. The main script will create, model, and populate aggregates.

3. Select business model then measure or fact table to aggregate

4. Set the logical levels, check the Use Surrogate Key? checkbox if the underlying physical joins are complex (OBIEE will create surrogate keys for the aggregate)

5. Next

6. Select database, catalog / schema, connection pool, and aggregate table name where the aggregate table will be created. Note:

          1. The aggregate table name is the physical table that is created in the database and can only contain a certain number of characters

          2. Dimensional aggregates will also be created, populated, and modelled. These are named automatically with the prefix SA_ by default (changeable in NQSConfig.ini option AGGREGATE_PREFIX)

          3. Check the Allow populate by default checkbox for the database

7. Click on “I am done” then Finish. Note: This does not create the aggregate. 

B) Test script with nqcmd

1. Open the resulting script in your favourite text editor
2. Notice the create aggregates BI SQL
3. This command will physically create, populate, and model the dimensional and fact aggregates
4. Make sure you have no RPD objects checked out
5. Run the script with nqcmd (see below for example)
6. Notice the creation of aggregate dimensions and fact in physical and BMM layers.
7. Test that aggregates are indeed used from Answers.

Example script (c:\temp\aggwiz1.sql):

create aggregates 

"ag_Faits_Lignes_MS"
for "Use Case 1: Calcul Parc"."Faits - Lignes"("Nb_Activations","Nb_Resiliations")
at levels ("Use Case 1: Calcul Parc"."Dim - Source"."Detail - Segment", "Use Case 1: Calcul Parc"."Dim - Date"."Mois")
using connection pool "OBIEE Local DB"."OBIEE - Connection Pool"
in "OBIEE Local DB".."POCFED01"

Example execution with nqcmd, change parameters as necessary:

c:\temp>nqcmd -u Administrator -p Administrator –d AnalyticsWeb –s aggwiz1.sql

RPD showing aggregates created by script:

Content tab for a logical table source showing aggregation level:

C) Schedule script with job manager

1. Modify your script and add the following line at the beginning: delete aggregates;
1. This will drop physical tables, and RPD BMM and Physical layer objects
2. In Job Manager, open a Scheduler Connection
3. Jobs->Add New Job
4. Enter Name, User ID, DSN, and Schedule accordingly (see below for my example)
5. Script Type: NQCmd
6. Set SQL Input File to your aggregate (optionally place your script in  server\Scripts\Scheduler directory)
7. OK
8. Click on Run Job(s) Now to test execution

Voila. You now have a scheduled aggregate. If you check the physical SQL that is generated you will notice populate and create BI SQL.