Online Tutorials & Training Materials |
Register Login

Filter on Non Confirmed dimensions giving Wrong results

|| || 4

Filter on Non Confirmed dimensions giving Wrong results

Hello Experts,

I'm stuck with an issue looking for suggestion/advice. The issue is related to records when the report is built on dimensions, non-confirmed dimensions and facts. Below is the details of RPD modelling and issue

• We have 3 facts f1, f2 and f3 and three dimensions d1 d2 and d3. Joins below
a) F1 -d1,d2, d3
b) F2- d2,d3
c) F3-d2
• I have set logical level to grand total for columns in f2 and f3 to non-confirmed dimensions d2 and d1 and d3
• Report is built on all columns from f1, f2, f3 and d1, d2, d3. Report executes fine but the results seems not complete set of records from all facts
• We have filter on it's time dimension and non-conformed . Though!!

Here’s the issue:
The report is bringing the records only as per joins between f1-d1,d2 d3 and ignoring other fact rows. Example Records from F1-d2 has 100, f2-d2 has 200 and f3-d3 has 50 the report displays only 100 rows against all facts and dimensions.
Investigated further and got to know obiee is doing left outer join between SQL’s from each facts.
How can I get all records from all facts...mean full outer join? Please suggest

Also this issue is only when there is a filter on non-confirmed dimension otherwise it’s working as desired.

Thanks in advance


  • 27 Feb 2014 9:57 pm Best Answer

    Hi Amit,

    There are different ways to address non-conformed dimensions and facts, depending on your reporting requirements.

    One way is to add reference to a "NA" member of the dimension to the fact that is missing this dimension. In your case, add the surrogate key for d1 NA member to f2 and f3 facts, and add the key for NA member of d3 to f3. This will create conformity between the three contexts. All records from f2 will appear as NA, d2, d3, and all records from f3 will appear as NA, d2, NA. This will allow you to bring to the report the right set of data, but if you apply condition to d1 or d3, you have to include "Or dim.col = NA", otherwise only records from f1 will be returned. This is very common practice in some reporting databases, but worst for reporting where one of the facts is aggregated level of the previous. Your totalwill always appear against "Not Applicable" value. Unless this is what you want. This method requires some development to insert references to the non-conformed dimensions in the fact tables.

    A second (and preferred) method is to force the report to create 3 queries in your example:
    - select d1.*, d2.*, d3.*, f1.measure from f1, d1, d2, d3 where f1.fk_d1 = d1.key and f1.fk_d2 = d2.key and f1.fk_d3 = d3.key
    - select d2.*, d3.*, f2.measure from f2, d2, d3 where f2.fk_d2 = d2.key and f2.fk_d3 = d3.key
    - select d2.*, f3.measure from f3, d2 where f2.fk_d2 = d2.key 

    In the Universe Designer enable the option "Multiple SQL statements for each context" and "Multiple SQL statements for each measure". This will force the above 3 sql statements to be generated when you drag and drop to the report objects from d1, d2, d3, and measures from f1, f2, f3 (the facts have to be in 3 different contexts). You need to merge the dimensions and combine their values, i.e. if the first query returns values a, b and c from d2, the second query returns values d, e from d2, and the third one returns yz, we need to see in the report all of them when d2 dimension is selected:
    a, b, c, d, e, yz

    In the data provider, if you open to view the sql scripts, you will see 3 queries, and the description "synchronized", not joined, as you have different dimensions in each query.
    You have to be careful and use the results in the right contexts at the report. For example, if you create table with d1, d2 and d3 dimensions, the f1 measure will show once, but f2 measure will repeat for each d1 dimension value. Use dynamic contexts in the calculations if you want to show all dimensions and all measures in same block.

    Hope that helps.

    Minka Tinkova

  • 13 Feb 2014 5:12 am

    Try adding a ( OR "is null " ) condition for the dimensions or may be for
    the fact column. So when there is outer join null's are not filtered.

  • 13 Feb 2014 5:12 am

    Okay in your physical Since D2 is common add that dim to your 3 Facts and
    then query should run fine.

  • 13 Feb 2014 5:13 am


    As per your requirement is concerned. If you do left out join it will give
    you only f1 value corresponding to the d1,d2,d3 dimensions value.

    What you will do that, Full outer join will not work.
    You have do the one by one join with your dimension key columns d1, d2, d3
    Then f1-d2 can have 100, f2-d2 has 200 and f3-d3 can have 50.

    Many thanks