Community

Welcome to the first infobright.org Data Warehousing challenge!

 What follows is an example of a PoC requirement that one of our engineers had to deal with.  

Set-up:

The business system writes records to the orders (omitted here) and line item tables as usual, except, instead of putting the negotiated contract shipping rate (not mathematically a precise function of total order weight in US pounds) in the orders table, it writes a separate line item record for the shipping charge but without a total order weight or unit price / pound on the shipping line.

The Goal:

The analysts in finance and accounting want to distribute shipping charges to the individual line items on a per pound basis for product profitability analyses.  The task is to calculate the total weight of each order, determine the price per pound for shipping for that order, and apply it to the weight of each line item.

Assumptions:

Basically none.  At least no restrictions on your assumptions.  Assume any tool you want, any technique, any (automated) pre-processing.  The only thing to keep in mind is that this is for a large volume load scenario in a data warehouse so row-by-row, cursor-based processing is discouraged.  (The procedural logic is easy – just way too slow.  Use SQL.  And as few statements as possible – preferably one.)

Given:

There is exactly one line item record for every order with the productcode ‘SHIP’.  

Download the sample data necessary to solve the challenge

One lucky person will receive an Apple iPod Nano-Chromatic for coming up with the solution first!  The challenge is open from Sept 29th, through to Monday October 13th at 12:00PM EST.  The winner will be announced on Tuesday October 14th at 12:00PM EST.  Judging will be handled by our DW Specialists - David Lutz, John Kemp, and Victoria Eastwood.

How to Participate:

1. Draft up your response
2. Set the subject to 'Community Challenge #1 - Response"
3. Send it to me at the following address:   mark dot windrim at infobright dot org
4. Think of how hip you'll be with your new iPod Nano-chromatic, should you win...