240 likes | 244 Views
DW (III): The Wal-mart Example. Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari. Outline. Wal-mart case Evolution of its DW Several types of DW models Apply the concept at Wal-mart What kind of information can the Walmart DW produce?.
E N D
DW (III): The Wal-mart Example Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari
Outline • Wal-mart case • Evolution of its DW • Several types of DW models • Apply the concept at Wal-mart • What kind of information can the Walmart DW produce?
Objective: to see the world’s largest & most successful DW in action • How do they get there • What information can the DW produce • How does it impact operation
Wal-mart: Background • Annual revenue 150B • 4,000 stores, 800,000 employees • High growth but faces stiff competition • Realize that they have to manage “one store at a time” to meet divergent needs • Data on averages are not enough
Wal-mart: Background • Randy Mott (CIO, Wal-mart): very detailed data, not summary data, is the key to success • Information support enables “Every store to operate as a local store.”
Walmart: Background • Leader in DW and gain huge competitive advantage by leveraging its IS • E.g. 96% percentage in-stock, one of highest in industry • 1990 : ~700G POS data • 1999: 101 Terabytes on NCR’s Teradata • 50+ applications running
The Wal-mart Case • Evolution leading up to DW: 1970 -- 2000 Automation Controlling Informative
Wal-mart • Automation – e.g. scanning bar codes • Controlling – e.g. inventory control • Informative – e.g. simple summary statistics from OLTP >> EIS (executive information system)
Wal-mart • Pre-DW : request directly from DB could cause performance problem (e.g. order entry delayed) PC Use SQL to extract information Operational DB
Wal-mart • Process laborious • E.g. planning group BIM (basic inventory management) : create SQL, download files in small files, (wait), import into spreadsheet, and perform forecast. • Must overcome data quality problem (e.g. data consistency across platforms)
Wal-mart • First creation of DW (around 1990) DW Orders Article DSS Receiving
5 Types of DW • DW • Data mart • Enterprise DW • Operational Data Store (ODS) • Federated DW Historically, Walmart has developed DW, Dmart & EDW
Data mart • POS basket-level analysis data mart • DB2>VM/SQL>Teradata • 1.2 B records in POS • Different data marts exist before Enterprise DW for historical reasons
Data mart Data marts DW Orders store Article dept Receiving
Data mart • Pros • Accommodate different information needs • E.g. manufacturing and distributing divisions • Restrict information access (privacy) • Enhance performance by taking load off DW • Cons • Consistency • No cross boundary information access
Enterprise DW • Company wide DW • 99% of Wal-mart’s data captured in EDW • Maintain 65 weeks of data • Closed loop concept • E.g. new article allocation. Use similar article sales data > analyze and estimate size > feed back into purchasing order (transaction) system
Wal-mart • DW is a DSS, not a functional system • E.g. merchandise manager review a historic chart helps her identify inventory problem • Better replenishing and allocation lead to higher profit
Wal-mart • Retail enterprise DW (p. 27) • Multi-function decision support • Cyclical feedback loop • Planning • Allocation • Dynamic purchasing • Replensishment • Category management
Wal-mart • More sophisticated analysis when customer data are available • E.g. Sam’s club • Data as much as Wal-mart’s • Number of customers only a quarter of Wal-mart’s
Example of DW app: Store Sales Analyses • Examples of reports • Example 1: Store sales report of region 2 district 10 week 9634 (8 stores) • Store size • Retail sales • Customer count • Average customer transaction
Report Example 1 • Data elements • Store information • Store ID, name/address, ZIP, manager, sqft, open date, remodel date, #fuel pumps… • Daily store sales information • Store identifer, year, week, date, sales, customer count (note: detail is down to day), fuel sales, labor costs, …
Example 2 Report • Comparable store sales • Extend on report 1, include sales from last year and figures of comparison (% increase/decrease on each attribute) • New data elements • Data from comparable weeks from last year (must therefore keep at least 53 weeks of data)
Other store level reports • Planned sales versus actual sales report • Flash sales report (by the hour!) • E.g., wine sales usually increase after 5pm. If one store consistently showed a decline after 5, then there could be problems such as not stocking properly • Departmental sales report
Summary • Wal-mart Case study • Evolution of DW at Wal-mart • Types of DW • Examples of DW applications