Aggregate Persistence

This exercise must be done on rpd’s which are deployed with the BI server is running

 Aggregate persistence is used to automate the creation and initial population of aggregate tables, persist them in backend datawarehouse schema and configures the rpd file to use them wherever its appropriate.  This is best way to deal with performance issue in datawarehouse tables for which intervention of ETL professionals is not needed.

Here are the steps to create an aggregate where a frequent query is fired for SALE_PRICE,MONTH and MODEL

Open rpd in online mode and and open the Utilities, choose “Aggregate Persistence”

Provide a name and location for the script file to be created

Select the metrics required to be persisted

Select the dimension level of the attributes that needs to be persisted. Check the use of “Surrogate Keys”

Select the Database, the schema and the connection pool and then specify the name of the aggregate table .

The created script now appears and simultaneously the script file is also created.

Once and check the script contents

Navigate to D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin

Check that nqcmd.exe is present. This is a utility from OBIEE to execute scripts.

Cd to D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin in a command window.

Issue the command nqcmd –d coreapplication_ON863956460 –u admin –p mypassword123 –s c:\agg_persistence.sql

Note that the entry after –d switch is the name of the OBIEE DSN. (to checn this Start>run>type odbcad32)

-u is the administrator username

-p is the administrator password

-s is script path

 Execute this command.

Once script executes, the command window displays the same as Processed. Note, any errors in this stage is because of 2 reasons :

·         BI Server is not running (opmnctl startall should be done) OR

·         The script is taken from an rpd that’s not deployed (take script from a rpd in online mode)

Once the script completes the changes done by it are visible.

·         New  tables are created in physical layer

·         Same are created in Database as well

·         New Logical table sources are created in BMM and mapped to respective columns

We can verify that column mapping to new LTS is done by opening the LTS’s created by the aggregate persistence wizard.

Notice that each of the objects created by aggregate persistence wizard in the rpd has a small “GEAR” symbol.