Oracle^® Business Intelligence Server Administration Guide > Query Caching in the Oracle BI Server > Creating Aggregates for Oracle BI Server Queries >

About Writing the Create Aggregates Specification

To create the script file, you can use the Aggregate Persistence Wizard in the Administration Tool or write the file manually.

NOTE:  It is recommended that you use the Aggregate Persistence Wizard. For instructions, refer to Aggregate Persistence Wizard.

If you do not want Oracle BI Server to modify your databases during aggregate creation, you can use the Aggregate Persistence Wizard to create the SQL file. After creating the SQL, you can use your database administration processes to create your aggregate tables.

Constraints Imposed During the Create Process

This section describes the constraints that are imposed during the create process.

Valid Measures

A valid measure must have a valid aggregation rule.

• The following constraints apply to level-based measures:
  • If the level is grand total alias, then that dimension must not be present in the list of levels for that aggregate specification.
  • Any other level defined for this measure, must be present in the list of levels for that aggregate specification.

    If the above constraints are not met, the entire aggregate specification will be discarded.

• A measure will be ignored by the create process if any of the following conditions are true:
  • Measure is mapped to a session or repository variable.
  • Measure is a derived measure.

    Measures that are ignored do not necessarily affect the aggregate specification. The remaining measures will be used to create the aggregate.

Valid Levels

A valid level must have a valid primary key.

• If a level is invalid, the aggregate specification will be discarded.
• Attributes of a level or its primary key will be ignored, if any of the following conditions are true:
  • Attribute is mapped to session or repository variables.
  • Attributes are not from the same logical table.

Valid Aggregate Specification

A valid aggregate specification has the following properties:

• Name length is between 1 and 18 characters (inclusive).
• At least one valid level must be specified.
• At least one valid measure must be specified.
• Must have a valid connection pool.
• Must have a valid output container (database/catalog/schema).
• Connection pool and container must belong to the same database.
• Only one level per dimension can be specified.
• Measures can only be from the same fact table.
• All logical components of the specification must be from the same subject area.

An aggregate specification will be ignored if the name already exists in the output container because level aggregates are scoped by the entire database. However, if different catalogs or schemas are specified for the same fact aggregate name, it is allowed to have multiple facts with the same name but different scope in the same database.

Guidelines for Writing the Create Aggregates Specification

All metadata names (except for logical fact columns) are fully qualified. There are two modes of operation: Create and Delete.

It is strongly recommended to place all aggregate specifications under a single Create Aggregates statement.

• Begin the script file with a Delete statement. It is essential to delete system generated aggregates before creating any new ones. This makes sure that data is consistent and it removes invalid or incomplete aggregates before you run the Create operation. The following statement is the syntax for deleting aggregates:

  Delete aggregates;

• The next statement should be a Create statement. The following is the syntax for creating aggregates:

  Create|Prepare aggregates

  <aggr_name_1>

  for logical_fact_table_1 [(logical_fact_column_1, logical_fact_column_2,...)]

  at levels (level_1, level_2, ...)

  using connection pool <connection_pool_name_1>

  in <schema_name_1>

  [ ,<aggr_name_2>

  for logical_fact_table_3 [(logical_fact_column_5, logical_fact_column_2,...)]

  at levels (level_3, level_2, ...)

  using connection pool <connection_pool_name_2>

  in <schema_name_2>] ;

• Creating multiple aggregates. To specify more than one aggregate in a single Create Aggregates statement, use the following guidelines:
  • Each of the multiple aggregate specifications are separated by a comma, and the entire aggregate creation script is terminated with a semi-colon.
  • In this file, only one Delete Aggregates statement should be specified at the beginning. The Oracle BI Administrator should make sure that only one delete is issued per ETL run (unless a reset is called for).

    CAUTION:  Any aggregate scripts that are run after the first one should not have a Delete Aggregates statement or all previously created aggregates will be removed.

• Creating aggregates with surrogate keys. For detailed instructions, refer to About Adding Surrogate Keys to Dimension Aggregate Tables

About Adding Surrogate Keys to Dimension Aggregate Tables

The join option default between fact and level aggregate tables uses primary keys from the level aggregate. If the primary key of the level is large and complex (composite of many columns), the join to the fact table will be expensive. A surrogate key is an artificially generated key, usually a number. A surrogate key, in the level aggregate table, simplifies this join and removes unnecessary columns (level primary key) from the fact table, resulting in a smaller-sized fact table. Adding surrogate keys to the dimension (level) aggregate tables can simplify joins to the fact tables and might improve query performance. Additionally, a surrogate key makes sure that each aggregate table has a unique identifier.

There may be cases in which a level is shared among multiple fact tables. One fact may use surrogate keys, and another may use primary keys from the dimension aggregate. The following are some options for resolving this issue:

• Set a metadata property for levels that indicates whether to use surrogate keys or primary keys.
• Always create a surrogate key for a level aggregate (relatively low cost operation). Then decide later if the fact aggregate should join to it using a surrogate or primary key.

An alternative to specifying the join type for each dimension is to specify if surrogate keys should be used for the entire star. This would result in simpler syntax but would also restrict the available user options and slow the aggregate creation process.

Surrogate Key Input for Create/Prepare Aggregates

The Oracle BI Administrator can create the aggregate star using the following join options:

• Primary Keys (default, if no option is specified)
• Surrogate Keys

Syntax for Create/Prepare Aggregates

The following syntax for create/prepare aggregates contains the change for [Using_Surrogate_Key]. The surrogate key option can be specified for each level. If unspecified, the fact and dimension tables are joined using the primary key from the level aggregate.

Create|Prepare aggregates

<aggr_name_1>

[file <output_file_name>]

for logical_fact_table_1 [(logical_fact_column_1, logical_fact_column_2,...)]

at levels (level_1 [Using_Surrogate_Key], level_2, ...)

using connection pool <connection_pool_name_1>

in <schema_name_1>

[ ,<aggr_name_2>

for logical_fact_table_3 [(logical_fact_column_5, logical_fact_column_2,...)]

at levels (level_3, level_2, ...)

using connection pool <connection_pool_name_2>

in <schema_name_2>] ;

Surrogate Key Output From Create/Prepare Aggregates

The changes to the current process are restricted to the physical metadata layer in the repository and the database.

When you use the UseSurrogateKeys join option, the following describes the results:

• For a level aggregate, the following occurs:
  • In the physical metadata, the following occurs:
    • The level aggregate table will have a new column called <level_name>_SKEY (check for collisions). This is the surrogate key column for the dimension aggregate.
    • The type of this column is UINT.
  • In the database, the following occurs:
    • The level aggregate table will also have a corresponding column called <level_name>_SKEY.
    • It can be populated using RCOUNT ().
• For a fact aggregate, the following occurs:
  • In the physical metadata, the following occurs:
    • The fact aggregate table will no longer contain columns from the level's primary keys.
    • Instead, a new column that corresponds to the level aggregate's surrogate key will be added to the table.
    • The type of this column will be identical to the level's surrogate key.
    • The column will have the same name as that in the level aggregate (check for collisions).
    • The fact table and the level table will be joined using this surrogate key only.
  • In the database, the following occurs:
    • The fact aggregate table will also have the corresponding surrogate key.
    • It is populated using new capabilities to be available through Populate.