Outer joins in the physical layer

Robert Angel

Hi,

I join (say) 4 tables in the physical layer that will act as a single dimension in the business model layer.

As physical joins I cannot specify outer joins on the physical layer.

True I can specify an outer join between the logical dimension and its fact(s), but say I have; -

core table

attribute table1

attribute bridge

attribute table3

I join; -

core => attribute table1

core => attribute bridge => attribute table 3

The contents of these 4 tables are modelled into one dimension table.

If core to attribute1 fails then I lose a full row of data

Ditto, if core to bridge to attribute3 fails I lose a full row of data

My preference is to never lose the core data and use the equivalent of Nvl(attribute_fieldx,'Unknown') on all of the attribute fields.

Am I correct that if I want this I only have two options; -

1. Build an ETL to achieve it

2. Use an opaque view or database view

Am I correct in my understanding and if so, why or why did the OBIEE developers think that you would never want outer joins on the physical layer - we don't all have the luxury of well formed data sources, sometimes you are modelling against 3NF sources and to only have the db view or opaque view is suboptimal on top of suboptimal...

My version is 12.2.1 if it makes any difference.

thanks for your input in anticipation,

Robert.

Gianni Ceresa

Hi,

It's not that developers didn't wanted an outer join in the physical join, it's just that the physical layer must not think about inner or outer join but just about "how to connect object A with object B" technically.

And you outer joins or inner joins you set them, as you said, in the business model.

But actually your need can be done in the business model. You said your 4 tables act as a single dimension.

So your dimension has a single LTS and you start with your core table. Then inside that same LTS you add your extra tables defining the relationship between them (so outer joins).

You add your 4 tables there and set your joins type and done. The available tables when you click the "+" are based on the relationship you defined in the physical layer, and the type answer you need of left outer join between core and bridge and core and table1 (and maybe bridge and table3, or you keep that one as inner).

Did you already try that option? Isn't it working for you?