New Features in the Oracle Business Intelligence Suite Bundle Patch 11.1.1.7.1

New Features in the Oracle Business Intelligence Suite Bundle Patch 11.1.1.7.1

• Enforce Limits Per Logical SQL Query
• Support for TimesTen Parallel Insert

Enforce Limits Per Logical SQL Query

New configuration settings are now available in the NQSconfig.INI file to control or enforce query limits on logical SQL language elements, as follows:

MAX_COLUMNS_IN_SELECT

Specifies the maximum number of columns in a SELECT statement, including all subtotaling expressions generated by Presentation Services.

This limit applies to all SELECT statements including derived or leaf select blocks. Setting this value to 0 does not represent unlimited. The limit that you set in this parameter applies to all users, including administrators, and all subject areas.
For example:

MAX_COLUMNS_IN_SELECT = 50;

MAX_LOGICAL_DIMENSION_TABLES

Specifies the maximum number of logical dimension tables that display in a single leaf logical request.

A single presentation column might references multiple logical tables when the corresponding logical column is derived from multiple logical tables. Also, multiple presentation tables might reference the same logical table. For example, suppose a query requests multiple logical tables such as EmployeeCity, EmployeeRegion, and EmployeeCountry. In this example, the table count is three even though all tables reference the same dimension.  Hidden dimension attributes are include in the total number of logical dimension tables. Setting this value to 0 does not represent unlimited. The limit that you set in this parameter applies to all users, including administrators, and all subject areas.
For example:

MAX_LOGICAL_DIMENSION_TABLES = 30;

MAX_LOGICAL_FACT_TABLES

Specifies the maximum number of logical fact tables that display in a single leaf logical request. This parameter also applies to implicit fact measures added by the Oracle BI Server.

Suppose this parameter is set to 0 and the query requests two dimensions which invokes the implicit fact measure. The query fails because the logical fact table limit was exceeded. Hidden fact attributes are include in the total number of logical fact tables. Setting this value to 0 does not represent unlimited. The limit that you set in this parameter applies to all users, including administrators, and all subject areas.
For example:

MAX_LOGICAL_FACT_TABLES = 5;

MAX_LOGICAL_MEASURES

Specifies the maximum number of unique logical measure columns, that is the unique dimension aggregations defined in the logical layer in a single logical request.

Some measures might be referenced multiple times in a single query, but are counted once. Measures that are based on the same physical attribute and aggregation rules but with different level-based setup are counted as different measures. For example, EmployeeCountry.Revenue is derived from Sales.Revenue with its level set to COUNTRY on the Product-Region dimension, but it is counted as a measure different from Sales.Revenue. Hidden fact attributes are include in the total number of logical measures. Setting this value to 0 does not represent unlimited. The limit that you set in this parameter applies to all users, including administrators, and all subject areas.
For example:

MAX_LOGICAL_MEASURES = 15;

MAX_SET_OPERATION_BLOCKS

Specifies the maximum number of union, intersect, or minus blocks that display in an incoming SQL query.

A query with a set operator contains at least two query blocks. Every query must have at least one query block. If you specify 0 in this parameter, then the Oracle BI Server does not execute a query. If you specify 1 in this parameter, then only queries that do not use set operators, and therefore contain only one query block, are included in the query. The limit that you set in this parameter applies to all users, including administrators, and all subject areas.
For example:

MAX_SET_OPERATION_BLOCKS = 15;

QUERY_LIMIT_WARNING_INSTEAD_OF_ERROR

Determines if an error message displays when the logical query limits are exceeded.

If this parameter is set to OFF and the logical query limits are exceeded, then the Oracle BI Server displays an error message and terminates the remainder of the query. If this parameter is set to ON and the logical query limits are exceeded, then the query completes and no error message displays, but a warning message indicating that the threshold was exceeded is logged in the nqserver.log file.
For example:

QUERY LIMIT_WARNING_INSTEAD_OF_ERROR = OFF;

Support for TimesTen Parallel Insert

If you are creating aggregates in TimesTen Release 11.2.2.5 or later, then you can now enable the new parallel insert feature. Parallel insert allocates multiple threads to accommodate the insertion of rows into a single table, which improves the performance of aggregate creation in TimesTen.

To enable the parallel insert feature:

1. Perform the procedure described in the section Enabling Performance Enhancement Features for TimesTen of the Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition to enable the following features: 
• Disable Redo Logging
• Create Indexes in Parallel
• Perform database checkpoints in the background

• Open opmn.xml for editing. You can find opmn.xml at:

• ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml

• Locate the ias-component tag for the Oracle BI Server process. For example:

• <ias-component id="coreapplication_obis1" inherit-environment="true>

• Under the <environment> subtag, update the following TimesTen variables:

• <variable id="ORACLE_BI_AGGR_PARALLEL_INSERT" value="ENABLE"/> 
• <!-- This enables parallel insert using TimesTen's default thread count allocations, providing faster creation of aggregates. Set the value to an integer >=2 to specify a custom thread count for TimesTen. AggrPersist will request that TimesTen allocate the input number of threads for use in performing parallel inserts. If the number of thread counts exceeds TimesTen's maximum number, then TimesTen and AggrPersist will use the maximum number instead. This maximum number can be overridden using a request variable. -->
• <variable id="ORACLE_BI_AGGR_DIMENSION_TO_FACT_RATIO = 1:4"/>
• <!-- Optional. This specifies the ratio of threads to dedicate to a dimension table versus a fact table. Using a ratio allows thread allocation to scale to the size of the TimesTen ThreadPool. There is one thread pool per TimesTen connection pool. If this variable is omitted or is invalid, the value defaults to 1:4. Suppose you specifiy a 1:4 ratio and there is one Dimension and one Fact table in an Aggregate Persistence batch of tables. When a TimesTen Connection Pool's thread pool has 5 threads, then one Dimension table will have one thread and one Fact table will have four threads. And, when a TimesTen Connection Pool's thread pool has ten threads, then one Dimension table will have two threads and one Fact table will have eight threads. -->

• Save and close the file.
• Re-start OPMN 

2. Repeat these steps on each computer that runs the BI Server process. If you are running multiple BI Server instances on the same computer, then be sure to update the ias-component tag appropriately for each instance in opmn.xml (for example, ias-component id="coreapplication_obis1", ias-component id="coreapplication_obis2", and so on).

Thanks and Regards,

Angad Kumar Shukla