Aggregate Persistance Wizard

In this topic, you understand the role and function of the Aggregate Persistence Wizard in creating and modeling aggregates to resolve query bottlenecks.

Aggregate tables store precomputed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems. This eliminates the need for run-time calculations and delivers faster results to users. The calculations are done ahead of time and the results are stored in tables. Aggregate tables should have fewer rows than the nonaggregate tables and, therefore, processing should be quicker.

The aggregate navigation capability of Oracle BI Server allows queries to use the information stored in aggregate tables automatically, without query authors or tools having to specify aggregate tables in the queries. Oracle BI Server allows users to concentrate on asking the right business questions, because the server decides which tables provide the fastest answers. For Oracle BI Server to have enough information to navigate to aggregate tables, certain metadata in the repository must be correctly configured.

The traditional process of creating aggregates for Oracle BI Server queries is manual. It can be tedious, requiring complicated data definition language (DDL) and data manipulation language (DML) scripts to be written for creating tables in the databases involved. Additionally, these aggregated tables need to be mapped to the repository metadata to be available for queries. This is a time-consuming and, possibly, error-prone process.

The Aggregate Persistence Wizard enables you to automate the creation of physical aggregate tables and their corresponding objects in the repository. The Aggregate Persistence Wizard creates an Oracle BI Enterprise Edition SQL script, which is executed by the BI Server. The script specifies each aggregate table to be created, the facts from the business model that should be included in it, and its dimensions and grain. When the BI Server runs the aggregate persistence SQL script, it generates DDL to create the required tables in the target database, internal instructions to generate the corresponding physical and aggregate navigation metadata, and data manipulation language (DML) to aggregate and load data from the base tables into the aggregate tables. The aggregate persistence script is intended to be run after each extraction, transformation, and loading (ETL) of the base tables, typically nightly. This can be done by an Oracle BI EE Job Manager job, or it can be run as a .bat or any other script called by a custom program.

The default prefix SA_ is automatically added to dimension (level) aggregates. You can change this default prefix by updating the AGGREGATE_PREFIX parameter in the AGGREGATE_PERSISTENCE section of the NQSConfig.INI file: AGGREGATE_PREFIX = "prefix_name" ;

Using the Aggregate Persistence Wizard

Select Tools>Utilities from the menu bar.

From the Utilities windows select Aggregate Persistence and click Execute. The Aggregate Persistence wizard opens.

In the Aggregate Persistence - Select File Location window enter the name of the SQL script file as aggregate.sql. Enter the folder where this file will be placed. Click Next.

In the Aggregate Persistence - Select Business Measures window, select the subject area 01 - Sample App Exa and following measures from the F0 Sales Base Measures table:

1-Revenue

2-Billed Quantity

3-Discount Amount

4-Paid Amount

Click Next.

In the Aggregate Persistence - Select Levels window, select dimension H0 Time.Month, H1 Products.Product Brand, and H3 Offices.Company. Click Next.

In the Aggregate Persistence - Select Connection Pool window, select the database as 02- Sample App Exa Data (ORCL). Make sure you select the Oracle database (ORCL) and not Essbase (ESSB). Select Catalog/Schema as 02 - Sample App Exa Data(ORCL) > BISAMPLE_EXA. Select the Connect Pool Exa Sample Connection. Change the default aggregate table name to ag_BISAMPLE_EXA. Click Next.

In the Aggregate Persistence - Finish window, check the script. Select I am done and click Next.

In the Aggregate Persistence - Finish Script window, check that the script is created and placed in the specified folder. Click Finish.

Open the aggragate.sql script file in edit mode. Check the script.

Running nqcmd in Command Line

Open a command window. Change directory to c:\bi\instances\intance1\bifoundation\OracleBIApplication\coreapplications\setup. Your environment may have a different path. Enter and run bi-init in the command window.

A second window opens. At the prompt in the second command window, enter:

nqcmd -d coreapplication_OH1291352497 -u weblogic -s c:\temp\aggregates.sql

d - Is the ODBC data source name for the Oracle BI Server to which you want to connect.

u - Username

s - SQL script file you created using the Aggregate Persistence wizard. For this tutorial it is c:\temp\aggregate.sql

Enter welcome1 as the password and press Enter.

Wait till you get Statement execute succeeded message.

You completed running the nqcmd command for creating aggregates with Aggregate Persistence Wizard..

Note: If the statement execution failed, make sure you do not have any summary tables in the database, Business Model layer, and in the Physical layer for the dimensions you are using, prior to running the nqcmd command. You can use the Delete aggregates command to delete them.