nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

substring on physical or logical join in obiee 11g rpd

Amol Palkar

Hi Team,

I am looking to join table A with substring on other table however in OBIEE rpd i am unable to do that.

Please let me know how could I and where could I do it ?

e.g.

SELECT * FROM XX_CAT_D T380257

LEFT OUTER JOIN XX_CAT_HIER_D T552844

ON SUBSTR(T380257.CONCATENATED_SEGMENT_VALUE,10,5)= T552844.HIER3_CODE

I can do above in oracle database but not in OBIEE.

Thanks,

Amol

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Physical join. Never logical!

Yes you can do it - you simply have to create a new join between the objects, click the function button and write the condition.

S R Battula

Use Opaque view in RPD Physical Layer.

[Deleted User]

O_o that should be the LAST thing you do if standard modelling does not work.

Thomas Dodds

Opaque view send an un-filtered query back to the database ... potential for a huge amount of data to be transferred only for the BI server to inefficiently find the one row you want.

Reserved for POC type work only ... where you know how to restrict the amount of data coming across

S R Battula

Opaques are only used when there is no other way to get to the final solution. if you go for Opaques better deploy opaque view in to database to reduce the burden on BI Server.

[Deleted User]

S R Battula wrote:Opaques are only used when there is no other way to get to the final solution. if you go for Opaques better deploy opaque view in to database to reduce the burden on BI Server.

So why is this the first thing you're advocating? Plus you explicitly said "Use Opaque view in RPD Physical Layer." without any qualification of the statement.

Please don't write just any random stuff and then correct ex-post with some lame comment which clearly wasn't included in the thought process behind the initial answer. That's simply not helpful and worse leads the orinigla poster of the question off into a wrong direction.

S R Battula

Can you please explain how to get sub sting using CAST function in RPD.

[Deleted User]

The screenshots are there to show the function button and that you can write any code there. Lead someone to a solution rather than pre-masticating.

A bit different than saying "Write a view in the RPD" and then saying to do it in the DB as a last resort mate...

But if it makes you happy:

"10 - System DB (ORCL)"."Catalog"."dbo"."ZZ - Rubbish 2"."SUBJECTAREA" = SUBSTRING("10 - System DB (ORCL)"."Catalog"."dbo"."ZZZ - Rubbish 1"."SUBJECTAREA" FROM 0 FOR 1000)

Thanks for playing. Better luck next time.

Gianni Ceresa

@Amol Palkar : define your join in the physical layer as Christian showed, if you already have an existing join like columnA = columnB delete it first as you can't change it on the fly (it's because of 2 different kind if join: "physical foreign key" vs "complex join").

In the formula window you see you have lot of functions available, so you can really easily make your substring.

@S R Battula: if you use opaque view for that why not go directly to a direct database request, or even better use SQL Developer instead of OBIEE? OBIEE can accept almost any kind of join rule between 2 objects. And the "little detail" is that you define the join condition in the physical layer and the kind of join (inner, left outer, right outer , full outer) in the business layer (without defining any condition here as it's the physical one used). So it's just about how the tool works and not "when there is no other way" (or you end up with opaque view to join your dimensions and your facts too, so curious to know how you design start schemas in the business layer with that approach !).

S R Battula

i think we won't get situation to join dimension and Fact using Opaque view. if you see the actual query by Amol Palkar it is having join between 2 dimensions and join condition is sub sting of VAR CHAR Data type. i accept with Christian.