Oracle Analytics Cloud and Server

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

Data Duplicates when Joining header table to Detail table

Received Response
51
Views
8
Comments
User_IP06I
User_IP06I Rank 4 - Community Specialist

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

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

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

    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 Rank 6 - Analytics Lead

    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
    Joel Rank 8 - Analytics Strategist

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

    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
    Joel Rank 8 - Analytics Strategist

    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.

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

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

  • User_IP06I
    User_IP06I Rank 4 - Community Specialist

    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?