Modeling invoice detail with rebate detail

We currently have a fact table at the line item level with the qty and $$ sold and another fact table with rebate amount for each line item

Some line item could have many rebates with the rebate amount.

Could look like this

Invoice number customer sequence product qty $ rebate sequence rebate code amount
123 1000 001 0001 10 125 001 10 5
002 20 6

002 0002 20 200 001 20 5

003 0003 30 300 no rebate

004 0001 1 10 001 10 4

The result to have is total qty and $ for customer with rebate code


customer qty $$ rebate code amount
1000 61 635 10 9
20 11


The problem now is that all qty and amount are double count because the two fact tables are related with the line item sequence

What other design could resolve that double counting ?

It looks like your design is fine - I think you just need to adjust your presentation. Do I understand correctly that it's the joined query that creates the double counting, because one detail line can have multiple rebates?

I need a bit more detail about the structure of the tables (and the relationship between them). Your initial sample data set isn't formatted such that I can understand it.

Dan

thanks for the answer,

I thought that was formatted so

The first fact table has a compound key on invoice id and entry sequence like this

invoice 123 K
product 100
sequence 001 K
amount 10$

invoice 123 K
product 200
sequence 002 K
amount 5$ ....

the other fact table is only for rebate detail and has compound key also on

invoice id
entry sequence
rebate sequence

so for each sequence entry you may have one or more sequence rebate
for each entry sequence, rebate sequence start at 001

invoice 123 K
entry sequence 001 K
rebate sequence 001 K
rebate code 25
amount 100

invoice 123 K
entry sequence 001 K
rebate sequence 002 K
rebate code 30
amount 200

invoice 123 K
entry sequence 002 K
rebate sequence 001 K
rebate code 30
amount 300

When you place sale amount and rebate amount, the join between two fact table are perform on invoice id and entry sequence so because an entry sequence may have more than one rebate sequence to total rebate is fine but the product item is equal to X time the number of rebate sequence

for the above example, the product on sequence 001 for 10$ will become 20$ because of there is 2 sequences rebate