Oracle® Business Intelligence Server Administration Guide > Oracle BI Administration Tool Utilities and Expression Builder > Utilities and Wizards >
Aggregate Persistence Wizard
Aggregates are created and persisted in the Oracle BI Server metadata as well as in the back-end databases. This section describes how you can use the Aggregate Persistence Wizard to create the SQL file that will be used to create aggregate tables and map them into the metadata.
Guidelines for Using the Aggregate Persistence Wizard
The traditional process of creating aggregates for Oracle BI Server queries is a manual process, requiring that you write complicated DDL and DML files that create and populate tables in the databases. Additionally, these tables need to be mapped into the repository metadata to be available for queries. This is a time-consuming and potentially error-prone process. The Aggregate Persistence Wizard allows the Oracle BI Administrator to automate the creation of these aggregate tables and their mappings into the metadata. The following is a list of the guidelines you should follow when using the Aggregate Persistence Wizard:
- From the Tools menu, choose Utilities > Aggregate Persistence Wizard, and then click Execute.
- In the Select file location dialog box, specify the complete path and file name of the aggregate creation script. You can specify a new or an existing file name. If you want to generate a DDL file, select the Generate DDL file check box.
- In the Select Business Model & Measures dialog box, select the items.
NOTE: The View Script button is not available during the creation of the first aggregate table block.
- In the Select Dimensions & Levels dialog box, expand the window to view all columns. You might want to specify a surrogate key to be used for the fact-dimension join.
The default join option between the fact and level aggregate tables is to use the primary keys from the level aggregate. If the primary key of the level is large and complex, the join to the fact table will be expensive. A surrogate key is an artificially generated key, usually a number. For example, a surrogate key in the level aggregate table would simplify this join, removing unnecessary (level primary key) columns from the fact table and resulting in a smaller fact table.
- In the Select Output Connection Pool, Container & Name, select the items. A default aggregate table name will be provided and a prefix (defined in NQSConfig.INI) is added to the file name.
- In the Aggregate Definition dialog box, the View Script button becomes available for use, the logical SQL appears for your review, and you have a choice of defining another aggregate (default) or ending the wizard.
- In the Complete Aggregate Script dialog box, the complete path and file name appears.
For information about using the SQL file to create aggregate tables, refer to Creating Aggregates for Oracle BI Server Queries.