Contents Overview 1 Introducing Partitions 2 Creating Partitions 7 Lab A: Creating a Partition in the Sales Cube15 Using Advanced Settings 19 Merging Partitions 24 Lab B: Applying Advanced Settings to Partitions 26 Review 29 Module 10: Managing Partitions 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 10: Managing Partitions iii BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Instructor Notes For enterprise-scale online analytical processing (OLAP) cubes developed in Microsoft ® SQL Server ™ 2000 Analysis Services, partitioning can improve both processing and query performance. In this module, students learn how to create partitions, how to define slices and filters, and the benefits of using partitions in cubes to improve scalability. After completing this module, students will be able to: ! Explain the benefits of partitioning. ! Describe the mechanics of the Partition Wizard. ! Explain when to define slices and when to define filters. ! Describe the purpose and mechanics of merging partitions. 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_10.ppt Preparation Tasks To prepare for this module, you should: ! Read all the student materials. ! Read the instructor notes and margin notes. ! Practice the lecture presentation and demonstration. ! 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. Presentation: 30 Minutes Labs: 30 Minutes iv Module 10: Managing Partitions 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. After defining multiple partitions, there are empty spaces in the cube even though there is data in the fact tables to support the cells. What causes this? Incomplete partitions can cause missing data. Incomplete partitions result when a partition is misdefined (perhaps using a member from a level that is too low for a slice) or not defined at all. Be careful—if a partition is misdefined and another partition is added to fix the problem, duplicate data can result. 2. What is the best way to split a partition into one or more different partitions? There is no direct way to do this. To split up an existing partition, you must redefine the partition on a smaller slice or modify the WHERE clause and then define new partitions. 3. If a different fact table than the one defined for the partition is used for an incremental load of the partition, what happens when the cube is refreshed or processed in the future? The incremental data will not be included. Analysis Services refers to only one fact table per cube partition. Module 10: Managing Partitions v BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module Strategy Use the following strategy to present this module: ! Introducing Partitions Start with an explanation of why partitions are used. Emphasize the fact that partitions are transparent—users and front-end applications see only cubes. Explain that partitions in a cube may have different storage modes, aggregation designs, and physical locations. Define remote partitions. ! Creating Partitions Explain each step involved in creating a partition—choosing the fact table, defining a data slice, assigning the partition location, and completing the partition. Finish by describing how to access commands in Analysis Manager. ! Using Advanced Settings Introduce students to the Advanced settings dialog box. Describe the settings available—specifying filters, enabling drillthrough options, and setting the aggregation prefix—and explain when to use each. ! Merging Partitions Explain to students why merging partitions can be beneficial. Use the Current Year/Prior Year/History partition example. Describe the steps involved in merging partitions. Emphasize the fact that, to be merged, two partitions must have the same storage mode and aggregation design. Module 10: Managing Partitions 1 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Overview ! Introducing Partitions ! Creating Partitions ! Using Advanced Settings ! Merging Partitions For enterprise-scale online analytical processing (OLAP) cubes developed in Microsoft ® SQL Server ™ 2000 Analysis Services, partitioning can improve both processing and query performance. In this module, you will learn how to create partitions, how to define slices and filters, and the benefits of using partitions in cubes to improve scalability. After completing this module, you will be able to: ! Explain the benefits of partitioning. ! Describe the mechanics of the Partition Wizard. ! Explain when to define slices and when to define filters. ! Describe the purpose and mechanics of merging partitions. Topic Objective To provide an overview of the module topics and objectives. Lead-in In this module, you will learn about partitions and their use in OLAP cubes. 2 Module 10: Managing Partitions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY # ## # Introducing Partitions ! Basic Architecture ! Partitioning Design ! Remote Partitions You create partitions in cubes to help manage cube storage, process cubes, and query cubes. In this section, you will learn about partition architecture and design and the use of partitions in OLAP cubes. Topic Objective To introduce the concept of partitions. Lead-in In this section, you will learn about partition architecture and design and the use of partitions in OLAP cubes. Module 10: Managing Partitions 3 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Basic Architecture ! Act As Physical Storage Mediums for Cube Data ! Improve Cube Performance $ Cube processing performance $ Query performance ! Are Transparent to Users ! Require SQL Server 2000 Enterprise Edition $ Analysis Server and Analysis Manager Computers Partitions are the physical storage mediums for cube data. A cube may have one or more partitions. Each partition may have a different storage mode with a different aggregation design. In addition, each partition may be located on a different server. All cubes initially have a single default partition. When you design aggregations for a one-partition cube, you are actually designing aggregations for the partition, not for the cube. When you process a single-partition cube, you are also processing the partition, not the cube. If a cube contains more than one partition, attempting to design storage for a cube opens up a dialog box that requires you to select a single partition for designing aggregations. You create partitions to improve cube processing and query performance, increasing the scalability of a cube. Partitions are processed either as part of a full cube process or independent from other cube partitions. By processing partitions independently, you isolate processing to a subset of cube data, and therefore reduce the processing time. In addition, queries can focus on a single partition and can perform faster data retrievals due to the smaller data set being accessed. Partitions are transparent. Users and application front-ends see only cubes—that is, they query a cube and not a partition. The cube reflects the combined data contained in all its partitions. To create multiple partitions in a cube, you must have the Enterprise Edition of SQL Server 2000 installed on the Analysis Server and on any computers administering the server. To install the Enterprise Edition of SQL Server 2000, the computer requires one of the following operating systems: ! Microsoft Windows NT ® Server 4.0 with Service Pack 5 ! Windows NT Server Enterprise Edition 4.0 with Service Pack 5 ! Microsoft Windows 2000 Advanced Server ! Windows 2000 Data Center Server Topic Objective To describe the fundamental characteristics of cube partitions. Lead-in Partitions are the physical storage mediums for cube data. 4 Module 10: Managing Partitions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Partitioning Design History Prior Year MOLAP 35% agg ROLAP 0% agg Current Year MOLAP 10% agg Partitions separate cube data into discrete storage areas. Each partition in a cube may have different: ! Storage modes—multidimensional OLAP (MOLAP), relational OLAP (ROLAP), or hybrid OLAP (HOLAP). ! Source fact tables—Salesfact2000 for one partition and Salesfact2001 for another. ! Aggregation designs—35 percent aggregation in one MOLAP partition versus 10 percent aggregation in a second MOLAP partition versus 0 percent aggregation in a third ROLAP partition. ! Storage locations—Server 1 in Pittsburgh, Server 2 in San Francisco, and so forth. Because of these factors, partitioning is the principal feature in Analysis Services for increasing cube scalability and designing storage to reflect user access and response time needs. In the preceding illustration, three partitions exist in one cube. The data in the partitions, representing the entire accounting history for a company, is organized by current year, prior year, and historical data. The aggregation design for each of the partitions reflects specific user access needs and storage considerations. The partitions are designed as follows: ! Current Year The current year partition design contains the highest aggregation percentage using the MOLAP storage mode—the fastest storage method. The design reflects the high number of users, the high frequency of the access of each user, and the presumed performance requirements for the reporting and analysis of current year data. Topic Objective To describe the use of different aggregation and storage designs in partitions. Lead-in Partitions separate cube data into discrete storage areas. Each partition in a cube may have different storage modes, aggregation designs, and physical locations. Delivery Tip Point out that agg stands for aggregation in the preceding illustration. [...]... TRAINER PREPARATION PURPOSES ONLY Module 10: Managing Partitions 29 Review Topic Objective To reinforce module objectives by reviewing key points ! Introducing Partitions Lead-in ! Creating Partitions ! Using Advanced Settings ! Merging Partitions The review questions cover some of the key concepts taught in the module 1 If you do not have the ability to create new partitions in Analysis Manager, what... History partition requires merging of the previous Prior Year and History partitions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 10: Managing Partitions 25 Partition Merge Procedure The steps for merging partitions are straightforward To merge two partitions, perform the following steps: 1 Expand the Partitions folder, and then right-click the name of the partition you... merge partitions based on different fact tables automatically You must first merge the respective fact tables manually, and then merge the partitions Analysis Manager does not warn you if you attempt to merge partitions accessing separate fact tables BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 26 Module 10: Managing Partitions Lab B: Applying Advanced Settings to Partitions. .. duplication issue in partitions ! Create a partition filter ! Merge two partitions Lab Setup To complete this lab, you need to complete lab A, Creating a Partition in the Sales Cube, found earlier in the module Estimated time to complete this lab: 20 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 10: Managing Partitions 27 Exercise 1 Updating the Sales Cube Partitions. .. and hardware availability BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 6 Module 10: Managing Partitions Remote Partitions Topic Objective To introduce the concept of remote partitions ! Remote Partitions Are Stored on a Separate Server $ You have the ability to define cube partitions as remote in Analysis Services Data stored separately $ Lead-in Processing performed separately... aggregation tables created by multiple partitions in the data source The aggregation prefix defined in the Partition Wizard is added to any aggregation prefixes defined at the cube level in the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 24 Module 10: Managing Partitions Merging Partitions Topic Objective To discuss the process of merging partitions and to explain the... existing partitions Lead-in In Analysis Manager, you can merge partitions into other partitions to consolidate cube data You can create multiple partitions from a cube with a single default partition In turn, multiple partitions can be merged to create a single partition In some situations, you can merge partitions to take advantage of optimized query performance Queries that access multiple partitions. .. transparent—that is, setup and administration is the same as for a non-remote partition BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 10: Managing Partitions 7 # Creating Partitions Topic Objective To introduce the mechanics of creating partitions ! Completing the Partition ! Present this entire section as an informal demonstration Switch back and forth between the slides and the... for other cube partitions 3 Select the Process the partition when finished check box to initiate processing of the partition after you click the Finish button 4 Click Finish BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 14 Module 10: Managing Partitions Accessing Commands Topic Objective To describe the process of updating, creating, modifying, and processing partitions in... Sales cube, expand the Partitions folder, and then notice the default partition in the Sales cube—Sales The default partition has the same name as the cube name 2 Right-click the Partitions folder and then click New Partition The welcome page of the Partition Wizard opens Click Next BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 10: Managing Partitions 17 ! To select . partitions. Emphasize the fact that, to be merged, two partitions must have the same storage mode and aggregation design. Module 10: Managing Partitions. characteristics of cube partitions. Lead-in Partitions are the physical storage mediums for cube data. 4 Module 10: Managing Partitions BETA MATERIALS