join type in physical diagram is read only

Question

I've got a join in my physical layer that's currently an inner join, but in fact needs to be an outer join. I can call up the physical foreign key properties from the physical diagram, and the join type is there, but it's read-only, can't change it. I can change other things in the window, like the tables and columns (though I don't want to, obviously).

The repository is in offline mod and in fact saved out under a different file name. Anyone know what I need to do to be able to change this?

dior29 · Answer

Thanks for the clarification, both Christian and Gianni. I found the join in the Business Model layer and I was able to make that a Right Outer join. After a repository refresh, the related report is returning all the records we expect.

Gianni Ceresa · Answer

It's perfectly normal ...

In the physical layer you simply define the condition connecting the 2 tables, not the inner or left/right/full outer nature of the join.

You then set the kind of join in the business model.

So forget the physical layer but go in the logical layer where you have the logical join between your fact and dimension. There you can define the kind of join (inner or outer) but not change the condition (column A = column B etc.).

EDIT: well ... what Christian said while I was writing...

[Deleted User] · Answer

1.) OBIEE does NOT handle inner vs outer in the physical layer.

2.) Inner join vs outer join is managed in the Business Model and Mapping Layer in the relation between entities.

3.) All physical layer direct joins are "read only"

4.) Only physical layer complex joins allow you to write code - but NOT inner vs outer!

5.) And you can't change from direct join to complex join just like that. You need to delete and recreate.

But for you the important bit is in the BMM layer. Physical outer joins - that simply is not how the tool works because through this setup you can use the same physical relationship as inner, left outer, right outer, full outer...so 4 different logical relationships rather than needing 4 physical representations.