Module 18: Case Study Working with the Foodmart Database

Chia sẻ: Vu Trung | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

132
lượt xem 15
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'module 18: case study working with the foodmart database', công nghệ thông tin, quản trị mạng phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Module 18: Case Study Working with the Foodmart Database

Module 18: Case Study—Working with the Foodmart Database Contents Overview 1 Building the Warehouse Cube 2 Lab A: Building the Warehouse Cube 8 Building the Sales Cube 16 Lab B: Building the Sales Cube 22 Building the Warehouse and Sales Virtual Cube 28 Lab C: Building the Warehouse and Sales Cube 33 Review 38
Information in this document is subject to change without notice. The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted. Complying with all applicable copyright laws is the responsibility of the user. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Microsoft Corporation. If, however, your only means of access is electronic, permission to print one copy is hereby granted. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.  2000 Microsoft Corporation. All rights reserved. Microsoft, BackOffice, MS-DOS, Windows, Windows NT, are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries. The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted. Other product and company names mentioned herein may be the trademarks of their respective owners. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database iii Instructor Notes Presentation: In this module, students apply concepts they have learned in modules 1 through 60 Minutes 17 in the course 2074A, Designing and Implementing OLAP Solutions Using Microsoft® SQL Server™ 2000. Students will use various cube and dimension Labs: techniques to build cubes from the Foodmart 2000 database by using 120 Minutes Microsoft SQL Server 2000 Analysis Services. In the labs, students are given high-level procedures and must recall the specific steps and actions for creating various dimension and cube structures. Students create a preliminary cube, and must then make changes to the cube by applying dimension and level properties. After completing this module, students will be able to: Create a cube based on user requirements. ! Create another cube with different dimensions and measures. ! Build a virtual cube. ! Materials and Preparation This section lists the required materials and preparation tasks that you need to teach this module. Required Materials To teach this module, you need the following materials: Microsoft PowerPoint® file 2074A_18.ppt ! Preparation Tasks To prepare for this module, you should: Perform all the student labs. ! Read all the student materials. ! Read the instructor notes and margin notes. ! Practice integrating the demonstrations with the lecture. ! Review the Trainer Preparation presentation for this module on the Trainer ! Materials compact disc. Review any relevant white papers that are located on the Trainer Materials ! compact disc. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
iv Module 18: Case Study—Working with the Foodmart Database Demonstration: Reviewing Lab A In this demonstration, you will view and discuss the solutions of lab A. The following demonstration procedures provide information that will not fit in the margin notes or is not appropriate for student notes. ! To restore a new database and define a data source 1. In Analysis Manager, right-click the server, click Restore Database, click the Look in list, click the file C:\Moc\2074A\Labfiles\L18\Answers\Module 18A.CAB, click Open, click the Restore button, and then click Close. 2. Expand the Module 18A database, double-click the Data Sources folder, right-click the FoodMart 2000 data source, and then click Edit. 3. Verify that FoodMart 2000 is selected in step 1. Click OK. ! To view the Warehouse cube 1. Expand the Cubes folder, right-click Warehouse, and then click Edit. 2. Point out that the cube contains the dimensions shown in the following table. Dimension Dimension type Product Shared Time Shared Warehouse Shared Store Shared First Opened Year Private 3. Highlight the fact that the cube contains the following measures: • Units Ordered • Units Shipped • Warehouse Sales • Warehouse Cost • Maximum Supply Time 4. Drill down on each dimension and verify that students have the correct number of levels in each dimension and that the levels are in the correct order. 5. Drill down on the First Opened Year dimension and verify that students have the correct expression in the Member Key Column and the Member Name Column for the level. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database v 6. Click the Maximum Supply Time measure, and verify that students use the Max Aggregate Function to define the measure. 7. Edit each of the three calculated members, and verify that students use the correct expressions to define each calculated member. 8. Close the Cube Editor. 9. Expand the Warehouse cube, expand the Partitions folder, and verify that students have two partitions defined in the cube: one partition for 1998 inventory data and one for 1997 inventory data. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
vi Module 18: Case Study—Working with the Foodmart Database Demonstration: Reviewing Lab B In this demonstration, you will view and discuss the solutions of lab B. The following demonstration procedures provide information that will not fit in Demonstration: the margin notes or is not appropriate for student notes. 10 Minutes ! To restore a new database and define a data source 1. In Analysis Manager, right-click the server, click Restore Database, click the Look in list, click the file C:\Moc\2074A\Labfiles\L18\Answers\Module 18B.CAB, click Open, click the Restore button, and then click Close. 2. Expand the Module 18B database, double-click the Data Sources folder, right-click the FoodMart 2000 data source, and then click Edit. 3. Verify that FoodMart 2000 is selected in step 1. Click OK. ! To view the Sales cube 1. Expand the Cubes folder, right-click Sales, and then click Edit. 2. Point out that the cube contains the dimensions shown in the following table. Dimension Dimension type Store Shared Product Shared Time Shared Quarter Virtual Customer Shared Gender Private Education Virtual Yearly Income Virtual 3. Highlight the fact that the cube contains the following measures: • Store Sales • Store Cost • Unit Sales 4. Notice the Customer dimension. The Customer dimension contains the Customer level that uses the customer_id as the Member Key Column, and lname, fname as the Member Name Column. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database vii 5. Notice the Product dimension. The Product Name level is disabled in the Cube Editor. 6. Edit each of the three calculated members, and verify that students use the correct expressions to define each calculated member. 7. Close the Cube Editor. 8. Expand the Sales cube, expand the Partitions folder, and verify that students have three partitions defined in the cube: one partition for 1997 sales data, one for 1998 sales data, and one for 1998 December sales data. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
viii Module 18: Case Study—Working with the Foodmart Database Demonstration: Reviewing Lab C In this demonstration, you will view and discuss the solutions of lab C. The following demonstration procedures provide information that will not fit in Demonstration: the margin notes or is not appropriate for student notes. 10 Minutes ! To restore a new database and define a data source 1. In Analysis Manager, right-click the server, click Restore Database, click the Look in list, click the file C:\Moc\2074A\Labfiles\L18\Answers\Module 18C.CAB, click Open, click the Restore button, and then click Close. 2. Expand the Module 18C database, double-click the Data Sources folder, right-click the FoodMart 2000 data source, and then click Edit. 3. Verify that FoodMart 2000 is selected in step 1. Click OK. ! To view the Warehouse and Sales virtual cube 1. Expand the Cubes folder, right-click Warehouse and Sales, and then click Edit. 2. Point out that the cube contains the following dimensions: • Time • Product • Store • Customer 3. Highlight the fact that the cube contains the following measures: • Units Shipped • Store Cost • Store Sales • Warehouse Sales • Warehouse Cost 4. Edit the Average Shipped Cost calculated member, and verify that students use the correct expression to define the calculated member. 5. Verify that Average Selling Price does not exist in the cube. 6. Close the Virtual Cube Editor. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database ix Other Activities Difficult Questions Below are difficult questions that students may ask you during the delivery of this module and answers to the questions. These materials delve into subjects that are within the scope of the module but are not specifically addressed in the content of the student notes. 1. How can you temporarily add 1997 inventory data to the Warehouse cube? You can incrementally update the Warehouse cube, defining the inventory_fact_1997 as the fact table from which to retrieve data. 2. What is an easy way to create the First Opened Year dimension in the Warehouse cube? In the Warehouse Cube Editor, create a new dimension by using the Dimension Wizard. Select the store table as the dimension table, create the dimension as a Time dimension, and then define the hierarchy containing two levels—year and month. At the end of the wizard, clear the Share this dimension with other cubes check box. After the dimension appears in the cube tree view, you can delete the month level, leaving one level, First Opened Date. Rename the level First Opened Year. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
x Module 18: Case Study—Working with the Foodmart Database Module Strategy Use the following strategy to present this module: Building the Warehouse Cube ! Begin by defining the schema that students will use to build the Warehouse cube. Let students know which tables to use, and then introduce the dimensions and levels that they will create. Next, introduce the measures to include in the cube. Finally, define the calculated members to create. Building the Sales Cube ! Introduce the schema of the Sales cube. Let students know which tables to use. Next, introduce the dimensions and levels that they will create and then introduce the measures to include. Finally, define the calculated members to create. Building the Warehouse and Sales Virtual Cube ! Explain that students will be creating a virtual cube named Warehouse and Sales by combining the Warehouse cube and the Sales cube. Introduce the dimensions and levels to be included, and then define which measures to add. Finally, explain that students will add calculated members by using two different methods—creating a new one, and importing several from the source cubes. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database 1 Overview Topic Objective To provide an overview of the module topics and Building the Warehouse Cube objectives. ! Lead-in Building the Sales Cube ! In this module, you will apply the concepts learned Building the Warehouse and Sales Virtual Cube ! in modules 1 through 17 of the course 2074A, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000 by creating various cubes from the Foodmart 2000 database. In this module, you will apply the concepts learned in modules 1 through 17 in the course 2074A, Designing and Implementing OLAP Solutions Using Microsoft® SQL Server™ 2000 by creating various cubes from the Foodmart 2000 database. After completing this module, you will be able to: Create a cube based on user requirements. ! Create another cube with different dimensions and measures. ! Build a virtual cube. ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
2 Module 18: Case Study—Working with the Foodmart Database # Building the Warehouse Cube Topic Objective To introduce the database, dimensions, and levels in Defining the Warehouse Schema the Warehouse cube. ! Lead-in Introducing the Dimensions and Levels ! In this section, you will learn about the database that you Analyzing the Measures ! use to build the Warehouse Defining the Calculated Members cube. In addition, you will ! learn about the dimensions and measures of the cube. In this section, you will learn about the schema of the database used to create the Warehouse cube by using Microsoft SQL Server 2000 Analysis Services. In addition, you will learn about the dimensions, measures, and calculated members that you will create in the Warehouse cube. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database 3 Defining the Warehouse Schema Topic Objective To introduce the Warehouse schema that students use to build the Warehouse cube. Lead-in You will be creating the Warehouse cube by using the tables in the Foodmart 2000 Microsoft Access 2000 database. The Foodmart 2000 database contains tables that you will use to create the Delivery Tips Warehouse cube. Let students know that they can turn back to this page You will create a cube that tracks Units Ordered, Units Shipped, Warehouse during the lab to identify Sales, Warehouse Cost, Maximum Supply Time, and Margin. Users need to levels and tables used to see these numeric values by Warehouse, by Store, by Time period, and by build dimensions. Product. You will need to include two years worth of data, as well as the variance of the two years—1997 and 1998. In addition, users want to evaluate Students can also open the the all the values based on the year in which the stores opened. database themselves in Access by navigating to The tables that you will use to build the cube are the following: \Program Files\Analysis Services\Samples on their Inventory fact tables—for 1997 and 1998 data ! computers and opening time_by_day Foodmart 2000.mdb. ! product ! product_class ! store ! warehouse ! warehouse_class ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
4 Module 18: Case Study—Working with the Foodmart Database Introducing the Dimensions and Levels Topic Objective To introduce the dimensions and levels created in the Product Warehouse cube. ! Lead-in Time ! You will create five dimensions in the Warehouse ! Warehouse cube. Store ! First Opened Year ! You will create five dimensions in the Warehouse cube. Before creating each Delivery Tips dimension, you will determine the source table and the levels that are required For classes with more to build the dimension. experienced students, briefly highlight the fact that Product they will be creating several dimensions in lab A. Do not Define the shared Product dimension by using the tables product and cover the dimension product_class. The Product dimension contains six levels: structures in detail. Product Family ! For less advanced classes, Product Department use this page as an ! opportunity to prepare Product Category ! students for the lab by Product Subcategory discussing each of the ! dimensions in detail. Brand Name ! Product Name ! Time Define the shared Time dimension by using the time_by_day table. The Time dimension contains three levels: Year ! Quarter ! Month ! Be certain that the Time dimension levels are specified with the proper Level Type properties. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database 5 Warehouse Define the shared Warehouse dimension by using the tables warehouse and warehouse_class. The Warehouse dimension contains four levels: Country ! Description ! State ! Warehouse ! Store Define the shared Store dimension by using the store table. The Store dimension contains four levels: Store Country ! Store State ! Store City ! Store Name ! First Opened Year Define the private First Opened Year dimension by using the store table. This defines the year in which each store first opened. Use an expression to generate the year for this dimension by using the datepart function. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
6 Module 18: Case Study—Working with the Foodmart Database Analyzing the Measures Topic Objective To describe the use of measures in the Units Ordered Warehouse cube. ! Lead-in Units Shipped ! You will create five measures in the Warehouse Sales ! Warehouse cube. Warehouse Cost ! Maximum Supply Time ! You will create five measures in the Warehouse cube. All measures come from the inventory fact tables. The measures to include in the cube are: Units Ordered ! Units Shipped ! Warehouse Sales ! Warehouse Cost ! Maximum Supply Time ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database 7 Defining the Calculated Members Topic Objective To describe the use of calculated members in the Margin Warehouse cube. ! Lead-in Warehouse Sales minus Warehouse Cost $ You will create three calculated members in the 1998 versus 1997 Variance ! Warehouse cube. The difference between the two years $ USA + Canada ! Rollup created by using the Sum function $ You will create three calculated members in the Warehouse cube. The calculated members to include in the cube are: Margin. Warehouse Sales - Warehouse Cost. ! 1998 versus 1997 Variance. The difference between the two years. ! USA + Canada. The rollup of the two members created by using the Sum ! function. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
8 Module 18: Case Study—Working with the Foodmart Database Lab A: Building the Warehouse Cube Topic Objective To introduce the lab. Lead-in In this lab, you will create a new cube, Warehouse, from start to finish. Explain the lab objectives. Objectives After completing this lab, you will be able to: Create a new cube from start to finish. ! Create several dimensions in a cube. ! Verify results of a cube. ! Prerequisites Before working on this lab, you must have: Experience working with dimensions. ! An understanding of dimensions, cubes, and measures. ! Experience working with the Cube Editor and the Dimension Editor. ! Experience working with the Calculated Member Builder. ! For More Information This module uses the Analysis Manager to create a new cube with several dimensions. For more information, note the following references. The Dimension Editor For more information on using the Dimension Editor interface and creating dimensions, see module 4, "Building Dimensions Using the Dimension Editor," and module 5, “Using Advanced Dimension Settings,” in course 2074A, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 18: Case Study—Working with the Foodmart Database 9 The Cube Editor For more information on using the Cube Editor, see module 6, "Working with Cubes and Measures," in course 2074A, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000. Calculated Members For more information on creating calculated members, see module 11, "Implementing Calculations Using MDX," in course 2074A, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000. Partitions For more information on creating partitions, see module 10, "Managing Partitions," in course 2074A, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000. Estimated time to complete this lab: 60 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
10 Module 18: Case Study—Working with the Foodmart Database Exercise 1 Creating the Warehouse Cube In this exercise, you will create a new cube, Warehouse, which contains five dimensions with varying specifications, levels, and structures. In addition, the cube contains five measures and three calculated members. You will process the cube and will confirm that the cube is correct by verifying cube values. The lab provides high-level procedures for you to follow. If you do not remember the specific steps for a procedure, refer to the modules outlined in the For Your Information section of the lab. If you still cannot determine the proper steps for a single procedure, then refer to the corresponding answer files, which are located in: C:\Moc\2074A\Labfiles\L18\Answers ! To create a new database 1. In Analysis Manager, create a new database, and then name the database Module 18. 2. Create a new data source in Module 18. 3. On the Provider tab of the Data Link Properties dialog box, click the Microsoft OLE DB Provider for ODBC Drivers. Click Next. 4. On the Connection tab, click the Use data source name list, and then click Foodmart 2000. 5. Click Test Connection to verify that the Foodmart 2000 data source is available to the local server. 6. Click OK, and close the Data Link Properties dialog box. ! To create a new cube 1. In the Module 18 OLAP database, create a new cube containing the following five measures defining 1998 inventory data: • Units Ordered • Units Shipped • Warehouse Sales • Warehouse Cost • Maximum Supply Time 2. Create and add the following five dimensions to the Warehouse cube: • Product • Time • Warehouse • Store • First Opened Year BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY