OBIEE Outer Join Issues

SonPat99

Dear GURUS,

I am facing some issues with the OUTER Join I am trying to use in my OBIEE Reports.

The details are as below:

OBIEE Version: OBIEE 12.2.1.3

OS: Windows 2012 R2

I have 3 Tables: 1 Fact (F) and 3 Dimensions (D1 - Time, D2 - Product, D3 - Customer), where D2 Left Outer Join F, D1 and D3 are Inner Joins to F.

The main requirement is:

I should get all the PRODUCTS from D2 irrespective of conditions/filters specified in for D1, D3 or F.

I have applied the filter on both D1 and D2 to select the time duration and Product Type, however, OBIEE is forming the Left Join in such a way that the D1 condition/filter is getting applied on the overall result returned and not on the subset of result.

select c1, --coming from Fact

     c2,

     c4,

     c5,

     c6,

     c7,

     c8,

     c9,

     c10,

     c11,

     c12,

     c13

from

    

          T619423 left outer join (

           T619540  inner join T619454  On T619454.METRIC_MONTH = T619540.MONTHID) On T619423.METRIC_DEFINITION_KEY = T619454.METRIC_DEFINITION_KEY

where  ( T619423.pqr = 1 and T619540.YEAR = 2017 and T619454.METRIC_MONTH <= 201711 and T619540.MONTHID <= 201711 )

group by

The highlighted condition is the one that has to be applied to the overall result returned from the F and D1, D3 resultset to get the LEFT JOIN.

Note: the details in the query are deleted on-purpose and the query is not incorrect/giving errors.

Request you to kindly suggest a way in which I can achieve this.

Thanks in advance.

Robert Angel

Hi,

if you have a structure (physical) that is thus; -

Fact => Dimension => Dimension

Then in the business model you should flatten this to

Fact => Dimension

With the physical joins on dimension giving you an outer join to the 2nd table.

However, irrespective of however many outer joins you have, if you have filters on the outer joined tables then they will never be true when the data is absent.

The only way to get around this is to make your filter; -

(value = 'X' OR value is null)

So two problems;

- outer joins in snowflake, resolved by modelling it down to single logical table, logical table source keeping your outer join

- filters on table content which may be absent - resolved by use of OR clause - however note that if your filters always need to be true in the original sense then essentially your requirement is claused

Silly example to illustrate; -

Bring me all houses with a white dog?

Would you really want all houses that did not have a dog at all in addition to all houses with a white dog?

Hope this helps,

Robert.

SonPat99

Hello Robert,

Thanks a lot for the reply.

I'm sorry, I missed to mention that the model is purely STAR Schema..

Saying that, I have to apply a filter where "T619423.pqr = 1" ...

I cannot use any other values as suggested (like NULL), because then some other unwanted values will come and definitely, there is no way I can delete these extra values as well...

Is there any setting applying which I can acheive the below:

from

   

          T619423 left outer join (

           T619540  inner join T619454  On T619454.METRIC_MONTH = T619540.MONTHID) On T619423.METRIC_DEFINITION_KEY = T619454.METRIC_DEFINITION_KEY

where  T619540.YEAR = 2017 and T619454.METRIC_MONTH <= 201711 and T619540.MONTHID <= 201711 ) WHERE T619423.pqr = 1

group by

Currently, the condition "WHERE T619423.pqr = 1" is coming alongwith other conditions I have applied...

Martin van Donselaar

The question is a bit unclear but it appears you want all products (where pqr =1) to show up in your reports, also the ones that do not have metric values.

Have you tried to use "Include Null Values" in your report?

Or for a cross tab report:

Hope this helps

Robert Angel

From your SQL I suspect that your only hope on this is either create a DB view that reflects the joins you want or an opaque view equivalent.

By its very nature it is going to be massively less performant than the original.

Could you "emulate" pulling in the nulls using a union report?

SonPat99

Thanks a lot Martin... I think this will pretty much solve the issue.

Although, I have taken a different approach as the data is very limited and will not grow much, I have included some null records to cover all the required data from the dimension table.

@Robert, Yes, my final option was to create some views and then work on that, however, I think including NULL records is less expensive than going for a view.

Thanks a lot to you guys for making my day a GOOD day.

Robert Angel

Thanks, it is refreshing to meet a newbie on the forums who explains their problem well, assigns correct answers and expresses thanks.

Sometimes I have patiently provided considerable detail and a user has done none of the above and frankly it makes you disinclined to help the user again.

So thank you!

Martin van Donselaar

That is basically what the Include rows with nulls would have done for you yes. :-)

Nice to hear you have solved your problem and I agree with Robert it's nice to get the acclaim for our effort.