# Model for these charges

## Model for these charges

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?

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?

**kangaroo**- Posts : 8

Join date : 2009-12-09

## Re: Model for these charges

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.

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.

**ngalemmo**- Posts : 3000

Join date : 2009-05-15

Location : Los Angeles

## Re: Model for these charges

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?

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?

Last edited by kangaroo on Tue Apr 20, 2010 10:30 am; edited 2 times in total (Reason for editing : Added example)

**kangaroo**- Posts : 8

Join date : 2009-12-09

## Re: Model for these charges

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.

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.

**kangaroo**- Posts : 8

Join date : 2009-12-09

## Re: Model for these charges

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.

**ngalemmo**- Posts : 3000

Join date : 2009-05-15

Location : Los Angeles

Similar topics

» Page Object Model

» FlipSize Model Contest

» FlipSize Model Contest

» Self-Generating Test Artifacts for Selenium/WebDriver.

» Jenis-jenis tema pada aqua Aquascape

» FlipSize Model Contest

» FlipSize Model Contest

» Self-Generating Test Artifacts for Selenium/WebDriver.

» Jenis-jenis tema pada aqua Aquascape

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum