0

I'm working on a datamart for our sales and marketing departments, and I've come across a modeling challenge. Our ERP stores pricing data in a few different ways:

  1. List pricing for each item
  2. A discount percentage from list pricing for a product line, either for groups of customers or for a specific account
  3. A custom price for an item, either for groups of customers or for a specific account

The Pricing department primarily uses this data operationally, not analytically. For example, they generate reports for customers ("What special pricing / discount %s do I have?") and identify which items / item groups need to be changed when they engage in a new pricing strategy.

Pricing changes happen somewhat regularly on a small scale, usually on a customer-by-customer or item-by-item basis. Infrequently, there are large-scale adjustments to list pricing and group pricing (discounts and individual items) in addition to the customer-level discounts.

My head has been in creating one or more fact tables to represent this process. Unfortunately, there's no pre-existing business key for pricing. There's also no specific "transaction date," since the ERP doesn't (accurately) maintain records of when pricing is changed. Essentially, a "pricing event" is going to be a combination of:

  1. Effective date
  2. End date
  3. Item OR product line
  4. (Not required for list price) customer or customer group
  5. A price amount OR discount percentage

A single fact table seems problematic in that I'm going to have to deal with a lot of invalid combinations of dimensions and facts. First, a record will never have both a non-NULL price amount and a non-NULL discount percentage; pricing events are either-or. Second, only certain combinations of dimensions are valid for each fact. For example, a discount percentage will only ever have a product line, not an individual item.

Does it make sense to model pricing as a fact table in the first place? If so, how many tables should I be considering? My intuition is to use at least two, one for the percentages and one for the price amounts, but this still leaves a problem where each record will either have a valid customer group OR a valid customer (or neither, for list prices), since we need to maintain customer-specific pricing separate from any group pricing that customer might have.

cisenb
  • 23
  • 1
  • 7

1 Answers1

1

You may need to keep them both as attributes and as facts.

  1. The price a certain item was sold for is a fact. When you multiply it by the quantity sold it's actually an additive measure. So, keep it in the fact table. Total discount applied is also additive, I'd keep it. You can later query "how much was discounted in 2019 per customer", which would be much harder to achieve without those facts.

  2. But if you also need to query things like "what's the discount customer X is on", then you should also keep that as an attribute of the customer dimension, and treat it as a type II dimension, so as to keep discount history. If you know when a certain discount was applied, great, if not take the 1st sale as the start date and you won't be too far off.

  3. Maybe the list price can also be kept as an attribute of product or product line in a dimension, but only if they don't change too often; but if most customers get discounts anyway that would be of limited use.

nsousa
  • 4,448
  • 1
  • 10
  • 15
  • Extended price is actually kept in the Orders and Invoices fact tables, so we can calculate things like average selling price. The problem with keeping price in the Customer table is that price is a combination of dimensional attributes: a customer's price for a specific item, a customer's discount for a particular product line, etc. The same applies to the Item table, with the exception of List Price, since you need pricing per customer or per customer group. – cisenb Nov 20 '19 at 16:24
  • if discounts and prices are customer and product specific, then they should be kept in the fact table. it should only be kept in the customer table if its values depend on customer alone and no other factors. The alternative would be a factless fact table with combinations customer/product and their respective prices, effective dates, discounts, etc. analysis may become complicated, though. – nsousa Nov 21 '19 at 10:20