Categories
- All Categories
- 101 Oracle Analytics News
- 9 Oracle Analytics Videos
- 14.3K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 56 Oracle Analytics Trainings
- 60 Oracle Analytics Data Visualizations Gallery
- 4 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Data Duplicates when Joining header table to Detail table

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.
ID | Header amount |
---|---|
1 | 300 |
ID | Line number | Detail amount |
---|---|---|
1 | 1 | 100 |
1 | 2 | 200 |
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
0 -
Yes, I'm aware that OBIEE doesn't work with SQL query. I don't have the SampleApp unfortunately.
0 -
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.
0 -
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.
0 -
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?
0 -
A single logical table with LTSs for the header and detail tables. The logical dimension will have a hierarchy with at least 3 levels:
- total
- header
- detail
You’ll then need to set the levels correctly for the LTSs of your logical fact.
0 -
Exactly. And the key part is: ALL attributes which aren't used for measure calculations go into the degenerate dimension.
0 -
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?
0