Wednesday 13 April 2011

How to use the Aggregate Persistence Wizard – Part II - Under the Hood

Following up to my previous post, this section looks at what happens under the hood when the logical SQL is executed. the Aggregate Persistence Wizard issues the following logical SQL:

  • create aggregates

. We will also look at:

  • delete aggregates;
  • prepare aggregates

 

Create Aggregates

The built-in Aggregate Manager module performs the following where the “create aggregates” logical SQL is issued:

  1. Defines Aggregate Creation Plan
    1. Phase 1 = dimensions
    2. Phase 2 = facts
  2. Create physical dimension tables
  3. Check in and save changes
  4. Populate dimension tables
    1. Example: SET VARIABLE DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1; populate "AG_Detail_000153B1" mode ( append table connection pool "OBIEE Local DB"."OBIEE - Connection Pool") as  select_business_model "Dim - Source"."Source_Type" as "Source_Typ000153A2","Dim - Source"."Segment" as "Segment000153A3", RCOUNT (1) as "Detail__000153B1SK" from "Use Case 1: Calcul Parc";
  5. Create logical table sources and set to state to Active
  6. Check in and save changes
  7. Create physical fact table
  8. Check in and save changes
  9. Populate physical fact with populate command (similar to above example)
  10. Create fact LTS and set state to Active
  11. Check in and Save Changes

-------------------- Aggregate Manager: *******BEGIN: Create Aggregates*******

-------------------- Aggregate Manager: *******END: Create Aggregates*******

Delete Aggregates

The built-in Aggregate Manager module performs the following where the “delete aggregates” logical SQL is issued:

  1. Deletes logical table sources for aggregate tables
  2. Checks in changes and saves RPD
  3. Drops physical db tables
  4. Deletes physical layer tables
  5. Checks in changes and save RPD

These actions are visible in the query log identified by:

-------------------- Aggregate Manager: *******BEGIN: Delete Aggregates*******

-------------------- Aggregate Manager: *******END: Delete Aggregates*******

prepare aggregates

Similar to create aggregates without the populate scripts

 

The populate command

Perhaps this can be used for an aggregate refresh script.

How to Use the Aggregate Persistence Wizard – Part I

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.

Mark Ritmman has a post on the subject here. My entry complements his.

3 Steps

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) image
  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 databaseimage
  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:

image

Content tab for a logical table source showing aggregation level:

image

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

image

Voila. You now have a scheduled aggregate. If you check the physical SQL that is generated you will notice populate and create BI SQL. It may be possible for a script to simply repopulate the aggregate instead of dropping and recreating it each time.

If you want more take a look at what goes on under the hood.