Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combining Sales fact with Cash Receipts Fact

Combining Sales fact with Cash Receipts Fact

Combining Sales fact with Cash Receipts Fact

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.

RE: Combining Sales fact with Cash Receipts Fact

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


RE: Combining Sales fact with Cash Receipts Fact

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.

Online Business Intelligence

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close