Double join in physical layer

Alex1

Hello,

I have an assignment to do in Administration Physical layer.

I have a fact table that has two columns: origin and destination.

On the other hand I have a dimension that has two columns: code and description. I have to join code with origin AND destination. When I add two columns with "AND" I have an error that Code can't use more than one.

"CODI"="DESTINATION" AND "CODI"="ORIGIN"

"CODI" Column is used more than once.

How can I fix this?

Thanks

Robert Angel

Hi,

if you want the same table for two purposes then you need to alias it twice and model the first using your first join condition, the second using your second.

You might also want to look at using lookup tables depending on your precise requirement.

[Deleted User]

Robert, I think Alex has a very different issue. His fact has two foreign keys which point to the same primary key in the dimension.

@Alex1 for this usage you can't "click" the join together. You create a new join in the physical layer but instead of clicking you immediately go to the forumla editor and use that! this will create a complex join (with red connecting lines instead of blue ones) and you can simply and easily use the same column twice.

Hope this helps

Robert Angel

On re-reading his post, yes, I think you are correct, my mistake, on first reading it looked like he wanted two things to be true simultaneously which were not compatible, so yes, your answer is what he is looking for.

I know the ideal situation is 1 join between fact and dimension and job done, but I always found it odd that the only way with this was a manual edit...

Gianni Ceresa

Sorry but I would go with Robert first answer...

Christian: I see what you say, and it would be right but ... the OP clearly try to match both foreign key at the same time with a "AND", which is logically impossible (or you open the door to 1=2 in SQL, and you don't want that). Your proposal could work with a "OR" instead of the "AND".

So my reading of what the OP want is more an alias of the dim table as Robert suggested, separate joins for the "ORIGIN" and "DESTINATION" tables and then bring them back as 2 LTS for the same logical dimension if that's what the OP want, or just keep them as 2 separate dimensions called "Origina" and "Destination".

Alex1

Thanks for your answers.

@Christian Berg If I use fx editor i have same error when I add my formula.

"CODI" Column is used more than once.

Can you provide me some snapshot to create a complex join in physical layer.

My problem is that I have two columns in my fact table that I want to join with one ID of a dimension table.

Thanks

Alex1

Ok Gianni Ceresa and Robert,

But if I create two different dimension tables user should select different columns to show results?

Gianni Ceresa

That was actually an open point: did you wanted a single column for your code/description or 2 different (one for origin and one for destination) ?

So the answer is: a single one!

Fine: add 2 LTS in the logical table and point once to the physical object joined as origin and one as the physical object joined as destination and done.

Gianni Ceresa

A complex join with a "OR" (because "AND" is impossible, ok ... possible yes, but a totally different meaning) works just fine in 12.2.1.3 .

You just can't edit an already existing foreign key join to change it into a complex join (even if you open the formula editor when you save it will refuse).

If you already have a join you first have to delete it, then create a new one and go directly in the formula editor and there it's fine.

What version of OBIEE are you using?

[Deleted User]

Ok so let's step back for a second and be precise here...

Which one is it? "OR" or "AND"?

It's either that you want roleplaying (for dim or fact) or you really look for occurrences of twin ID matches.

Please be precise or we will just keep circling an issue which simply was badly explained.

Alex1

Ok,

my steps are:

1st. Create two alias of my dimension:

2nd: In phisical layer join this tables to fact table:

Origin Fact_table --> Code_NewOrigin

Destiny Fact_table --> Code_NewDestiny

3rd: Drag two columns and Fact table to BMM

4th: Have to LTS in one table and rename table:

Is all correct? Do I need anything else?

Now I drag this table and fact table into Presentation Layer and I obtain results.