Data model for a price book

[#1]

I wonder if anyone could share their thoughts on how to design a price book. We have a priice book with SKU, price and date. The prices for some items will change multiple times per week other will remain static for years.

What is the best way to code this so that when I have invoices on any date they will always "lookup" the price published on or before that date?

I thought about making a price book for every possible date, but I think this would be very ineffecient. We have about 15000 items (price/date) and millions of invoices.

Any ideas are welcome as I have no problem in redesigning the price book structure in QV or SQL in order to support my query.

Thanks & regards,

Bruce

[#2 · Accepted solution]

From performance stamp point it's better to have price per book per date. It's not too many records for an year in your case: 15 000 * 365 = 5475000. Otherwise you can implement one of the algorithms for "Slowly changing dimensions" http://en.wikipedia.org/wiki/Slowly_changing_dimension

[rwunderlich]

It may depend on whether you wil do the "lookup" at script time or in chart. Price per day, as Nick suggested, may be feasible in either case.

The QV Cookbook
http://robwunderlich.com/Download.html

has a sample titled "Fill values in a data range using previous values" that implements a price per day model using IntervalMatch.

-Rob

[#4]

Thank you Rob & Nick it has given me some insights. I think I will set this up with interval match, it just did not seem (to me) an efficient method showing 365 days of prices for a price that change only once per year.

Regards

Bruce

[#5]

Hi Rob,

I have tried to adjust the example of Rob to my needs. In my case, I only have one single date per record - so I tried to get min/max and then add the missing dates for each product.

ORIGIN:

Product	DateOfPriceChange	Price
Apple	01.01.2012	110
Orange	02.01.2012	230
Orange	03.01.2012	250
Apple	03.01.2012	130
Apple	06.01.2012	120
Orange	06.01.2012	210
Orange	07.01.2012	215
Orange	08.01.2012	220

RESULT:

Product	DateOfPriceChange	Price
Apple	01.01.2012	110
Apple	02.01.2012	110
Apple	03.01.2012	130
Apple	04.01.2012	130
Apple	05.01.2012	130
Apple	06.01.2012	120
Apple	07.01.2012	120
Apple	08.01.2012	120
Orange	01.01.2012	0
Orange	02.01.2012	230
Orange	03.01.2012	250
Orange	04.01.2012	250
Orange	05.01.2012	250
Orange	06.01.2012	210
Orange	07.01.2012	215
Orange	08.01.2012	220

What I tried:

/* Determine the min and max dates in the data for use in building the DateTable */

tempTable:

LOAD min(PriceValidFromDate) as mindate,

    max(PriceValidFromDate) as maxdate

RESIDENT PriceBook

;

/* Write the min and max values to variables for use in the DateTable loop. */

LET vMinDate = fieldvalue('mindate',1)-1; // Subtract one for loop start

LET vMaxDate = fieldvalue('maxdate', 1);

DROP TABLE tempTable;    // Don't need this table anymore

/* Generate a DateTable for the range seen in the Rental data.

We will also create additional dimensions in the preceeding LOAD like "Month" for use in the charts.

See the QV Cookbook example "Generate date (calendar) table" for more information on generating date tables.

*/

DateTable:

LOAD *,

    month(PriceValidFromDate) as Month,

    year(PriceValidFromDate) as Year,

    QuarterName(PriceValidFromDate) as Quarter

;

LOAD date($(vMinDate) + IterNo()) as RentedDay

AUTOGENERATE 1

WHILE $(vMinDate) + IterNo() <= $(vMaxDate)    

;

/* Now that we have a table of dates, use IntervalMatch to link the DateOut & DateIn fields

to distinct dates that fall between the range of DateOut & DateIn.

I'm choosing to do a JOIN of Rentals with the generated IntervalMatch table to avoid the synthetic key

that would be created if I left this as a seperate table.

The downside to using JOIN is that we increase the frequency counts of the transaction data (fact table), but this is easily handled by

using DISTINCT where necessary in the chart expressions.

*/

LEFT JOIN(PriceBook) IntervalMatch (RentedDay) LOAD PriceValidFromDate RESIDENT PriceBook;

/*

Create a counter field we can use to sum up the days rented. This is optional, as we could count something something on the fact table

like "EquipmentID". If you count something from the fact table, make sure it's not a Key!

*/

LEFT JOIN(PriceBook) LOAD 1 as RentDayCounter AUTOGENERATE 1;

Any kind of help is kindly appreciated!

Best regards,

Sebastian