OBIEE - Aggregate Persistence (Script)

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.

Model Check Manager

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

Create the script

To create the script, you can do it:

• manually
• with Summary Advisor
• or with the help of the aggregate Persistence Wizard

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

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.

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>

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.

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

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

Privileges

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

Diagnostic

As diagnostic log, you have:

• the NQQuery.log. Example:

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

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.

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.

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.