Assignment 4 - Mini Case for Practice Problems in Module 3/Lecture 3 This mini case study provides practice and preparation for the concepts that you learned in module 3. You will get to apply most of the concepts from module 3 and some of the concepts from module 2. This mini case study contains two data sources with sample data along with a statement of business needs. Using the data sources and business needs, you will specify a dimensional model with dimensions, measures, and grain, create a schema design for the data warehouse that integrates the data sources, identify summarizability problems in the design, and populate data warehouse tables from sample rows in the data sources. Data Sources The case study involves two data sources for a retail firm. The Purchase database supports purchase transactions to replenish retail inventory. A purchase consists of a heading with the purchase number, date, payment method, delivery date, and supplier. A purchase contains a collection of products with the quantity and unit cost recorded on a purchase line along with links to the product and purchase heading. Each product has one preferred supplier. However, a purchase can use a non-preferred supplier if necessary. Individual stores of the retail firm also maintain an inventory of custom products ordered from local suppliers. These products are ordered through the purchase spreadsheets for custom products. Inventory practices for custom products are informal. New products are typically purchased when the manager senses new demand for local items. The ERD in Figure 1 supports the purchase database. Tables 1 to 4 show sample data for the tables in the purchases database. The supply purchase spreadsheet (Table 5) contains a Mini Case for Practice Problems in Module 3/Lecture 3 sample of purchases of custom products from local suppliers. The Stock column in the spreadsheet indicates the quantity in stock at the time of purchase. Supplier Purchase SuppNo SuppName SuppPhone SuppEmail SuppDisc PurchFrom PrefSupp PurchNo PurchDate PurchPayMethod PurchDelDate Contains Product ProdNo ProdName ProdQOH ProdPrice ProdNextShipDate PurchLine BoughtOn PLQty PLUnitCost Figure 1: ERD for Retail Purchase Operations Table 1: Sample Data for the Supplier Table SuppNo S2029929 S3399214 S4290202 S4298800 S4420948 S5095332 SuppName ColorMeg, Inc. Connex Ethlite Intersafe UV Components Cybercx SuppEmail custrel@colormeg.com help@connex.com ordering@ethlite.com orderdesk@intersafe.com custserv@uvcomponents.com orderhelp@cybercx.com SuppPhone SuppDisc (720) 444-1231 0.10 (206) 432-1142 0.12 (303) 213-2234 0.05 (512) 443-2215 0.10 (303) 321-0432 0.08 (212) 324-5683 0.00 Table 2: Sample Data for the Product Table ProdNo P0036566 P0036577 P1114590 P1412138 P1445671 P1556678 P3455443 P4200344 ProdName 17 inch Color Monitor 19 inch Color Monitor R3000 Color Laser Printer 10 Foot Printer Cable 8-Outlet Surge Protector CVP Ink Jet Color Printer Color Ink Jet Cartridge 36-Bit Color Scanner SuppNo ProdQOH ProdPrice ProdNextShipDate S2029929 12 $169.00 02/20/2013 S2029929 10 $319.00 02/20/2013 S3399214 5 $699.00 01/22/2013 S4290202 100 $12.00 S4298800 33 $14.99 S3399214 8 $99.00 01/22/2013 S3399214 24 $38.00 01/22/2013 S4420948 16 $199.99 01/29/2013 2 Mini Case for Practice Problems in Module 3/Lecture 3 P6677900 Black Ink Jet Cartridge P9995676 Battery Back-up System S3399214 S5095332 44 12 $25.69 $89.00 02/01/2013 Table 3: Sample Data for the Purchase Table PurchNo P2224040 P2345877 P3249952 P3854432 P9855443 PurchDate 02/03/2013 02/03/2013 02/04/2013 02/03/2013 02/07/2013 SuppNo S2029929 S5095332 S3399214 S4290202 S4420948 PurchPayMethod PurchDelDate Credit 02/08/2013 PO 02/11/2013 PO 02/09/2013 PO 02/08/2013 PO 02/15/2013 Table 4: Sample Data for the PurchLine Table PurchNo P2224040 P2224040 P2345877 P3249952 P3249952 P3249952 P3249952 P3854432 P9855443 ProdNo P0036566 P0036577 P9995676 P1114590 P1556678 P3455443 P6677900 P1412138 P4200344 PLQty 10 10 10 15 10 25 25 50 15 PLUnitCost $100.00 $200.00 $45.00 $450.00 $50.00 $21.95 $12.50 $6.50 $99.00 Table 5: Sample Spreadsheet Data for Custom Product Purchases ProdCode ProdDesc CPC1 Souvenir 1 CPC2 Souvenir 2 CPC3 Souvenir 3 Supp Omart Smart Pmart Qty Stock Unit Price PurchDate Amount 20 1 $2.00 13-Feb-2014 $40.00 10 2 $3.50 14-Feb-2014 $35.00 20 0 $1.50 11-Feb-2014 $30.00 Data source size statistics To compute grain size, you should use these estimates about cardinalities of tables and unique values of some columns. • Product rows: 1,000 • Supplier rows: 100 • Purchase rows: 100,000 per year • PurchLine rows: 500,000 per year • Spreadsheet rows: 1,000 per month; new spreadsheet each month 3 Mini Case for Practice Problems in Module 3/Lecture 3 • Unique products in a spreadsheet for one year: 100 • Unique suppliers in a spreadsheet for one year: 20 Business Needs The main purpose of the data warehouse is to track inventory balances over time. Inventory balances are a type of snapshot. Snapshots are typical in applications in which balances are involved, such as account balances in financial services, enrollment in courses, reservations in hospitality and travel, and head count in personnel management. Snapshots cannot be aggregated over time correctly. Summing quantities and values over time is not meaningful. The basic values for inventory tracking are quantity on hand and inventory value. Inventory valuation can be complex as many accounting methods exist to value inventory. For this case, the purchase price or unit cost of the inventory can be used for valuation. The data warehouse should support detailed tracking of inventory to the individual product, purchased by date, and supplier. Here are typical computations for analyzing and tracking inventory balances using the quantity on hand and inventory value: • The average quantities and stock values in each time period • The opening and closing balances for each time period • The change in inventory levels between consecutive periods and parallel periods • The minimum and maximum inventory levels in a time period • The relative contribution of the stocked item to the overall stock value 4 Mini Case for Practice Problems in Module 3/Lecture 3 Problems 1. You should identify dimensions, map dimensions to data sources, and specify dimension hierarchies. For each dimension, you should identify its data sources and attributes in each data source. For hierarchical dimensions, you should indicate the levels from broad to narrow. 2. You should specify measures, related data sources, and measure aggregation properties. 3. Identify the grain in your dimensional design using the business needs as a guideline. You should then indicate relative storage requirements for the grain using the statistics for the data sources. Using the cardinality estimates provided, you should determine either the fact table size or sparsity and then compute the unknown grain size variable. For example, you should compute sparsity if the fact table size is given. 4. Extend your analysis to design a star schema (or variation) to support inventory analysis. For each table, you should define the table name, primary key, and columns. You do not need to write complete CREATE TABLE statements. 5. Identify summarizability problems in your star schema and indicate preferred resolutions of the summarizability problems. For incomplete dimension-fact relationships, you should also indicate if columns in a dimension table allow null values. 6. You should populate your data warehouse tables based on the data in the sample tables and spreadsheet. You do not need to write SQL INSERT statements or insert the data into your tables. You can just show table listings in your solution. You should indicate mappings from data sources into tables. For example, a mapping may involve generating new primary key values for a data warehouse table or using a default value for a missing value. 5
Purchase answer to see full attachment