Modeling Price Bands for a Vendor

We have a scenario where vendors in our system are assigned to what is called a Price Band. A Price Band represents basically the amount of average sales per month.

Example:
Band 1 = $ 0.00 - $1,999.99 sales per month
Band 2 = $2,000 - $5,999.99 sales per month
Band 3 = $6,000 - $19,999.99 sales per month
Band 4 = $20,000.00 or greater sales per month

Each band has a number of food items in it with a max price that the vendor is allowed to charge for a particular item.

Example:
Price Band 1 - Item 1 - $2
Price Band 1 - Item 2 - $3.25
Price Band 1 - Item 3 - $1.75
Price Band 2 - Item 1 - $2.50
Price Band 2 - Item 2 - $3.75
Price Band 2 - Item 3 - $2.25
Price Band 3 - Item 1 - $2.99
Price Band 3 - Item 2 - $4.09
Price Band 3 - Item 3 - $2.39

I am trying to figure out how to dimensionally model this and make sure I assign each vendor the correct surrogate key that ties them to the correct Price Band and the items in that Price Band on the date that vendor was assigned to a Price Band. I would also need to create new records if anything about the Price Band changes, such as its name or price.

Vendor 2468 was assigned to Price Band 2 on 5/1/2014. Price Band 2 went into effect on 4/1/2014. I need to assign them to the correct Price Band 2 record but Price Band 2 has multiple items for it.

I was thinking that I have a table with the following:
SK_PriceBand
PriceBandID
PriceBandName
EffectiveDate

And then another table:
SK_PriceBand
ItemNumber
Price
EffectiveDate

The vendor table would join to the first table to get the SK_PriceBand and the second table would do a lookup in the first table and write the corresponding SK_PriceBand.

So, I picture the values in the first table to be:
SK_PriceBand: 456
PriceBandID: 1
PriceBandName: Price Band 1
EffectiveDate: 2014-04-01

SK_PriceBand: 457
PriceBandID: 2
PriceBandName: Price Band 2
EffectiveDate: 2014-04-01

SK_PriceBand: 458
PriceBandID: 3
PriceBandName: Price Band 3
EffectiveDate: 2014-04-01


Then, I picture the second table as follows:
SK_PriceBand: 456
ItemNumber: 1
Price: $2.00
EffectiveDate: 2014-04-01

SK_PriceBand: 456
ItemNumber: 2
Price: $3.25
EffectiveDate: 2014-04-01

SK_PriceBand: 456
ItemNumber: 3
Price: $1.75
EffectiveDate: 2014-04-01

SK_PriceBand: 457
ItemNumber: 1
Price: $2.50
EffectiveDate: 2014-04-01

SK_PriceBand: 457
ItemNumber: 2
Price: $3.75
EffectiveDate: 2014-04-01

SK_PriceBand: 457
ItemNumber: 3
Price: $2.25
EffectiveDate: 2014-04-01

SK_PriceBand: 458
ItemNumber: 1
Price: $2.99
EffectiveDate: 2014-04-01

SK_PriceBand: 458
ItemNumber: 2
Price: $4.09
EffectiveDate: 2014-04-01

SK_PriceBand: 458
ItemNumber: 3
Price: $2.39
EffectiveDate: 2014-04-01

Again, if anything about the Price Band changes, I would need to write the appropriate records in both tables.

Does this look correct or could someone suggest a better design?

I don't get the point of what it is you are trying to do. Are you producing a price list of some sort? Is what you are trying to load pricing for the upcoming month?

It would seem to me the band is determined by prior sales, correct? So for a given period, the band is known. Why not have a simple fact table with time period, vendor, item, and band as dimensions and price as a measure (and possibly the sales amount that established the band)? You would then have a history of contractual maximum prices for vendors over time. Assuming they don't retroactively change the bands around and re-price everything, this should be fine.

Also, Price Band and Item price look like a parent-child relationship. If they are then you could create a Dimension that has both which you would link to any fact tables that had the grain of Item; and also create an "aggregated" dimension with just the Price Band information for use with any facts that had a grain of Price Band.

To basically reiterate the point that I think ngalemmo is making, what are your business/reporting requirements? The purpose of a dimensional model is to help answer business questions - you don't just convert your transactional model to a dimensional model. If you go back to your business requirements as the driver for your design then it can often take out a lot of the complexity that you see in your transactional model (though equally business requirements can turn a simple transactional model into a complex dimensional model!)

Thanks for the replies. Let me start out by saying I work for the State of Ohio WIC program and have been modeling their mart for nearly eight years but, for some reason, I am not exactly wrapping my head around this one.

Basically we have a vendor table and every vendor is assigned to a price band. The users want to compare the items they submitted for payment against the price for an item within that price band. For example, a participant went to Walmart and bought a gallon of milk for $2.99. Now, Walmart wants paid for that milk. A report is run for every vendor in a particular price band to see how much they actually charged for the gallon of milk, for which they want paid that amount, versus what a gallon of milk should be for vendors in the particular price band to which Walmart is assigned. That particular price band has a gallon of milk at $3.25, so the amount this vendor is charging WIC is within that price band so they will get paid what they asked. We might have another vendor in that same price band that is charging $3.59 and we need to identify this exception and find out why this is happening. Perhaps they are assigned to the wrong price band or they are overcharging their customers, thus overcharging the WIC program.

I need to have a price band table, or I suspect more than one, that has each price band and every item for that price band. The name of the price band can change as well as the prices of all of the items within that price band. A vendor can only belong to one price band but yet we need to compare every item for which they want paid to the items in the price band to which they have been assigned to find exceptions. This is why I thought the tables I proposed would do the job but am very open to any suggestions to make it better.

I do not understand Kimball's bridge tables and weighting factors very well and create "bridge tables" to suit my needs and they work great. Our vendor table is actually what I call a bridge table in that it joins to a number of different fact tables, mostly related to sales, but it has a bunch of surrogate keys within it that tie to dimension tables. For example, it has surrogate keys that tie to the address dimension table for the location of the store and the location of the owner, another that ties to a name dimension table for the name of the contact person as well as the name of the owner and a bunch that tie to the date table (certification date, contract begin and end date).

I hope I have better explained what I am trying to do and I sincerely thank you, and anyone else, who takes the time to read this and offer suggestions. It might give me some ideas about how to model other parts of my mart.

Banding appears as a special structure when banding is not known at the time of the fact. The fact would contain some value (measure or dimensional attribute) that is joined to a band table that contains the low and high value for the band, descriptions and any other information. If you were to implement this structure, the band table would contain item key, sales range, date range, and price.

However, it appears in your case that the band is established prior to the fact (sale), so you could handle it as a standard dimension and assign the appropriate band reference when the fact is stored.

I must not be describing this very well and perhaps price point is a better description than price band. That is just what the program calls it. Each item has a specific price and not a range of prices. Based on price surveys, a particular item should cost x amount at the big stores, should cost y at Mom and Pop stores and should cost z at rural stores. Each vendor is classified into one of those categories (there are more) and should not be charging the participants, thus the WIC program, any more than the price for that item within that price point(band).

To use my previous examples:

Price Band ID 1 - Big Stores - Box of Cereal - $2
Price Band ID 1 - Big Stores - Gallon of Milk - $3.25
Price Band ID 1 - Big Stores - 1/2 Gallon Orange Juice - $1.75

Price Band ID 2 - Mom and Pop Stores - Box of Cereal - $2.50
Price Band ID 2 - Mom and Pop Stores - Gallon of Milk - $3.75
Price Band ID 2 - Mom and Pop Stores - 1/2 Gallon Orange Juice - $2.25

Price Band ID 3 - Rural Stores - Box of Cereal - $2.99
Price Band ID 3 - Rural Stores - Gallon of Milk - $4.09
Price Band ID 3 - Rural Stores - 1/2 Gallon Orange Juice - $2.39

A vendor belongs to one of the three types of stores and I want to assign the key of the table that has that type of store. I certainly don't want to store three separate vendor records for the same vendor with a key to the price point(band) and the item. I do see the potential, though, of not necessarily storing a key representing the price point(band) in the vendor record, but instead taking the price point(band) ID of the vendor along with the item and looking in a single price point(band) dimension table for a match based on the redemption date and writing the surrogate key to the redemption fact. That would allow reporting from the price point(band) dimension table because of that surrogate as well as anything about the vendor because of the surrogate pointing back to the vendor. That might be the best way but I am still open to suggestion.

So if I have understood this correctly, your key measures/questions are what was actually paid for any item and whether it was below the relevant pricing band price? If so then I do the following:

Fact Table holding the Amount Paid measure
Flag (degenerate dim, on a junk dim or other appropriate dim) indicating how the actual price relates to the price band price . Could be a Y/N flag (for actual below price band or not) or an A(bove), E(qual) or B(elow) flag depending on how complicated you want to make it.
Vendor Dim
Any other relevant Dims (apart from price band and item)

Then we need to deal with the Price band and Item information - and I can think of various possibilities:
1. If you want to have report calculations involving the price band value and amount paid value for an item then put the Price Band value in your fact table as another measure (which is probably not going to be an additive measure)
2. If you just want to display the price band price on a report then have them as attributes on a Dim
3. You could have item and price band in a single Dim - if you think these are logically attributes of the same entity or have a hierarchical relationship
4. You can put item in one Dim and Price Band in another Dim - if they are logically different entities
Whatever Dims you have at the end of your design process you then relate them to the fact table.

You'll probably need a number of reference/lookup tables in your staging area in order to be able to populate these Dims and Fact correctly.

Hope this helps a bit

They want to be able to see the price paid and the price for that item based on the vendor's price band. They might also want a report which shows the vendor and all of the items in their price band. If I put the price brand amount in the fact for that item for that vendor, then I would have to go through the fact every time to run that report. The fact will be very large and I want to avoid that, which is why I was trying to get a surrogate key from a price band table to put in the vendor record so that the vendor table would join to a price band table. I was visualizing that price band table joining to a table of items which would have the prices and such for each item.

Vendor: Walmart
Item: Gallon of Milk
Price Charged: $2.97
Price Band Amount for Vendors in this Price Band: $3.35

Price band amount of $3.35 could change to another price at a later date and I need to keep the history of those changes and have a link between the vendor and their price band and then a link between the price band and all the items within that price band.

If you have a process that analyses sales for a month, determines the price targets for different bands, then uses these calculated targets against past sales, then produce an aggregate fact table that contains the vendor, item, target price, band and anything else that may be handy. Use this against the sales facts to perform the analysis.

If the target price is predetermined prior to recording sales, then just make it a type 2 dimension with item & band as natural keys and reference from the facts when loading.

FootyRef - when you say "I would have to go through the fact every time to run that report" I'm a bit concerned. Every report should be based on one or more fact tables - the whole dimensional model theory is based on you reporting on the measures in a fact table. As long as you design and index (and potentially create aggregates in) your model correctly then data volumes should not be an issue - unless you are looking at 100s of millions of records in which case you'd be looking at hardware solutions like Exadata, Teradata, etc.

If you want to report on the state of a vendor at any given point in time then create a (possible factless) fact table that supports this report.

In the case I am mentioning, the users want to look at the list of vendors, list the price band into which they are classified and the prices for each item within that price band.  They should not have to go through a large sales fact table to get that information.  The fact table is more geared to what the vendors charged the WIC program whereas the report shown below is the predetermined prices of items for a particular type of store (based on sales).

Example:

Vendor ID: 123
Vendor Name: Walmart
Price Band ID: 1
Price Band Name: Big Stores
Item: Box of Cereal
Price: $2.00
Item: Gallon of milk
Price: $3.25
Item: 1/2 Gallon Orange Juice
Price: $1.75

Vendor ID: 456
Vendor Name: Geri and Jerry's Corner Market
Price Band ID: 2
Price Band Name: Mom & Pop Stores
Item: Box of Cereal
Price: $2.50
Item: Gallon of Milk
Price: $3.75
Item: 1/2 Gallon Orange Juice
Price: $2.25

Vendor ID: 789
Vendor Name: Jed's Country Carryout
Price Band ID: 3
Price Band Name: Rural Stores
Item: Box of Cereal
Price: $2.99
Item: Gallon of Milk
Price: $4.09
Item: 1/2 Gallon Orange Juice
Price: $2.39

I think my design will be:
A price band/point "bridge" table with a surrogate key, price band/point ID, start date, end date and current indicator.
A price band/point detail table with the same surrogate key as in the previous table so they can join, price band/point ID, name, item, price, start date, end date and current indicator.
Vendor table with a surrogate key, vendor ID, all pertinent columns, price band/point surrogate to the original price band/point table based on the price band/point ID the vendor is assigned and the date they were assigned to it, start date, end date and current indicator.
Sales fact table with all pertinent information with a surrogate key pointing to the vendor table based on the vendor ID and sales date.

This is the only way I can see this working so that the users can get reports for:
1. Sales data for each item for a vendor (joining the vendor table to the sales fact table by the surrogate key).
2. Sales data for each item for a vendor compared to the price band/point price for each of those items (joining the vendor table to the sales fact table by the surrogate key and joining the vendor table to the price band/point table which joins to the price band/point detail table).
3. Price band/point data for each item for a vendor without having to go to the sales fact (joining the vendor table to the price band/point table which joins to the price band/point detail table without needing the sales fact table).

Any other ideas?

This is starting to look like the sales vs quota pattern. Here you would build two fact tables, one for the sales and one for quotas and then drill across to combine.

BoxesAndLines wrote:This is starting to look like the sales vs quota pattern.  Here you would build two fact tables, one for the sales and one for quotas and then drill across to combine.

While there are sales, there are no quotas. Basically, the price band/point table is what a store of that type (amount of sales) "should" be charging for each item. By the way, everyone, I have simplified what item is. A UPC is scanned at the store and that UPC belongs to a particular Category & SubCategory which is an item (Gallon of milk, box of cereal, 1/2 gallon of juice, etc.). Every price band/point row is the price band ID, the Category/SubCategory and the price has been set by a group within our program based on the type of store (amount of sales). I was just saying "Gallon of milk", "box of cereal" and "1/2 gallon of juice" for the sake of simplicity.

Sorry - I probably wasn't being clear. You have two completely separate reporting requirements: one about sales and one about vendor setup. You should be creating different fact tables for each - each designed to answer the questions relating just to its own area. I wasn't suggesting that the users should go through a large sales fact table to answer queries about a vendor setup - they should be going through the small vendor setup fact table.

Regards,

nick_white wrote:Sorry - I probably wasn't being clear. You have two completely separate reporting requirements: one about sales and one about vendor setup. You should be creating different fact tables for each - each designed to answer the questions relating just to its own area. I wasn't suggesting that the users should go through a large sales fact table to answer queries about a vendor setup - they should be going through the small vendor setup fact table.

Regards,

Why would I need to set up a fact that says Vendor 1 has all these items and these are the prices they should be charging, Vendor 2 has all these items and these are the prices they should be charging, Vendor 3 has all these items and these are the prices they should be charging, etc., when I could have one or more price band/point tables that just have the values in there once based on the date that price band/point was set and assign a vendor to a price band/point which ties them to all the items in that price band/point?

Price band 1 - Item 1 - Set Price
Price band 1 - Item 2 - Set Price
Price band 1 - Item 3 - Set Price
Price band 2 - Item 1 - Set Price
Price band 2 - Item 2 - Set Price
Price band 2 - Item 3 - Set Price
Price band 3 - Item 1 - Set Price
Price band 3 - Item 2 - Set Price
Price band 3 - Item 3 - Set Price

Every price band has all the same items and each item may or may not be the same price in one band as the other bands. A vendor can only belong to one of the price bands.

Because you said:

FootyRef wrote:In the case I am mentioning, the users want to look at the list of vendors, list the price band into which they are classified and the prices for each item within that price band.  They should not have to go through a large sales fact table to get that information.  The fact table is more geared to what the vendors charged the WIC program whereas the report shown below is the predetermined prices of items for a particular type of store (based on sales).

A fact table is an association between contexts. Some facts are transactional, others reflect business state. What you are describing is a business state. You have a vendor, that belongs to a classification (a band), that has a limit price, that is associated with an item, for a particular period of time.

The price is in context of the vendor, band, item, and time.

You can use this same table to compare against sales and report variance against the band price. Things don't get much simpler.

ngalemmo wrote:Because you said:

FootyRef wrote:In the case I am mentioning, the users want to look at the list of vendors, list the price band into which they are classified and the prices for each item within that price band.  They should not have to go through a large sales fact table to get that information.  The fact table is more geared to what the vendors charged the WIC program whereas the report shown below is the predetermined prices of items for a particular type of store (based on sales).

A fact table is an association between contexts.  Some facts are transactional, others reflect business state.  What you are describing is a business state.  You have a vendor, that belongs to a classification (a band), that has a limit price, that is associated with an item, for a particular period of time.

The price is in context of the vendor, band, item, and time.

You can use this same table to compare against sales and report variance against the band price.  Things don't get much simpler.

OK, I guess I am failing miserably at describing this with just words because what is being returned to me isn't what I am asking. I have even asked the data warehouse manager for one of, if not, the largest data warehouses that I know of in the State of Ohio government and he says "you've got a bit of a tricky one there". There is even more complexity than I have stated here but it is only related to setting the surrogate key of the price band in the vendor table because the source data does not have a date that the price band was assigned to the vendor. Because of this, I do not have a date to use to compare against the effective date and end date of the price bands themselves.

Thanks, everyone, for your responses. I blame myself for not explaining the scenario properly but I think I have the design the way I am going to do it with the sales fact, the price band bridge and the price band detail, while still needing to handle the slowly changing aspect of the price band bridge and assigning the correct record to the single vendor record.

The two facts would be what prices the vendors are selling and what prices the vendors should be selling.

BoxesAndLines wrote:The two facts would be what prices the vendors are selling and what prices the vendors should be selling.

The more I think about this, the more it seems correct, but I have not described the full complexity of the source data so I am not sure how to implement this solution. If something about a vendor changes, such as contact information or a new certification period, for example, then a new vendor record is written for the vendor. If something about a price band changes, such as the price of an item, then a new record is written for the price band. All I know about a vendor is that they are a particular price band. There are many items associated with a particular price band. I am trying to figure out how I would write fact records any time one or the other changes - either a vendor or a price band item. That is why it is so tough for me to visualize a fact table representing the prices the vendors "should" be charging.

So, have we agreed the target price relating to a band, item, and vendor is a snapshot fact? (Or, if you keep a time bound history of prices, an accumulating snapshot fact).

Then the thing to understand is a FK from a fact to a type 2 dimension reflects the state of the dimension at the time of the fact, not the state of the fact at the time of the dimension. Therefore, changes to dimensions have no effect on a fact table.

If you remove price from your band dimension and instead place it in your snapshot fact, then a change in the price is correctly reflected as a new row in the snapshot with appropriate time bounds.

The issue that remains is how to join the sales to the price limit fact given that some of the keys are type 2. There are a variety of solutions to this. One is to simply use the natural key on the dimension, another is to assign a type 1 surrogate key to the dimension as well (i.e. use the first type 2 value and persist it through all future versions of the dimension. You can then store the type 1 value as an additional key in the fact tables giving you a direct mechanism to join after aggregating the facts.