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 Business Intelligence takes advantage of the aggregates in source databases. See Managing Logical Table Sources (Mappings). The Oracle Business Intelligence aggregate persistence automates the creation and loading of the aggregate tables and their corresponding Oracle Business Intelligence 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 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 you could get incorrect results if a query hits a dependent child 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 holds the aggregates. Because the Oracle BI Repository enables federation, the aggregated target can use the same database as the detailed source, or in a completely different database. You must create the dedicated connection pool before you run the Aggregate Persistence Wizard, so the correct connection pool is 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" ;
You must appropriately secured and restrict access to the target schema used to store aggregates. The schema should have privileges to connect, create, and drop tables and indexes. By default, only users who have administrator privileges 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 could persist without the VPD filter, posing a security risk.
Oracle Business Intelligence automatically creates more usable aggregates and creates aggregates without the need to fix data set errors or modeling problems.
Surrogate Keys
Aggregate persistence can create surrogate keys for joining dimensions to fact aggregate tables.
In most cases the source and the target databases are not the same instance.
The Oracle BI Server uses the hash join method to improve surrogate key creation. Where possible, a new request variable is automatically added to the fact aggregate population query and when this request variable is set, the query engine builds hash joins for the dimension tables in parallel before joining to the fact table.
The Oracle BI Summary wizard displays the Use surrogate keys option to suggests when you should use surrogate keys. When this option is selected, the using_surrogate_key clause is added to all levels in the aggregate specification.
Auto Correction (Hardening) of Level Keys
Aggregate persistence auto-corrects or hardens level keys that are not unique.
The Oracle BI Summary Advisor recommends aggregates with level keys that are unique as defined, or with level keys that are auto-corrected (hardened) to make unique keys. Modifications to underlying data might impact such aggregates.
To improve performance, Oracle suggests creating aggregates using surrogate key rather than natural keys. Auto-correction, or hardening, is not as effective when natural keys are used, especially in the prepare-create mode of operation.
Unbalanced (Ragged) and Skip-Level Hierarchies
Aggregate persistence creates aggregates for logical dimensions with unbalanced or skip-level hierarchies. You can create aggregates with or without using surrogate keys. The Oracle BI Summary Advisor recommends aggregates that contain logical dimensions with unbalanced and skip-level hierarchies.
Chronological Keys
The Oracle BI Server requires chronological keys to support time series functions such as AGO, TODATE, and PERIODROLLING.
Time series functions operate correctly when only the lowest key in the logical dimension is chronological.
Aggregate persistence generates chronological keys with the CK_ prefix for time levels without chronological keys. A new column is added to the physical dimension aggregate table to store the chronological key value, and a new logical column is added to the logical table of the time dimension. The column is mapped to the new column added to the physical dimension aggregate table.
The delete aggregates statement automatically removes all metadata created to support generated chronological keys.
Count Distinct Measures
The Oracle BI Server uses aggregates with count distinct measures to serve queries for these measures at higher grains.
The Aggregate Persistence wizard includes the Persist Count Distinct Measures as raw values option, when selected appends as_raw_values to all the valid count distinct measures specified. When the Persist Count Distinct Measures as raw values option is selected, aggregate persistence sets an aggregation expression override on the corresponding logical column for the system-generated aggregate logical table source. The Oracle BI Summary Advisor recommends both methods of persistence for count distinct measures.
Occurrences such as a network failure, no disk space on the database, or a bad aggregate request result in aggregate persistence errors.
When a series of aggregates are being created, and the creation of one aggregate fails, the aggregate persistence engine skips creation of the failed aggregate and its dependencies and proceeds to the next aggregate in the list. Check the log files to identify failed aggregates.
If there are errors, you must remove the failed aggregates 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.
Automatically remove the failed aggregates using the Delete Aggregates specification. In particular, use this technique to remove any orphan aggregate dimensions, those not joined to any other fact table.
Run the Model Check Manager to ensure that your repository does not contain modeling problems that can affect Oracle BI Summary Advisor and aggregate persistence performance and results. See Using Model Check Manager to Check for Modeling Problems.
When creating aggregates, you must identify which queries would benefit substantially from aggregated data.
You can achieve the best results by aggregating to the highest level possible.
To identify slow-running queries, perform the following tasks:
Enable usage tracking in the Oracle BI Server. Usage tracking statistics can be used in a variety of ways, such as database optimization, aggregation strategies, and billing users or departments based on the resources they consume. The Oracle BI Server tracks usage at the detailed query level. When you enable usage tracking, statistics for every query are written to a usage tracking log file or inserted into a database table.
Note:
It is strongly recommended that you use the direct insertion into a database method for usage tracking. See Managing Usage Tracking for in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition information about usage tracking.
Analyze the query run times and identify the slowest running queries as candidates for aggregation. The run time for creating aggregates is dependent on the type of aggregates selected by the user. Creating aggregates from large fact tables is slower than from smaller tables. You should carefully select the aggregates to create.
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 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:
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:
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 Turning On Usage Tracking and Turning 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 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. The Summary Advisor Statistics Table is populated with usage statistics.
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 Administration Tool to generate an aggregate specification, and then use the aggregate specification to create aggregates using nqcmd. See Generating an Aggregate Specification Script and Running the Aggregate Specification Against the Oracle BI Server.
Oracle BI Summary Advisor supports aggregate creation on Oracle TimesTen In-Memory Database, Oracle BI EE, or when using Oracle Database In-Memory on Oracle Exalytics. Refer to System Requirements and Certification.
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.
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.
Note:
The Summary Advisor does not include measures that are not 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 does not include measures that are invalid for aggregate persistence in its recommended aggregates.
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 Business Intelligence.
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.
Use the Upload Repository Command to upload the repository and make it available for queries. See Making the Repository Available for Queries.
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 |
|
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. |
You must enable usage tracking before collecting Summary Advisor statistics.
See Managing Usage Tracking in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
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. For upgrading customers, the Summary Advisor parameters are not 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:
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 are using for usage tracking.
Save and close the file.
Restart the Oracle BI Server.
If you have 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
Open bi-config.xml
UNIX: $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.
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 Using 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 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.
The Oracle BI Summary Advisor Wizard is available if you are running Oracle BI EE or when using Oracle Database In-Memory on Oracle Exalytics. You can execute 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 cannot 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, Microsoft SQL Server, and IBM DB2.
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 do not 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 execute. 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 is not issued by Summary Advisor if an entry for that particular grain already exists in the Summary Advisor cache files, regardless of whether it is 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 Oracle BI Administration Tool computer:
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 do not select this option, all measures in a logical fact table are included in the recommendation, including measures that were not used in the workload analyzed by Summary Advisor.
Oracle recommends running the Model Check Manager to ensure that your repository does not contain modeling problems that could affect Oracle BI Summary Advisor performance and results. See Using 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.
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%
You can use the Oracle BI Server utility nqaggradvisor to run the Summary Advisor from the command line instead of using the Oracle BI 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 are running Oracle Business Intelligence 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 executing 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 is not 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 is not satisfied. The default value is 1.
minQueryTime is the minimum query time threshold in seconds for each logical table source before it is included in the Summary Advisor execution. 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 execution.
endDate is the end date for statistics to include in the Summary Advisor execution.
-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 were not 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
You can use the Aggregate Persistence Wizard to create the SQL file used to create and load aggregate tables and map them into the metadata.
Execute the resulting SQL file against a running Oracle BI Server.
Oracle recommends 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.
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 Oracle Exalytics machine, you can use the Summary Advisor feature instead of the Aggregate Persistence Wizard to identify which aggregates increase query performance and to generate a script for creating the recommended aggregates.
See the following:
Running the Aggregate Specification Against the Oracle BI Server
Using Oracle BI Summary Advisor to Identify Query Candidates for Aggregation
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.
Run Model Check Manager to ensure that your repository does not contain modeling problems that can affect aggregate creation and performance.
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.
Select Tools , select Utilities , select Aggregate Persistence, and then click Execute.
In Select File Location, 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.
You can select Generate target DDL in a separate file if you want the DDL 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:
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
Run the generated DDL file against the target database to create the table.
Run the create aggregates script to populate the table.
Click Next after you have finished specifying options on the Select File Location screen.
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.
Select Persist 'Count Distinct' measures as raw values to add the as_raw_values clause to all valid count distinct measures and to set an aggregation expression override on the corresponding logical column for each system-generated aggregate logical table source. Setting this option enables aggregate persistence to store actual values that are distinct-counted. If you do not select this option, then aggregate persistence stores pre-computed counts for the specified level combinations.
The View Script button is not available during the creation of the first aggregate table block.
Click Next after you have selected the appropriate measures.
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 use 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.
Click Next after you have selected the appropriate level of aggregation.
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_ . You can changed the prefix by updating the AGGREGATE_PREFIX property in NQSConfig.INI.
Click Next after you have provided connection pool information.
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.
In the Finish Script screen, the complete path and file name appears. Click Finish.
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:
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 does not 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 do not 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.
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.
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 is not available, a warning appears explaining that Model Check Manager cannot 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 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 is recommended that you use Filtered by Statistics, or run it only for selected objects, to improve performance.
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.
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 Administration Tool. The validaterpd utility is available on both Windows and UNIX systems.
To run validaterpd in Model Check mode, you must specify the DSN of a running Administration Tool.
The location of the validaterpd utility is:
BI_DOMAIN/bi/bitools/bin
See Using 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 do not provide the password argument, you are prompted to enter the password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide password arguments either on the command line or in scripts.
Note:
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 a whitelisted 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 is not 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 is not specified, all objects are checked. If -W is also specified, the white list file can only contain business models and logical fact tables, other objects are not 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 RPD using the DSNName connection, checks all models in the RPD, 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 RPD 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 whitelist can only contain business models and logical fact tables. Other objects are not checked.
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 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 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:
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 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 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 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).
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 is allowed to have multiple facts with the same name but different scope in the same database.
The aggregate specification is discarded if any dimension is not joined to a fact.
All metadata names, except 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.
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 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 cannot 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 are not 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.
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 is not dimension-based.
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.
Note:
Any aggregate scripts that are run after the first one should not have a Delete Aggregates statement, or all previously created aggregates are removed.
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 are 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'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 is 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.
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] ;
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.
Learn how to run the aggregate specification script against the Oracle BI Server.
Before you run the script, you must create an ODBC DSN (data source Name) for the Oracle BI Server and ensure that the correct log level is set. You must manually create a DSN for the Oracle BI Server to run the aggregate specification against for a single-node deployment. When the deployment is a multi-node cluster, you must run the aggregate specification directly against the source Oracle BI Server. Create a non-clustered DSN for the source Oracle BI Server to run the aggregate specification against. Use the Cluster Manager in the Administration Tool in online mode to determine which Oracle BI Server is the source.
Note:
In a clustered environment, the aggregate specification script performs a rolling restart of the destination Oracle BI Servers in the background. As a best practice, you should avoid making other configuration changes in Fusion Middleware Control or the configuration files while running the aggregate persistence script. Only the destination servers are restarted in the rolling restart. Changing the configuration, might send the Oracle BI Server a different set of configuration settings than the destination Oracle BI Servers. If the configuration changed, restart the source Oracle BI Server.
After creating a DSN, you can execute the script using nqcmd as a user who is a member of the BI Administrators group. See Using nqcmd to Test and Refine the Repository.
The queries and errors are logged to nqquery.log when using an Oracle BI EE 11g version. When using Oracle BI EE 12c, the queries and errors are logged in the obis1_query.log located in the DOMAIN_Home/servers/obis1/logs.
See “Integrating Other Clients with Oracle Business Intelligence” in Integrator's Guide for Oracle Business Intelligence Enterprise Edition for information about how to create an ODBC DSN for the Oracle BI Server .
Trace logs are recorded if the logging level is at least 2. The logging events include the aggregate execution plan and the order in which the aggregates are created and deleted. Higher logging levels provide more details about the query and execution plans - for example, specify logging level 4 or higher to see the queries being executed. Error logs are recorded if the logging level is at least 1, and to nqserver.log regardless of the logging level.
Use one of the following methods to set the logging level:
Set the logging level in the repository user object for the user who plan to run the script. See Managing the Query Log in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
Create and set the LOGLEVEL system session variable. LOGLEVEL overrides logging levels set on individual users. See Creating Session Variables.
After executing the SQL script, aggregates are created and persisted in the Oracle BI Server metadata, as well as, in the back-end databases.
When a series of aggregates are being created, and the creation of one aggregate fails, the aggregate persistence engine skips creation of the failed aggregate and its dependencies and proceeds to the next aggregate in the list.
Check the log files to identify failed aggregates. If orphan aggregate dimensions, those not joined to any other fact table, were created, use the Delete Aggregates command to remove them.
The table summarizes the user tasks to persist aggregates for different life cycle use cases.
Life cycle use cases focus on operations against single or multiple aggregate persistence targets, and do not describe operations for single or multiple-node deployments. User tasks are the same for both single-node deployments and multiple node deployments with the only difference is related to a clustered deployment. In a clustered deployment, you must connect to the controllerOracle BI Server. A rolling restart of the subordinate servers is performed in the background. See Running the Aggregate Specification Script.
| 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 |
|
2 |
Deleting aggregates for a single aggregate persistence target |
To delete aggregates, use 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 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 |
|
5 |
Deleting aggregates for multiple aggregate persistence targets |
To delete aggregates on multiple targets, use 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. |
|
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:
|
When you have aggregate clones across multiple aggregate persistence targets, you can use double buffering to avoid downtime when refreshing the aggregates.
You manually call the aggregate create and delete SQL statements in a way that controls the refresh to set up double buffering.
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. 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.
When specifying 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.
When specifying multiple inactive schemas, use a comma-separated list. Make sure there are no spaces in the list.
The Refreshing Aggregate Clones on Two Targets example show to use double buffering to refresh aggregates on two targets.
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.
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";
Delete the aggregate clone for the first target:
set variable LOGLEVEL=7 : delete aggregates "tgt1".."double1"."myfactaggr";
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";
Delete the aggregate clone for the second target:
set variable LOGLEVEL=7 : delete aggregates "tgt2".."double2"."myfactaggr";
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"; 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 Oracle BI Administration Tool. See Specifying SQL Features Supported by a Data Source.
This section contains the following topics:
See Oracle Exalytics for specific instructions on setting up 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 Installation Guide.