OBIEE / Essbase MDX generation issues finally resolved

In one of my recent posts I was talking about the different patch levels for 10.1.3.4.0 and 10.1.3.4.1 and their impact on functionality and the MDX produced. Interestingly enough not all of the problems appear with every cube outline imported into OBIEE. Oracle initially had troubles reproducing some of the errors (using the normal OOB Essbase sample cubes).

To recap: the changes to the connectivity DLLs first introduced in patch 7349048 (for 10.1.3.4.0) enabled OBIEE / Essbase security integration, but had some side effects. Performance dropped up to 3000% in some cases, standard report level totals or pivot totals stopped working etc. This is mostly due wrongly produced MDX. Especially for the grand totals the situation is quite clear:

With
set [Currency2] as 'Filter([Currency].Generations(2).members, (([Currency].CurrentMember.MEMBER_ALIAS = "US Dollar" OR [Currency].CurrentMember.MEMBER_Name = "US Dollar")))'
set [Time Period2] as 'Filter([Time Period].Generations(2).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "2009" OR [Time Period].CurrentMember.MEMBER_Name = "2009")))'
set [Time Period3] as 'Filter(Generate({[Time Period2]}, Descendants([Time Period].currentmember, [Time Period].Generations(3),SELF), ALL), (([Time Period].CurrentMember.MEMBER_ALIAS = "Quarter 1" OR [Time Period].CurrentMember.MEMBER_Name = "Quarter 1")))'
member [Currency].[CurrencyCustomGroup]as 'Sum([Currency2])', SOLVE_ORDER= AGGREGATION_SOLVEORDER
member [Time Period].[Time PeriodCustomGroup]as 'Sum([Time Period3])', SOLVE_ORDER= AGGREGATION_SOLVEORDER
select
{ [Account].[Sales]
} on columns
from [Sample.Sample]
where ([Currency].[CurrencyCustomGroup],[Time Period].[Time PeriodCustomGroup])

The MDX simply contains no "on rows" specification. These changes remained in all subsequent patches and are also part of 10.1.3.4.1. Hence, this release suffers from the same problems.

On 10.1.3.4.0, there exists patch 8444119 which basically was constructed after 10.1.3.4.1. 8444119 itself does not solve any of the issues mentioned above. It does however include a small, non-documented switch which resolves all these issues. NOTE: this is NOT documented and will only work on 10.1.3.4.0! Oracle told me they're working on a similar fix for 10.1.3.4.1 and on updating the patch information, but no sign of either yet.

In a 10.1.3.4.0 environment, install patch 8444119, then create a new environment variable: "OBIS_Essbase_CustomGroup_Generation". Set the value to "0" (without the quotes). This will correct the issues and correct the MDX fired against Essbase. Also, performance will be stabilized and go back to the level of an unpatched 10.1.3.4.0.

Oracle unfortunately didn't specify in detail what this OBIS_Essbase_CustomGroup_Generation "performance knob" (as they call it) does behind the scene and what the other two available settings "1" and "2" entail. Hopefully they update patch 8444119 soon since its documentation still says nothing about the existance of this environment variable.

Document 845594.1 has been created an published on Metalink. This is the only document existing which points to or mentions OBIS_Essbase_CustomGroup_Generation. So if you're out there, going against Essbase cubes and running 10.1.3.4.0 with 8444119 (or any MDX-influencing other patch below) or a full blown 10.3.4.1 then check it out.

WARNING: While document 845594.1 outlines the issue and the solution, the version it refers to is WRONG!

"Applies to:
Business Intelligence Suite Enterprise Edition - Version: 10.1.3.4.1 [1900] - Release: 10g"

Patch 8444119 and the use of environment variable "OBIS_Essbase_CustomGroup_Generation" do only apply for patch 10.1.3.4.0. You can not use this solution with 10.1.3.4.1.

Cheers,
Christi@n