Kishore Analytics Solutions: Creating and Modeling Aggregate Tables in OBIEE11g

Purpose

This tutorial shows you how to use the Aggregate Persistence Wizard to create and model aggregate tables to increase query performance by precalculating frequently accessed measure aggregations.

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. You should also determine the database 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.

Prerequisites

Before starting this tutorial, you should:

1.	Have access to or have installed Oracle 10g Database (preferably version 10.2)
2.	Have access to or have installed the sample schema This example uses the SH schema included in the Oracle10g Database. You can also install the schema by following the instructions in the Installing the Sample Schemas and Establishing a Database Connection OBE tutorial.
3.	Have access to or have installed Oracle BI EE 10.1.3.x
4.	Have set up Oracle BI Scheduler
5.	Have set up the Sales History (SH.rpd) repository by performing the following steps: Click Start > All Programs > Administrative Tools > Services. In the Services dialog box, select Oracle BI Presentation Server, and then select Action > Stop to stop the service. Also, stop the Oracle BI Server. These services can be stopped in any order. Create a directory named SetupFiles under <InstallDrive>. Download the SH.zip file from here, copy it to the SetupFiles directory, and extract the file. Using Windows Explorer, navigate to the SetupFiles folder and copy the sh folder and its contents to<InstallDrive>:\OracleBIData\web\catalog. This is a backup of the Presentation Catalog corresponding to the Sales History content. Open <InstallDrive>:\OracleBIData\web\config\instanceconfig.xml in Notepad and edit the CatalogPath section to point to the directory you just copied (as shown in the following screenshot). The path should be<InstallDrive>:/OracleBIData/web/catalog/sh. Navigate to the SetupFiles directory and copy sh.rpd to <InstallDrive>:\OracleBI\server\Repository. Open *<InstallDrive>:\OracleBI\server\Config\NQSConfig.ini* in Notepad and edit the repository name to point to the sh.rpd repository you just copied. In the Repository section, enter # before "Star" to comment out any other repository name entry. Then, below this entry, create a new line and enter Star = sh.rpd, DEFAULT;to point to your repository (as shown in the following screenshot): Note: NQSConfig.ini is the initialization file read by the Business Intelligence Server when it starts up. It contains a number of parameters that control server settings and behavior. In this step, you have set up the parameter that informs the server to read the sh.rpd metadata repository. Note: The screenshots for this tutorial were taken in a Windows XP environment. Therefore, the Start menu options may vary slightly if you are using any other Windows environment.

Using the Aggregate Persistence 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.

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 Fact Table: SalesFacts

Measures	Dimension/Grain	Source Database
“Amount Sold,”“Unit Cost,”“Gross Profit,” and many more	ProductsDim/”Products Detail” ChannelsDim/”Channels Detail” PromotionsDim/”Promotions Detail” TimesDim/”Times Detail” CustomersDim/”Customers Detail”	orcl/SH

Aggregate Fact Table: ag_Salesfacts

Measures	Dimension/Grain	Source Database
“Amount Sold”	ProductsDim/Category ChannelsDim/NA PromotionsDim/NA TimesDim/”Times Detail” CustomersDim/”State Province”	orcl/SH (the same as the source database)

To build and model aggregates, you perform the following steps:

To create a dedicated connection pool, perform the following steps:

1.	Select Start > All Programs > Oracle Business Intelligence > Administration.
2.	Select File > Open > Offline and select sh.rpd in <InstallDrive>:\OracleBI\server\Repository to open the repository in offline mode. Enter Administrator as the username and password, and click OK.
3.	Right-click the orcl SH database and select New Object > Connection Pool. .
4.	In the Connection Pool dialog box, enter Aggregate Connection Pool as the name, select the OCI 10g/11gconnection type, enter your database TNS Name and SH as the username and password, and then click OK. Note that the SH database user is the schema owner of SH in the Oracle database. You use the schema owner's user ID for the aggregate connection pool because the connection pool must have privileges to create and drop tables, as well as read from and write to them. The Aggregate Persistence Wizard creates an Oracle BI EE SQL script that is executed by the BI Server using the dedicated connection pool. You need a dedicated connection pool because end users would connect to the data warehouse with much more restrictive privileges.
5.	In the Confirm Password dialog box, enter SH as the password again and click OK.
6.	Select File >Save to save your changes. Click No to decline the global consistency check.
7.	Select File > Close to close the repository.
8.	In the Services dialog box, start the Oracle BI Presentation Server and the Oracle BI Server.

To build the aggregate table and metadata script, perform the following steps:

1.	Select File > Open > Online. In the Open Online AnalyticsWeb dialog box, enter Administrator as the username and password, and clickOpen.
2.	Select Tools > Utilities. In the Utilities dialog box, select Aggregate Persistence Wizard and click Execute.
3.	In the wizard's "Select file location" window, click Browse to specify the location and the name of the script you are creating. Do not select the Generate DDL File check box. This option is used to create a second script that defines the aggregate tables on the database and repository, but does not populate them. This is useful for database administrators who want to make granular changes to the database tables generated by the system.
4.	The Select Script dialog box opens with the BI Server's Repository directory that is selected by default. In the "File name" field, enterCREATE_AGG and ensure that the *SQL command file (.sql)** file type is selected. This file stores the aggregate specifications and is updated if more aggregates are specified subsequently.
5.	Click Open to close the Select Script dialog box.
6.	Click Next to proceed to the Select Business Model & Measures window, in which you specify the measure or fact table that you want to aggregate. In the top pane, select the SH business model. In this case, this is the only available business model. However, if more than one is available, you can select only one business model.
7.	In the bottom pane, expand the Salesfacts fact table. Again, you can select only one fact table per wizard execution. Note, however, that you can reuse the wizard repeatedly to build each aggregate fact table that you want to build. Subsequent SQL instructions created by the wizard are appended to the same, single script.
8.	Select the Amount Sold measure. Note that you can select multiple measures.
9.	Click Next to proceed to the Select Dimensions & Levels window. Select the following levels to specify what level of detail is stored in the aggregate table. Leave the Use Surrogate Key option deselected. ProductsDim: Category TimesDim: Times Detail CustomersDim: State Province Leave the ChannelsDim and PromotionsDim dimensions blank. In this step, you set the grain of the aggregate table. This grain is used to create the DDL for the tables and the aggregating DML for loading the tables. It is also used to generate the Oracle BI metadata that is used to access the tables.
10.	Click Next to proceed to the "Select output Connection Pool, Container & Name" window. In the top pane, select the orcl SH database. In the second pane, expand orcl SH and select the SH catalog. Select Aggregate Connection Pool in the Connection Pool field and the default table name that appears in the "Aggregate table name" field, ag_Salesfacts. The connection pool can be in a different database than the source.
11.	Click Next to proceed to the Aggregate Definition window. This window displays the Oracle BI EE logical SQL that generates the aggregate tables based on the parameters defined in the other wizard windows. This script tells the BI Server what to name the star, where to source it from, what facts to include, and what its grain is. If you choose to define another aggregate, it is added as another logical SQL statement in the script. Review the logical SQL, and then select I am done.
12.	Click Next. The Complete Aggregate Script window is displayed to confirm that the script has been generated. Click Finish.
13.	In Windows Explorer, navigate to *<InstallDrive>:\OracleBI\server\Repository* and verify that the CREATE_AGG.sql script file was created.
14.	Open the CREATE_AGG.sql file and add a delete aggregates; statement to the beginning of the script. Because the script is run after each load of the database, the old aggregates from the previous load must be dropped.

To schedule an aggregate creation job, perform the following steps:

1.	Select Manage > Jobs. In the Job Manager window, select File > Open Scheduler Connection.
2.	Enter Administrator as the Administrator name and password and click OK to connect to the Scheduler.
3.	Select All Jobs and then select Jobs > Add New Job.
4.	Enter Aggregate Persistence Job as the name of the job, and Administrator as the user ID. Select NQCmd as the script type and specify AnalyticsWeb as the data source name (DSN) to connect to the BI Server. Finally, specify the location of the aggregate creation script, <InstallDrive>:\OracleBI\server\Repository\CREATE_AGG.sql.
5.	In the Trigger Type drop-down list, notice that you can set the script to run daily, weekly, or at other intervals. Select Run Nowand click OK.
6.	Verify that the job completes successfully in the Job Manager.

To verify aggregates and metadata, perform the following steps:

Verify that the aggregates are created in the Physical layer of the repository.

a.	If necessary, open the SH repository in online mode and log in as Administrator/Administrator.
b.	In the Physical layer, ensure that the aggregates were created in the SH schema. There should be one new ag_SalesFactsaggregate and three new dimension aggregates in the Physical layer. Notice that the aggregate tables are indicated in red in the Administration tool.
c.	Right-click each aggregate table and select Update Row Count to check connectivity. Confirm that you see the following row counts: ag_SalesFacts: 158,142 rows SA_Categor…: 5 rows SA_State_P…: 145 rows SA_Times…: 1,461 rows.
d.	Double-click ag_SalesFacts, check out objects, and select the Foreign Keys tab in the Physical Table properties dialog box. Ensure that joins are created between ag_SalesFacts and the new dimension aggregates.
e.	Double-click the foreign keys to view the join relationships in the Physical Foreign Key dialog box. When you are done, clickCancel to close the Physical Foreign Key dialog box.
f.	Click Cancel to close the Physical Table - ag_SalesFacts dialog box.

Verify that the aggregates are created in the Business Model and Mapping layer of the repository.

a.	In the Business Model and Mapping layer, open the Sources folder for the SalesFacts, Customers, Times, and Productslogical tables and ensure that new logical table sources are created for the aggregates.
b.	In the Sources folder for the SalesFacts logical table, double-click the ag_SalesFacts logical table source. Check out the objects.
c.	Click the General tab and ensure that the ag_SalesFacts logical table source maps to the ag_SalesFacts physical table.
d.	Click the Column Mapping tab and ensure that the Amount Sold logical column maps to the corresponding physical columns in the ag_SalesFacts physical table.
e.	Click the Content tab and ensure that the logical levels are set correctly.
f.	Click Cancel to close the ag_SalesFacts Logical Table Source dialog box.
g.	Check in the changes, check for global consistency, and save the repository.

Verify that the aggregates are created in the database.

a.	Open Internet Explorer and enter the following URL to open Oracle Enterprise Manager: *http://<machine name>:1158/em*. Enter SH as the username and password, and log in.
b.	Click the Administration link.
c.	Click the Tables link.
d.	Ensure that SH is entered as the schema name, and click Go.
e.	Ensure that the four new aggregate tables are created in the SH schema.
f.	Log out from Oracle Enterprise Manager and close the browser.

To test in Answers, perform the following steps:

1.	If necessary, log in to Answers as Administrator. Click the Reload Server Metadata link.
2.	Select the SH subject area and create and run the following query: Customers.Country Region, Sales Facts.Amount Sold with a filter for Calendar Year = 2001
3.	Select Settings > Administration > Manage Sessions > View Log to inspect the query log. Ensure that the query uses the ag_SalesFacts table and the related aggregate dimension tables.