hi all,
I am new to olap and working on my first ever OLAP project with a
manufacturing company. I am working on a sales cube, the measures of which
will come from Invoice table. There are two type of invoices, customer
invoice and second sale invoice. The difference is when due to some reason
the customer do not wish to accept 'all' or 'some of the' gods, some or all
can be sent to other customer and a second invoice is made on this customer.
The problem is while doing this transfer the rate can be different.
e.g.
the company has made
invoice 'inv_1' to 'cust_x' with
'prod_1' @ Rs '100/item' and no of units are 10, so the prod value becomes
1000.
after including taxes etc the invoice value becomes say 1500.

now 'cust_x' has some problem acepting the goods and he requests to route it
to other customer. so company asks 'cust_y' and he agrees with different rate
of the product. Now company prepares second sale invoice
invoice 'inv__s_1' to 'cust_y' with
'prod_1' @ Rs '90/item' and no of units are 10, so the prod value becomes 900.
after including taxes etc the invoice value becomes say 1400.

here, both the invoices are stored in the same table. I have to keep them in
different table (i think,) to show proper results in cube. my problem is,
do i need to have 2 fact tables? i.e. f_sales and f_second_sales
how can i calculate the actual sale [sale value and second sale value]
There is a possibility that the only some of the items are included in the
second sale
e.g. cust_x keeps 5 items and rest are routed to cust_y with different rates
in such cases i have to calculate sale value like this
sale of cust_x = actual items kept with him * rate [5 * 100]
sale of cust_y = actual items kept with him * rate [5 * 90]
actual sale againt invoice 'inv_1' [original customer inv] = sale of cust_x
+ sale of cust_y
Both has almost same attributes but because of the calculation I can't keep
them in a single table as this would show repeated entries for a single
invoice and resulting figures wioll b wrong.
I am mainly interested in the calculation part and how to keep this details
in fact table.
I'll apreciate suggestions to work around this problem.
I hope to get some good suggestions from u guys and save me from failing in
my first ever project.

kshitij.