Skip Headers
Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1)

Part Number E10540-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

13 Creating and Persisting Aggregates for Oracle BI Server Queries

This chapter explains how to set up and use aggregate persistence in Oracle Business Intelligence.

Most data warehouse practitioners create aggregated data tables to dramatically improve the performance of highly summarized queries. These aggregate tables store precomputed results that are aggregated measures (typically summed) over a set of dimensional attributes. Using aggregate tables is a typical technique used 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 aggregate navigation capability of the Oracle BI Server allows 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 Business Intelligence has an aggregate navigation feature to take advantage of those aggregates in source databases (for more information, see Chapter 11). However, it can be time consuming to create and maintain the data aggregation, as well as load database scripts and the corresponding metadata mappings. For that reason, Oracle Business Intelligence provides an aggregate persistence feature that automates the creation and loading of the aggregate tables and their corresponding Oracle Business Intelligence metadata mappings.

This chapter contains the following topics:

About Aggregate Persistence in Oracle Business Intelligence

Use the Aggregate Persistence feature to create aggregates for Oracle BI Server queries. The Aggregate Persistence Wizard lets you automate the creation of the aggregate specification script. When you run this script against a live Oracle BI Server, aggregate tables are created by the aggregate persistence engine and are mapped into the metadata for navigation. When aggregates are persisted, indexes and statistics are created on relational tables for greater performance.

The Aggregate Persistence Wizard creates a SQL script that you can run on a scheduled basis against the Oracle BI Server. In the Aggregate Persistence Wizard, you specify the measures, dimensionality, and other parameters of each star or cube based on your performance design. The script should run after each load of the base-level tables, so that the aggregates are always synchronized with the detail-level data when the load window completes and users begin to run queries.

Aggregate creation runs against the master server in a cluster. It takes some time for the metadata changes to propagate to the slaves. The cluster refresh time is a user-controlled option and results might be incorrect if a query hits a slave server before it is refreshed. It is the administrator's responsibility to set an appropriate cluster refresh interval.

Aggregate persistence requires a dedicated connection pool to create tables or cubes in the target database that will hold the aggregates. Because the Oracle BI repository enables federation, the aggregated target can be on the same database as the detailed source, or in a completely different database. This dedicated connection pool must be created before you run the Aggregate Persistence Wizard, so it can be selected during the appropriate step of the wizard.

The default prefix SA_ is automatically added to dimension (level) aggregates. You can change this default prefix by updating the AGGREGATE_PREFIX parameter in the AGGREGATE_PERSISTENCE section of the NQSConfig.INI file:

AGGREGATE_PREFIX = "prefix_name" ;

The target schema used to store aggregates must be appropriately secured and should not allow public access. The schema should have privileges to connect, create, and drop tables and indexes. By default, only users who belong to the BIAdministrators group can manage aggregates.

Do not use aggregate persistence against tables with active Virtual Private Database (VPD) security filters. There is a possibility that the aggregate information might be persisted without the VPD filter, posing a security risk.

Identifying Query Candidates for Aggregation

When creating aggregates, you must identify which queries would benefit substantially from aggregated data. You will achieve the best results by aggregating to the highest level possible. To identify slow-running queries, perform the following tasks:

Using Oracle BI Summary Advisor to Identify Query Candidates for Aggregation

If you are running Oracle Business Intelligence on the Oracle Exalytics Machine, you can use the Oracle BI Summary Advisor feature to identify which aggregates will increase query performance and to generate a script for creating the recommended aggregates.

Note:

If you are not running Oracle Business Intelligence on the Oracle Exalytics Machine, the Oracle BI Summary Advisor feature is not 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, however, you need to analyze usage tracking statistics to identify which aggregates will increase query performance. As an alternative to manually identifying aggregates, which can be a slow and laborious process, you can use the Summary Advisor feature, which intelligently recommends an optimal list of aggregate tables based on query patterns that will achieve maximum query performance gain while meeting specific resource constraints. Summary Advisor then generates an aggregate creation script that can be run to create the recommended aggregate tables.

There are several parts to the Summary Advisor feature:

  • Statistics collection into a designated statistics database. You must enable the Usage Tracking feature to collect Summary Advisor statistics.

  • Summary Advisor execution through the Summary Advisor Wizard in the Administration Tool to evaluate the collected statistics and recommend aggregates.

The following sections describe these tasks:

Gathering Summary Advisor Statistics

Before Summary Advisor can generate recommendations, you must obtain a representative sample of usage statistics for Summary Advisor to use. Use one of the following approaches to accomplish this task:

  • Enable Usage Tracking and Summary Advisor Logging on a production system, and let users run queries against BI Server for several days. The Summary Advisor Statistics Table will be populated with usage statistics. See "Turning On Usage Tracking" and "Turning On Summary Advisor Logging" for more information.

    Note that enabling Usage Tracking and Summary Advisor Logging will have a minor system performance impact on production systems.

  • 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 have 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 "Using nqcmd to Test and Refine the Repository" for more information about using nqcmd. The Summary Advisor Statistics Table will be populated with usage statistics.

  • If your system had Usage Tracking enabled, but not Summary Advisor Logging enabled, you can run the Summary Advisor Statistics Generation tool to transfer Usage Tracking data to the Summary Advisor Statistics Table. This scenario applies to pre-11.1.1.6.0 users upgrading to the latest version of Oracle BI Enterprise Edition with the Oracle BI Summary Advisor feature. These users may have Usage Tracking data, but not Summary Advisor statistics.

Generating and Using Summary Advisor Recommendations

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

To accomplish this, run the Oracle BI Summary Advisor Wizard in the Administration Tool to generate an aggregate specification, and then use the aggregate specification to create aggregates using nqcmd. See "Using the Oracle BI Summary Advisor Wizard" and "Running the Aggregate Specification Against the Oracle BI Server" for more information.

Currently, Oracle BI Summary Advisor only supports aggregate creation on Oracle TimesTen In-Memory Database. Refer to "System Requirements and Certification" for information about supported versions.

You can also save your Summary Advisor options to a file, so that you can re-run the Oracle BI Summary Advisor Wizard later without having to enter the same options again.

Setting 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 "Create Database Schemas Using the Repository Creation Utility (RCU)" in Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence for more information.

Typically, you use the database you installed for use with Oracle Business Intelligence 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.

To set up the statistics database:

  1. 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 Business Intelligence for Summary Advisor statistics, because this database has the RCU-created tables already.

  2. Open the Administration Tool and import the database into the Physical layer. See "Importing Metadata from Relational Data Sources" for more information.

  3. Save and close the repository.

  4. Use Fusion Middleware Control to upload the repository and make it available for queries. See "Making the Repository Available for Queries" for more information.

Turning On Usage Tracking

You must also enable usage tracking before collecting Summary Advisor statistics. See "Managing Usage Tracking" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Turning On Summary Advisor Logging

When you are ready to collect statistics, you can enable Summary Advisor logging. For new (non-upgraded) installations, the Summary Advisor parameters are centrally managed. You use the System MBean Browser in Fusion Middleware Control to set the Summary Advisor parameters.

To enable Summary Advisor logging using the System MBean Browser:

  1. In Fusion Middleware Control, in the Navigator window, expand the WebLogic Domain folder and the bifoundation_domain node.

  2. Right-click the AdminServer node and select System MBean Browser.

  3. Expand Application Defined MBeans, then expand oracle.biee.admin, then expand Domain: bifoundation_domain.

  4. Lock the domain, as follows:

    1. Expand BIDomain and select the BIDomain MBean where group=Service.

    2. Display the Operations tab.

    3. Click the lock link.

  5. Expand BIDomain.BIInstance.ServerConfiguration, then select the BIDomain.BIInstance.ServerConfiguration MBean.

  6. Ensure that the UsageTrackingCentrallyManaged attribute is set to true. When UsageTrackingCentrallyManaged is set to false, the following parameters are managed using the NQSConfig.INI file on each Oracle BI Server computer rather than the SystemMBean Browser:

    • SummaryAdvisorTableName

    • SummaryStatisticsLogging

    • UsageTrackingConnectionPool

    • UsageTrackingDirectInsert

    • UsageTrackingEnabled

    • UsageTrackingPhysicalTableName

  7. Set the SummaryAdvisorTableName attribute 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:

    "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 are using for usage tracking.

  8. Set SummaryStatisticsLogging to one of the following options:

    • Enter YES to enable Summary Advisor logging.

    • Enter LOG_OUTER_JOINT_QUERIES_ONLY to enable 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.

  9. After applying your changes, release the lock on the domain, as follows:

    1. Return to the BIDomain MBean where group=Service under oracle.biee.admin, Domain:bifoundation_domain, BIDomain.

    2. Display the Operations tab.

    3. Click one of the commit operations.

  10. Go to the Oracle Business Intelligence Overview page and click Restart.

For upgrading customers, the Summary Advisor parameters are not centrally managed by default. You can set UsageTrackingCentrallyManaged to true as described in the previous procedure, and use the System MBean Browser to update the parameters, or 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 are using for usage tracking.

  3. Save and close the file.

  4. Restart the Oracle BI Server.

  5. If you have multiple Oracle BI Server instances, then repeat these steps in each NQSConfig.INI file for all Oracle BI Server instances.

Using the Oracle BI Summary Advisor Wizard

After Summary Advisor statistics have been generated, you can run the Oracle BI Summary Advisor Wizard in the Administration Tool to generate an aggregate specification script that you can then run to create the aggregates. The Summary Advisor Wizard can only be run in online mode.

Before you run the Summary Advisor Wizard, you must map the target database where you plan to create the aggregates into the Physical layer. To do this, manually create the necessary database, connection pool, and physical schema objects.

To run the Oracle BI Summary Advisor Wizard:

  1. Open your repository in the Administration Tool in online mode.

  2. It is recommended that you run Model Check Manager to ensure that your repository does not contain modeling problems that will affect Oracle BI Summary Advisor performance and results. See "Using Model Check Manager to Check for Modeling Problems" for more information.

    Note:

    Because Model Check Manager runs queries against back-end data sources for some checks, it is recommended to run it during off-peak periods. In addition, it can take a long time to run Model Check Manager for large repositories. Use Filtered by Statistics, or run it only for selected objects, to improve performance.

  3. Select Tools, then select Utilities.

  4. Scroll and select Oracle BI Summary Advisor, then click Execute.

    The Oracle BI Summary Advisor Wizard is only available if you are running Oracle Business Intelligence on the Oracle Exalytics Machine.

  5. On the first page of the Oracle BI Summary Advisor Wizard, Filter Logs - Logical Fact Tables, you can optionally select specific logical fact tables for which to generate Summary Advisor recommendations. By default, all logical fact tables are included.

    If you have 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. See step 11 for information about saving your criteria to a file.

    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 cannot proceed. Click Generate Statistics to generate Summary Advisor logging statistics based on your existing usage tracking data. See "Using the Oracle BI Summary Advisor Statistics Generator to Generate Statistics" for more information.

    Click Next when you are ready to move to the next screen.

  6. On the Filter Logs - Time Window screen, you can optionally filter the Summary Advisor logging statistics based on time period. To do this, enter a Start Date and End Date for statistics that you want to include in the Summary Advisor execution. Click Update to refresh the view after entering a time period.

    Click Next when you are ready to move to the next screen.

  7. On the Filter Logs - Execution Time Threshold screen, you can optionally filter by setting a minimum query time threshold for each logical table source. For example, if you specify 5 seconds for Minimum Cumulative Time, only logical table sources with a cumulative total query time of five seconds or greater will be included in Summary Advisor recommendations.

    Click Next when you are ready to move to the next screen.

  8. On the Targets screen, select the target container and associated connection pool for aggregates. This is the location where the aggregate tables will be created. You can optionally 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. Click Clear All to clear the existing list of targets, or click the Delete button next to an individual target to remove it.

    Only database schemas for Oracle TimesTen In-Memory Database are currently supported for use with Oracle BI Summary Advisor.

    Click Next when you are ready to move to the next screen.

  9. On the Select File Location screen, click Browse to select the location where the aggregate specification (a SQL script) will be stored.

    Click Next.

  10. On the Stopping Criteria screen, you can optionally specify run constraints for this set of recommendations, as follows:

    • You can specify the maximum time that Summary Advisor will run 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%

    Click Next when you are ready to move to the next screen.

  11. On the Miscellaneous screen, you can optionally 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.

    Click Next.

  12. On the Run screen, click Run to generate recommendations using the Summary Advisor process.

    Click Stop at any point to stop the process. When Summary Advisor stops or runs to completion, it will display the aggregate recommendations it has found so far.

    When the process completes, click Next.

  13. On the Filter Aggregates screen, review the current set of aggregate recommendations. You can optionally exclude certain aggregates from the creation process by deselecting the Include option for that row.

    Click Next.

  14. On the Finish Script screen, review the script that will be generated. If you are satisfied, click Finish to save the script.

See "Running the Aggregate Specification Against the Oracle BI Server" for information about using the SQL file to create aggregate tables.

Using the Oracle BI Summary Advisor Statistics Generator to Generate Statistics

You can use the Oracle BI Summary Advisor Statistics Generator to generate Summary Advisor logging statistics based on your existing usage tracking data.

The user who runs the Statistics Generator must have the oracle.bi.server.impersonateUser permission. See "Creating Application Policies Using Fusion Middleware Control" in Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition for more information about managing permissions.

To generate Oracle BI Summary Advisor statistics:

  1. Launch the Statistics Generator by clicking Generate Statistics on the first page of the Oracle BI Summary Advisor Wizard. The Oracle BI Summary Advisor Statistics Generator screen is displayed.

    Figure 13-1 shows the Statistics Generator screen.

    Figure 13-1 Oracle BI Summary Advisor Statistics Generator

    Description of Figure 13-1 follows
    Description of "Figure 13-1 Oracle BI Summary Advisor Statistics Generator"

  2. For Container and Table, select the appropriate options to form the fully-qualified name of the physical table in the Oracle BI repository that contains usage tracking data (the input table). Queries from this table are used to generate summary advisor statistics.

    It is a best practice to specify a different input usage tracking table than the one specified in the UsageTrackingPhysicalTableName MBean or the PHYSICAL_TABLE_NAME parameter in NQSConfig.INI. Specifying a different table prevents the input usage tracking table from getting affected by the new data generated by running the Statistics Generator.

    Also note that in the input usage tracking table, the QUERY_BLOB physical column object must have a length that fits the underlying data. This is required to prevent truncation when the Statistics Generator reads logical SQL strings from it. If the QUERY_BLOB column does not exist, the Statistics Generator reads logical SQL strings from the QUERY_TEXT column.

  3. For Start Date and End Date, optionally enter a start and end date to filter the usage tracking data. The date range is inclusive.

  4. For Shortest Query Time, optionally enter a number in seconds to filter on query time. If you specify this filter, only usage tracking data for queries that took as long as or longer than the specified value is used.

  5. For Logical Repository Name, optionally enter the name of one or more Oracle BI repositories to filter on repository name. If you specify this filter, only usage tracking data for queries that were requested against these repositories is used.

    Specify the values in comma-separated format: RPD_name_1[,RPD_name_2,...]

  6. Optionally select Include queries that exceeded row limits to include such queries. If this option is not selected, only usage tracking data for queries that completed successfully is used.

  7. For Execution Mode, optionally select Execute queries to generate more accurate timing data (slow) to enable execution of physical queries during summary advisor statistics generation.

  8. Click Generate Statistics to generate Oracle BI Summary Advisor statistics from the given usage tracking data.

Using the Aggregate Persistence Wizard to Generate the Aggregate Specification

You can use the Aggregate Persistence Wizard to create the SQL file that will be used to create and load aggregate tables and map them into the metadata. The resulting SQL file must be executed against a running Oracle BI Server.

It is strongly recommended that you use the Aggregate Persistence Wizard because it automatically enforces many of the constraints necessary when generating the aggregate specification. However, you can manually write the aggregate Logical SQL as an alternative to using the wizard. Make sure to follow the guidelines described in "Writing the Create Aggregates Specification Manually" if you choose to write your own aggregates specification.

Before you run the Aggregate Persistence Wizard, you must map the target database where you plan to create the aggregates into the Physical layer. To do this, manually create the necessary database, connection pool, and physical schema objects.

Note:

If you are running Oracle Business Intelligence on the Oracle Exalytics Machine, you can use the Summary Advisor feature instead of the Aggregate Persistence Wizard to identify which aggregates will increase query performance and to generate a script for creating the recommended aggregates. See "Using Oracle BI Summary Advisor to Identify Query Candidates for Aggregation" for more information.

To use the Aggregate Persistence Wizard:

  1. It is recommended that you run Model Check Manager to ensure that your repository does not contain modeling problems that will affect aggregate creation and performance. See "Using Model Check Manager to Check for Modeling Problems" for more information.

    Note:

    Because Model Check Manager runs queries against back-end data sources for some checks, it is recommended to run it during off-peak periods. In addition, it can take a long time to run Model Check Manager for large repositories. Use Filtered by Statistics (where available), or run it only for selected objects, to improve performance.

  2. Open your repository in the Administration Tool, if it is not open already.

    You must run Model Check Manager in online mode. However, you can run the Aggregate Persistence Wizard in either online or offline mode.

  3. Select Tools > Utilities > Aggregate Persistence, and then click Execute.

  4. On the Select File Location screen, specify the complete path and file name of the aggregate creation script. You can specify a new or an existing file name.

    Typically, when you run the SQL script against the Oracle BI Server, it creates DDL and runs it against the target database schema to create the aggregate tables, then loads them from the source, and finally creates the Oracle BI Server metadata so the aggregate navigation feature can use the new tables.

    Alternatively, you can select Generate target DDL in a separate file if you want the DDL to be stored in a separate file from the Oracle BI Server SQL script. Selecting this option gives you the flexibility to alter the auto-generated DDL and run it independently of the Oracle BI Server. For example, you may want to alter the storage parameter or index settings.

    When you select Generate target DDL in a separate file, two SQL scripts are generated in the directory you specify in the Location field:

    • The create aggregates script (script_name)

    • The prepare aggregates script (script_name_DDL)

    After selecting Generate target DDL in a separate file and completing the wizard steps, you typically do the following:

    1. Run the prepare aggregates script against the server. This action creates a DDL file at the following location:

      ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obisn\
      aggr
      
    2. Run the generated DDL file against the target database to create the table.

    3. Run the create aggregates script to populate the table.

    Click Next after you have finished specifying options on the Select File Location screen.

  5. In the Select Business Measures screen, select the measures on which you want to aggregate. To do this, select a business model in the upper pane, then select a single fact table or a set of measures in the lower pane. You cannot select measures that span multiple fact tables. Use Ctrl-click to select multiple measures, or use Shift-click to select a range of consecutive measures.

    Note that the View Script button is not available during the creation of the first aggregate table block.

    Figure 13-2 shows the Select Business Measures screen.

    Figure 13-2 Aggregate Persistence Wizard: Select Business Measures Screen

    Description of Figure 13-2 follows
    Description of "Figure 13-2 Aggregate Persistence Wizard: Select Business Measures Screen"

    Click Next after you have selected the appropriate measures.

  6. In the Select Levels screen, specify the level of aggregation by selecting a logical level for one or more dimensions. You can specify a surrogate key to be used for the fact-dimension join.

    The default join option between the aggregated fact and dimension tables is the primary key defined in the logical level you selected. If the primary key of the level is large and complex, the join to the fact table is expensive, so using a surrogate key is recommended in this case. A surrogate key is an artificially generated key, usually a number. For example, a surrogate key in the level aggregate table would simplify this join, removing unnecessary (level primary key) columns from the fact table and resulting in a leaner fact table.

    Using a surrogate key only changes the query response time, not the logical results of the queries. However, generating the surrogate keys can have the side effect of increasing the aggregate table load time. Therefore, the recommended setting is as follows:

    • If the primary key for the logical level you have selected is already a single, numeric column, you typically should not select the Use Surrogate Key option since it may add to load processing time without producing a performance benefit.

    • If the primary key for the logical level you have selected is a text string, or consists of multiple logical columns, you typically should use a surrogate key to improve the performance of the queries that join to that aggregate dimension. However, keep in mind that generating the surrogate key can increase the load time for that aggregate dimension table.

    See "Adding Surrogate Keys to Dimension Aggregate Tables" for additional information about surrogate keys.

    Figure 13-3 shows the Select Levels screen.

    Figure 13-3 Aggregate Persistence Wizard: Select Levels Screen

    Description of Figure 13-3 follows
    Description of "Figure 13-3 Aggregate Persistence Wizard: Select Levels Screen"

    Click Next after you have selected the appropriate level of aggregation.

  7. In the Select Connection Pool screen, select the appropriate items to specify a location for the aggregate table.

    A default aggregate table name is provided, and a prefix is added to the table name. The default prefix for the generated fact table is ag. For tables created for dimension (level) aggregates, the default prefix is SA_ and can be changed by updating the AGGREGATE_PREFIX property in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about changing configuration settings.

    Figure 13-4 shows the Select Connection Pool screen.

    Figure 13-4 Aggregate Persistence Wizard: Select Connection Pool Screen

    Description of Figure 13-4 follows
    Description of "Figure 13-4 Aggregate Persistence Wizard: Select Connection Pool Screen"

    Click Next after you have provided connection pool information.

  8. In the Finish screen, the View Script button becomes available for use, and the Logical SQL script appears for your review. Choose whether to define another aggregate (default) or end the wizard, and then click Next.

  9. In the Finish Script screen, the complete path and file name appears. Click Finish.

See "Running the Aggregate Specification Against the Oracle BI Server" for information about using the SQL file to create aggregate tables.

Using Model Check Manager to Check for Modeling Problems

This section describes 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 Model Check Manager to check your repository metadata for issues that might affect the success of Oracle BI Summary Advisor or the aggregate persistence engine, such as identifying level primary keys that are not unique.

Although the user experience of running Model Check Manager is very similar to running the Consistency Check Manager, there are three key differences between the two tools:

  • Unlike the Consistency Check Manager, Model Check Manager requires access to the summary statistics table (when using Filtered by Statistics) and back-end data sources for some checks. Because some of the back-end queries can be expensive, it is recommended to run Model Check Manager during off-peak periods.

  • Model Check Manager can only be run in online mode.

  • Model Check Manager does not make any changes to repository metadata - it only flags possible problems.

Similar to the Consistency Check Manager, Model Check Manager returns both error and warning messages. You must fix errors identified by Model Check Manager, or Oracle BI Summary Advisor recommendations might be incorrect, and the aggregate persistence engine might fail to create aggregates. It is recommended that you fix warnings, but not required. Issues identified by warnings result in suboptimal recommendations from Oracle BI Summary Advisor, or suboptimal performance from the aggregate persistence engine.

Running Model Check Manager

For Oracle BI Summary Advisor, run Model Check Manager after you have gathered Summary Advisor statistics, but before you run the Oracle BI Summary Advisor Wizard. For aggregate persistence, run Model Check Manager right before you run the Aggregate Persistence Wizard. Alternatively, you can run Model Check Manager to identify problems for selected objects after initial aggregate creation failure.

To run Model Check Manager globally, select the File menu, then select Check Models. Then, choose one of the following options from the submenu:

  • Complete: Checks all objects.

  • Filtered by Statistics: Checks only fact table objects and associated dimensions 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 is not available, a warning appears explaining that Model Check Manager cannot filter by statistics.

To run Model Check Manager for selected objects, 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. Note that the Filtered by Statistics menu option is only available for fact table objects and business model objects on the Oracle Exalytics Machine.

When using Model Check Manager with large repositories, it is recommended that you use Filtered by Statistics (where available), or run it only for selected objects, to improve performance.

Using the Model Check Manager Dialog

When you run Model Check Manager for one or more objects, the Model Check Manager dialog appears, as shown in Figure 13-5.

Figure 13-5 Model Check Manager

Surrounding text describes Figure 13-5 .

To edit the repository to correct problems, double-click a row to open the properties dialog for that object, or select a row and click Go To. Then, correct the problem and click OK.

You can also perform the following actions in the Model Check Manager dialog:

  • Use the options in the Display box to show only errors, only warnings, or both.

  • Click the column headings to sort the rows of messages.

  • Click Save As to save the Model Check Manager results in text, CSV, or XML format.

  • Select one or more rows and click Copy to copy the messages so that you can paste them in another file such as a spreadsheet. Clicking Copy without any rows selected copies all messages.

  • To check the model again, click Check All Objects to perform a global check. Or, click the Refresh button in the top right corner to check only the objects that had errors in the last check.

  • Click Show Qualified Name to display the qualified names of objects in the Object column.

  • View the status bar to see what objects were checked and to see a summary of all the rows displayed.

Writing the Create Aggregates Specification Manually

If you choose not to use the Aggregate Persistence Wizard to create the script file, you can write the file manually. It is recommended that you use the Aggregate Persistence Wizard.

If you do not 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 will create 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. Note that 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?

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 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, 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 do not 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 be discarded. Also, 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 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 is 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.

    Note that the aggregate specification is discarded if any dimension is not joined to a fact.

How to Write 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 that you place all aggregate specifications under a single Create Aggregates statement.

Follow these guidelines when writing the aggregate specification:

  • Begin the script file with a Delete statement. It is 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 optionally include a comma-separated list of physical tables to delete. The tables you include must be system-generated (by a previous run of the aggregate creation script). Any dimension tables joined to listed fact tables are also deleted.

    Note that if a dimension table is joined to more than one fact table, it will not be deleted unless the other joined table is also listed.

    In addition to fact tables, you can also use the Delete statement to delete orphan dimension tables (that is, dimension tables that are not joined to any other fact table). Orphan dimension tables sometimes occur when aggregate creation fails.

  • 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] ;
    
  • To specify multiple aggregates in a single Create Aggregates statement, follow these guidelines:

    • 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 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 are removed.

For information about creating aggregates with surrogate keys, see the following section.

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), 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 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 might be cases in which a level is shared 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 (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.

About the Create/Prepare Aggregates Syntax

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] ;

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.

When you use the Using_Surrogate_Key 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 has a new column called levelName_upgradeIDSK (check for collisions). This is the surrogate key column for the dimension aggregate. Note that levelName is truncated if the total number of characters exceeds 18.

    • In the database, the following occurs:

      • The level aggregate table also has a corresponding column called levelName_upgradeIDSK. Again, levelName is truncated if the total number of characters exceeds 18.

      • It can be populated using RCOUNT().

  • For a fact aggregate, the following occurs:

    • In the physical metadata, the following occurs:

      • 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 (check for collisions).

      • The fact table and the level table are joined using this surrogate key only.

    • In the database, the following occurs:

      • The fact aggregate table also has the corresponding surrogate key.

      • It is populated using new capabilities to be available through Populate.

Running the Aggregate Specification Against the Oracle BI Server

This section describes how to run the aggregate specification script against the Oracle BI Server. Before you run the script, you must create an ODBC DSN for the Oracle BI Server and ensure that the correct log level is set. Then, you can execute the script using nqcmd as a user who is a member of the BI Administrators group.

To run the aggregate specification script:

  1. To run the aggregate creation script, you must connect directly to a DSN for a running Oracle BI Server and not to a clustered DSN. Note the following:

    • Single-node cluster: Because the DSN created upon install for each Oracle BI Server is clustered by default, even for a single-node deployment, you must manually create a DSN for the Oracle BI Server to run the aggregate specification against.

    • Multi-node cluster: You must run the aggregate specification directly against the master Oracle BI Server. Create a non-clustered DSN for the master Oracle BI Server to run the aggregate specification against. You can use the Cluster Manager in the Administration Tool in online mode to determine which Oracle BI Server is the master.

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

  2. It is recommended that you set an appropriate logging level before executing the script. Trace logs are logged to nqquery.log 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 logged to nqquery.log if the logging level is at least 1, and to nqserver.log regardless of the logging level.

    First, set the logging level using one of the following methods:

    Then, you must also 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... ;
    

    Note that you must use a colon as a delimiter when setting request variables using nqcmd.

  3. As a user in the BI Administrators group, use nqcmd to connect to the non-clustered DSN for the Oracle BI Server that you created in step 1. Then, run the aggregate specification script.

    See "Using nqcmd to Test and Refine the Repository" for more information about running nqcmd.

    Note:

    In a clustered environment, the aggregate specification script performs a rolling restart of the slave Oracle BI Servers in the background. It is a best practice to avoid making other configuration changes in Fusion Middleware Control or the configuration files while running the aggregate persistence script. Because only the slave servers are restarted in the rolling restart, a situation might result where the master Oracle BI Server has a different set of configuration settings loaded than the slave Oracle BI Servers. If this occurs, restart the master Oracle BI Server.

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

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

Life Cycle Use Cases for Aggregate Persistence

Table 13-1 summarizes the user tasks to persist aggregates for different life cycle use cases.

Note that these use cases focus on operations against single or multiple aggregate persistence targets, and do not describe operations for single or multiple-node deployments. For the most part, user tasks are the same for both single-node deployments and multiple node deployments. The only difference is that in a clustered deployment, you must connect to the master Oracle BI Server, and a rolling restart of the slave servers is performed in the background. See "Running the Aggregate Specification Against the Oracle BI Server" for more information.

Table 13-1 Life Cycle Use Cases for Aggregate Persistence

Number Use Case Description

1

Creating aggregates for a single aggregate persistence target

To only create aggregates, modify the aggregate creation script to remove the delete aggregates statement at the beginning. Then, use nqcmd to run the script.

2

Deleting aggregates for a single aggregate persistence target

To delete aggregates, use nqcmd to run the delete aggregates statement directly, as follows:

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

For example:

Delete aggregates;

or

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

3

Refreshing aggregates for a single aggregate persistence target

Use nqcmd to run the aggregate creation script, which contains statements to first delete, then create the aggregates.

Alternatively, you can manually delete the aggregates as described in use case 2, then create aggregates as shown in use case 1. This manual method is useful for situations where you want to delete all aggregates, but the aggregate creation script only specifies certain aggregates to be deleted.

4

Creating aggregates for multiple redundant aggregate persistence targets

To create aggregate clones on multiple targets, modify the aggregate creation script to copy the create aggregates statements as many times as you have targets.

For example, say you have a script containing the following create aggregates statement:

set variable LOGLEVEL=7 : create aggregates
"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt1"."cp"
in "tgt1".."double1";

You would then copy the block, paste it below the first block, and modify the connection pool and schema information for your second target. For example:

set variable LOGLEVEL=7 : create aggregates
"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt2"."cp"
in "tgt2".."double2";

After you have copied and modified the block for all your targets, save the script. Then, use nqcmd to run the aggregate creation script.

5

Deleting aggregates for multiple aggregate persistence targets

To delete aggregates on multiple targets, use nqcmd to run the delete aggregates statement directly for the affected fact tables. For example:

set variable LOGLEVEL=7 : delete aggregates
"tgt1".."double1"."myfactaggr";
set variable LOGLEVEL=7 : delete aggregates
"tgt2".."double2"."myfactaggr";

6

Refreshing aggregates for multiple redundant aggregate persistence targets

See "Using Double Buffering to Refresh Highly Available Aggregates" for information about this use case.

7

Refreshing aggregates for multiple partitioned aggregate persistence targets

In some cases, you might have different aggregates partitioned across multiple targets. This approach maximizes memory use, but does not provide highly available aggregates. To refresh partitioned aggregates, use one of the following methods as appropriate for your deployment:

  • Run the Aggregate Persistence Wizard multiple times against the different targets to generate a set of aggregate creation scripts, then run the scripts.

  • If you are running Oracle Business Intelligence on the Oracle Exalytics Machine, run Oracle BI Summary Advisor and specify multiple targets in the Targets screen. Then, run the aggregate creation script.


Using Double Buffering to Refresh Highly Available Aggregates

When you have aggregate clones across multiple aggregate persistence targets, you can use double buffering to avoid downtime when refreshing the aggregates. To do this, you manually call the aggregate create and delete SQL statements in a way that controls the refresh.

You start by deleting aggregates on the first target. Next, you create the aggregates on the first target, specifying the targets where aggregates have not yet been deleted as inactive schemas, so that the old data is not used in the refresh. Then, you repeat this process for each target. Note that you do not need to specify inactive schemas when refreshing the last target because by that point, the data in the other schemas has already been refreshed.

To specify inactive schemas, set the request variable INACTIVE_SCHEMAS before the create aggregates statement. For example:

set variable INACTIVE_SCHEMAS='"tgt2".."double2"' :

Only specify schemas that have not yet been refreshed as inactive schemas. Do not specify a schema that has already been refreshed or that you have just deleted.

To specify multiple inactive schemas, use a comma-separated list. Make sure there are no spaces in the list.

Example 13-1 illustrates how to use double buffering to refresh aggregates on two targets.

Example 13-1 Refreshing Aggregate Clones on Two Targets

Assume that you have the following aggregate clones on targets tgt1 and tgt2:

"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt1"."cp"
in "tgt1".."double1",

"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt2"."cp"
in "tgt2".."double2";

To make sure that at least one aggregate clone is available during the refresh, follow these steps:

  1. Delete the aggregate clone for the first target:

    set variable LOGLEVEL=7 : delete aggregates "tgt1".."double1"."myfactaggr";
    
  2. Create the aggregate for the first target, making sure to specify the second target as an inactive schema so that the data is not used in the refresh:

    set variable LOGLEVEL=7, INACTIVE_SCHEMAS='"tgt2".."double2"' : create aggregates
    "myfactaggr"
    for "FACT_1"("MEASURE_1")
    at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
    using connection pool "tgt1"."cp"
    in "tgt1".."double1";
    
  3. Delete the aggregate clone for the second target:

    set variable LOGLEVEL=7 : delete aggregates "tgt2".."double2"."myfactaggr";
    
  4. Create the aggregate for the second target. Because the first target has already been refreshed, do not specify any inactive schemas:

    set variable LOGLEVEL=7 : create aggregates
    "myfactaggr"
    for "FACT_1"("MEASURE_1")
    at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
    using connection pool "tgt2"."cp"
    in "tgt2".."double2"; 
    

Note:

When you have aggregate clones across multiple aggregate persistence targets, the additional instances are hot-spares that take over the query load while the initial instance is being refreshed. The aggregate clones are not used for load balancing the incoming queries.

Troubleshooting Aggregate Persistence

This section describes how to troubleshoot the Summary Advisor and aggregate persistence process. It includes the following topics:

About Aggregate Persistence Errors

The following is a list of some reasons errors can occur:

  • Network failure

  • No disk space on the database

  • Bad aggregate request

If there is an error in the creation of any aggregate, then the entire aggregate request is terminated and subsequent aggregates are not created. Aggregates that are already created and checked in remain checked in. If there are errors, you must remove them at the time of the error or at the next ETL run in one of the following ways:

  • Manually remove the aggregates from the metadata and the database. To identify the aggregate metadata, you can query the repository using the Is System Generated filter for physical tables and logical table sources. See "Querying the Repository" for more information.

  • Automatically remove all the aggregates using the Delete Aggregates specification.

Avoiding Aggregate Persistence Errors

Run Model Check Manager to ensure that your repository does not contain modeling problems that will affect Oracle BI Summary Advisor and aggregate persistence performance and results. See "Using Model Check Manager to Check for Modeling Problems" for more information.

Tips for Creating Aggregates on TimesTen Sources

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 Reference for more information.

Note also that when the Oracle BI Server builds aggregates on a TimesTen source, if a cancel is issued during the insert statement to TimesTen, the SQLCancel call has no effect. The Oracle BI Server returns to the callers when the current insert statement is finished.

See also Oracle Fusion Middleware Installation and Administration Guide for Oracle Exalytics In-Memory Machine for specific instructions on setting up TimesTen sources on the Oracle Exalytics Machine.