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.

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

See Administering Oracle Analytics Server.

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:

1. 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.

2. 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.

3. Save and close the file.

4. Restart the Oracle BI Server.

5. 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

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 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.

See Run the Aggregate Specification Script.

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