Create and Persist Aggregates for Oracle BI Server Queries

14 Create and Persist Aggregates for Oracle BI Server Queries

Learn how to set up and use aggregate persistence in Oracle Analytics Server.

Most data warehouse practitioners create aggregated data tables to improve the performance of highly summarized queries. The aggregate tables store precomputed results that are combined measures, usually summed, over a set of dimensional attributes. Use aggregate tables to improve query response times in decision support systems.

If you write SQL queries or use a tool that only understands what physical tables exist and not their meaning, then using aggregate tables becomes more complex as the number of aggregate tables increases. The Oracle BI Server’s aggregate navigation capability enabled queries to use the information stored in aggregate tables automatically. The Oracle BI Server lets you concentrate on asking the right business question, and then the server decides which tables provide the fastest answers.

Oracle Analytics Server takes advantage of the aggregates in source databases. See Manage Logical Table Sources (Mappings). The aggregate persistence automates the creation and loading of the aggregate tables and their corresponding metadata mappings to minimize the time required to create and maintain the data aggregation, as well as load database scripts and the corresponding metadata mappings.

This chapter contains the following topics:

Use Oracle BI Summary Advisor to Identify Query Candidates for Aggregation

If you're running Oracle Analytics Server on the Oracle Exalytics Machine, you can use the Oracle BI Summary Advisor feature to identify which aggregates increase query performance and to generate a script for creating the recommended aggregates. If you aren't running Oracle Analytics Server on the Oracle Exalytics Machine, the Oracle BI Summary Advisor feature isn't available.

This section contains the following topics:

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

Use Model Check Manager to Check for Modeling Problems

Learn how to use Model Check Manager to check for modeling problems that might affect Oracle BI Summary Advisor and the aggregate persistence engine.

This section contains the following topics:

About Model Check Manager

You can use the Model Check Manager to check your repository metadata for issues that might affect the success of the Oracle BI Summary Advisor or the aggregate persistence engine.

The Model Check Manager requires access to the summary statistics table, when using Filtered by Statistics, and back-end data sources for some checks. Some of the back-end queries can impact performance, you should run the Model Check Manager during off-peak periods.
You can only run the Model Check Manager in online mode.
The Model Check Manager doesn't make any changes to repository metadata. The Model Check Manager only flags possible problems.

The Model Check Manager returns both error and warning messages. You must fix errors identified by Model Check Manager. If you don't fix the errors, the Oracle BI Summary Advisor could provide incorrect recommendations, and the aggregate persistence engine could fail to create aggregates. You should fix warnings. Issues identified by warnings result in suboptimal recommendations from Oracle BI Summary Advisor, or suboptimal performance from the aggregate persistence engine.

Model Check Manager runs parallel queries against the database for better performance. By default, 24 threads are enabled. To change the default number of threads for model check manager, create and set an operating system environment variable called MODEL_CHECKER_MAX_THREADS. The maximum number of threads you can specify is 100.

Run Model Check Manager

For Oracle BI Summary Advisor, run Model Check Manager after you've gathered Summary Advisor statistics, but before you run the Oracle BI Summary Advisor Wizard.

To run Model Check Manager globally using the Administration Tool, select the File menu, then select Check Models. You can use the following options:

Complete: Checks all objects in the Business Model and Mapping layer of the Oracle BI repository.
Filtered by Statistics: Checks only fact table objects and associated dimensions in the Business Model and Mapping layer that have been actively queried according to the statistics table. Select this option to speed up the process for large repositories.

This option is only available on the Oracle Exalytics Machine. If you attempt to filter by statistics on a non-Exalytics system, or if you attempt to filter when the statistics table isn't available, a warning appears explaining that Model Check Manager can't filter by statistics.

See the following sections for information about setting up the Summary Advisor statistics table:

To run Model Check Manager for selected objects using the Model Administration Tool, right-click one or more business models, dimension objects, or logical fact tables and select Check Model. Then, choose Complete or Filtered by Statistics from the submenu, as described in the preceding list. The Filtered by Statistics menu option is only available for fact table objects and business model objects.

When using Model Check Manager with large repositories, it's recommended that you use Filtered by Statistics, or run it only for selected objects, to improve performance.

In the Model Administration Tool, from the File menu, select Check Models.

Resolve Model Errors

After running the Model Check Manager for one or more objects, the Model Check Manager opens so that you can correct errors in the repository.

Run the Model Administration Tool in online mode.

In the Model Check Manager results, double-click a row to open the Properties dialog, or select a row and click Go To.
Correct the problems using the information in the Error Description.
Rerun the Model Check to verify that all of the issues are resolved.

Check Models Using the validaterpd Utility

You can check models from the command line using the Oracle BI Server validaterpd utility with the -L option.

Running this utility with -L performs the same model checks as Model Check Manager in the Model Administration Tool. The validaterpd utility is available on both Windows and Linux systems.

To run validaterpd in Model Check mode, you must specify the DSN of a running Model Administration Tool.

The location of the validaterpd utility is:

BI_DOMAIN/bi/bitools/bin

See Use the validaterpd Utility to Check Repository Consistency.

Syntax

The validaterpd utility takes the following parameters in Model Check mode:

validaterpd -L -D DSN_name -U DSN_user_name [-P DSN_password] 
{-O output_txt_file_name |-C output_csv_file_name | -X output_xml_file_name} [-W]
[-S] [-8]

Where:

-L: Specifies Model Check mode.

-D: The DSN of a running Oracle BI Server.

-U: The user name for the Oracle BI Server DSN.

-P: The password for the Oracle BI Server DSN.

The password argument is optional. If you don't provide the password argument, you're prompted to enter the password when you run the command. To minimize the risk of security breaches, Oracle recommends that you don't provide password arguments either on the command line or in scripts.

The password argument is supported for backward compatibility only. For scripting purposes, you can pass the password through standard input.

-O Use this option to output the results in a text file.

-C Use this option to output the results in a CSV file.

-X Use this option to output the results in an XML file.

-8 Use this option to specify UTF-8 output (optional).

-W You can include an allowed list objects file. This text file specifies a limited number of logical objects that you want to check. Enter the fully-qualified name of each logical object on a single line. If -W isn't specified, all logical objects are checked.

-S Use this option to check only objects that have been actively queried according to the statistics table. If -S isn't specified, all objects are checked. If -W is also specified, the allowed list file can only contain business models and logical fact tables, other objects aren't checked. This option is only available on the Oracle Exalytics machine.

Examples

validaterpd -L -D DSNName -U Username -O results.txt
Give password: my_dsn_password

The preceding example connects to an Oracle BI repository using the DSNName connection, checks all models in the Oracle BI repository, and writes output to results.txt.

validaterpd -L -D DSNName -U Username -O results.txt -W whitelist.txt -S
Give password: my_dsn_password

The preceding example connects to an Oracle BI repository using the DSNName connection, performs a model check, and writes output to results.txt. Only objects listed in whitelist.txt are checked. Furthermore, because -S is specified, only objects that have been actively queried according to the statistics table are checked.

When -W and -S are both specified, the allowed list can only contain business models and logical fact tables. Other objects aren't checked.

Write the Create Aggregates Specification Manually

You can write the script file manually, instead of using the Aggregate Persistence Wizard to create the script file. Oracle recommends that you use the Aggregate Persistence Wizard.

If you don't want the Oracle BI Server to modify your databases during aggregate creation, then you can specify this in the Aggregate Persistence Wizard by selecting the option Generate target DDL in a separate file. The Aggregate Persistence Wizard creates a DDL file, the prepare aggregates script, that you can use to create the empty aggregate tables. After this, you need to run the create aggregates script to populate the aggregate tables. This option provides some flexibility in case the database access to create tables is restricted. You must run the prepare aggregates script before you run the create aggregates script.

This section contains the following topics:

What Constraints Are Imposed During the Create Process?

You can learn about constraints are imposed during the create process.

The following constraints 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 mustn't 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 aren't met, then the entire aggregate specification is discarded. In addition, a measure is 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.
- Measure has a default aggregation rule of None.
Measures that are ignored don't necessarily affect the aggregate specification. The remaining measures are used to create the aggregate.
Valid levels

A valid level must have a valid primary key. If a level is invalid, the aggregate specification is discarded. Attributes of a level or its primary key are ignored if any of the following conditions are true:
- Attribute is mapped to session or repository variables.
- Attributes aren't 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).
- Specify at least one valid level.
- Specify at least one valid measure.
- 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 is ignored if the name already exists in the output container, because level aggregates are reviewed by the entire database. However, if different catalogs or schemas are specified for the same fact aggregate name, it's allowed to have multiple facts with the same name but different scope in the same database.

The aggregate specification is discarded if any dimension isn't joined to a fact.

Write the Create Aggregates Specification

All metadata names, except logical fact columns, are fully qualified.

There are two modes of operation: Create and Delete. It's strongly recommended that you place all aggregate specifications under a single Create Aggregates statement.

See Add Surrogate Keys to Dimension Aggregate Tables.

Delete Statement for Aggregate Specification

Begin the script file with a Delete statement. It's essential to delete system-generated aggregates before creating new ones.

This ensures that data is consistent and removes invalid or incomplete aggregates before you run the Create operation. The following statement is the syntax for deleting aggregates:

Delete aggregates [list of fully qualified physical table names];

For example:

Delete aggregates "src".."INCR"."fact_1", "src".."INCR"."fact_2";

You can include a comma-separated list of physical tables to delete. You must include system-generated tables from a previous run of the aggregate creation script. Any dimension tables joined to listed fact tables are also deleted.

If a dimension table is joined to more than one fact table, you can't delete the table unless the other joined table is also deleted.

In addition to fact tables, you can also use the Delete statement to delete orphan dimension tables, these are dimension tables that aren't joined to any other fact table. Orphan dimension tables sometimes occur when aggregate creation fails.

The Delete statement also removes the logical key and logical column that were added to the time dimension's logical table when chronological keys were added for aggregate persistence.

Create Statement for Aggregate Specification

The Create statement should follow the Delete 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, count_distinct_logical_fact_column_1 as_raw_values, ...)]   
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] ;

The as_raw_values must accompany a count-distinct measure with a simple aggregate rule. A simple aggregate rule has only one rule, which isn't dimension-based.

Multiple Aggregates in Aggregate Specification

Use these guideline to specify multiple aggregates in a single Create Aggregates statement.

Ensure that each of the multiple aggregate specifications are separated by a comma, and the entire aggregate creation script is terminated with a semicolon.
In this file, only one Delete Aggregates statement should be specified at the beginning. Make sure that only one delete is issued per ETL run, unless a reset is needed.

Any aggregate scripts that are run after the first one shouldn't have a Delete Aggregates statement, or all previously created aggregates are removed.

Where Clause for Aggregate Specification

You can add an optional Where clause to the Create statement.

The Where clause filters the data that you want to aggregate and creates fragmented aggregates, or aggregates for only a fragment of data in the base fact table. The Where clause also sets the Fragmentation content field located on the Logical Table Source dialog. In most cases, the creation of fragmented aggregates maximizes query acceleration while minimizing the cost of creating and maintaining the aggregate.

The following examples show when you would use fragmented aggregates:

If you're working with the time dimension and want your aggregates to include data only from the last three years.
If your company reports primarily on revenue in the United States and wants the aggregates to include only United States data.

The following is an example of a valid Create statement with the Where clause:

Create Aggregates
Revenue_By_Year
for  "sales"."sales" at levels("sales".timedim.year)   
where("sales"."time"."calendar year"=2007)
using connection pool aggrtarget.cp1
in aggrtarget..schema1

Logical Column Requirements

The logical column that you specify in the Where clause must meet the following requirements:

The logical column must belong to a dimension.
If the logical column belongs to a dimension included in the aggregate specification, then it must be at or above the level of the aggregate.
If you use operational_oper, then the logical column's data type must match the constant's data type in inlist.

If you use inclusion_oper, then the logical column's data type must match all the constants' data type in inlist.

Where Clause Grammar

The grammar for the Where clause in the create aggregate specification is a subset of the Where grammar for a Logical SQL filter. The grammar the Oracle BI Server supports for the create aggregate specification differs slightly from the Logical SQL filter.

Review the following Create statement and its Where clause:

create aggregate aggr1 for fact1 at levels(11,12...) where (filter_list) using ....

The following are the acceptable grammar rules:

filter_list ::= filter logical_oper filter_list

| filter

| '(' filter_list ')'

filter ::= logical_column relational_oper constant

| logical_column inclusion_oper '(' inlist ')'

relational_oper ::= '=' | '!=' | '<' | '>' | '<=' | '>=' | 'like'

inlist ::= constant ',' inlist

| constant

logical_oper ::= 'and' | 'or'

inclusion_oper ::= 'in' | 'not in'

Add 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, that is composed of many columns, then the join to the fact table is expensive. A surrogate key is an artificially generated key, usually a number. A surrogate key in the level aggregate table simplifies the 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. A surrogate key ensures that each aggregate table has a unique identifier.

It's possible for sharing a level among multiple fact tables. One fact might use surrogate keys, and another might 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. You can decide later after observing performance if you should create a fact aggregate using a surrogate or primary key.

You could specify using surrogate keys for the entire star which results in simpler syntax, restricts the available user options, and slows the aggregate creation process.

About the Create/Prepare Aggregates Syntax

The syntax for create/prepare aggregates contains the change for Using_Surrogate_Key.

You can specify a surrogate key option 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] ;

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

For a level aggregate in the physical metadata, the Using_Surrogate_Key join option does the following:

The level aggregate table has a new column called levelName_upgradeIDSK, check for collisions. This is the surrogate key column for the dimension aggregate. The levelName is truncated if the total number of characters exceeds 18.
For a level aggregate in the database, the Using_Surrogate_Key join option does the following:

The level aggregate table also has a corresponding column called levelName_upgradeIDSK. You can populate the table using RCOUNT().
For a fact aggregate in the physical metadata, the Using_Surrogate_Key join option does the following:
- The fact aggregate table no longer contains columns from the level's primary keys.
- Instead, a new column that corresponds to the level aggregate's surrogate key is added to the table.
- The type of this column is identical to the level's surrogate key.
- The column has the same name as that in the level aggregate, and checks for collisions.
- The fact table and the level table are joined using this surrogate key only.
For a fact aggregate in the database, the Using_Surrogate_Key join option does the following:

The fact aggregate table has the corresponding surrogate key. The table is populated using new capabilities available through Populate.

Create Aggregates on TimesTen Sources

These topics describe configuration steps and features related to aggregate creation on TimesTen sources.

To create aggregates with compressed tables in TimesTen, you must enable COMPRESSED_COLUMNS in the Features tab of the Database dialog in the Model Administration Tool. See SQL Features Supported by a Data Source.

This section contains the following topics:

See Oracle Exalytics for specific instructions on setting up TimesTen sources.

Enable PL/SQL for TimesTen

To create aggregates on TimesTen sources, you must ensure that PL/SQL is enabled for the instance, and that the PL/SQL first connection attribute PLSQL is set to 1.

You can enable PL/SQL at install time, or run the ttmodinstall utility to enable it post-install, see Oracle TimesTen In-Memory Database Installation Guide.

Enable Performance Enhancement Features for TimesTen

You can disable redo logging and run database checkpoints in the background to enhance performance.

Edit the obis.properties file located at BI_DOMAIN\config\fmwconfig\bienv\obis.
Add the following TimesTen variables:
- ORACLE_BI_TT_DISABLE_REDO_LOGGING=1
  
  The ORACLE_BI_TT_DISABLE_REDO_LOGGING=1 element disables redo-logging, enabling faster creation of aggregates.
  
  Set the ORACLE_BI_TT_DISABLE_REDO_LOGGING=1 element to 0 (zero), to disable the feature.
- ORACLE_BI_TT_BACKGROUND_CHECKPOINT_INTERVAL=10
  
  The ORACLE_BI_TT_BACKGROUND_CHECKPOINT_INTERVAL element changes how often TimesTen flushes its data to disk. If the element is missing, the default is every 10 seconds. If you explicitly set ORACLE_BI_TT_BACKGROUND_CHECKPOINT_INTERVAL=N, for example, to 10, the flush to disk occurs every N seconds, 10 as in this example. If you set ORACLE_BI_TT_BACKGROUND_CHECKPOINT_INTERVAL to 0 (zero) background flushing is disabled. Enabling background flushing speeds up creation of aggregates, by avoiding a large blocking flush at the end of the aggregate creation process.
Save and close the obis.properties file.
Restart the Oracle BI Server.