Saturday, 9 May 2015

OBIEE Non-Conforming Dimensions

BI Forum 2015Yesterday BI Forum 2015 in Brighton ended with the Gerd's session Driving OBIEE Join Semantics on Multi Star Queries as User (slides available at SlideShare). It was a great pleasure to speak in Brighton and to get quite good information in other OBIEE, data discovery and data visualisation sessions. This article adds some details to presentation just mentioned.

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 11.1.1.7.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:

OBIEE Star Schema Non Conforming Dimensions

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.
Global filters are commonly used filters within a report. They apply to the full query. Local filters just filter measure field content and look like:

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 11.1.1.7.1.

No comments:

Post a Comment