rn

E8.3 Consider the following scenario involving Big Z Inc., an automotive products wholesaler. Big Z Inc. wants to create the analytical database (data warehouse) to analyze its order quantities. The two available data sources, Source 1 and Source 2, are described below. Source 1 The Big Z Inc. Human Resources Department Table, shown in Figure 8.47. Source 2 The Big Z Inc. Orders Database, shown in Figure 8.48. The data warehouse has to enable an analysis of order quantities by: date, including: full date day of week day of month month quarter year time product, including: product ID product name product type product supplier name customer, including: customer ID customer name customer type customer zip depot, including: depot ID depot size depot zip order clerk, including: order clerk id order clerk name order clerk title order clerk education level order clerk year of hire E8.3a Based on the sources and requirements listed above, create a dimensional model that will be used for the dimensionally modeled data warehouse for Big Z Inc. E8.3b Using data from Figures 8.47 and 8.48, illustrate how the tables in the dimensional model created in E8.3a would be populated. HUMAN RESOURCES DEPARTMENT TABLE (EMPLOYEE DATA) Education Level YearOfHire EmployeelD Name Antonio Wesley Lilly OC1 OC2 OC3 Title Order Clerk Order Clerk Order Clerk High School College College FIGURE 8.47 Source 1: The Big Z Inc. Human Resources Department Table. 2001 2005 2005 DepotZip DepotSize OCName DEPOT D1 D2 D3 DepotID ORDERCLERK H Small Large Large C1 C2 C3 OCID DEPOT DepotID DepotSize DepotID DepotSize DepotZip OCID OCName OC1 Antonio OC2 Wesley OC3 Lilly DEPOT OCID OCName LATT ORDER OrderID OrderDate ORDERCLERK ORDERCLERK ORDER ShipsFrom DepotZip 60611 60660 60611 01 02 03 04 05 06 CUSTOMER CustomerID CustomerName Auto Doc Bo's Car Repair JJ Auto Parts Processes ProductID OrderID CustomerID C1 C2 C3 C1 C2 C3 P1 P2 P3 ProductName PRODUCT ProductID Quantity D1 D1 D2 D2 D3 D3 CustomerType Repair Shop Repair Shop Retailer PRODUCT Includes PRODUCT ProductID ProductName Product Type SupplierID (FK) ORDEREDVIA ProductID (FK) OrderID (FK) Quantity Product Type ORDER OrderID OrderDate Order Time CustomerID (FK) DepotID (FK) OCID (FK) CustomerZip 60137 60140 60605 Product Name ProductType BigGripper Traction Wiz SureStart Tire Tire Battery Order Time DepotID OCID OrderDate Order Time OC1 1-Jan-2013 9:00:00 AM OC2 2-Jan-2013 9:00:00 AM OC3 2-Jan-2013 9:30:00 AM OC1 3-Jan-2013 9:00:00 AM OC2 3-Jan-2013 9:15:00 AM OC3 3-Jan-2013 9:30:00 AM Supplies Submits Supplier D ST ST BE SUPPLIER SupplierID SupplierName CustomerZip CUSTOMER CustomerID CustomerName CustomerType CustomerZip P1 P2 P1 P2 P3 P3 P2 P1 CustomerID CustomerName 000 ST BE SupplierID SUPPLIER 01 01 02 03 04 05 SupplierName 06 06 CUSTOMER SUPPLIER SupplierID SupplierName Super Tires Batteries Etc ORDEREDVIA ProductID OrderID Quantity CustomerType 4 8 12 4 7 5 8 4