Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
substring on physical or logical join in obiee 11g rpd

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
Answers
-
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.
0 -
Use Opaque view in RPD Physical Layer.
0 -
O_o that should be the LAST thing you do if standard modelling does not work.
0 -
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
0 -
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.
0 -
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.
0 -
Can you please explain how to get sub sting using CAST function in RPD.
0 -
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.
0 -
@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 !).
0 -
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.
0