OBIEE building join to all bridges when fact brought in

525401

I have a standard dimensional model that has a fact, multiple bridge tables, and dimensions.  In OBIEE answers if I bring in a fact table column to the analysis, a simple count, the query that is generated joins to all bridge tables to the fact.  I have not brought in any other columns from bridges or dimensions yet.  My data has data sets which are completely mutually exclusive, meaning there are some folks that have some bridge records but no records in other bridge tables.  So the result is always a count of zero.  I've looked at the Logical Table Sources and am considering changing the joins from the fact to the bridges to outer joins.  I'm in a constrained environment where I can't experiment much.  Does anyone know if this is the reason why obiee is bringing in inner joins to all bridge tables on inclusion of one fact column?  I've never had to do this in the past with previous versions of this rpd.

Gianni Ceresa

525401 wrote:

... I've never had to do this in the past with previous versions of this rpd.

Does it mean you did an update / upgrade and now it is generating the queries in a different way? What version are you using and what version was the "past" ?

525401

I'm on 11.1.1.7.150  .  I'm using a rpd developed by a separate group of people.  When I say previous version I mean the version they gave me previously.  I don't believe they changed anything major.  Just seems like obiee is now generating a big inner query to all bridges when any fact column is included.  I tried changing the setup in the business layer to have all outer joins.  The obiee query language query seems to include the outer join, but when I go down to the log of the physical query it is just the same big inner query.  Is it possible this is a caching issue even though it's definitely a different query? 

Gianni Ceresa

Well, if you don't change the analysis itself, you don't change OBIEE version and the query change then there aren't 10 places where to look for...

525401 wrote:

....I don't believe they changed anything major. ...

Never supposed that, mainly if you aren't part of the dev team doing the job

Many things can change the way a query is generated and inner / outer joins.

If your cache is enabled then try to disable it as a test (but if it's cached you don't see the physical query as it stops before to get there matching a cached result).

Andrew Fomin.

"I'm using a rpd developed by a separate group of people.  When I say previous version I mean the version they gave me previously.  I don't believe they changed anything major."

I think the best you can do is compare current rpd with the "good" one and find out what exactly was changed (Admin tool: File->Compare). So we may analyse the difference instead of guessing.