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.
Issue
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 11.1.1.6.0 is as follows:
WITH
SAWITH0 AS (select distinct T185463.COLL as c1
from
CC_FINANCIAL_DETAIL T185463 /* Fact Financial Detail */ )
select distinct 0 as c1,
D1.c1 as c2
from
SAWITH0 D1
order by c2
This is what you would expect. A distinct listing of 1 column. However, after upgrading the instance to 11.1.1.6.2 BP1, this is the sql that is generated:
WITH
SAWITH0 AS (select T185463.COLL_GROUP as c1
from
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
from
SAWITH0 D1
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
Solution
1) Take a copy of the 11.1.1.6.0 .RPD file.
2) Open the 11.1.1.6.2 version of the Admin tool
3) Open the 11.1.1.6.0 version of the .RPD file with the 11.1.1.6.2 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 (11.1.1.6.2) OBIEE server will not work. The version of that RPD is already 11.1.1.6.2, so the 11.1.1.6.2 Admin tool will not “upgrade” it to the current version. You need to use the 11.1.1.6.0 RPD.
Blog author: Brian Makarewicz
Brain is a contributing blog author on the Business Intelligence Practice team at BizTech.
