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

Technorati Tags: OBIEE,Aggregate Persistence Wizard,10g,11g,Aggregates

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.