BizTech Blog

OBIEE issues

Posted by Stephen Goldsmith

Find me on:

Oct 22, 2012 9:54:00 AM

The latest bundle patch set has been available for OBIEE for a few months now. We have noticed an issue where the SQL generated by the RPD is different after the Patch set is applied.


The new SQL generated by the .RPD file aggregates data incorrectly. For example, I created a report based off of one column in a fact table. The SQL that was correctly generated in is as follows:

SAWITH0 AS (select distinct T185463.COLL as c1
CC_FINANCIAL_DETAIL T185463 /* Fact Financial Detail */ )
select distinct 0 as c1,
D1.c1 as c2
order by c2

This is what you would expect. A distinct listing of 1 column. However, after upgrading the instance to BP1, this is the sql that is generated:

SAWITH0 AS (select T185463.COLL_GROUP as c1
CC_COUNTS_ALL T171648 /* Fact Counts All */ ,
CC_COUNTS_DETAIL T171654 /* Fact Counts Detail */ ,
CC_FINANCIAL_DETAIL T185463 /* Fact Financials Detail */
WHERE ( T171648.GRP_ID = T171654.GRP_ID AND T171654.COLL = T185463.COLL AND T171654.GRP_ID= T185463.GRP_ID) )
select 0 as c1,
D1.c1 as c2
order by c2

This doesn’t make much sense. There are a few things wrong with this query, but the most obvious is the lack of a distinct clause. With more complex answers based off of the business model, you start to see that all of the aggregation is incorrect. The upgraded RPD is issuing Cartesian joins in many cases.

Here is the solution


1) Take a copy of the .RPD file.

2) Open the version of the Admin tool

3) Open the version of the .RPD file with the version of the Admin tool.

4) Save the .RPD file and upload it.

Note that opening a copy of the .RPD file from the upgraded ( OBIEE server will not work. The version of that RPD is already, so the Admin tool will not “upgrade” it to the current version. You need to use the RPD.

Blog author: Brian Makarewicz

Brain is a contributing blog author on the Business Intelligence Practice team at BizTech.

Topics: Oracle Business Intelligence