Aggregate Persistence Wizard

Hello All,

After long time, I hope all are doing well. Let discuss today what aggregate table is and why we use aggregate table in OBIEE.

Use of Aggregate table: Aggregate tables store precalculated measures that have been aggregate over a set of dimensional attributes. This is very useful technique for speeding up query response time in decision support systems. This eliminates the need of run time calculations and delivers faster results to users.

Steps:

Create all Dimension Tables, Fact Tables & Hierarchies, which are required to be aggregated. Below you will see the dimensional table, fact table and hierarchies.

Go to Tools then Utilities, Select Aggregate Persistence Wizard, and then click Execute Button.

Now select appropriate path where you need to generate Aggregate Table SQL.

Note: Click Generate DDL file for first time generation of Aggregate Table.

Click next, to move to next page (Select Business Model & Measure Page)

In Select Business Model & Measure Page, Select Appropriate Business Model & then select associated Fact / Measure.

Click next, to move to next page (Select Dimensions & Levels).Select appropriate level of dimension & check Use Surrogate Key.

Click next, to move to next page (Select output Connection Pool, Container & Name).

Click next, to move to next page (Aggregate Definition).

Select I am Done (Radio Button).Click Next.

Click Finish. Your Aggregate Table is created Now & available at the path you specified in “Select File Location” Page.

I then close the wizard down and take a look at the script that it’s generated.

To view generated script move to - C:agg 

Before I run the script, I insert another command into the script before the “create aggregates” command, to clear down any aggregates that might already exist:

Now to execute the create table scripts using nqcmd Command , your need to know the SYSTEM DSN for the OBIEE 11G server.You can find it at the ODBC Administrator.

Type odbcad32 from windows run

If you happen to see more than one DSN name for obiee server then get the name from the Add button as shown below.

Set the environment variables by the bi-init.cmd as shown below

D:\obiee/instances/instance2/bifoundation/O racleBIApplication/coreapplication/setup/bi-init.cmd

Issue the blow command

nqcmd.exe -u weblogic -p Rakesh123 -d coreapplication_OH530560862 -s D:\agg.sql

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

-u is the administrator username

-p is the administrator password

-s is script path

Once script executes, the command window displays the same as Processed. Note, any errors in this stage are 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)

I take a look in the scheme in which it’s created the aggregates; I see that some system-generated tables are now there:

Looking at the Physical Layer in the BI Administration tool, I can see the new aggregate tables created and registered, and shown in red to show they’re aggregates.

Similarly, new Logical Table Sources have been created in the business model to link these aggregate tables to the existing logical model.

Now, if I run a query through Answers that selects the Quantity measure at this level of aggregation, and then check the NQQuery.log file to check that the new aggregate tables are being used, I see it’s all working ok.

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

- Rakesh patil

- rakeshpatil002@gmail.com