substring on physical or logical join in obiee 11g rpd — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

substring on physical or logical join in obiee 11g rpd

Received Response
342
Views
12
Comments
Amol Palkar
Amol Palkar Rank 3 - Community Apprentice
edited Aug 13, 2024 6:06PM in Oracle Analytics Cloud and Server

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

«1

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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.

    pastedImage_0.png

    pastedImage_1.png

  • S R Battula
    S R Battula Rank 4 - Community Specialist

    Use Opaque view in RPD Physical Layer.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    S R Battula Rank 4 - Community Specialist

    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]
    [Deleted User] Rank 2 - Community Beginner 
    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
    S R Battula Rank 4 - Community Specialist

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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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:

    pastedImage_0.png

    "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
    Gianni Ceresa Mod

    @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
    S R Battula Rank 4 - Community Specialist

    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.