Use Oracle BI Summary Advisor to Identify Query Candidates for Aggregation

About Oracle BI Summary Advisor

To reduce query time, you can create aggregate tables that store precomputed results for queries that include rolled-up data.

Before creating aggregates, you need to analyze usage tracking statistics to identify which aggregates could increase query performance. You can use the Summary Advisor to get an optimal list of aggregate tables based on query patterns that might achieve maximum query performance gain while meeting specific resource constraints. The Summary Advisor generates an aggregate creation script that you can run to create the recommended aggregate tables.

This section contains the following topics:

Gather Summary Advisor Statistics

Before Summary Advisor can generate recommendations, you must obtain a representative sample of usage statistics for Summary Advisor to use.

Enabling Usage Tracking and Summary Advisor Logging has a minor system performance impact on production systems.

Use one of the following approaches to gather Summary Advisory statistics:

Enable Usage Tracking and Summary Advisor Logging on a production system, and let users run queries against Oracle BI Server for several days. The Summary Advisor Statistics Table is populated with usage statistics. See Turn On Usage Tracking and Turn On Summary Advisor Logging.
In a test environment, run a representative workload against the Oracle BI Server to gather Summary Advisor statistics. A representative workload is a list of commonly requested Logical SQL statements. You typically obtain a representative workload from your production environment.

After you've the representative workload, enable Usage Tracking and Summary Advisor Logging on the Oracle BI Server in your test environment, and use the nqcmd utility to run the workload against the Oracle BI Server. See Use nqcmd to Test and Refine the Repository. The Summary Advisor Statistics Table is populated with usage statistics.

Generate and Use Summary Advisor Recommendations

After the Summary Advisor Statistics table is populated with representative data, the Summary Advisor can analyze the data and generate aggregate recommendations to speed up queries.

Run the Oracle BI Summary Advisor Wizard in the Model Administration Tool to generate an aggregate specification, and then use the aggregate specification to create aggregates using nqcmd. See Generate an Aggregate Specification Script and Run the Aggregate Specification Script.

Oracle BI Summary Advisor supports aggregate creation on Oracle TimesTen In-Memory Database, Oracle Analytics Server or when using Oracle Database In-Memory on Oracle Exalytics.

You can also save your Summary Advisor options to a file, and re-run the Oracle BI Summary Advisor Wizard later without re-entering the same options.

About Measure Subset Recommendations

Learn about using the Summary Advisor with aggregates and specific measures.

When the Only include measures used in queries option is set in the Summary Advisor wizard, the Summary Advisor only recommends aggregates that contain specific measures that are both present in the analyzed query workload, and that can optimize the query workload if aggregates are created.

The Summary Advisor doesn't include measures that aren't used in the query workload in its recommended aggregates.
The size estimation of aggregate fact tables is based on the recommended measure subset, instead of using all the measures of a logical fact table during estimation.
The Summary Advisor doesn't include measures that are invalid for aggregate persistence in its recommended aggregates.

Set Up the Statistics Database

Before you can use the Oracle BI Summary Advisor feature, you must set up a database to store the collected statistics.

You must run the Repository Creation Utility (RCU) on the target database to create the required statistics schema.

See Installing and Configuring Oracle Analytics Server.

You use the database you installed for use with Oracle Analytics Server as the statistics database because this database already has the RCU-created schemas. The RCU-created table name for Summary Advisor is S_NQ_SUMMARY_ADVISOR.
You also need to import the database into the Physical layer of the Oracle BI repository.
You must use the same database for Summary Advisor that you use for usage tracking. If you already have a database and schema set up for usage tracking, you can skip the steps in this section.

See Import Metadata from Relational Data Sources.

Run the Repository Creation Utility on an external database of your choice.
You can skip this step if you choose to use the database you installed for use with Oracle Analytics Server for Summary Advisor statistics, because this database has the RCU-created tables already.
Open the Model Administration Tool and import the database into the Physical layer.
Save and close the repository.

Use the Upload Repository Command to upload the repository and make it available for queries. See Make the Repository Available for Queries.

Columns in the S_NQ_SUMMARY_ADVISOR Table

Review the columns in the S_NQ_SUMMARY_ADVISOR table.

Column	Description
GROUPBYCOLUMNIDVECTOR	Upgrade IDs for logical column objects that represent group-by columns in a processing path. A processing path is an internal Oracle BI Server term. It represents a subquery that involves a single fact logical table source.
LOGICALFACTTABLEID	Upgrade ID of the logical fact table.
LOGICALTABLESOURCEIDVECTOR	Upgrade IDs of the logical table sources.
LOGICAL_QUERY_ID	Foreign key that references the ID column in `S_NQ_ACCT`. This column helps identify the Logical SQL that generated this processing path.
MEASURECOLUMNIDVECTOR	Upgrade IDs for logical column objects that represent measures in a processing path.
PROCESSINGTIMEINMILLISEC	Time spent on this processing path, in milliseconds.
QUERYLEVELIDVECTOR	Upgrade IDs of the logical levels in a processing path.
QUERYSTATUS	For internal use only.
ROW_COUNT	The number of rows retrieved in a processing path. Data in this column is reserved for use by Oracle BI Summary Advisor.
SOURCECELLLEVELIDVECTOR	Upgrade IDs of the logical levels in the logical table source.
VERSION	Version number of the Oracle BI Server.

Turn On Usage Tracking

You must enable usage tracking before collecting Summary Advisor statistics.

See Administering Oracle Analytics Server.

Turn On Summary Advisor Logging

When you're ready to collect statistics, you can enable Summary Advisor logging. For new (non-upgraded) installations, the Summary Advisor parameters are centrally managed. For upgrading customers, the Summary Advisor parameters aren't centrally managed by default.

Enabling Summary Advisor logging

You can manage the Summary Advisor parameters using NQSConfig.INI.

To enable Summary Advisor logging in NQSConfig.INI when central management is disabled for these parameters, follow these steps:

On the Oracle BI Server computer, open the NQSConfig.INI file in a text editor. You can find this file at:
```
ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn
```
Make a backup copy of the file before editing.
In the [USAGE_TRACKING] section, update the following parameters:
- Set SUMMARY_STATISTICS_LOGGING to one of the following options:
  - YES: Enables Summary Advisor logging.
  - LOG_OUTER_JOINT_QUERIES_ONLY: Enables Summary Advisor logging only for logical queries that contain outer joins. Consider using this option when the minor performance impact of enabling full Summary Advisor logging is a concern.
- Set SUMMARY_ADVISOR_TABLE_NAME to the name of the fully-qualified database table for collecting statistics, as it appears in the Physical layer of the Oracle BI repository. For example:
```
SUMMARY_ADVISOR_TABLE_NAME = "My_DB"."DEV_BIPLATFORM"."S_NQ_SUMMARY_ADVISOR";
```
  The table name you specify must belong to the same database object and connection pool that you're using for usage tracking.
Save and close the file.
Restart the Oracle BI Server.
If you've multiple Oracle BI Server instances, then repeat these steps in each NQSConfig.INI file for all Oracle BI Server instances.

Adding Summary Advisor to the Administration Tool Menu

If you open the repository file in online mode on an Exalytics Server, and the Oracle BI Summary Advisor menu option isn't listed under Tools/Utilities, you may need to enable it manually.

Open bi-config.xml
- Linux: $DOMAIN_HOME/config/fmwconfig/biconfig/core/bi-config.xml
- Windows: %DOMAIN_HOME%\config\fmwconfig\biconfig\core\bi-config.xml
Search for the following attribute: <bi:hw-acceleration>.
Set this attribute to true.
Restart the Business Intelligence services. The menu option should now be visible.

Generate an Aggregate Specification Script

After generating Summary Advisor statistics, you can run the Oracle BI Summary Advisor Wizard to generate an aggregate specification script that you can later run to create the aggregates.

You can only run the Summary Advisor Wizard in online mode. You can also run the Oracle BI Summary Wizard from the command line. See Use the nqaggradvisor Utility to Run the Oracle BI Summary Advisor.

Before you run the Summary Advisor Wizard, you must map the target database, used for creating the aggregates, into the Physical layer. You must manually create the necessary database, connection pool, and physical schema objects.

If you've used the Oracle BI Summary Advisor Wizard previously and saved your filter criteria, targets, and other options as an XML file, you can click Load Parameters from File to load the previously saved options into your current wizard session.

The Oracle BI Summary Advisor Wizard is available if you're running Oracle Analytics Server or when using Oracle Database In-Memory on Oracle Exalytics. You can run the aggregate script, recommended by Summary Advisor or manually defined aggregates using Oracle Database In-Memory on Oracle Exalytics as the target.

If your Summary Advisor table, specified in the SummaryAdvisorTableName in the System MBean Browser, or the SUMMARY_ADVISOR_TABLE_NAME parameter in NQSConfig.INI is empty, Summary Advisor can't proceed.

Summary Advisor Setting Recommendations

In the Summary Advisor’s Miscellaneous page, Oracle recommends:

Selecting Use surrogate keys to improve the performance of queries using the aggregates.
Selecting Prefer optimizer estimates to improve performance during the Summary Advisor process.

The Prefer optimizer estimates option enables using cardinality estimates that originate out of the database query optimizer whenever possible, rather than issuing actual count queries. You can use the Prefer optimizer estimates option with Oracle Database.

For Summary Advisor to use database query optimizer estimates, obtain up-to-date statistics on the concerned database objects. See the Oracle Database documentation for more information.

If you don't select the Prefer optimizer estimates option, Summary Advisor issues count queries to the back-end data sources to obtain row counts (cardinality) for certain queries on the data sources that can sometimes take a long time to run. Refer the appropriate database documentation for guidelines on how to obtain the best estimates. For example, when using Oracle Database, you might want to use the column group feature to improve cardinality estimates for multiple column queries.

A query that attempts to sample a particular grain isn't issued by Summary Advisor if an entry for that particular grain already exists in the Summary Advisor cache files, regardless of whether it's an actual count query or a cardinality estimate query.

You should remove the Summary Advisor cache files when selecting or deselecting the Prefer optimizer estimates option. To do this, delete NQAggregate.Stats.Cache.txt and NQAggregate.LTS.Stats.Cache.txt in the following directory on the computer with Model Administration Tool installed:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\
coreapplication_obisn\aggr
```
Select Only include measures used in queries to include measures used in queries. See About Measure Subset Recommendations.

If you don't select this option, all measures in a logical fact table are included in the recommendation, including measures that weren't used in the workload analyzed by Summary Advisor.

See Summary Advisor Stop Criteria Run Constraints.

Oracle recommends running the Model Check Manager to ensure that your repository doesn't contain modeling problems that could affect Oracle BI Summary Advisor performance and results. See Use Model Check Manager to Check for Modeling Problems.

Oracle recommends running the Model Check during off-peak periods. The Model Check Manager runs queries against back-end data sources for some checks. Running the Model Check Manager for large repositories can take a long time. Use Filtered by Statistics, or run it only for selected objects, to improve performance.

Open your repository in the Model Administration Tool in online mode.
Select Tools, and then select Utilities.
Select Oracle BI Summary Advisor, and then click Execute.
Optional: In Filter Logs - Logical Fact Tables, generate Summary Advisor recommendations for all logical fact tables, or select specific logical fact tables, and click Next.
Optional: In Filter Logs - Time Window, enter a Start Date and End Date to filter the Summary Advisor logging statistics based on time period, and click Update to refresh the view after entering a time period.
Optional: In Filter Logs - Execution Time Threshold, specify the number of seconds for Minimum Cumulative Time to filter by a minimum query time threshold for each logical table source.
In Targets, select the target container and associated connection pool for the location of aggregate tables.

You can specify more than one target container.
Specify the Database Schema, Connection Pool, and Capacity for the target in megabytes, then click Add Target to add it to the list.
In Select File Location, click Browse to select the location for storing the aggregate specification, a SQL script, and click Next.
Optional: In Stopping Criteria, specify run constraints for the set of recommendations.
Optional: In Miscellaneous, specify the maximum size of any single aggregate, in megabytes.
You can also specify the location of an XML output file that stores the criteria and options from this session to re-use in a future Summary Advisor session.
In Run, click Run to generate recommendations using the Summary Advisor process.

(Optional) You can click Stop at any point to stop the process. When Summary Advisor stops or runs to completion, the aggregate recommendations are displayed.
When the process completes, click Next.
In Filter Aggregates, review the current set of aggregate recommendations.

You can exclude certain aggregates from the creation process by deselecting the Include option for that row.
On the Finish Script screen, review the script, and then click Finish to save the script.

See Run the Aggregate Specification Script.

Summary Advisor Stop Criteria Run Constraints

In the Summary Advisor wizard Stopping Criteria page, you can specify run constraints for the set of recommendations.

Consider the following:

You can specify the maximum time that Summary Advisor runs before returning results.
You can specify a minimum percentage improvement to performance gain of all affected queries in the workload when adding a new aggregate.

Summary Advisor uses an iterative optimization algorithm. For each round of the iteration, Summary Advisor Summary Advisor evaluates a different set of aggregates. When you specify a minimum percentage improvement on this screen, Summary Advisor compares the estimated gain between two consecutive rounds, and stops when the incremental improvement is less than the specified minimum percentage.

The following formula describes the estimated gain between rounds:
```
Estimated Gain = [(total query time at the beginning of the round) - 
(total query time at the end of the round)] / (Initial total query time 
prior to the first round)
```
For example:
- Initial total query time = 1000s
- End of Round 1:
  
  Total query time = 500s
  
  Gain = (1000 - 500)/1000 = 50%
- End of Round 2:
  
  Total query time = 250s
  
  Gain = (500 - 250)/1000 = 25%

Use the nqaggradvisor Utility to Run the Oracle BI Summary Advisor

You can use the Oracle BI Server utility nqaggradvisor to run the Summary Advisor from the command line instead of using the Model Administration Tool.

After Summary Advisor statistics have been generated, use nqaggradvisor to generate an aggregate specification script that you can then run to create the aggregates. The nqaggradvisor utility is only available if you're running Oracle Analytics Server on the Oracle Exalytics Machine.

The location of the nqaggradvisor utility is:

BI_DOMAIN/bi/bitools/bin

Syntax

The nqaggradvisor utility takes the following parameters.

nQAggrAdvisor -d dataSource | -u userName | -o outputFile | 
-c tupleInQuotes [-p password] [-F factFilter] [-z maxSizeAggr]
[-g gainThreshold] [-l minQueryTime] [-t timeoutMinutes] 
[-s startDate] [-e endDate] [-C on/off] [-M on/off] [-K on/off]

Where:

dataSource is the ODBC data source name for the Oracle BI Server to which you want to connect and run Summary Advisor.

userName is the user name with which to log into the data source. The specified user must have the privilege required to open the Administration Tool in online mode and use the Oracle BI Summary Advisor Wizard.

outputFile is the fully qualified path and file name of the output aggregate specification script.

tupleInQuotes is the aggregate persistence target. You must specify the fully qualified connection pool, fully qualified schema name, and capacity in megabytes.

password is the password corresponding to the userName. If not specified, the user is prompted for a password when running nQAggrAdvisor.

factFilter is the fact filter file name. The fact filter file contains the fully qualified names of logical fact tables for which to generate Summary Advisor recommendations. Add each logical fact table's fully qualified name on a separate line. If a fact filter file isn't specified, then all logical fact tables in the repository are included in the analysis.

maxSizeAggr is the maximum size of an aggregate in megabytes.

gainThreshold is the minimum percentage improvements to performance gain of all affected queries in the workload required by Summary Advisor when adding a new aggregate in its iterative optimization algorithm. Summary Advisor stops when this value isn't satisfied. The default value is 1.

minQueryTime is the minimum query time threshold in seconds for each logical table source before it's included in the Summary Advisor process. The default value is 0.

timeoutMinutes is the maximum time in minutes that Summary Advisor runs before returning results. Specify 0 for unlimited. The default value is 0.

startDate is the start date for statistics to include in the Summary Advisor process.

endDate is the end date for statistics to include in the Summary Advisor process.

-C specifies whether to use optimizer estimates. Specify on or off. The default is off.

-M specifies which measures to include in the recommendation. Specify on to include measures used in the workload. Specify off to include all measures in a logical fact table including those measures that weren't used in the workload analyzed by Summary Advisor. The default is off.

-K specifies whether to use surrogate keys. Specify on or off. The default is on.

Examples

The following example shows how to correctly specify the tupleInQuotes parameter:

nQAggrAdvisor -d "AnalyticsWeb" -u "Administrator" -p "ADMIN" -o "C:\temp\aggr_advisor.out.txt" -c "DW_Aggr"."Connection Pool","DW_Aggr".."AGGR",1000

The following example shows how to correctly specify the gainThreshold , startDate , and endDate parameters.

nQAggrAdvisor -d "AnalyticsWeb" -u "Administrator" -p "ADMIN" -o "C:\temp\aggr_advisor.out.txt" -F "C:\temp\fact_filter.txt" -g 10 -c "TimesTen_instance1"."Connection Pool","dbo",2000 -s "2011-05-02 08:00:00" -e "2011-05-07 18:30:00" -C on -M on -K off