Contents Overview 1 Introducing Dimension and Cube Processing 2 Processing Dimensions 5 Processing Cubes 15 Lab A: Processing Dimensions and Cubes 27 Lab B: Updating Dimension Data 30 Lab C: Incrementally Updating Cubes 36 Optimizing Cube Processing 41 Troubleshooting Cube Processing 49 Review 54 Module 9: Processing Dimensions and Cubes 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 9: Processing Dimensions and Cubes iii BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Instructor Notes Multidimensional online analytical processing (OLAP) databases include schema and data, both of which change and need updating from the relational database management system (RDBMS) on a regular basis. In this module, students learn to manage dimension and cube processing with Microsoft ® SQL Server ™ 2000 Analysis Services. After completing this module, students will be able to: ! Understand the difference between OLAP schema and data. ! Process dimensions. ! Perform the three types of cube processes. ! Optimize cube processing. ! Troubleshoot cube processing. 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 Microsoft PowerPoint ® file 2074A_09.ppt Preparation Tasks To prepare for this module, you should: ! Read all the student materials. ! Read the instructor notes and margin notes. ! Complete all the demonstrations. ! Practice the lecture presentation and demonstration. ! Complete the lab. ! 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: 75 Minutes Lab: 75 Minutes iv Module 9: Processing Dimensions and Cubes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Demonstration: Rebuilding the State Dimension The following demonstration procedures provide information that will not fit in the margin notes or is not appropriate for student notes. In this demonstration, you will rebuild the State dimension and will show students the impacts to the Sales Units cube after the rebuild. ! 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\L09\Module 09.CAB, click Open, click Restore, and then click Close. 2. Double-click Module 09 to expand the database. 3. Below Module 09, double-click the Data Sources folder, right-click the Module 09 data source, and then click Edit. 4. Click the Connection tab of the Data Link Properties dialog box, and then verify that localhost is selected in step 1. 5. In step 2, verify that Use Windows NT Integrated security is selected. 6. In step 3, verify that Module 09 is selected. 7. Click Test Connection and verify that the test succeeded. Click OK twice. ! To browse the Sales Units cube 1. In the Module 09 database, browse the Sales Units cube. 2. Show students that the cube contains two dimensions—State and Time— and one measure—Sales Units. 3. Point out that the cube is processed and able to accept queries. ! To rebuild the State dimension 1. In the Module 09 database Shared Dimensions folder, right-click the State dimension, and then click Process. The Process a Dimension dialog box opens. Here you see two options for processing a dimension—incrementally updating the dimension and rebuilding the dimension. 2. Click Rebuild the dimension structure, and then click OK. The Process dialog box opens and steps through the dimension rebuild. 3. After the dimension finishes processing, double-click the line in the Process dialog box that begins with a yellow SQL icon. Here you see the SQL that executes to rebuild the State dimension: SELECT DISTINCT "dbo"."State"."Country", "dbo"."State"."Region", "dbo"."State"."STATE_ID", "dbo"."State"."State_Name" FROM "dbo"."State" 4. Close the View Trace Line window, and then click Close. Demonstration: 10 Minutes Module 9: Processing Dimensions and Cubes v BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY ! To attempt to browse the Sales Units cube 1. In the Module 09 database, right-click the Sales Units cube, and then click Browse Data. 2. Notice that you receive the following error: “Unable to browse the cube ‘Sales Units’. Cube not processed. To browse sample data for this cube, open Cube Editor, and then on the View menu, click Data.” You did not change the State source data, and therefore the dimension did not require a rebuild. However, if the dimension is rebuilt, the Sales Units cube is unavailable. After you rebuild a shared dimension, all cubes containing the shared dimension are unavailable for user access. 3. Click Close. vi Module 9: Processing Dimensions and Cubes 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 are incremental updates useful if the Analysis Server is unaware of data warehouse loads and it therefore does not know what data to load from a table? You can create a column in the fact table that has an identifying record, such as a time stamp or a batch number. You can include the unique record in the WHERE clause of the associated partition definition and filter records based on that identifier. 2. Are multiple partitions of a cube processed sequentially, even if they reside on different computers? Yes, unless you specify that you want certain partitions to be built in parallel. You can do this by using Decision Support Objects (DSO). Module 9: Processing Dimensions and Cubes vii BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module Strategy Use the following strategy to present this module: ! Introducing Dimension and Cube Processing Define the term processing and explain that Analysis Server creates SQL statements to extract information from the data source. Next, define the terms schema and data as they are used in Analysis Services. ! Processing Dimensions Explain to students that dimensions must be processed when they are first designed and whenever there are changes or updates in the source dimension tables. Describe the interface and the steps to follow to process a shared dimension. Explain that there are two ways to process a shared dimension—a rebuild or an incremental update. Describe when to use each type of processing and the implications of doing so. Explain that private dimensions are created and manipulated in single cubes, and that to process a private dimension, the entire cube must be processed. Finally, define relational OLAP (ROLAP) dimensions and changing dimensions and explain when to use them. ! Processing Cubes Begin with an explanation of when to process a cube and how to get to the Process a Cube dialog box. Describe the three options available in the dialog box—full process, refresh, and incremental update. Describe when to use each option and the implications of doing so. Introduce the Incremental Update Wizard and the steps involved in performing an incremental update—specifying the data source and specifying the filter expression. Explain what happens behind the scenes when using a filter. Discuss available properties that affect cube and dimension processing. ! Optimizing Cube Processing Explain to students that, while query performance is obviously a high priority when using OLAP cubes, processing time is also important. Describe several ways to improve cube processing performance— optimizing the data source, optimizing the cube schema, optimizing cube design, and optimizing Analysis Server. Explain how to perform each of these types of optimization. ! Troubleshooting Cube Processing Describe the three most common cube problems related to processing— missing data, processing errors, and insufficient memory and disk space. Give tips and techniques for solving each type of problem. Module 9: Processing Dimensions and Cubes 1 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Overview ! Introducing Dimension and Cube Processing ! Processing Dimensions ! Processing Cubes ! Optimizing Cube Processing ! Troubleshooting Cube Processing Multidimensional online analytical processing (OLAP) databases include schema and data, both of which change and need updating from the relational database management system (RDBMS) on a regular basis. In this module, you will learn to manage Analysis Server dimension and cube processing with Microsoft ® SQL Server ™ 2000 Analysis Services. After completing this module, you will be able to: ! Understand the difference between OLAP schema and data. ! Process dimensions. ! Perform the three types of cube processes. ! Optimize cube processing. ! Troubleshoot cube processing. Topic Objective To provide an overview of the module topics and objectives. Lead-in In this module, you will learn about dimension and cube processing and the various ways to perform processes. 2 Module 9: Processing Dimensions and Cubes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY # ## # Introducing Dimension and Cube Processing ! Definition of Processing ! Overview of Schema and Data In this section, you are introduced to dimension and cube processing, and will learn the difference between OLAP schema and OLAP data. Topic Objective To introduce the concept of cube and dimension processing. Lead-in In this section, you are introduced to dimension and cube processing, and will learn the difference between OLAP schema and OLAP data. [...]... Module 9: Processing Dimensions and Cubes all shared dimensions found in the cube You do this by selecting the Incrementally update the shared dimensions used in this cube check box found in the Process a Cube dialog box BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 9: Processing Dimensions and Cubes 11 Processing Private Dimensions Topic Objective To discuss the processing. .. refreshes and full processes, refer to the next section in this module, Processing Cubes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 12 Module 9: Processing Dimensions and Cubes Understanding ROLAP and Changing Dimensions Topic Objective To discuss ROLAP and changing dimensions ! ROLAP Dimensions $ $ Require SQL Server 2000 Enterprise Edition $ ! Force you to define their cubes. .. Understanding the distinction between schema and data is important for database administrators and OLAP architects BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 9: Processing Dimensions and Cubes 5 # Processing Dimensions Topic Objective To introduce dimension processing Lead-in In this section, you will learn about dimension processing and the various ways of processing. .. Lead-in Processing Options After you design a cube, you must process the cube to populate it with data MOLAP cubes, ROLAP cubes, and HOLAP cubes all require processing There are three different processes for cubes, the mechanics and logic of which are described in the next section BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 16 Module 9: Processing Dimensions and Cubes Cube Processing. .. dimension processing and the various ways to process dimensions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 6 Module 9: Processing Dimensions and Cubes Dimension Processing Overview Topic Objective To explain the purpose and mechanics of dimension processing ! Purpose of Dimension Processing $ $ Lead-in Before data can be loaded into a cube, you must first create and process.. .Module 9: Processing Dimensions and Cubes 3 Definition of Processing Topic Objective To define the term processing, as used in Analysis Services ! Processing Must Occur Prior to Users Querying the Cube ! Dimension Processing Loads Dimension Data ! Cube Processing Loads Cube Data and Creates Aggregations ! Processing Uses SQL Queries to Populate Dimension and Cube Data Lead-in... Manager ! An understanding of dimensions and cubes Estimated time to complete this lab: 15 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 28 Module 9: Processing Dimensions and Cubes Exercise 1 Processing State and Sales Units In this exercise, you will rebuild the State dimension and perform a full process of the Sales Units cube ! To restore a new database and define a... dimensions Non-ROLAP dimensional structures are created on the server for cubes that use the MOLAP, hybrid OLAP (HOLAP), and relational OLAP (ROLAP) storage modes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 9: Processing Dimensions and Cubes 7 Shared Dimension Processing Mechanics You can initiate the processing of a shared dimension in either of two ways: ! Expand... not affect other cubes by processing private dimensions Therefore, you can isolate cube -processing needs by defining dimensions as private You cannot process private dimensions without also processing the cubes in which they reside, and no options or commands exist that process private dimensions independently There are two methods of processing private dimensions: ! Perform an incremental update or... Processing Dimensions and Cubes Processing Options Topic Objective To describe available cube and dimension processing properties Switch to Analysis Manager, and show students how to access each property as you describe it Processing Optimization Mode ! Stop Processing on Key Errors Key Error Limit Key Error Log File ! Delivery Tip ! ! You have access to several properties that affect cube and dimension processing . 1 Introducing Dimension and Cube Processing 2 Processing Dimensions 5 Processing Cubes 15 Lab A: Processing Dimensions and Cubes 27 Lab B: Updating. to dimension and cube processing, and will learn the difference between OLAP schema and OLAP data. Module 9: Processing Dimensions and Cubes 3 BETA