OBIEE multi star queries are a powerful but complex Oracle BI functionality. They allow simple retrieval of new business insights and deliver added value due to cross-functional analyses. Presentation introduces a major change within Oracle BI query logic since release of OBIEE 22.214.171.124.1 (see also Oracle Support document OBIEE: Setting Fact to Total Level Of Hierarchy No Longer Forces Outer Join). Example uses an OBIEE logical model with two non-conforming dimensions Invoice Item and Return:
The figure Invoice Qty has Return total level set and Return Qty total level of Invoice Item. If conformed content is added to the query then OBIEE performs inner, left or full outer joins on conformed columns depending on filter types applied:
- Non-conforming inner join when global filters are applied to both non-conforming dimensions.
- Non-conforming left or right outer join when non-conforming dimensions are filtered mutually exclusive by a global and a local filter.
- Non-conforming full outer join when only local filters are used for non-conforming dimensions.
FILTER(<measure> USING <expression>)
Presentation slides provide query details and OBIEE generated SQL for all combinations mentioned above. This behaviour isn't feasible for all query cases. For deactivation insert the following statement into the Prefix field of Advanced tab of Oracle BI Answers (same procedure as shown in OBIEE Variable LOGLEVEL and Report Based Logging):
SET VARIABLE FOJ_GRAND_TOTAL_LBM=1;
After applying OBIEE server is forced to only perform full outer joins as prior to OBIEE 126.96.36.199.1.