Running the Aggregate Specification Script

Learn how to run the aggregate specification script against the Oracle BI Server.

Before you run the script, you must create an ODBC DSN (data source Name) for the Oracle BI Server and ensure that the correct log level is set. You must manually create a DSN for the Oracle BI Server to run the aggregate specification against for a single-node deployment. When the deployment is a multi-node cluster, you must run the aggregate specification directly against the source Oracle BI Server. Create a non-clustered DSN for the source Oracle BI Server to run the aggregate specification against. Use the Cluster Manager in the Administration Tool in online mode to determine which Oracle BI Server is the source.

Note:

In a clustered environment, the aggregate specification script performs a rolling restart of the destination Oracle BI Servers in the background. As a best practice, you should avoid making other configuration changes in Fusion Middleware Control or the configuration files while running the aggregate persistence script. Only the destination servers are restarted in the rolling restart. Changing the configuration, might send the Oracle BI Server a different set of configuration settings than the destination Oracle BI Servers. If the configuration changed, restart the source Oracle BI Server.

After creating a DSN, you can execute the script using nqcmd as a user who is a member of the BI Administrators group. See Using nqcmd to Test and Refine the Repository.

The queries and errors are logged to nqquery.log when using an Oracle BI EE 11g version. When using Oracle BI EE 12c, the queries and errors are logged in the obis1_query.log located in the DOMAIN_Home/servers/obis1/logs.

See “Integrating Other Clients with Oracle Business Intelligence” in Integrator's Guide for Oracle Business Intelligence Enterprise Edition for information about how to create an ODBC DSN for the Oracle BI Server .

Trace logs are recorded if the logging level is at least 2. The logging events include the aggregate execution plan and the order in which the aggregates are created and deleted. Higher logging levels provide more details about the query and execution plans - for example, specify logging level 4 or higher to see the queries being executed. Error logs are recorded if the logging level is at least 1, and to nqserver.log regardless of the logging level.

Use one of the following methods to set the logging level:

Set the logging level in the repository user object for the user who plan to run the script. See Managing the Query Log in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
Create and set the LOGLEVEL system session variable. LOGLEVEL overrides logging levels set on individual users. See Creating Session Variables.

Connect directly to a DSN for a running Oracle BI Server and not to a clustered DSN.
Set an appropriate logging level, least 2, before executing the script.
edit the aggregate creation script directly to set the logging level as a request variable in each delete aggregates or create aggregates statement, for example:
```
set variable LOGLEVEL=7 : delete aggregates;
set variable LOGLEVEL=7 : create aggregates... ;
```
Use a colon as a delimiter when setting request variables using nqcmd.
As a member of the BI Administrators group, use nqcmd to connect to the non-clustered DSN for the Oracle BI Server that you created in step 1.
Run the aggregate specification script.

After executing the SQL script, aggregates are created and persisted in the Oracle BI Server metadata, as well as, in the back-end databases.

When a series of aggregates are being created, and the creation of one aggregate fails, the aggregate persistence engine skips creation of the failed aggregate and its dependencies and proceeds to the next aggregate in the list.

Check the log files to identify failed aggregates. If orphan aggregate dimensions, those not joined to any other fact table, were created, use the Delete Aggregates command to remove them.