hachim haddouti, adv. dbms & dw csc5301, ch4 adv. dbms & dw ch 4 hachim haddouti

Adv. DBMS & DW

CH 4

Hachim Haddouti

Shipments: The Most Powerful Database

• shipment invoice -- accompanies each shipment• Each line item of shipment invoice corresponds to a SKU

• The most powerful database

• the one place where we can see all products, customers, conracts/deals, discounts, money owed, variable/fixed costs, customer satisfaction...

Design Principle: «For any company that ships products to customers, or performs a similar function, the best place to start a data warehouse is with shipments. »

What are the Dimensions, Grain?

• Grain: each shipments invoice line item

• Dimensions: – time, – product, – ship-to, – ship-from, – ship mode, – deal.

• drill across -- from fact table to fact table (ch 5)

The ship-to dimension• one record for each possible customer location; 10K to 10M (e.g. shipping to

grocery store chains)• multiple hierarchies – location (city, County, State), organizational (Bill-to,

customer division, dept., region, customer corporate), sales organization (sales team, sales district, sales region)

– DP: «Any dimension whose records define a point in space automatically is capable of supporting multiple independent geographic hierarchies ».

– DP: «It is natural and common, especially for customer-oriented dimensions, for a dimension to simultaneously support multiple independent hierarchies. Drilling up and drilling down within each of these hierarchies must be supported in a data warehouse. »

– DP: »Two loosely correlated attributes that have a many-to-many relationship can be modeled either as a single compound dimension, such as the ship-to-bill-to example, or they can be modeled as separate dimensions, at the designer’s discretion. »

The deal dimension

• Similar to Promotion Dim in ch2, but not consumer promotion, also called Contract dim.

• describes the full combination of terms, allowances, and incentives that pertain to the particular invoice line item (assuming they are well correlated).

• If uncorrelated, then maybe split into separate dimensions

• Issues of user convenience and administrative expenditure to decide whether only one dimension or multiple dimensions (think also of the size of fact table by increasing the no of keys)

Ship From, Ship Mode

Ship from dim: • simple dim; one record for manufacturer warehouse or

shipping location

The ship mode dimension• new ways: direct store delivery, cross docking, back

hauling, custom pallet creation

Profit and Loss (P&L)

• top line (undiscounted value) to bottom line (after discounts, allowances, costs)

• This list of revenues & costs is called Profit and Loss (P&L)• Elements of P&L have the following interpretation

– Quantity shipped (comparaison but take care about rate conversion)– extended list price (unit rate x qty shipped)– extended gross invoice price (usually the same as above)– extended allowances -- total of off invoice allowances –for

promotional/deal related allowances– extended discounts – amount subtracted for volume or payment terms

• DP: «A significant effort should be made to allocate allowances, discounts, and activity-related components of cost down to the line item (i.e, product) level in businesses that ship products to their customers. »

Profit and Loss cont.

• Elements of P&L have the following interpretation cont.– Exended net invoice price -- payment amount before tax– extended fixed manufacturing cost (not presented to customer)– extended variable manufacturing cost -- activity based, or could

be arbitrarily set– extended storage cost – cost for storage prior to being

shipped to the customer– extended distribution cost – transportation cost from

manufacture to shipment point, eg. Freight cost– accrued deal cost – estimated cost for special payments– contribution (extended net invoice – final calculation of

all costs above), also called Margin, Pocket Profit, Brand Available

Customer satisfaction, Invoice no

• simple measures of delivery -- on time, complete, damage free (1 or 0)

• Collected by using EDI link to customer’ s delivery dock

• The invoice number in the fatc table is a …………. Dimension