Module 12: Working with Virtual Cubes

Chia sẻ: Vu Trung | Ngày: | Loại File: PDF | Số trang:26

Thêm vào BST

Báo xấu

66
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'module 12: working with virtual cubes', công nghệ thông tin, quản trị mạng phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Module 12: Working with Virtual Cubes

Module 12: Working with Virtual Cubes Contents Overview 1 Understanding Virtual Cubes 2 Obtaining Logical Results 4 Building a Virtual Cube 5 Lab A: Creating Virtual Cubes 10 Creating Calculated Members 14 Lab B: Adding Calculated Members to a Virtual Cube 16 Review 19
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 12: Working with Virtual Cubes iii Instructor Notes Presentation: A virtual cube is an online analytical processing (OLAP) cube that combines or 25 Minutes extracts data from one or more source cubes. These cubes are called virtual because they consume no disk storage, other than metadata. In this module, Labs: students learn when to use virtual cubes and the mechanics of how to build 20 Minutes them in Microsoft® SQL Server™ 2000 Analysis Services. After completing this module, students will be able to: Understand when to use virtual cubes and know their benefits. ! Know the rules for constructing meaningful virtual cubes. ! Build virtual cubes by using the Virtual Cube Wizard. ! Define calculated members in virtual cubes by using the Calculated Member ! Builder. 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_12.ppt. Preparation Tasks To prepare for this module, you should: Read all of the student materials for this module. ! Read the instructor notes and margin notes. ! 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 12: Working with Virtual Cubes Other Activities Difficult Questions Below are difficult questions that students may ask you during the delivery of this module and answers to the questions. There 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 does the enabling or disabling of levels in the source cube affect the enabling or disabling of levels in the virtual cube? Enabling or disabling levels in the source cube does not affect the enabling or disabling of levels in the virtual cube. Levels in the source cube are completely separate from levels in the virtual cube, so that, when users are using a virtual cube, they can access members for querying or for creating calculations down to the lowest level in the dimension, regardless of level enabling or disabling in the source cube. 2. Can you add a virtual dimension to a virtual cube without also adding the dimension on which the virtual dimension depends? No. When you add a virtual dimension to a virtual cube, the regular dimension is added to the virtual cube automatically. 3. Can you base virtual cubes on linked cubes? Yes. 4. When you process a source cube, are all virtual cubes that use the source cube data automatically processed? No. 5. Can a virtual cube combine source cubes that use multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP (HOLAP) storage modes? Yes. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 12: Working with Virtual Cubes v Module Strategy Use the following strategy to present this module: Understanding Virtual Cubes ! Begin with a definition of virtual cubes and describe their characteristics. Discuss virtual cube limitations and storage. Obtaining Logical Results ! Explain that, when building a virtual cube from more than one source cube, students must follow some simple rules to obtain logical results. Explain that they need to use dimensions and measures that the source cubes have in common—otherwise there is no analytical value in combining the cubes. Building a Virtual Cube ! Introduce students to the Virtual Cube Wizard. While giving the lecture, open the Virtual Cube Wizard on your computer and show students the various interfaces. Describe the steps involved in creating a virtual cube— choosing the source cube, choosing the measures, choosing the dimensions, and finalizing the cube. Creating Calculated Members ! Explain that calculated members can be added to virtual cubes by using the Virtual Cube Editor. Describe the different ways to add calculated members to virtual cubes—creating calculated members and importing them. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 12: Working with Virtual Cubes 1 Overview Topic Objective To provide an overview of the module topics and Understanding Virtual Cubes objectives. ! Lead-in Obtaining Logical Results ! In this module, you will learn about virtual cubes and how Building a Virtual Cube ! to create them. Creating Calculated Members ! A virtual cube is an online analytical processing (OLAP) cube that combines or extracts data from one or more source cubes. These cubes are called virtual because they consume no disk storage other than metadata. In this module, you will learn when to use virtual cubes and the mechanics of how to build them in Microsoft® SQL Server™ 2000 Analysis Services. After completing this module, you will be able to: Understand when to use virtual cubes and know their benefits. ! Know the rules for constructing meaningful virtual cubes. ! Build virtual cubes by using the Virtual Cube Wizard. ! Define calculated members in virtual cubes by using the Calculated Member ! Builder. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
2 Module 12: Working with Virtual Cubes Understanding Virtual Cubes Topic Objective Virtual Cube Characteristics ! To introduce the characteristics, limitations, Combine data from multiple cubes # and storage of virtual cubes. Provide users with a subset of a single cube # Lead-in A virtual cube combines or Are used to limit user access to cube data # extracts data from one or Dimension Limitations more source cubes. In many ! respects, a virtual cube is to Cannot slice dimensions # Analysis Server what a view is to a relational database Cannot include only some of a dimension’s levels # management system (RDBMS). Storage and Performance ! Can include MOLAP, ROLAP, and HOLAP cubes # Provide endless cube flexibility # A virtual cube is a cube that combines or extracts data from one or more source cubes. In many respects, a virtual cube is to Analysis Server what a view is to a relational database management system (RDBMS)—it provides a different view of the underlying data without storing any additional data except the metadata that defines the virtual cube. Note This module defines virtual cubes as being derived from source cubes. Source cubes are the cubes from which a virtual cube is derived. Virtual Cube Characteristics To users and applications, a virtual cube is simply a cube. It looks and behaves exactly like a source cube. A virtual cube can be used to: Combine data from two or more source cubes. For example, a cube ! containing sales data might be combined with a cube containing forecasts to measure actual sales versus forecast sales. Note If a virtual cube is based on more than one source cube, when the virtual cube is queried, it queries the source cubes in parallel. A virtual cube can combine up to 64 source cubes. Provide users with a subset of the dimensions and measures from a single ! source cube. This is useful when a source cube contains a great many measures and dimensions, and a group of users is interested in only a subset. Provide users with a subset of the dimensions and measures from a single ! source cube as a security measure. For instance, the source cube might contain a profit measure that certain users or groups of users are not allowed to see. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 12: Working with Virtual Cubes 3 There is no limit to the number of virtual cubes that can be defined in a database. Dimension Limitations Following are limitations of virtual cubes: Virtual cubes cannot slice the dimensions of the source cubes. For example, ! suppose a source cube contains the dimension Region. If Region is placed in a virtual cube, the entire dimension is included. You cannot include only the Western region. You cannot choose which levels of the dimension are included. For ! example, you cannot include summary levels and exclude bottom-level members. The entire dimension, with all levels, must be included. The Measures dimension is an exception to these rules. You can pick the specific measures that populate a virtual cube. Storage and Performance Because virtual cubes consume no disk space, they rely on the underlying storage of the source cubes. Therefore: A single virtual cube can include multidimensional OLAP (MOLAP), ! relational OLAP (ROLAP), and hybrid OLAP (HOLAP) source cubes. Because a single source cube can contain MOLAP, ROLAP, and HOLAP ! partitions, there is endless flexibility in designing virtual cubes. Virtual cubes automatically use aggregations from the source cubes. The performance overhead imposed by the virtual cube on top of the source cubes is negligible. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
4 Module 12: Working with Virtual Cubes Obtaining Logical Results Topic Objective To explain the importance of using common measures and dimensions from the source cubes. Lead-in When building a virtual cube based on more than one source cube, you must follow some simple rules to obtain logical results. When building a virtual cube based on more than one source cube, you must Delivery Tips follow some simple rules to obtain logical results. Explain the preceding illustration by comparing two Common dimensions and measures are required from source cubes: cubes with different dimensionality. Combining The source cubes should contain one or more shared dimensions that are the ! them into a virtual cube same, and at least one of the common shared dimensions should be included would produce nonsensical in the virtual cube. results. Analysis Manager does not enforce this rule, but the result will be ! nonsensical if it is not followed. This can be easily visualized from the preceding spreadsheet illustration. The first cube contains region and time dimensions. The second cube contains sales representatives and scenario dimensions. Because the two cubes have nothing in common, it makes no sense to base a virtual cube on them. Note There is no analytical value in combining cubes if they do not share a dimension. Include at least one measure from each source cube in the virtual cube or ! there is no reason to combine the cubes. Again, Analysis Manager will not prevent you from doing this. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 12: Working with Virtual Cubes 5 $ Building a Virtual Cube Topic Objective To go through the steps of creating a virtual cube. Choosing the Source Cubes ! Lead-in To create a virtual cube, you Choosing the Measures ! use the Virtual Cube Wizard. Choosing the Dimensions ! Finalizing the Virtual Cube ! You build a virtual cube simply by using the Virtual Cube Wizard. To display the wizard, right-click the Cubes folder and then click New Virtual Cube. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
6 Module 12: Working with Virtual Cubes Choosing the Source Cubes Topic Objective To show how to choose source cubes for a virtual cube. Lead-in The first step in creating a virtual cube is to select the source cube or cubes for the virtual cube. At the first step of the Virtual Cube Wizard, select the source cube or cubes Delivery Tip from which the virtual cube will be derived. Enhance your lecture by opening the Virtual Cube Tip Remember that if you select multiple source cubes, these cubes should Wizard and showing have at least one shared dimension in common. students the corresponding interfaces. To select a source cube, either click a cube from the Available cubes box and Encourage students to then click the arrow (>), or double-click the cube. To select all the cubes, click follow along with your the double arrow (>>). The names of the selected cubes appear in the Virtual demonstration on their cube includes box. To remove a cube from this list, either click the cube and computers. then click the back arrow (
Module 12: Working with Virtual Cubes 7 Choosing the Measures Topic Objective To show how to select measures for a virtual cube. Lead-in The next step is to add measures to the virtual cube. The next step is to add measures to the virtual cube. The wizard displays the Delivery Tip measures from the selected source cube or cubes in the Available measures Encourage students to box. follow along with your informal demonstration on Tip Remember to include at least one measure from each source cube. The their computers. source cube name appears in the second column of the Available Measures box. To select a measure, either click a measure from the Available measures box and then click the arrow (>), or double-click the measure. To select all the measures, click the double arrow (>>). The names of the selected measures appear in the Selected measures box. To remove a measure from this list, either click the measure and then click the back arrow (
8 Module 12: Working with Virtual Cubes Choosing the Dimensions Topic Objective To show how to choose dimensions for a virtual cube. Lead-in The next step is to choose the dimensions for the virtual cube. Next, choose the dimensions. The wizard displays the dimensions from the Delivery Tip selected source cube or cubes in the Available dimensions box. Encourage students to follow along with your Tip If the virtual cube is based on more than one source cube, you must select informal demonstration on at least one shared dimension in common between the cubes to obtain a logical their computers. result. To select a dimension, either click a dimension from the Available dimensions box and then click the arrow (>) or double-click the dimension. To select all the dimensions, click the double arrow (>>). The names of the selected dimensions appear in the Selected dimensions box. To remove a dimension from this list, either click the dimension and then click the back arrow (
Module 12: Working with Virtual Cubes 9 Finalizing the Virtual Cube Topic Objective To explain how to name and process a virtual cube. Lead-in The last two steps are to name the cube and process it. The last steps are to name the cube and process it. Because a virtual cube contains no data, it processes quickly. Type the name of the virtual cube in the Delivery Tip Virtual cube name box and then decide when to process the cube—now or Encourage students to later. Click the appropriate radio button and then click Finish. follow along with your informal demonstration on their computers. Note If the source cubes or dimensions are not available at the time of processing a virtual cube, the Analysis Server processes the cubes and dimensions automatically as part of the virtual cube process. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
10 Module 12: Working with Virtual Cubes Lab A: Creating Virtual Cubes Topic Objective To introduce the lab. Lead-in In this lab, you will create two virtual cubes. You first will create a virtual cube as a subset of a source cube. You will then create a virtual cube from two source cubes. Explain the lab objectives. Objectives After completing this lab, you will be able to: Create a virtual cube that is a subset of an existing cube. ! Create a virtual cube from multiple source cubes. ! Prerequisites Before working on this lab, you must have: Experience working with Analysis Manager. ! An understanding of virtual cubes. ! Estimated time to complete this lab: 10 minutes BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 12: Working with Virtual Cubes 11 Exercise 1 Creating the Basic Sales Virtual Cube In this exercise, you will build a virtual cube, Basic Sales, that is a subset of the Sales cube. This virtual cube does not contain certain sensitive data, such as Sales by Employee. By giving users access to the Basic Sales virtual cube instead of the Sales cube, you can limit their access to certain data. ! 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, find and click the file C:\Moc\2074A\Labfiles\L12\Module 12.CAB, click Open, click Restore, and then click Close. 2. To expand the database, double-click Module 12. 3. Double-click the Data Sources folder below Module 12, right-click the Module 12 data source, and then click Edit. 4. In the Data Link Properties dialog box, click the Connection tab, 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 12 is selected. 7. Click Test Connection and verify that the test succeeded, click OK, and then click OK to exit the Data Link Properties dialog box. ! To start the Virtual Cube Wizard 1. In the Module 12 database, right-click the Cubes folder, and then click New Virtual Cube. 2. To bypass the welcome page, click Next. ! To select the source cube • In the Available cubes box, double-click the Sales cube, and then click Next. The wizard shows the regular measures (not calculated members) in the selected database. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
12 Module 12: Working with Virtual Cubes ! To select measures and dimensions 1. In the Available measures box, double-click the following measures: • Sales Units • Sales Dollars 2. Click Next. 3. In the Available dimensions box, double-click the following dimensions: • Time.Calendar • Product • State 4. Click Next. ! To finalize the cube 1. Type Basic Sales in the Virtual cube name box, click Process now, and then click Finish. Close the Process dialog box. The Virtual Cube Wizard leaves you in the Virtual Cube Editor. 2. Browse the cube data, and then close the Virtual Cube Editor. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Module 12: Working with Virtual Cubes 13 Exercise 2 Creating the Actual and Forecast Cube In this exercise, you will build a virtual cube combining the Sales and Sales Forecast cubes. The Sales cube contains sales information and the Sales Forecast cube contains forecast information. You will combine the two cubes to compare forecast units with sales units. ! To start the Virtual Cube Wizard 1. In the Module 12 database, right-click the Cubes folder, and then click New Virtual Cube. 2. To bypass the welcome page, click Next. ! To select the source cubes • In the Available cubes box, double-click the Sales cube, and then double- click the Sales Forecast cube. Click Next. ! To select measures and dimensions 1. In the Available measures box, double-click the following measures: • Sales Units from the Sales cube. • Sales Units from the Sales Forecast cube. You want to compare the same measure from two different cubes. Note that, because the virtual cube requires a unique name for each measure, the wizard names the second Sales Units measure Sales Units 1. 2. To rename the Sales Units 1 measure, click it, and then press F2. 3. When the name appears highlighted, type Forecast Units as the new name for the measure. Click Next. 4. In the Available dimensions box, double-click the following dimensions: • Product • Time.Calendar • Scenario 5. Click Next. ! To finalize the cube 1. Type Actual and Forecast in the Virtual cube name box, click Process now, and then click Finish. Close the Process dialog box. The Virtual Cube Wizard leaves you in the Virtual Cube Editor. 2. Notice the cube intersections where no data exists. 3. Browse the cube data, and then close the Virtual Cube Editor. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
14 Module 12: Working with Virtual Cubes Creating Calculated Members Topic Objective To explain how to create calculated members in a virtual cube. Lead-in You can create or import calculated members in virtual cubes. You can create calculated members in virtual cubes. In addition, you can import calculated members from the source cubes. The interface for managing calculated members in virtual cubes is very similar to the interface used in Delivery Tip regular cubes. Encourage students to follow along with your Working with the Virtual Cube Editor informal demonstration on their computers. After you create a virtual cube, it appears in the list of cubes below the Cubes folder in Analysis Manager. If you need to edit a virtual cube, right-click the cube and then click Edit. The Virtual Cube Editor opens. The Virtual Cube Editor is similar to the Cube Editor that you use to manage regular cubes. Like the Cube Editor, the Virtual Cube Editor contains the cube tree view ! and the Properties pane. Whereas the Cube Editor has a schema and data pane, the Virtual Cube ! Editor has only a data pane containing the Cube Browser. You can create new calculated members, calculated cells, actions, and ! named sets in the Cube Editor and the Virtual Cube Editor. In addition to creating new calculated members, calculated cells, actions, ! and named sets, you can import these objects into virtual cubes by using the Virtual Cube Editor. As in the Cube Editor, when you create new calculated members in the ! Virtual Cube Editor, you use the Calculated Member Builder to define and edit the calculations. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY