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 Module 18: Case Study—Working with the Foodmart Database BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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, <plus other appropriate product names or titles. Replace this example list with list of trademarks provided by copy editor. Microsoft is listed first, followed by all other Microsoft trademarks in alphabetical order. > are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries. <This is where mention of specific, contractually obligated to, third party trademarks, which are added by the Copy Editor> 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. Module 18: Case Study—Working with the Foodmart Database iii BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Instructor Notes In this module, students apply concepts they have learned in modules 1 through 17 in the course 2074A, Designing and Implementing OLAP Solutions Using Microsoft ® SQL Server ™ 2000. Students will use various cube and dimension techniques to build cubes from the Foodmart 2000 database by using 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. Presentation: 60 Minutes Labs: 120 Minutes iv Module 18: Case Study—Working with the Foodmart Database BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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. Module 18: Case Study—Working with the Foodmart Database v BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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. vi Module 18: Case Study—Working with the Foodmart Database BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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 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 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. Demonstration: 10 Minutes Module 18: Case Study—Working with the Foodmart Database vii BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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. viii Module 18: Case Study—Working with the Foodmart Database BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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 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 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. Demonstration: 10 Minutes Module 18: Case Study—Working with the Foodmart Database ix BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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. x Module 18: Case Study—Working with the Foodmart Database BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 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. [...]... 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 the Warehouse cube Lead-in In this section, you will learn about the database that you use to build the Warehouse cube In addition, you will learn about the dimensions and measures of the cube ! Defining the Warehouse Schema ! Introducing the. .. PURPOSES ONLY Module 18: Case Study Working with the Foodmart Database 19 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 Quarter Define the virtual Quarter dimension by using the Quarter level from the Time dimension The Quarter... 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 Delivery Tips Let students know that they can turn back to this page during the lab to identify.. .Module 18: Case Study Working with the Foodmart Database 1 Overview Topic Objective To provide an overview of the module topics and objectives ! Building the Warehouse Cube Lead-in ! Building the Sales Cube ! Building the Warehouse and Sales Virtual Cube In this module, you will apply the concepts learned in modules 1 through 17 of the course 2074A, Designing and Implementing... ONLY Module 18: Case Study Working with the Foodmart Database 23 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. .. view and discuss the solutions of lab A The steps for this demonstration are included in the Instructor Notes Encourage students to follow along with the demonstration by comparing their Warehouse cube to the archived version that you demonstrate BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 16 Module 18: Case Study Working with the Foodmart Database # Building the Sales Cube... 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... 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 Module 18: Case Study Working with the Foodmart Database 11 Tip To determine the tables and columns to include in the dimensions, browse the dimension tables either in Analysis Manager or in the database schema... add the following dimensions to the Sales cube: • Quarter • Customer • Gender • Education • Yearly Income Tip To determine the tables and columns to include in the dimensions, browse the dimension tables either in Analysis Manager or in the database schema presented earlier in the section BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 18: Case Study Working with the Foodmart. .. schema of the database used to create the Sales cube by using Analysis Services In addition, you will learn about the dimensions, measures, and calculated members that you will create in the Sales cube BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 18: Case Study Working with the Foodmart Database 17 Defining the Sales Schema Topic Objective To introduce the Sales schema . have the correct expression in the Member Key Column and the Member Name Column for the level. Module 18: Case Study Working with the Foodmart Database. 2000 by creating various cubes from the Foodmart 2000 database. 2 Module 18: Case Study Working with the Foodmart Database BETA MATERIALS FOR MICROSOFT