How to populate a logical column from two physical column? — Oracle Analytics

Oracle Analytics Cloud and Server

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

How to populate a logical column from two physical column?

Received Response
51
Views
11
Comments
Iliaa
Iliaa Rank 3 - Community Apprentice

In BMM layer of my rpd I want to create a logical table with two logical column.

The first logical column should be populated from data of the two physical column of two separated table, say from physical-column0 and physical-column1.

and the second logical column should have a binary value that determines the physical column from which the value of the first column of the current row is populated.

For example, if the first column of row x is derived from physical-table1 then the value of the second logical column should be 1, and 0 otherwise.

How can I do it?

«1

Answers

  • For your logical table you add 2 logical table source, one for each of the physical table. In the column definition you hardcode the value of the 2nd column for both the LTS (so you have your source) and then you define a fragmentation rule in your 2 LTS based on the value of the hardcoded column.

  • Iliaa
    Iliaa Rank 3 - Community Apprentice

    Gianni, thank you for the answer,

    I'm new to obiee and your answer is hard for me to understand

    The first part of your answer, i.e. hardcoding the 2nd logical column for each LTS, seems to be easy, but the second part, i.e. defining a fragmentation rule for each LTS to populating the 1st logical column, is very confusing for me. To define fragmentation I read this page but I think my problem is somewhat differ from it.

    Could you please elaborate your answer and give me an in detail explanation?

    p.s. To be as clear as possible, I demonstrate my problem bellow.

    Table-0:

    ID     X     ...

    ----------------------------------

    1        b     ...

    3         c     ...

    Table-1:

    ID     Y     ...

    ----------------------------------

    2       p     ...

    5       f     ...

    Logical Table:

    Col1     Col2

    1               0

    3               0

    2               1

    5               1

  • Have a look at this (same as your case but with different names ...) :

    Column B (your "Col2") is the hardcoded value, I used 'table 1' and 'table 2' but you can use 0 and 1 as numbers.

    Column A is your "Col1", each logical table source point to its own column.

    For each logical table source you set a rule for "Fragmentation content" and you check the checkbox below. The rule use the value of the hardcoded column "Col2".

    Capture.PNG

    Capture2.PNG

    Capture1.PNG

    Capture3.PNG

    ( You also find it here:  2-in-1 : RPD Magic and Hyperion Planning "Adapter" slide 41 )

  • Iliaa
    Iliaa Rank 3 - Community Apprentice

    Great answer. It works like a charm

    I greatly appreciate your help, especially for attaching the images.

    From your answer it seems that there are many more hidden features than what are explained in oracle official tutorial for creating repository.

    If there exists a good tutorial, could you please introduce it to me?

    ... and also I need best practices about creating analyses and dashboards, but unfortunately oracle official tutorial is very poor in this context.

    Kind Regards,

    Ali

  • Hi Ali,

    The official tutorial cover the basic basic things, but the tool is quite big and is extremely powerful in generating queries, so you will never have a tutorial covering all the topics.

    I would say that for all these things you better start following the main blogs about OBIEE and ideally attend events with a BI track as well (Kscope16 end of June for example).

    Best practice is a complex things, mainly because any "best practice" is just a possible way and you are not supposed to apply it but challenge your own way with the "best practice" one and in case improve your own way...

  • Iliaa
    Iliaa Rank 3 - Community Apprentice

    Thanks a lot.

  • Iliaa
    Iliaa Rank 3 - Community Apprentice

    Dear Gianni,

    Recently, I've asked two questions in this community but I didn't get answer for them.

    Since these questions are important for me, I've notified you from this thread to help

    me again, please. Unfortunately, it seems that there is no other way in this community

    to contact or notify someone.

    The questions: question1, question2

    Thanks in advanced,

    Ali

  • Hi Ali,

    For your "question2" keep answering to the thread as you have the right people on it to give you the answer (rmoff for example).

  • Iliaa
    Iliaa Rank 3 - Community Apprentice

    OK, but he didn't responded to my reply. Maybe I should to notify him again.

    Thanks

  • Actually it's you that still need to answer 2 of his questions: expected workload and the kind of reports you expect (as you have the definition in the reply of Manoj).

    And also keep in mind we all have a life and a work next to the forum, so having to wait a day for an answer isn't that much in the end (or the only other option if you need an answer in the 5 minutes is to hire a consultant who will be paid to give you the answer).