We are using Oracle OLAP 11.2.0.2.0 with an 11g Cube, 7 Dimensions, Compressed and partitioned by Month.

We have run into a performance issue when implementing OBIEE.

The main issue we have is a delay while drilling on a hierarchy. Users have been waiting 7-12 seconds per drill on a hierarchy, and the query is only returning a few cells of data. We have managed to isolate this to slow performing queries on CUBE_TABLE.

For example, the following query returns one cell of data:

-----

SELECT FINSTMNT_VIEW.BASE, FINSTMNT_VIEW.REPORT_TYPE, FINSTMNT_VIEW.COMPANY, FINSTMNT_VIEW.SCENARIO, FINSTMNT_VIEW.PRODUCT, FINSTMNT_VIEW.ACCOUNT, FINSTMNT_VIEW.SITE, FINSTMNT_VIEW.TIME
FROM "SCHEMA1".FINSTMNT_VIEW FINSTMNT_VIEW
WHERE
FINSTMNT_VIEW.REPORT_TYPE IN ('MTD' )
AND FINSTMNT_VIEW.COMPANY IN ('E01' )
AND FINSTMNT_VIEW.SCENARIO IN ('ACTUAL' )
AND FINSTMNT_VIEW.PRODUCT IN ('PT' )
AND FINSTMNT_VIEW.ACCOUNT IN ('APBIT' )
AND FINSTMNT_VIEW.SITE IN ('C010885' )
AND FINSTMNT_VIEW.TIME IN ('JUN11' ) ;

Not sure if it helps, maybe useful to know. I dont know how you determined that the aw attach time is not the issue.

***********
You can attach the aw before running the queries.

For OBIEE: You can use the OBIEE "Execute on connect" hook present in Connection Pool "Connection Scripts" tab to run some dbms commands (even olap specific dbms commands) like "SELECT dbms_aw.interp('aw attach aw_name ro') FROM dual". The corresponding detach command via "SELECT dbms_aw.interp('aw detach aw_name') FROM dual" can be executed in "Execute on disconnect".

Based on some advice provided by David Greenfield a while back regd aw attach mode in CUBE_TABLE based queries:

If the AW is attached before the cube table call, then it is left attached (i.e. session mode).
If it is not attached, then it is attached for the duration of the query only.

OBIEE and olap interaction regd aw attach operations seems to work as follows:
a) If aw is already attached for obiee connection pool connection (active), then calling query via CUBE_TABLE will not attach/detach the aw (session mode)
b) If aw is NOT attached prior to execution of request/query against CUBE+TABLE based view then the connection will attach the aw, execute the query and detach the aw once again (query mode)

It's much better if your queries are running as per scenario a) above.

***********
Another point to consider is that aw attach time can be significantly larger for a privileged user (dba role) who has access to many aws beyond the login/application schema ambit. if your application login is sufficient and you have given dba access to this user/role then it may be worthwhile to test out after revoking the dba grant (explicitly grant other needed privileges).

I think you are not using OBIEE 11.1.1.5, that is why you are struggling with CUBE_TABLEs. Starting with OBIEE 11.1.1.5, it is so simple now to query from Oracle-OLAP cubes, and we don't use CUBE_TABLEs at all.

Anyway ....since you are using 11.2.0.2, you should enable query logging and then check CUBE_OPERATIONS_LOG. This will give you a detailed picture of what is going on in the olap engine. For example, it could be that LOOP OPTIMIZATION may not be happening.

Here are the steps that you do in your SQL client.

(1). Execute the following.

being dbms_cube_log.enable(dbms_cube_log.TYPE_OPERATIONS, dbms_cube_log.TARGET_TABLE, dbms_cube_log.LEVEL_MEDIUM); end

select * from cube_operations_log
--where sid = '' or sql_id = ''
order by time desc;

If you know the SID or SQL_ID then you can provide that too.

Some information that you will see is:

(1). SUBOPERATION = ‘GDILoopOpt’ row will tell you if LOOP OPTIMIZED is being used. If it is ‘DISABLED’ then figure out why it is. For a cube load that is ok.

(2). NAME = 'LIMIT_FAST' or 'LIMIT_FULL' or 'LIMIT_INHIER' rows will show the LIMIT commands that were done. You can also see how many members was a particular dimension limited to (by looking at statlen in VALUE column). This is where it limits the cube to a small slice, so that less rows are returned to relational engine.
NAME = 'CUR_DIM_LENGTH' will also show you the similar thing.

(3). NAME = 'ROWS_FAILED_FILTER' or 'ROWS_RETURNED' or 'ROWS_READ' rows show useful information about HOW many rows were read from olap to relational engine and then how many “failed” the filter criteria and then finally how many rows were returned.

(4). ‘predicates pruned’ means that a WHERE clause was fully implemented as limit(s) inside OLAP engine.

This query returns in 6.006 seconds using SQL Developer, if I then take the CUBE_OPERATION_LOG for this query and subtract the start time from the end time, I only get 1.67 seconds. This leaves 4.3 seconds unaccounted for... This is the same with the my other queries, see actual time and logged time below: