Module 5: Using Advanced Dimension Settings Contents Overview Working with Levels and Hierarchies Working with Time Dimensions 10 Lab A: Creating Time Dimensions 18 Lab B: Creating Hierarchies from Single Columns 22 Creating Custom Rollups 29 Lab C: Creating a Custom Rollup 34 Introducing Member Properties 37 Lab D: Creating Member Properties 42 Understanding Virtual Dimensions 44 Lab E: Creating Virtual Dimensions 50 Review 54 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, the 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 5: Using Advanced Dimension Settings iii Instructor Notes Presentation: 50 Minutes Labs: 70 Minutes There are several types of dimensions that can be created by using Microsoft® SQL Server™ 2000 Analysis Services In this module, you lead students through a solutions-oriented discussion of various advanced dimension settings and methods used to develop online analytical processing (OLAP) dimensions and cubes This module contains labs that give students hands-on experience with various types of real-world dimensions After completing this module, students will be able to: ! Work with dimension levels and hierarchies ! Understand and work with time dimensions ! Create custom rollup dimensions ! Define member properties at dimension levels ! Create virtual dimensions from member properties and member levels 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_05.ppt Preparation Tasks To prepare for this module, you should: ! Read all the student materials ! Read the instructor notes and margin notes ! Complete the demonstration ! 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 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY iv Module 5: Using Advanced Dimension Settings Demonstration: Creating Virtual Dimensions Demonstration: To place a virtual dimension in a cube, you must perform the following tasks: 10 Minutes Create the source member property, if the virtual dimension is based on a member property Create a new virtual dimension Add the virtual dimension to the cube Process the cube 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 In Analysis Manager, right-click the server, click Restore Database, click the Look in list, click the file C:\Moc\2074A\Labfiles\L05\Module 05.CAB, click Open, and then click Restore After the database successfully restores, click Close Double-click Module 05 to expand the database Below Module 05, double-click Data Sources, right-click the Module 05 data source, and then click Edit Click the Connection tab of the Data Link Properties dialog box, and then verify that localhost is selected in step In step 2, click Use Windows NT Integrated security In step 3, click Module 05 from the Select the database on the server list Click Test Connection and verify that the test succeeded, click OK, and then click OK to close the Data Link Properties dialog box ! To create a new member property In Analysis Manager, expand the Module 05 database, expand the Shared Dimensions folder, right-click Customers, and then click Edit In the Schema pane, right-click the Customer table, and then click Browse Data Notice the gender column You will create a new virtual dimension based on gender, because users need to see customer by gender in their reports Close the Browse Data window Double-click the Name level, right-click the Member properties folder, and then click New Member Property Click gender under the Customer table, and then click OK Click the Data tab, expand the Customers dimension to the lowest level, and then click on a few leaf members to show the member property values in the Member properties pane Click Save, and then close the Dimension Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings v ! To create a new virtual dimension In the Module 05 database, right-click the Shared Dimensions folder, point to New Dimension, and then click Wizard Click Next, click Virtual Dimension, and then click Next In the Available Dimensions pane, click Customers, notice Name.Gender in the Member properties pane, and then click Next In the Available member properties pane, double-click Name.Gender, and then click Next twice Type Gender in the Dimension name box, and then click Finish to open the Dimension Editor Close the Dimension Editor ! To create a new cube In the Module 05 database, right-click the Cubes folder, point to New Cube, click Wizard, and then click Next Click the custfact table, and then click Next Double-click store_sales and store_cost, and then click Next Double-click Customers, CustTime, and Gender, and then click Next Click Yes when the dialog asks to count fact table records Type Gender Cube in the Cube name box, and then click Finish ! To browse the virtual dimension In the Cube Editor, click the Tools menu, click Process Cube, and then click No to process the cube without designing aggregations In the Process a Cube dialog box, click OK When the cube is finished processing, click Close Click the Data tab to browse the cube Pivot the Customers dimension so that it becomes the column dimension and pivot the Gender dimension so that it becomes the row dimension Click Save, and then close the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY vi Module 5: Using Advanced Dimension Settings 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 Why you need to disable the counting of members when creating dimensions? Some dimensions are so large that Analysis Manager cannot successfully count the members at all the levels Analysis Manager can close down while attempting to count millions of members Therefore, in some instances you need to manually enter the member counts due to the technical restriction in Analysis Manager when working with extremely large dimensions How front-end applications display dimensions containing multiple hierarchies? The front-end applications that developers use most often to browse cubes, namely Microsoft Excel 2000 and the Cube Browser, not display multiple hierarchies in the same way that most third-party applications display them Excel 2000 and the Cube Browser treat each hierarchy as a separate, independent dimension However, most thirdparty applications direct the user to select one hierarchy per dimension at any one time to browse cube data Therefore, the users never view multiple hierarchies together on the same report How much detail you typically define in a Time dimension? All Time dimensions vary in the level of detail that they store OLAP systems tend to store data at a more summarized level than operational systems Therefore, a common hierarchical structure in Time dimensions includes Year, Quarter, and Month However, many companies track daily sales in OLAP cubes and therefore store data down to the Day level Manufacturing systems exist that define Time down to the Minute level The levels defined in a Time dimension depend on the reporting and processing requirements of the cube How slow are queries that use virtual dimensions? Query performance depends on a variety of factors, including cube complexity, the number of members in a virtual dimension, the amount of data accessed by the query, and even network traffic Therefore, you must test the performance of queries that incorporate virtual dimensions to ensure that the response times are not excessively slow Virtual dimensions in Analysis Services have improved since their initial release in SQL Server [version 7.0] OLAP Services Nevertheless, you should always test performance before releasing cubes to users BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings vii Module Strategy Use the following strategy to present this module: ! Working with Levels and Hierarchies Explain the importance of levels in the hierarchical structure of a dimension Define the various level settings and describe how these settings enhance levels and hierarchies Define the hierarchies and describe how to create multiple hierarchies in a single dimension Describe the ways to create grouping levels in dimensions containing large amounts of members ! Working with Time Dimensions Explain the importance of Time dimensions in OLAP cubes Describe the characteristics of Time dimensions and explain how to create them Discuss various Time dimension properties and why they are used Explain what a fiscal date hierarchy is and when it used Finally, describe how to use a string to define a Time dimension ! Creating Custom Rollups Define custom rollups and describe how they are used to model hierarchical calculations in dimensions Introduce the use of unary operators, and describe the different operators and their behaviors Finish the section by describing how to employ custom rollups in a dimension and the restrictions related to creating them in cubes ! Introducing Member Properties Introduce the use of member properties in dimensions Describe the characteristics of member properties and explain the steps of creating and previewing a new member property ! Understanding Virtual Cube Dimensions Begin the section by defining virtual dimensions Explain when they are used and how they are created Compare virtual dimensions to regular dimensions and discuss when each type of dimension should be used BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings Overview Topic Objective To provide an overview of the module topics and objectives ! Working with Levels and Hierarchies Lead-in ! Working with Time Dimensions ! Creating Custom Rollups ! Introducing Member Properties ! Understanding Virtual Dimensions In this module, you will learn about advanced dimension types and settings There are several types of dimensions that can be created by using Microsoft® SQL Server™ 2000 Analysis Services This module consists of a solutionsoriented discussion of various advanced dimension settings and methods used to develop online analytical processing (OLAP) dimensions and cubes This module contains labs that give you hands-on experience with various types of real-world dimensions After completing this module, you will be able to: ! Work with dimension levels and hierarchies ! Understand and work with time dimensions ! Create custom rollup dimensions ! Define member properties at dimension levels ! Create virtual dimensions from member properties and member levels BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings # Working with Levels and Hierarchies Topic Objective To introduce the concept of levels and hierarchies ! Defining a Hierarchy ! Creating Multiple Hierarchies ! In this section, we will discuss important settings and processes that enhance levels and hierarchies Applying Level Settings ! Lead-in Grouping Members into Levels Creating levels in dimensions establishes the hierarchical structure of dimensions Levels exist in each dimension and determine the detail and summary data stored in cubes In this section, you will learn about the following topics that pertain to levels and hierarchies: ! Advanced settings that apply to levels and dimensions ! The definition of a hierarchy and how levels define hierarchies ! The process for creating multiple hierarchies in a dimension ! The Grouping property that creates member groupings in dimensions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 42 Module 5: Using Advanced Dimension Settings Lab D: Creating Member Properties Topic Objective To introduce the lab Lead-in In this lab, you will create two member properties in the Product dimension Explain the lab objectives Objectives After completing this lab, you will be able to: ! Create member properties in dimensions ! Preview member properties Lab Setup To complete this lab, you must first complete: ! Lab A, Creating Time Dimensions, found earlier in the module ! Lab B, Creating Hierarchies from Single Columns, found earlier in the module ! Lab C, Creating a Custom Rollup, found earlier in the module Estimated time to complete this lab: 10 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings 43 Exercise Creating Product Dimension Member Properties In this exercise, you will create two member properties, Price and Category Manager They exist at separate levels in the Product dimension You will create and preview the member properties in the Dimension Editor ! To create the Price member property In Analysis Manager, expand Module 05, expand the Shared Dimensions folder, right-click Product, and then click Edit In the dimension tree, expand the Product level so that the Member properties folder appears below it The member property Brand Name exists for the Product level You will add another member property to the level Drag the Price column from the Product table onto the Member properties folder for the Product level Click the Data tab, and in the Dimension Members pane, expand All Product, Bread, and Bagels Click the 1218 – Bagels member to see its price displayed in the Member properties pane You can now reference Price values in cube calculations By adding Price as a member property, you can use its values in the cube even though it does not exist in the cube fact table ! To create the Category Manager member property In the dimension tree, click the Subcategory level On the Insert menu, click Member Property In the Insert Member Property dialog box, double-click Category_Manager In the Dimension Members pane, expand the All Product and Bread members, and then click Bagels You can see the manager name Sheri Nowmer in the Member properties pane Click Save to save the Product dimension Close the Dimension Editor Note You can create a member property at any level A member property applies to the single level If products and families also had managers, you would need to create a separate member property for each level BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 44 Module 5: Using Advanced Dimension Settings # Understanding Virtual Dimensions Topic Objective To introduce the concept of virtual dimensions ! Placing Virtual Dimensions in Cubes ! A virtual dimension is a logical dimension based on either a member property of another dimension or a level in a dimension Defining Virtual Dimensions ! Lead-in Using Virtual Dimensions A virtual dimension is a logical dimension based on either a member property of another dimension or a level in a dimension You create virtual dimensions to reduce cube storage and to reduce cube processing time In this section, you will learn about virtual dimensions and their use in cubes The following topics will be discussed: ! Defining virtual dimensions ! Placing virtual dimensions in cubes ! Using virtual dimensions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings 45 Defining Virtual Dimensions Topic Objective To define virtual dimensions and their characteristics ! Appear as Regular Dimensions to Users ! Can Contain Multiple Levels ! Can Contain Member Properties ! Do Not Have Aggregations ! Do Not Increase Cube Storage and Processing Time ! Let us discuss some characteristics of virtual dimensions Are Based on Levels and Member Properties of Other Dimensions ! Lead-in Increase Query Times for Queries that Reference Them When deciding on dimension types for the cubes, you must understand the benefits of using virtual dimensions versus regular dimensions The following are characteristics of virtual dimensions: ! Virtual dimensions are based on member properties and levels of physical dimensions ! From the user’s point of view, a virtual dimension is no different from a physical dimension Users view and manipulate virtual dimensions just like any other dimension ! Virtual dimensions can contain multiple levels defined in their structure ! Virtual dimensions can contain member properties associated with their levels ! Analysis Services does not create aggregations for virtual dimensions ! Virtual dimensions not affect cube storage or cube processing time ! Virtual dimensions increase query times for queries that reference them This is because aggregations are not created for virtual dimension members at cube processing time However, virtual dimensions not affect queries that not reference them BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 46 Module 5: Using Advanced Dimension Settings Placing Virtual Dimensions in Cubes Topic Objective To define a few requirements associated with adding a virtual dimension to a cube ! ! The process of creating virtual dimensions and adding them to cubes is simple if you are aware of a few related issues The Source Dimension Must Exist within the Cube ! Lead-in The Member Property Must Exist Prior to Creating the Virtual Dimension Virtual Dimensions Are Added in the Same Way as Regular Dimensions The process of creating virtual dimensions and adding them to cubes is uncomplicated if you are aware of a few related issues: ! Before you can create a virtual dimension based on a member property, the member property must exist You create member properties in the Dimension Editor for shared dimensions and in the Cube Editor for private dimensions ! You cannot add a virtual dimension to a cube unless the source dimension that supplies the member property or level is also included in the cube ! You add virtual dimensions to cubes in the same way you add physical dimensions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings 47 Using Virtual Dimensions Topic Objective ! To describe situations in which you create a virtual dimension versus situations in which you create a regular dimension When to Create a Virtual Dimension $ $ Virtual dimensions are very useful, but may not always provide the best solution for the users ! Cube storage is a problem $ Lead-in Cube processing times are a problem A minority of users require a dimension When to Create a Regular Dimension $ Cube processing times are not a problem $ Cube storage is not a problem $ Query times must be fully optimized $ Delivery Tip Use this slide to summarize the discussion of virtual dimensions Students should understand when to use and when not to use virtual dimensions in a cube The dimension requires features not available in virtual dimensions Virtual dimensions are very useful, but may not always provide the best solution for the users You must determine the most optimal situations in which to use virtual dimensions in cubes When to Create a Virtual Dimension The following list describes situations in which you create a new virtual dimension for the cube: ! Cube Processing Times Are a Problem You can reduce cube processing times by using virtual dimensions instead of regular dimensions No data or aggregations are produced for virtual dimensions and therefore cubeprocessing times are not impacted by the addition of virtual dimensions to the schema of a cube ! Cube Storage Is a Problem Virtual dimensions not add data or aggregations to a cube Therefore, the addition of a virtual dimension to the cube schema does not affect cube size ! A Minority of Users Require a Dimension If only a few users need to see information by a particular variable—for example, customer data by gender—then that variable is a good candidate for a virtual dimension Virtual dimensions can cause slow queries, depending on the number of members found in the virtual dimension and on the number of members in the source dimension However, the only queries impacted by virtual dimensions are those queries that include the virtual dimension In the above gender example, gender reports may be slow for the few users accessing them, but no reports for the other users are impacted BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 48 Module 5: Using Advanced Dimension Settings When to Create a Regular Dimension The following list describes situations in which you create a regular dimension—versus a virtual dimension—for a cube ! Cube Processing Times Are Not a Problem If the cube processing times are very short, create a regular dimension and see how adding it to the cube impacts processing time ! Cube Storage Is Not a Problem Cube processing problems are much more common and difficult to deal with than cube storage problems However, if you not have problems with cube processing times and storage, create regular dimensions instead of virtual dimensions ! Query Times Must Be Fully Optimized Query time performance is most often the most important gauge of a successful OLAP cube In most cases, users demand the fastest queries possible If using a regular dimension improves query performance considerably, and cube processing and storage does not pose a problem, define the dimension as regular ! The Dimension Requires Features Not Available in Virtual Dimensions A number of dimension features are not available when you define a dimension as virtual For example, if you need to create a dimension based on a parent-child dimension table, you must create it as a regular dimension You need to understand the requirements of the dimension and whether or not it is supported as a virtual dimension BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings 49 Demonstration: Creating Virtual Dimensions Topic Objective To demonstrate how to create a virtual dimension Lead-in In this demonstration, you will learn how to create a virtual dimension and how to add it to a cube Delivery Tips The steps for this demonstration are included in the Instructor Notes Tell students that they will be performing the same exercise themselves in the next lab Therefore, if they follow along with your demonstration, they can skip the repeated procedures in the lab To place a virtual dimension in a cube, you must perform the following steps: Create the source member property, if the virtual dimension is based on a member property, Create a new virtual dimension Add the virtual dimension to the cube Process the cube In this demonstration, you will learn how to create a virtual dimension and how to add it to a cube BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 50 Module 5: Using Advanced Dimension Settings Lab E: Creating Virtual Dimensions Topic Objective To introduce the lab Lead-in In this lab, you will create two more virtual dimensions in the Module 05 database Objectives Explain the lab objectives After completing this lab, you will be able to: ! Create a virtual dimension based on a member property ! Create a virtual dimension based on a level in a dimension Lab Setup To complete this lab, you must first complete: ! Lab A, Creating Time Dimensions, found earlier in the module ! Lab B, Creating Hierarchies from Single Columns, found earlier in the module ! Lab C, Creating a Custom Rollup, found earlier in the module ! Lab D, Creating Member Properties, found earlier in the module Estimated time to complete this lab: 10 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings 51 Exercise Creating the Gender Dimension In this exercise, you will create a virtual dimension based on a member property defined in the Customer dimension You will create a new member property, Gender, and will create a new virtual dimension from Gender Finally, you will create a new cube and will add the Customer and Gender dimensions to its schema ! To create a new member property In Analysis Manager, expand the Module 05 database, expand the Shared Dimensions folder, right-click Customers, and then click Edit In the Schema pane, right-click the top of the customer table, and then click Browse Data Notice the gender column You are going to create a new virtual dimension based on gender, because users need to see customer by gender in their reports Close the Browse Data window Double-click the Name level, right-click the Member properties folder, and then click New Member Property Click gender under the Customer table, and then click OK Click the Data tab, expand the Customers dimension to the lowest level, and then click on a few leaf members to show the member property values in the Member properties pane Click Save, and then close the Dimension Editor ! To create a new virtual dimension In the Module 05 database, right-click the Shared Dimensions folder, point to New Dimension, and then click Wizard Click Next, click Virtual Dimension, and then click Next In the Available Dimensions pane, click Customers, notice Name.Gender in the Member properties pane, and then click Next In the Available member properties pane, double-click Name.Gender, and then click Next twice Type Gender in the Dimension name box, and then click Finish to open the Dimension Editor Close the Dimension Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 52 Module 5: Using Advanced Dimension Settings ! To create a new cube In the Module 05 database, right-click the Cubes folder, point to New Cube, click Wizard, and then click Next Click the custfact table, and then click Next Double-click store_sales and store_cost, and then click Next Double-click Customers, CustTime, and Gender, and then click Next Click Yes when the dialog asks to count fact table records Type Gender Cube in the Cube name box, and then click Finish ! To browse the virtual dimension In the Cube Editor, click the Tools menu, click Process Cube, and then click No to process the cube without designing aggregations In the Process a Cube dialog box, click OK When the cube is finished processing, click Close Click the Data tab to browse the Gender Cube Pivot the Customers dimension so that it becomes the column dimension, and pivot the Gender dimension so that it becomes the row dimension Click Save, and then close the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings 53 Exercise Creating the Quarter Dimension In this exercise, you will create a virtual dimension based on a level in the BrandTime dimension You will then add the virtual dimension to the Brand cube to see how you can split a dimension across separate axes in a report by using virtual dimensions ! To create a new virtual dimension In the Module 05 database, right-click the Shared Dimensions folder, point to New Dimension, and then click Wizard Click Next, click the Virtual Dimension option, and then click Next Select the Display member keys and names check box, click BrandTime in the Available Dimensions pane, and then click Next Double-click Quarter in the Available member properties pane, and then click Next twice Type Quarter in the Dimension name box, and then click Finish to open the Dimension Editor Close the Dimension Editor ! To add Quarter to the Brand Cube In the Module 05 database, expand the Cubes folder, right-click Brand Cube, and then click Edit Right-click the Dimensions folder and then click Existing Dimensions Double-click Quarter and then click OK Click the Tools menu, click Process Cube, and then click Yes when the dialog asks if you want to continue Click No to process the cube without designing aggregations Click OK in the Process a Cube dialog box When the cube is finished processing, click Close Click the Data tab to browse the Brand Cube In the Product list, click the All Product member 10 Pivot the Quarter dimension so that it becomes the column dimension and pivot the BrandTime dimension so that it becomes the row dimension You will notice that the two dimensions give you the ability to view the date information across multiple axes 11 Click Save, and then close the Cube Editor BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY 54 Module 5: Using Advanced Dimension Settings Review Topic Objective To reinforce module objectives by reviewing key points ! Working with Levels and Hierarchies Lead-in ! Working with Time Dimensions ! Creating Custom Rollups ! Introducing Member Properties ! Understanding Virtual Dimensions The review questions cover some of the key concepts taught in the module What naming convention is used to differentiate multiple hierarchies in a dimension? Multiple hierarchies in a dimension have the naming convention of DimensionName.HierarchyName Describe some reasons to create a separate date dimension table in the data warehouse source A separate date dimension table can contain time-related properties to enhance analysis In addition, using a separate table reduces overall storage space Finally, a separate date dimension table facilitates the creation of a shared date dimension used in multiple cubes When can you not use a custom rollup in a dimension? You cannot create a custom rollup dimension in a cube that uses the Distinct Count aggregation method How virtual dimensions affect query performance? When you not reference them in a query, virtual dimensions not affect query performance However, you will have performance penalties in queries that reference virtual dimensions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module 5: Using Advanced Dimension Settings 55 What are positive and negative characteristics of virtual dimensions? Virtual dimensions not increase cube size or processing time However, they are slower when referenced in queries, and they not contain all functionality included with regular dimensions BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY THIS PAGE INTENTIONALLY LEFT BLANK ... ONLY Module 5: Using Advanced Dimension Settings 11 Using the Dimension Wizard to Create Dates Topic Objective To describe the benefits of using the Dimension Wizard when creating Time dimensions... 10 Module 5: Using Advanced Dimension Settings # Working with Time Dimensions Topic Objective To introduce the concept of Time dimensions ! Using a Separate Table for Dates ! Setting Time Dimension. .. PURPOSES ONLY Module 5: Using Advanced Dimension Settings v ! To create a new virtual dimension In the Module 05 database, right-click the Shared Dimensions folder, point to New Dimension, and