Oracle Business Intelligence

Products Banner

Data Duplicates when Joining header table to Detail table

Received Response
45
Views
8
Comments

Hello,

I have a Fact Header table and Fact detail table from which I've to create a single report. Whenever I Join the two tables the data is getting duplicated. Header amount is nothing but the sum of  detail amount by ID. I've the data as shown in the tables below. Please Suggest me how to model this in OBIEE and can you please show me how I can write a SQL query where the data won't be duplicated.

Thanks.

IDHeader amount
1300

IDLine numberDetail amount
11100
12200

Answers

  • You are aware, I hope, that OBI works with models and not with SQL queries?

    Also do you have SampleApp? I contains header/detail examplea in a working RPD

  • User_IP06I
    User_IP06I ✭✭✭

    Yes, I'm aware that OBIEE doesn't work with SQL query. I don't have the SampleApp unfortunately.

  • Martin van Donselaar
    Martin van Donselaar ✭✭✭✭✭

    Of course, Header Amount will duplicate if you include it in your analysis and you just joined together the header and detail table in the physical layer, that is basic relational datamodeling concepts.

    There should just be one fact, not Fact header and detail, If you are quering a operational (relational) datamodel then you should model both tables into a single logical fact.

    You should only include amounts from the lowest detail level (the grain) of the fact, in this case from the little information that you gave us I assume this is Detail Amount.

  • Joel Acha
    Joel Acha ✭✭✭✭✭

    To add on to @Martin van Donselaar, you need a single logical fact with the physical detail and header tables as logical table sources as well as a logical dimension to setup  a hierarchy. This should make the logical fact aggregate aware and at query time dependent on the dimension attributes used (in your case, ID and Line Number) it should determine which physical table is best to retrieve the data.

  • User_IP06I
    User_IP06I ✭✭✭

    Thanks for the response. Let me summarize what I understood from your answer.

    1. Create a Logical fact with Multiple LTS. i.e. one LTS is Detail Fact and the other is Header fact.

    2. Create Logical dimensions for both Header and Detail table attributes. Then set the Hierarchy for them for respective Measure columns.

    And the question I have is

    1. Do I have to create single logical dimension for both tables or separate ones?

  • Joel Acha
    Joel Acha ✭✭✭✭✭

    A single logical table with LTSs for the header and detail tables. The logical dimension will have a hierarchy with at least 3 levels:

    1. total
    2. header
    3. detail

    You’ll then need to set the levels correctly for the LTSs of your logical fact.

  • Exactly. And the key part is: ALL attributes which aren't used for measure calculations go into the degenerate dimension.

  • User_IP06I
    User_IP06I ✭✭✭

    Forgive my ignorance. I'm kind of confused.

    1. Do I have to Join the two facts in Physical layers? ( I suppose Yes)

    2. Do I have to Have all the measures in Logical fact and Join that with a Logical table which has header and detail as LTS. Then create a logical dimension with Total, Header and Detail levels and assign the respective measures?