Trying to create an outer join in obiee

user2053645

That's not what I'm looking to do.  Just an observation about the tool compared to some other BI tools I've seen.  I do need to use obiee for the purposes

of presenting information.  I don't have another option presently.  Thank you.

Gianni Ceresa

Did you open a SR and asked Oracle?

I guess I saw some threads where that kind of cases ended up being fixed with MOS giving some "magic keyword" to set in the analysis as variables to influence the way OBIEE generate queries. (I didn't read all the details in the Word files, just looked quickly)

Martin van Donselaar

I have never had or seen issues with this no, I suggest that you follow Gianni's advise and raise an SR

user2053645

Gianni - Thank you.  Yes, I did open an SR with Oracle.  Here is some more information that might be of interest.  Any thoughts?

I was able to create an outer join where there was only 1 physical column join between two tables.

In my example, I have 3 columns that are part of the physical join between the two tables.

Is that the issue?  Is there a way to do an outer join where there are multiple columns that are part of the physical join?

Thomas Dodds

Why don't you have a single surrogate key that takes into account the data source, integration and other key values?  Simplify ...

user2053645

Thomas - Are you talking about in the database or in obiee?  If in the database, yes, completely agree.

Our modeling team is looking at that option.  But if you're talking about doing something in obiee, please elaborate.  I attached

a screenshot (word doc) to the ticket with how I set up the 3 key join (I just spelled it out in the expression area).

Thanks.

Srinivas Malyala-Oracle

option a: You can create views with all the tables your looking for  in physical layer and use it as table.In view you can specify any type of sql (any joins).

               another possible option: Create a view with surrogate key(concatenating 3 columns with some separator character)  for each table in the Physical Layer and use this as join.

                   

option b: If data source is always coming from one source you can ignore this datasource column in join. If integration_Id is unique then you may not needed additional check on INTRVN_NUM  so you can ignore this column too. But I am not how your data is organized. Take a look at you may not need additional 2 conditions.

Thomas Dodds

I'm referring to the database ... use drives physical data model design.