OBIEE 11.1.1.7 creating wrong join

samujjwalbasu

Hi All,

I have come up with a very frustrating issue.

I am trying to explain the situation below:

I have got this fact table joined to three main dimensions (Organisation, Product and Month).

The fact table is monthly partitioned. Our fact data is also identified by YEAR_MONTH (i.e. 201601) not by any specific date. The YEAR_MONTH is our partition key for the fact table.

Similar to the fact table the Organisation and Product dimensions are also monthly partitioned. We have monthly snapshot data in each partition.

The Month table is one record per month for 20 years. This is a non partitioned table

In the physical layer we have joined the fact table to the Organisation and Product dimension using the business key and the YEAR_MONTH column. (i.e. org_dim.business_key = fact.business_key and org_dim.year_month = fact.year_month) and only joined the MONTH dim with the fact table (i.e. month_dim.year_month = fact.year_month).

But when OBIEE is generating the query it is doing like below:

org_dim.business_key = fact.business_key

org_dim.year_month = month_dim.year_month

month_dim.year_month = fact.year_month

This returns the correct results, but very very bad for performance.

Ideally I was expecting it to create the following join:

org_dim.business_key = fact.business_key

org_dim.year_month = fact.year_month

month_dim.year_month = fact.year_month

Any idea why my OBIEE is changing my physical join, and how I can force it to not to change the physical join?

Thanks in advance.

Regards,

Samujjwal Basu

[Deleted User]

OBI does precisely what you configure physically. It doesn't change joins at random.

So either your join is wrong our you configured multiple circular ones (which is also wrong).

Gianni Ceresa

I would agree with Christian but I'm guessing if I didn't see something similar as well at some point.

Because the joins, as you say, still return the same result, from a logical point view they are equivalent. And I guess I saw few cases where OBIEE try to improve the query and so add filters where needed or adapt joins to make the query better.

Of course I agree with you in your case it make things worst ...

I'm not sure of how you can change that, just guessing if you define them as complex joins it will maybe avoid OBIEE to touch them (as a complex join in general is really a condition it takes like that as it's not a ColumnA = ColumnB like the physical foreign key.

It's really just a guess, so I didn't test or anything ...

If you want to try you delete the actual joins and go to Manage > Joins and then you select "Physical" > "Complex join" and you add a new one for each one of your joins.

[Deleted User]

Just the fact that org_dim.year_month = month_dim.year_month "gets replaces by" org_dim.year_month = fact.year_month for me smells like multiple conflicting joins exist and the BI server just choses that wrong one. Still means the base config is messed up.

Gianni Ceresa

Yes, that's why I started by "I would agree with Christian"

But, if after cleaning up the model, making sure there aren't double joins and circular joins etc. the result is still the same ....

SPowell42

Just curious why your org and product dimensions also use the year / months in their joins? Not understanding why you'd want to have to create a whole new set of orgs / products monthly? If you have 100,000 products, do you end up with a whole new copy of those for every month (i.e. 24 million records - 100,000 per month * 12 months per year * 20 years)?

Thanks,

Scott

[Deleted User]

Monthly org snapshots most likely. Not the most elegant way as it creates tons of redundancy...

Thomas Dodds

I suspect a physical redesign and a simplification effort to avoid the double column join would help this.

Fact should maintain relationship between org, product and time ...