Module 7: Case Study—Creating the Store Expense Cube Contents Overview Building the Store Expense Cube Lab A: Building the Store Expense Cube Updating the Store Expense Cube 12 Lab B: Modifying the Store Expense Cube 13 Review 20 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 7: Case Study—Creating the Store Expense Cube iii Instructor Notes Presentation: 30 Minutes Labs: 60 Minutes In this module, students apply concepts they have learned in modules through in course 2074A, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000 Students will use various cube and dimension techniques to build an online analytical processing (OLAP) cube for store expenses 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 then must 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 ! Update dimensions and add new dimensions to a cube Materials and Preparation This section provides the required materials and preparation tasks that are needed to teach this module Required Materials To teach this module, you need the following materials: ! Microsoft PowerPoint® file 2074A_07.ppt Preparation Tasks To prepare for this module, you should: ! Read all of the student materials ! Read the instructor notes and margin notes ! Complete all of the demonstrations ! Practice the lecture presentation and demonstrations ! Complete the labs ! 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 7: Case Study—Creating the Store Expense Cube Demonstration: Reviewing Lab A Demonstration: In this demonstration, you will view and discuss the solutions of lab A 10 Minutes The following demonstration procedures provide information that will not fit in the margin notes or are not appropriate for student notes ! To restore a new database and define a data source In Analysis Manager, right-click the server, click Restore Database, click the Look in list, find and click the file C:\Moc\2074A\Labfiles\L07\Answers\Module 07A.CAB, click Open, and then click Restore Expand the Module 07A database, double-click the Data Sources folder, right-click the Module 07 data source, and then click Edit Verify that localhost is selected in step and that Module 07 is selected in step Click OK ! To view the Store Expense cube Expand the Cubes folder, right-click Store Expense, and then click Edit Point out that the cube contains the dimensions shown in the following table Dimension Dimension type Scenario Shared Time Private Store Shared Store Manager Private Account Shared Department Shared Mention that the in real-world OLAP databases, Time dimensions usually are defined as shared dimensions used within multiple cubes The lab contains a private Time dimension to challenge students by presenting a different method for creating a Time dimension Highlight the fact that the cube contains one measure—Amount 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 Drill down on the Account dimension and verify that students have the correct members in the correct order Save the Store Expense cube and close the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube v Demonstration: Reviewing Lab B In this demonstration, you will view and discuss the solutions of lab B Demonstration: 10 Minutes The following demonstration procedures provide information that will not fit in the margin notes or are not appropriate for student notes ! To restore a new database and define a data source In Analysis Manager, right-click the server, click Restore Database, click the Look in list, find and click the file C:\Moc\2074A\Labfiles\L07\Answers\Module 07B.CAB, click Open, and then click Restore Expand the Module 07B database, double-click the Data Sources folder, right-click the Module 07 data source, and then click Edit Verify that localhost is selected in step and that Module 07 is selected in step Click OK ! To view the Store Expense cube Expand the Cubes folder, right-click Store Expense, and then click Edit Point out that the cube contains the dimensions shown in the following table Dimension Dimension type Scenario Shared Time Private Store Shared Store Manager Private Account Shared Department Shared Store Type Virtual Browse the cube, and notice the Scenario dimension The Scenario dimension does not contain the All Scenario member Notice the Time dimension It does not contain the All Time member Pivot the Store dimension to the row area in the browser Double-click the Store Country level heading, double-click the Store State level heading, and notice that the Mexico state is hidden Drill down to the Store Name level and notice the names of the stores They contain both the city name and the store name Browse the Store Manager dimension, and notice the First Letter level that groups the store managers by the first letter of their names In the Store Manager dimension, notice that no null member names exist at the First Letter level All null member names are hidden BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY vi Module 7: Case Study—Creating the Store Expense Cube ! To view Store Type and Account In the Cube Editor, browse the Store Type dimension and verify that it contains seven members, including the All Store Type member Pivot the Account dimension to the row area, expand the dimension to all levels, right-click the members, and click Member Properties to view the unary operators for each member Point out that the Account dimension does not contain the All Account member Point out that the members in the Account dimension are not sorted in alphanumeric order based on the Account description Ask students if they received the same values in the lab when they were asked to verify cube results Save the Store Expense cube and close the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube vii 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 Where in Analysis Manager you make changes to existing dimensions? Where you update existing dimensions depends on the type of dimension that requires modification You modify shared dimensions in the Dimension Editor You modify private dimensions in the Cube Editor Why you want a dimension to be balanced in structure? When a dimension is balanced, you take advantage of precalculations for cube aggregations Therefore, user queries execute faster than if the queries were made against dimensions without precalculated aggregations Balanced dimensions contain the same number of levels in each dimension branch You create a balanced dimension by defining a standard dimension (not a parent-child dimension) when you create it in the Dimension Wizard or the Dimension Editor Unbalanced dimensions must be defined as parent-child dimensions, and not have the same number of levels in each dimension branch Parent-child dimensions provide flexibility in dimension structure, allowing for complex hierarchies such as charts of accounts and organizational structures However, query performance can be slower when you use parent-child dimensions than when you use standard dimensions, because aggregates are not stored for parent-child dimensions In lab B, why students process the Store dimension after updating the Member Name Column of the Store Name level? Students process and rebuild the Store dimension after the update of the Member Name Column because change to the dimension structure affects the processing of the cube Without the rebuild, cube processing fails in the Store Expense cube The dimension does not force a rebuild at cube processing time, which is why students rebuild the dimension in the Dimension Editor If Store were a private dimension, the dimension processes automatically at cube processing time However, because Store is a shared dimension, the students must explicitly process the dimension themselves The students not process the other shared dimensions, Scenario and Account Analysis Manager detects the changes made to the Scenario and Account structures, and the dimensions process automatically when the cube is processed BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY viii Module 7: Case Study—Creating the Store Expense Cube When creating a level from an expression, why must both the Member Key Column and the Member Name Column include the expression? The Member Key Column includes the expression because it builds the level and the members in the level based upon the expression The Member Name Column includes the expression because it names each of the members based on the expression BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube ix Module Strategy Use the following strategy to present this module: ! Building the Store Expense Cube Introduce the Store Expense data source schema Briefly describe the dimensions and levels that are created in lab A Describe the use of the one measure, Amount, in the Store Expense cube After students complete lab A, open the archived OLAP database and explain the Store Expense cube ! Updating the Store Expense Cube Explain to students that they will modify the Store Expense cube in lab B After students complete lab B, open the archived OLAP database and explain the Store Expense cube BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube Analyzing the Measures Topic Objective To describe the use of measures in the Store Expense cube Lead-in You will create one measure in the Store Expense cube named Amount Measure You will create one measure in the Store Expense cube named Amount You create only one measure because the Account dimension contains various expense and revenue accounts that aggregate based on a rollup column The amount column in the expense_fact table is the source of the Amount measure You define the measure when you initially create the Store Expense cube BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube Lab A: Building the Store Expense Cube Topic Objective To introduce the lab Lead-in In this lab, you will create a new cube, Store Expense, 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 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube For More Information This module uses the Analysis Manager to create a new cube with several dimensions For more information, see the following references The Dimension Editor For more information about 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 with Microsoft SQL Server 2000 The Cube Editor For more information about using the Cube Editor interface, see module 6, "Working with Cubes and Measures," in course 2074A, Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000 Estimated time to complete this lab: 30 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube Exercise Creating the Store Expense Cube In this exercise, you will create a new cube, Store Expense, which contains six dimensions with varying specifications, levels, and structures You will process the cube and confirm that the cube is correct by verifying cube values The lab provides high-level procedures for you to follow If you not remember the specific steps for a procedure, refer to the modules outlined in the For More Information section of the lab If you still cannot determine the proper steps for a single procedure, refer to the corresponding answer files, which are located in: C:\Moc\2074A\Labfiles\L07\Answers ! To create a new database In Analysis Manager, create a new database, and then name the database Module 07 Create a new data source in Module 07, and then connect to the SQL Server 2000 database, Module 07 ! To create a new cube In the Module 07 OLAP database, create a new cube containing one measure, Amount, and containing the following six dimensions: • Scenario • Time • Store • Store Manager • Account • Department Tip To determine the tables and columns to include in the dimensions, browse the dimension tables in either Analysis Manager or SQL Server Enterprise Manager BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 10 Module 7: Case Study—Creating the Store Expense Cube Use the following table to create the dimensions with various dimension specifications Dimension Name Dimension Type Levels/Members Scenario Shared One level with four members: Adjustment for Budget input, Current Year’s Actuals, Current Year’s Budget, Forecast Time Private Three levels: Year, Quarter, Month Store Shared Four levels: Store Country, Store State, Store City, Store Name Store Manager Private One level: Store Manager Account Shared Parent-child hierarchy with Account Description member names Department Shared Three levels: Area, Department, Position Name the cube Store Expense Process the cube, without defining aggregations or storage mode ! To verify cube results In the cube browser, verify that you retrieve $2,527.11 for the following member combination: • Current Year’s Actuals • Davis store manager • Permanent Store Positions department • WA stores • Quarter 1, 2000 • Net Income • Amount Save the Store Expense cube, and then close the Cube Editor or Cube Browser, depending on the currently opened window BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube 11 Demonstration: Reviewing Lab A Topic Objective To demonstrate solutions to lab A Lead-in In this demonstration, you will view and discuss the solutions to lab A Delivery Tips The steps for this demonstration are included in the Instructor Notes In this demonstration, you will view and discuss the solutions of lab A Encourage students to follow along with the demonstration by comparing their Store Expense cube to the archived version that you demonstrate BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 12 Module 7: Case Study—Creating the Store Expense Cube Updating the Store Expense Cube Topic Objective To describe the modifications that students will make to the Store Expense cube ! Updating the Dimensions $ $ Store Manager $ ! Store $ In lab B, you will make several modifications to the Store Expense cube Time $ Lead-in Scenario Account Creating the Dimension $ Store Type In lab B, you will make several modifications to the Store Expense cube You will make changes to the following dimensions: ! Scenario ! Time ! Store ! Store Manager ! Account You will update each dimension in either the Dimension Editor or the Cube Editor, depending on whether the dimension is shared or private You will determine the location for updating dimensions In addition, you will determine the appropriate properties to change to perform several of the dimension, level, and member modifications Finally, you will create a new dimension, Store Type, which will be a virtual dimension You will need to recall the steps for creating virtual dimensions and for adding virtual dimensions to a cube BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube 13 Lab B: Modifying the Store Expense Cube Topic Objective To introduce the lab Lead-in In this lab, you will modify the Store Expense cube by updating cube dimensions Explain the lab objectives Objectives After completing this lab, you will be able to: ! Make changes to existing dimensions ! Add dimensions to 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 Lab Setup To complete this lab, you need to first complete lab A, Building the Store Expense Cube, found earlier in the module BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 14 Module 7: Case Study—Creating the Store Expense Cube For More Information This module uses the Analysis Manager to create a new cube with several dimensions For more information, see the following references The Dimension Editor For more information about 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 with Microsoft SQL Server 2000 The Cube Editor For more information about using the Cube Editor interface, see module 6, "Working with Cubes and Measures," in course 2074A, Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000 Estimated time to complete this lab: 30 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube 15 Exercise Modifying the Store Expense Cube In this exercise, you will update the Store Expense cube, adding a new dimension, modifying existing dimensions, and verifying results The lab provides high-level procedures for you to follow If you not remember the specific steps for a procedure, refer to the modules outlined in the For More Information section of the lab If you still cannot determine the proper steps for a single procedure, refer to the corresponding answer files, which are located in: C:\Moc\2074A\Labfiles\L07\Answers ! To update the Scenario dimension In the Module 07 database in Analysis Manager, expand the Shared Dimensions folder, right-click Scenario, and then click Browse Dimension Data Notice the member All Scenario It does not make sense to have an All member in the Scenario dimension, because the four Scenario members not add up to a meaningful value Remove the All Scenario member from the Scenario dimension ! To update the Time dimension • Remove the All Time member from the Time dimension ! To update the Store dimension Open the Dimension Editor for the dimension Store Browse the dimension in the Data pane and then notice the two Mexico members: The Store dimension does not organize Mexico into states or provinces Therefore, the member Mexico repeats in the Store State level to verify that the dimension is balanced BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 16 Module 7: Case Study—Creating the Store Expense Cube Apply a level property to suppress the second Mexico in the Store dimension Tip The Dimension Editor reflects the hiding of the member However, to see whether a member is hidden in the Cube Editor, you must perform a specific browsing technique To browse the Store dimension in the Cube Editor to see if the second Mexico is suppressed, pivot the Store dimension to the row area and double-click the level headings, not the individual members In the Data pane, expand the Store dimension and notice the member names at the Store Name level Currently, the member names at the Store Name level not present descriptive information An analyst may not know that Store is located in Mexico City You will update the member name to include the city name in addition to the store name Update the Store Name level so that the member names include the city name and the store name The format of the member names should be: CityName – StoreName Click the Tools menu, and then click Process Dimension Save the dimension, rebuild the dimension structure, close the Process dialog box, and then close the Dimension Editor ! To update the Store Manager dimension Add a new level to the Store Manager dimension, First Letter, which groups the store managers by the first letter of their names The dimension should look like the following: After adding the new level, notice the members with no names at both levels of the dimension Apply level settings to suppress the members with no names \ BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube 17 ! To create the Store Type dimension Create a new dimension, Store Type, as a virtual dimension containing one level, Store Type Add Store Type to the Store Expense cube ! To evaluate the Account dimension In the Cube Editor for Store Expense, process the cube without defining aggregations or storage mode Verify that you retrieve $373,823.44 for the following member combination: • Current Year’s Actuals • All Store Manager • All Department • All Store • All Store Type • 2000 • Net Income • Amount Why is the amount $373,823.44 incorrect for this member combination? The Account dimension is summing Total Expense, when it should be subtracting Total Expense from Net Sales to calculate Net Income ! To update the Account dimension Open the Dimension Editor for the Account dimension Browse the dimension and notice how all members aggregate The problem with the Account dimension exists because all account members sum up to Net Income However, you can use a column called account_rollup to define how each of the members should aggregate Browse the dimension table, account, to view the account_rollup values Update the Account dimension to use the account_rollup column to define how each member aggregates in the dimension Modify a level property so that the Account members appear in the same order as the account id column values Remove the All Account member from the Account dimension BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 18 Module 7: Case Study—Creating the Store Expense Cube ! To verify cube results In the Cube Editor for Store Expense, process the cube without defining aggregations or storage mode In the Data pane, verify that you retrieve $847.20 for the following member combination: • Current Year’s Actuals • Store managers that begin with S • All Department • All Store Type • San Francisco – Store 14 • 2000 • Total Expense • Amount Verify that you retrieve $10,272.00 for the following member combination: • Current Year’s Actuals • Store managers that begin with B • All Department • All Store • Supermarket store type • 2000 • Total Expense • Amount Verify that you retrieve negative $373,823.44 for the following member combination: • Current Year’s Actuals • All Store Manager • All Department • All Store • All Store Type • 2000 • Net Income • Amount The value is negative because the Net Income member is equal to Net Sales minus Total Expense, and the cube does not contain data for Net Sales Save the Store Expense cube, and then close the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube 19 Demonstration: Reviewing Lab B Topic Objective To demonstrate solutions to lab B Lead-in In this demonstration, you will view and discuss the solutions to lab B Delivery Tips The steps for this demonstration are included in the Instructor Notes In this demonstration, you will view and discuss the solutions of lab B Encourage students to follow along with the demonstration by comparing their Store Expense cube to the archived version that you demonstrate BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 20 Module 7: Case Study—Creating the Store Expense Cube Review Topic Objective To reinforce module objectives by reviewing key points ! Building the Store Expense Cube Lead-in ! Updating the Store Expense Cube The review questions cover some of the key concepts taught in the module In what interface does Analysis Manager automatically create Member Key Column values and Level Types for Time dimensions built from Date/Time columns? The Dimension Wizard What are the steps for creating a virtual dimension from a member property and adding the virtual dimension to a cube? You first add the member property to a dimension Next, you create the virtual dimension from the member property by using the Dimension Wizard Finally, you add the virtual dimension to the cube in the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY ... PURPOSES ONLY Module 7: Case Study—Creating the Store Expense Cube Store Define the shared Store dimension by using the store table The Store dimension contains four levels: ! Store Country ! Store State... Cube Defining the Store Expense Schema Topic Objective To introduce the Store Expense schema that students use to build the Store Expense cube Lead-in You will be creating the Store Expense cube. .. build the Store Expense cube In addition, you will learn about the dimensions and measures of the cube Discuss the business reason for implementing the Store Expense cube ! Defining the Store Expense