Model for these charges

[kangaroo]

How would I model the following order system?

Base Daily 4 x $50 | $200
Extra Hourly 5 x $25 | $125
Extra Weekly 3 x $300 | $900
Discount A | -$20
Discount B Percentage 5% of base @ $200 = -$10
Extra Option Z | $600
Extra Option Q | $100
Tax | $30
Fee | $20
Fee | $10
Total | $1955

The part I'm having a difficult time grasping is that there can be multiple discounts that can be a percentage off or a fixed dollar amount off. Also, the product is structured by hourly, daily, weekly. There is also extra hour, extra day, extra weekly "rates" that the consumer purchases. Any ideas of what this order fact and product dimension should look like?

[ngalemmo]

All the major ERP systems have the notion of a line distribution table (SAP refers to them as 'pricing components', Oracle uses another term). Basically, they are accounting breakdowns of what went into the price shown on an order line.

In a dimensional DW that has such information I would create two fact tables to record sales data. One would be at the traditional order line level and the other at the line distribution level. The latter table would have additional dimensions, such as one that identifies what piece of the price this is, what revenue account gets hit and so on. In this situation, the order line fact is essentially a summary of the line distribution fact and each fact table would be used separately to support different kinds of reporting and analysis.

If each item you listed represents a line on the sale, then you don't need the second table, as each, presumably, would have a charge code (aka a 'product id') that identifies what the charge/discount is. You would not store total, as that would be the simple sum of the lines.

[kangaroo]

Thanks a lot. You're a great help on these forums.

After looking at it some more, each item does represent a line of the sale, so I think it would make sense to have only one fact. The total amount is then the sum of the lines like you said.

Now, I'm looking at the medical bill example in the DWT book and noticed a "paid amount" field in the billing fact table. I would like to do something similar, but I'm not quite sure it makes sense, since payments are received for the total bill and not each individual line item. How would this work in either example? Take this example for instance.

Invoice ID (DD)
Product Key (PK)
Sales Quantity
Sales Dollar Amount
Commission Dollar Amount

If I add:
Last Payment Date
Paid Amount

Would that be a correct model?

What would I do if the user pays the full amount? Do I simply update Paid Amount so that it is equal to the Sales Dollar Amount for each line item of the order/invoice? Or would this be the actual total payment. What if the user pays only $100 (of the total bill for $210) and the sales dollar amount for the three line items are $75, $75, $60? What would I do for the line item where the product is a discount and the sales dollar amount is actually a negative dollar amount?

[kangaroo]

I guess my options are then to either (1) add the payment fields to the current line item fact table with a new transaction type field or (2) create a new fact table for payments.

If I do #2, would it matter that the grain of this fact table would be different than the grain of the line item fact table? The grain would be each payment received (or refund issued) for the invoice instead of the invoice line item level.

[ngalemmo]

I would go with #2. In cases were payments are received against an invoice, you always go with option #2, a separate fact table to record payments. The only time I would consider option 1 is if both the sale and payment occur at the same time and if you can reasonably allocate payment to the line. But those situations are typically retail sales where payment is always in full, so payment amount is superfluous, usually you just wind up with a dimension to cover payment method and, possibly, a degenerate dimension to capture credit card number if needed and applicable.

[Sponsored content]