OBIEE - Aggregate Persistence (Script)

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

The Aggregate Persistence functionality is the ability to:

  • create and populate aggregate tables,
  • and map them to the metadata repository in order to use the aggregate navigation capability of the Oracle BI Server

via a script.

The Aggregate table candidates are determined by analysis of usage tracking data:

Since OBIEE 11g, when aggregates are persisted, indexes and statistics are created on relational tables for greater performance.

The script will:

  • Materialized data in the database
  • create physical tables in the repository
  • create Logical Table Sources and mapping in the repository (For both Facts and Dimensions)
  • automatically updated slaves in the cluster

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.

Advertising

3 - Prerequisites

3.1 - Timesten

3.1.1 - PL/SQL

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.

3.1.2 - Connection Script

call ttoptsetflag('rowlock',0)
call ttoptsetflag('tbllock',1)

See the ttoptsetflag Built-In Procedures in the Oracle® TimesTen In-Memory Database Reference. This function allow to alter the generation of execution plans by the TimesTen query optimizer.

3.1.3 - Script

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.

3.2 - Connection pool

Aggregate persistence requires a dedicated connection pool with a SCHEMA to create tables or cubes in the target database that will hold the aggregates.

The user should have privileges to connect, create, and drop tables and indexes.

Advertising

3.3 - Model Check Manager

Run Model Check Manager to ensure that your repository does not contain modeling problems that will affect aggregate persistence results.

4 - Script

4.1 - Create the script

To create the script, you can do it:

SET variable LOGLEVEL=7 : DELETE aggregates "agg_1_quarter","agg_2_month"; 
-- Without a list of aggregates OBIEE will deletes ALL aggregates, for instance
-- delete aggregates;
 
SET variable LOGLEVEL=7 : CREATE aggregates
"agg_1_quarter”
for "01 - Sample App"."F0 Revenue Base Measures“ ("1- Revenue“)
at levels ("01 - Sample App"."H0 TIME"."Quarter“)
USING connection pool "05 - Sample App Data (TimesTen)"."TimesTenCP”
in "05 - Sample App DATA (TimesTen)".."BISAMPLE_TT",
"agg_2_month”
FOR "01 - Sample App"."F0 Revenue Base Measures“ ("1- Revenue“)
at levels ("01 - Sample App"."H0 Time"."Month“)
using connection pool "05 - Sample App DATA (TimesTen)"."TimesTenCP”
IN "05 - Sample App Data (TimesTen)".."BISAMPLE_TT";

4.2 - Run

The SQL script can be run on a scheduled basis against the Oracle BI Server.

For instance, with nqcmd:

nqcmd -d Dsn -u User -p Pwd -s agg.sql -o output.txt
-------------------------------------------------------------------------------
          Oracle BI Server
          Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------

In a cluster, it must be run against the master server. 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.

Advertising

4.3 - Gain

<MATH> \text{Gain Expected} = \text{Query Time with Base Fact Table} . \frac{\text{Aggregate Fact Table’s row count}}{\text{Base Fact Table’s row count}} </MATH>

5 - How to

5.1 - Delete all existing aggregates

With Nqcmd, the following statement must be fired:

SET variable LOGLEVEL=7 : DELETE aggregates;

Example:

ora_home\bifoundation\server\bin>nqcmd -d exalytics-01.hotitem.local -u AdminUserLogin -p AdminUserPassword

-------------------------------------------------------------------------------
          Oracle BI ODBC Client
          Copyright (c) 1997-2011 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------



        [T]able info
        [C]olumn info
        [D]ata type info
        [F]oreign keys info
        [P]rimary key info
        [K]ey statistics info
        [S]pecial columns info
        [Q]uery statement
Select Option: Q

Give SQL Statement: set variable LOGLEVEL=7 : delete aggregates;
set variable LOGLEVEL=7 : delete aggregates;
[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43119] Query Failed:
[nQSError: 46036] Internal Assertion: Condition fIter != hiMap.end(), file server/NQSAdmin/AggrPersi
st/Src/SAAPAggrUtility.cpp, line 252.
Statement preparation failed

Don't worry with the error message, the Business Logical Aggregate are suppressed but not the physical one.

5.2 - Aggregation Plan

You can see the aggregation plan in the NQQuery.log with all database SQL fired.

Example:

-------------------- Aggregate Manager: Aggregate Creation Plan:
Phase 1
	Component 1
		Type: Dimension
		Source: "COBI_DM_CNT"."D3 Dim OrganisatieDim"
		Level(s): [Medewerkers]
		Physical Name: SA_Medewer0004DDA6
Phase 2
	Component 1
		Type: Fact
		Source: "COBI_DM_CNT"."F1 Fact Kpi Contact"
		Level(s): [Medewerkers]
		Physical Name: ag_F1_Fact_Kpi_Con

6 - Configuration

6.1 - Bi Server

In the nqsconfig.ini

###############################################################################
#
#  Aggregate Persistence Section
#
###############################################################################
 
[AGGREGATE_PERSISTENCE]
 
# Aggregate Persistence defaults
# The prefix must be between 1 and 8 characters long
# and should not have any special characters ('_' is allowed).
AGGREGATE_PREFIX = "SA_";  
AGGREGATE_THREAD_POOL_SIZE = 5;  
AGGREGATE_AW_NAME = "OBI_AW";  
PREAGGREGATE_AW_CUBE = YES;  

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

6.2 - Privileges

By default, only users who belong to the BIAdministrators group can manage aggregates.

7 - Support

7.1 - Diagnostic

As diagnostic log, you have:

------ Aggregate Manager: Discarding aggregate (multiple databases specified): ag_F1_Fact_Kpi_Con
  • and the output file of the nqcmd command with the o command line option.

7.2 - Discarding aggregate (multiple databases specified)

Verify that you have specified a schema and not only the database in the IN operator of the create statement.

7.3 - Existing aggregate does not match request

Two reasons that can lead to this errors:

  • the script is bad and contains bad columns such as formulas. Try to make of with the wizard
[nQSError: 84011] [Aggregate Persistence] Existing aggregate does not match request.
  • after a error suppressing the aggregates, you may have to suppress them also from the physical layer.
Aggregate Manager (Error): SA_Medewer0004DDA6: 
[nQSError: 84011] [Aggregate Persistence] Existing aggregate does not match request.

7.4 - Object "" is already checked out or temporarily locked

Delete aggregates;
[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.
Statement preparation failed

When trying to delete aggregate, you may get this error. Be sure to unlock all objects by performing for instance a File > Undo All Changes.

In the NQQuery.log, you will find:

Aggregate Manager (Error): 2000:214140: 
[nQSError: 36004] Object COBI_DM_CNT is already checked out or temporarily locked.
[nQSError: 84002] [Aggregate Persistence] Metadata could not be checked out.

8 - Documentation / Reference