Combining Sales fact with Cash Receipts Fact

[Cartesianjoin]

A salesrep has the ability to issue a discount associated with an entire order and not to a specific item on the order.

The discount is entered as a customer credit to the AR / billing system matched to the orderId # and is mapped to a salesrep discount type along with a comment reason.

For me to determine the exact profit and loss per order
I will summarize the entire sales details fact to the order# (a degenerate table) and have columns for discount amt, discount type, credit, credit type (fk) salesrep discount.

We can now determine if we made money on the sale or not.

Sales Summary fact is a rollup from sales combined with the discounts from the cash receipts fact and will look as follows.

Gross sales, gross profit (qty discounts are in the sales fact table calculated as part of cost, special discounts for line items are in the sales fact table as a # column and fk to discount dimension), discount amt, discount type(fk), credit amt , credit type (fk), salesrep discount amt,
.
We have the following dimensions around this fact:

Sales Rep (employee), customer /divisional(hierachicy), time, branch, (I may have a comment dimension)


Any thoughts.

 

[STREETSMARTBI]

If I understand your question, you want to create a rollup summary sales fact table from the transactional sales fact table and add the discount column and reason code and link them to two new dimensions that only exists at the grain of the summary level but not the transactional level.

I had done the same thing a few years ago using this same technique and it work fine. Make sure you keep the orderid in the transactional fact table. You must also be sure that the orderid will not be repeated or reused or your summary rollups will be wrong.

The discount issued by the sales rep is to the grain of the entire order and not an item so you could not associate it to the sales fact tbale. You could in theory use some ratio to apporiton the entire discount over the item rows but this would improperly associate the discount to an item discount instead of an order discount which it is.

Good luck to you

 

[BIonHand]

Yes, the only reason to apportion the discount down to the item was if there was a requirement to calculate profit or loss at the item level.

John

http://www.BIonHand.com

Online Business Intelligence