Part 3: On your own Profile the following table in the Northwind Traders Database. The first two have been done for you in the previous part. Table Type Row Business Key One Row is Count Employees None (PK used) An Employee Data Employee Territories None (PK's used) An Employee assigned to a territory. Master 49 Business Process Customers Suppliers Products Shipments (of Orders) Details (of an Order) Write SQL Queries to answer the following questions which might be associated with functional business requirements in a data warehouse. For each of the following provide a screen shot of the SQL query, its output, and make sure your name appears in the screenshot. 1. List the customer contact names and titles sorted by company name. 2. Factoring in discounts, what is total amount of product sold? 3. Provide a list of product category names with counts of products in each category. 4. Select a specific customer and display that customer's orders with total amount of product sold for each order. 5. Select a specific employee and each order, how it was shipped (shipvia) company who shipped it, and the total number of days elapsed from order date to shipped date. Part 3: On Your Own After you have finished up the sales reporting, it's time to move on to the other three business processes: + Sales reporting-Senior management would like to be able to track sales by customer, employee, product and supplier, with the goal of establishing which preduets are the top-sellers which employees place the most-orders, and who are the best-suppliers 2. Order Fulfillment and Delivery. There is a need to analyze the order fulfillment process to see if the time between when the order is placed and when it is shipped can be improved. 3. Product Inventory Analysis. Management requires a means to track inventory, On Order, and Re-Order levels of products by supplier or category. Inventory levels should be snapshotted daily and recorded into the warehouse for analysis. 4. Sales Coverage Analysis. An Analysis of the employees and the sales territories they cover. In this part, you will repeat the process outlined in part 2 of the lab for the remaining three business processes. When you are finished you should have the following in your High Level Dimensional Modeling Workbook: 1. A completed Bus Matrix with all 4 business processes in it. No dimensions should repeat. To re-use a dimension for another business process, include an X at its intersection 2. A completed Attributes and Metrics list. Specifically you should define facts and derived facts and important dimension attributes. 3. Along the way if you encounter issues or unknowns record them under the Issues List tab so you remember to address them at some point in the process. Tip: Keep in mind you can only model the data you have. If it's not in your external world source data (in this case, it's Northwind Traders) then you cannot include it in your data warehouse! Part 3: On Your Own Let's complete the detailed worksheet for the Sales Reporting and Order Fulfillment business processes. At Minimum, you should have the following tables. High Level Bus Matrix Create this in the Detail Level as a Database Table Customers DimCustomer (we did this is part 2) Order Date, Ship Date, Etc... DimDate (done for you already) Employees DimEmployee Products DimProduct Sales Reporting Fact Sales Order Fulfillment FactOrder Fulfillment NOTE: You may have others such as Shippers and Suppliers based on your Bus Matrix design. Part 3: On your own Profile the following table in the Northwind Traders Database. The first two have been done for you in the previous part. Table Type Row Business Key One Row is Count Employees None (PK used) An Employee Data Employee Territories None (PK's used) An Employee assigned to a territory. Master 49 Business Process Customers Suppliers Products Shipments (of Orders) Details (of an Order) Write SQL Queries to answer the following questions which might be associated with functional business requirements in a data warehouse. For each of the following provide a screen shot of the SQL query, its output, and make sure your name appears in the screenshot. 1. List the customer contact names and titles sorted by company name. 2. Factoring in discounts, what is total amount of product sold? 3. Provide a list of product category names with counts of products in each category. 4. Select a specific customer and display that customer's orders with total amount of product sold for each order. 5. Select a specific employee and each order, how it was shipped (shipvia) company who shipped it, and the total number of days elapsed from order date to shipped date. Part 3: On Your Own After you have finished up the sales reporting, it's time to move on to the other three business processes: + Sales reporting-Senior management would like to be able to track sales by customer, employee, product and supplier, with the goal of establishing which preduets are the top-sellers which employees place the most-orders, and who are the best-suppliers 2. Order Fulfillment and Delivery. There is a need to analyze the order fulfillment process to see if the time between when the order is placed and when it is shipped can be improved. 3. Product Inventory Analysis. Management requires a means to track inventory, On Order, and Re-Order levels of products by supplier or category. Inventory levels should be snapshotted daily and recorded into the warehouse for analysis. 4. Sales Coverage Analysis. An Analysis of the employees and the sales territories they cover. In this part, you will repeat the process outlined in part 2 of the lab for the remaining three business processes. When you are finished you should have the following in your High Level Dimensional Modeling Workbook: 1. A completed Bus Matrix with all 4 business processes in it. No dimensions should repeat. To re-use a dimension for another business process, include an X at its intersection 2. A completed Attributes and Metrics list. Specifically you should define facts and derived facts and important dimension attributes. 3. Along the way if you encounter issues or unknowns record them under the Issues List tab so you remember to address them at some point in the process. Tip: Keep in mind you can only model the data you have. If it's not in your external world source data (in this case, it's Northwind Traders) then you cannot include it in your data warehouse! Part 3: On Your Own Let's complete the detailed worksheet for the Sales Reporting and Order Fulfillment business processes. At Minimum, you should have the following tables. High Level Bus Matrix Create this in the Detail Level as a Database Table Customers DimCustomer (we did this is part 2) Order Date, Ship Date, Etc... DimDate (done for you already) Employees DimEmployee Products DimProduct Sales Reporting Fact Sales Order Fulfillment FactOrder Fulfillment NOTE: You may have others such as Shippers and Suppliers based on your Bus Matrix design