This tutorial shows you how to use the Aggregate Persistence Wizard in Oracle BI 11g, to create and model aggregate tables to increase query performance by precalculating frequently accessed measure aggregations.
Time to Complete
Approximately 30 minutes
This tutorial shows you how to create and model aggregate tables using the Aggregate Persistence Wizard. Data warehouse performance bottlenecks are often due to measure aggregation (for example, summing orders at different levels of a dimensional hierarchy) that is being performed at run time. Having identified candidate aggregate tables for inclusion in your data warehouse, you use the Aggregate Persistence Wizard to create and model aggregates to relieve the bottleneck and precalculate and store the data for better query response.
When you use the Aggregate Persistence Wizard to create aggregates, it is important that you have a fully functional business model, with a complete set of measures, dimensions, and hierarchies. Before building tables with the wizard, you should have conceptually designed the desired set of aggregates on paper. The conceptual design should include the set of aggregate stars by name, the set of facts in each star, and the dimensionality and grain of each. To help you in designing aggregates, you can enable usage tracking. When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query, and it inserts the statistics directly into a database table. Usage tracking is also helpful in determining which user queries are creating performance bottlenecks, based on query frequency and response time. To learn how to set up usage tracking, you can go through the tutorial on Setting Up Usage Tracking in Oracle BI 11g. You should also determine the database where the aggregates are to be deployed in. This is usually the existing database that contains the base tables that are being aggregated. However, the tables can be placed in a different database. These minimum requirements for the logical and physical design details are sufficient to create the tables with the Aggregate Persistence Wizard. You could use the Model Checker to see if there are any errors in the model.
Before starting this tutorial, you should:
- Have access to or have installed Oracle Database 11.2 or higher
- Have access to or have installed Oracle BI EE 18.104.22.168 OBI EE
- Download the Sample rpd from here.
- Have Sample Apps rpd running in online mode.
Setting Up the Data Schema in Oracle Database
To create the required tablespace and BISAMLE_EXA user (replace paths to appropriate values on your environment) execute the following SQL statements:
Creating the Aggregates
Unlike the Consistency Check Manager, Model Check Manager requires access to back-end data sources for some checks. Because some of the back-end queries can be expensive, it is recommended to run Model Check Manager during off-peak periods.
Model Check Manager can only be run in online mode.
Model Check Manager does not make any changes to repository metadata - it only flags possible problems.
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" ;
In this topic, you use the Aggregate Persistence Wizard to create and model aggregate tables to support query performance.
The conceptual design of your aggregate star includes the following characteristics.
|Base Facts.“1-Revenue",”2 - Billed Quantity", 3-Discount Amount, 4-Paid Amount||
Time/”T02 Per Name Month"
Doing a Consistency Check
Running the Model Checker
You can use Model Check Manager to check your repository metadata for issues that might affect the success of the aggregate persistence engine, such as identifying level primary keys that are not unique.
Although the user experience of running Model Check Manager is very similar to running the Consistency Check Manager, there are three key differences between the two tools:
Similar to the Consistency Check Manager, Model Check Manager returns both error and warning messages. You must fix errors identified by Model Check Manager, or the aggregate persistence engine might fail to create aggregates. It is recommended that you fix warnings, but not required.
Run Model Check Manager right before you run the Aggregate Persistence Wizard. Alternatively, you can run Model Check Manager to identify problems for selected objects after initial aggregate creation failure. If there are errors in Model Checker, make sure they are not related to the dimensions you are selecting to build the aggregate.Since the rpd for this tutorial is a modified version of Sample Apps rpd, run the Model Checker for the dimensions that will be used for this tutotrial. To run Model Check Manager for the dimensions perform the following steps:
Using the Aggregate Persistence Wizard
Running nqcmd in Command Line
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.
Verifying that the Aggregates were Successfully Created
In this section you will check whether all the aggregates were successfully created.
Verifying the Aggregates in Physical Layer
Verifying the Aggregates in the Business Model and Mapping Layer
Creating Analysis to Use the Aggregates
In this tutorial, you have learned how to create and model aggregate tables using the Aggregate Persistence Wizard.
- Lead Curriculum Developer: Kasturi Shekhar
- Other Contributors: Alan Lee, Pravin Janardanam, Philippe Lions, and Lalitha Venkataraman
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed
- Show/Hide All Images:
- To show/hide all the screen shots. By default, all images are displayed.
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.