Creating Aggregates Using Aggregate Persistence Wizard in Oracle BI 11g

Overview

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.


Prerequisites

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 11.1.1.7 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:


create tablespace BISAMPLE_EXA datafile '\home\oracle\app/oracle\oradata\orcl\bisample_exa.dbf' size 10M autoextend on default compress;
create temporary tablespace BISAMPLE_EXA_TEMP tempfile '\home\oracle\app\oracle\oradata\orcl\bisample_exa_temp.dbf' size 10M autoextend on;



create user BISAMPLE_EXA identified by BISAMPLE_EXA default tablespace BI SAMPLE_EXA temporary tablespace BISAMPLE_EXA_TEMP;
grant dba to BISAMPLE_EXA;



Copy the dump file from here. Unzip and copy over to your database machine.
 Login to BISAMPLE_EXA schema and create a directory object to point to the directory where you’ve copied this file. Execute the SQL statement:
Create or replace directory sampleapp as 'C:\bisample_exa';



Open Windows command and navigate to the directory containing bisample_exa.dmp file. Import the dump using the following command:
impdp BISAMPLE_EXA/BISAMPLE_EXA directory=sampleapp dumpfile=bisample_exa.dmp schemas=bisample_exa 



Once imported, login to BISAMPLE_EXA schema and verify all tables are created and loaded (SAMP_REVENUE_F table should have 5million rows)




Download the tutorial SampleApp30212A for AP rpd from here and make it online. This is a Sample Apps rpd modified for this tutorial. For the tutorial, B-Sample Sales Exa subject area will be used. Enter Admin123 as the Repository password.



Create a query with the following:


• Time.T02 Per Name Month
• Products.P4 Brand
• Offices.D4 Company
• Base Facts.1-Revenue
• Base Facts.2-Billed Quantity
• Base Facts.3-Discount Amount
• Base Facts.4-Paid Amount

Click Results.



Note how long it takes for the query to return the results.

 

Creating the Aggregates

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.


Measures	Dimension/Grain	Source Database
Base Facts.“1-Revenue",”2 - Billed Quantity", 3-Discount Amount, 4-Paid Amount	Products/”P4 Brand” Time/”T02 Per Name Month" Office/"D4 Comapny"	BISAMPLE_EXA

 

Doing a Consistency Check

Open SampleApp30212 for AP.rpd in online mode in BI Administration Tool. Enter Admin123 as the Repository password. Enter the userid and password for your installation. For this tutorial we will use as User weblogic and Password welcome1. Make a note of the ODBC data source name. In this example it is coreapplication_OH1291352497.







Click File>Check Global Consistency.



The Consistency check is done.






Once the consistency check is complete Consistency Check Manager window is opened. Make sure there are no errors. Close Consistency Check Manager and save the rpd. If there are any errors, please remove the errors.



 

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:

• 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.
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:

In the Business Model and Mapping Layer, select the D0 Time dimension in 01-Sample App Exa business model. Right click and select Check Model.






Model Checker has the following two options:


• Complete: Checks all objects in the Business Model and Mapping layer of the Oracle BI repository.
• Filtered by Statistics: Checks only fact table objects and associated dimensions in the Business Model and Mapping layer that have been actively queried according to the statistics table. Select this option to speed up the process for large repositories.
  This option is only available on the Oracle Exalytics Machine. If you attempt to filter by statistics on a non-Exalytics system, or if you attempt to filter when the statistics table is not available, a warning appears explaining that Model Check Manager cannot filter by statistics.






Click Complete. When complete Model Checker Manager window will show all the errors, if any. Make sure there are no errors. Close Model Checker Manager.



Similarly run the Model Checker for the Product and Office dimensions. In each case, make sure there are no errors. Close Model Checker Manager.

 

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 as02 - 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.

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

Close and open the BI Administration Tool in online mode. In the Physical Layer, ensure that the aggregates were created in BISAMPLE_EXA schema. There should be one new ag_BISAMPLE_EXA aggregate table with the columns you selected for aggregation.



Scroll down the Physical Layer and you should have three dimension aggregates tables beginning with SA_. One for each dimension selected.



View company names from Offices dimension aggregate table. Check out objects when prompted.



Update row count to see the number of rows for Fact aggregate. There are 648 rows in the table. Check out objects when prompted.



Update row counts for all the new aggregates and you should see the following row count. Check out objects when prompted.
ag_BISAMPLE_EXA - 684 rows
SA_Month* - 79
SA_Product* - 9
SA_Offices* - 3

Double-click ag_BISAMPLE_EXA to open the physical dialog box, click the Foreign Keys tab. Verify that the joins are created between ag_BISAMPLE_EXA and the three dimension aggregates.



Double-click the foreign key for the month dimension, to view the relationship in the Physical Foreign Key dialog box.



Double-click the foreign key for the product brand dimension, to view the relationship in the Physical Foreign Key dialog box.





Similarly you can check for Office Company.

 

Verifying the Aggregates in the Business Model and Mapping Layer

In Business Model and Mapping Layer, open Sources folder for 01-Sample App Exa. F0 Sales Base Measures and confirm that a new logical table called 02 - Sample App Exa Data (ORCL)_BISAMPLE_Exa_ag_BISAMPLE_EXA is created.



Open Sources folder for D0 Time dimension and confirm that a new logical table called 01 - Sample App_Exa Data (ORCL)_BISAMPLE_EXA_SA_Monthxxxxxxx is created.



Open Sources folder for D1 Products (Level Based Hier) dimension and confirm that a new logical table called 01 - Sample App Data (ORCL)_BISAMPLE_SA_Productxxxxxxx is created.



Open Sources folder for D3 Offices dimension and confirm that a new logical table called 02 - Sample App Exa Data (ORCL)_BISAMPLE_Exa_SA_Officesxxxxxxx is created.



Double-click the 01 - Sample App Data (ORCL)_BISAMPLE_ag_BISAMPLE logical table source. Click Check Out. In the General tab and confirm that the 02 - Sample App Exa Data (ORCL)_BISAMPLE_Exa_ag_BISAMPLE_Exalogical table source maps to the 02 - Sample App Exa Data (ORCL).._BISAMPLE_Exa.ag_BISAMPLE_Exa physical table.



Click the Column Mapping tab and confirm that the logical measure columns map to corresponding physical columns in the ag_BISAMPLE_Exa physical table.






Click the Content tab and confirm that the logical levels are set correctly.






Click Cancel to close the Logical Table Source dialog box.
Repeat for D0 Time, D1 Products (Level Based Hier), and D3 Offices to confirm that the logical levels are set correctly for the new logical table sources generated by the aggregate persistence wizard.














Close all dialog boxes. Note: You do not have to do anything in the Presentation Layer.

Creating Analysis to Use the Aggregates

 

Sign in to Oracle BI as weblogic with password welcome1. Return to Analyses Editor and create a new analysis in B - Sample Sales Exa subject area.






Reload server metadata or if required re-start all the services.



Create a query with all your selections. Select the following:


Time.T02 Per Name Month
Products.P4 Brand
Offices.D4 Company
Base Facts.1-Revenue
Base Facts.2-Billed Quantity
Base Facts.3-Discount Amount
Base Facts.4-Paid Amount

Click Results.

Notice how fast the query returned.

You can save the query. Click the Administration link at the top. Click Manage Session from the Administration tab.










Inspect the query statement and confirm that the query used the columns you requested.



Click View Log in the Action column.



Scroll down and inspect the query log. Confirm that the query used the ag_BISAMPLE_EXA aggregate table and the related dimension SA_Month, SA_ProductBrand, and SA_OfficesCompany aggregates.