1 / 9

Adv. DBMS & DW CH 4

Adv. DBMS & DW CH 4. Hachim Haddouti. Shipments: The Most Powerful Database. shipment invoice -- accompanies each shipment Each line item of shipment invoice correspond s to a SKU The most powerful database

rory
Download Presentation

Adv. DBMS & DW CH 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Adv. DBMS & DWCH 4 Hachim Haddouti Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  2. 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. » Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  3. 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) Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  4. 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. » Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  5. 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) Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  6. 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 Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  7. 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 ratex 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. » Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  8. 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 Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

  9. 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 Hachim Haddouti, adv. DBMS & DW CSC5301, Ch4

More Related