Module 16: Deploying an OLAP Solution

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

0
52
lượt xem
13
download

Module 16: Deploying an OLAP Solution

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

Tham khảo tài liệu 'module 16: deploying an olap solution', 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ủ đề:
Lưu

Nội dung Text: Module 16: Deploying an OLAP Solution

  1. Module 16: Deploying an OLAP Solution Contents Overview 1 Introducing DTS 2 Executing and Scheduling Packages 10 The Analysis Services Processing Task 12 Lab A: Creating a Package 16 Copying and Archiving OLAP Databases 19 Review 24
  2. 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
  3. Module 16: Deploying an OLAP Solution iii Instructor Notes Presentation: Automating cube processing, copying server objects, and moving databases 45 Minutes from testing to production environments are important steps that come at the end of an online analytical processing (OLAP) application’s deployment phase. Lab: In this module, students learn the mechanics and techniques for these essential 15 Minutes processes. After completing this module, students will be able to: Describe the role of Data Transformation Services (DTS). ! Create a DTS package. ! Define an Analysis Services processing task. ! Copy, archive, and restore OLAP databases. ! 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_16.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. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  4. iv Module 16: Deploying an OLAP Solution Demonstration: Creating a Package In this exercise, you will create a new package that processes the HR cube in the Foodmart 2000 database. Demonstration: 15 Minutes The following demonstration procedures provide information that will not fit in the margin notes or are not appropriate for student notes. ! To create a DTS package 1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager. 2. Expand Microsoft SQL Servers, expand SQL Server Group, expand the local server, and then expand the Data Transformation Services folder. 3. Right-click Local Packages, and then click New Package. The DTS Designer appears. This is where you design the tasks you want to automate. 4. Click the Package menu, and then click Save. 5. Type OLAP Update as the name of the package, and then click OK. ! To create an Analysis Services Processing Task 1. Drag the Analysis Services Processing Task icon from the Task tool palette onto the DTS Designer workspace. The Analysis Services Processing Task dialog box appears. 2. In the Description box, type Process HR Cube. 3. Select the Local server checkbox at the bottom of the dialog box. You can use the OLAP Update package on any Microsoft® SQL Server™ 2000 computer, because you are not saving the server name in the package. Instead, you assign the server as LOCALHOST. 4. In the tree view on the left side of the dialog box, expand LOCALHOST, expand Foodmart 2000, expand the Cubes folder, and then click the HR cube. Notice the processing options available for cubes on the right side of the dialog box. 5. Click Refresh data, and then click OK. You created an Analysis Services Processing Task that refreshes the HR cube in the Foodmart 2000 database. ! To execute the package 1. On the DTS Designer toolbar, click Execute. 2. Click OK, and then click Done after the package finishes processing. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  5. Module 16: Deploying an OLAP Solution v ! To define a precedence constraint 1. From the DTS Designer Task tool palette, click Execute Process Task. The Execute Process Task Properties dialog box opens. 2. Type Command Script in the Description box. 3. Click the file C:\Moc\2074A\Labfiles\L16\CMDScript.cmd, click Open, and then click OK. 4. Click the Process HR Cube task, and press the CTRL key as you click the Command Script task. Important The order in which you select the two tasks is critical to setting up the precedence constraints properly. 5. Click the Workflow menu, and then click Failure. A red-striped arrow appears showing the relationship between the two tasks. ! To save and schedule a DTS package 1. Close the DTS Package window, and then click Yes when prompted to save the package. 2. In the Enterprise Manager window, click Local Packages, right-click the OLAP Update package, and then click Schedule Package. 3. Click OK if Enterprise Manager warns you that the SQL Server Agent is stopped. 4. In the Edit Recurring Job Schedule dialog box, click the Daily option in the Occurs group, and click 7:00 PM in the Occurs Once At box. 5. Click OK to schedule the task. The scheduled task becomes a SQL Server Agent job. To edit or delete the schedule, you must go into the SQL Server Agent folder. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  6. vi Module 16: Deploying an OLAP Solution 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. What does the Local server check box in the Analysis Services Processing Task dialog box do? If you select the Local server check box in the Analysis Services Processing Task, the local server name is not attached to the package. Therefore, you can execute the package on multiple servers without receiving errors because of incorrect server names. 2. Can you create packages that execute other packages? Yes. 3. Do you need to install SQL Server 2000 Enterprise Manager to design a package? You can start the DTS Designer only from the Enterprise Manager interface. You can start the DTS Import and DTS Export wizards externally to Enterprise Manager. You can design packages programmatically outside the DTS Designer by using Component Object Model (COM) compliant programming languages. 4. What are potential issues with buying third-party OLE DB providers? DTS has a series of interfaces that an OLE DB provider must support in the same order as DTS. Not all OLE DB providers are compliant with DTS. For example, some OLE DB providers are not thread safe, but report that they are. You can use an OLE DB provider test kit to test OLE DB provider compliance. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  7. Module 16: Deploying an OLAP Solution vii Module Strategy Use the following strategy to present this module: Introducing DTS ! Begin by defining DTS and describing the tasks that it can perform. Define a package as a collection of tasks—a data connection task, a data transformation task, assorted processing tasks, and precedence constraint tasks. Describe each type of task in detail. Introduce the DTS Designer, explaining how to access it and describing the various areas of the interface. Explain the steps involved in creating a package—defining a data source, defining a transformation, and adding DTS tasks. Executing and Scheduling Packages ! Explain to students that, after creating a package, they then define when and how often to run the package. Describe the various ways to execute a package. Finally, describe how to schedule a package, emphasizing that the SQL Server Agent must be running for a scheduled package to execute. The Analysis Services Processing Task ! Define the OLAP Processing task and describe when and how to use it. List the various objects that the OLAP processing task can process. Copying and Archiving OLAP Databases ! Explain that copying and archiving OLAP objects are two administrative tasks that can be very useful in the deployment of an OLAP application. Describe how to perform a copy and paste in SQL Server 2000 Analysis Services and list some uses of copy and paste. Finally, describe how to create an archive of a database and how to restore an archived database. Emphasize that you can only restore and archive databases, not other items such as cubes or servers. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  8. Module 16: Deploying an OLAP Solution 1 Overview Topic Objective To provide an overview of the module topics and Introducing DTS objectives. ! Lead-in Executing and Scheduling Packages ! In this module, you will learn about various OLAP The Analysis Services Processing Task ! deployment techniques. Copying and Archiving OLAP Databases ! Automating cube processing, copying server objects, and moving databases from testing to production environments are important steps that come at the end of an online analytical processing (OLAP) application’s deployment phase. In this module, you will learn the mechanics and techniques for all these essential processes. After completing this module, you will be able to: Describe the role of Data Transformation Services (DTS). ! Create a DTS package. ! Define an Analysis Services processing task. ! Copy, archive, and restore OLAP databases. ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  9. 2 Module 16: Deploying an OLAP Solution # Introducing DTS Topic Objective To introduce DTS. Lead-in Reviewing DTS ! In this section, you will be introduced to DTS and its Defining DTS ! role in data movement and transformations. Working with DTS Packages ! Using the DTS Designer ! In the corporate world, you create and store data in many different formats, platforms, and systems. Microsoft® SQL Server™ 2000 DTS gives you the ability to move heterogeneous data sources on a regular basis, performing transformations along the way. In this section, you are introduced to DTS and its role in data movement and transformations. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  10. Module 16: Deploying an OLAP Solution 3 Reviewing DTS Topic Objective To introduce DTS in the Microsoft data warehousing suite of products. Lead-in DTS is the feature of SQL DTS DTS Analysis Services OLTP DW Server that moves and Clients Source Storage transforms data. Transforming and Moving Data ! Scheduling DTS Tasks ! Automating OLAP Administrative Tasks ! One important goal of corporate information processing is to centralize the data so that analysts can compare and combine different data elements for analysis purposes. To do so, companies are moving data into central repositories, data warehouses, or OLAP databases. This movement of data occurs on a regular basis. Therefore, it is appropriate to automate the transformation of data in some way. DTS is a feature built into SQL Server 2000 that moves and transforms data. Features of DTS include: Transforming and moving data between heterogeneous data sources. ! Scheduling DTS tasks. ! Automating OLAP administrative tasks. ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  11. 4 Module 16: Deploying an OLAP Solution Defining DTS Topic Objective To define DTS and describe its use. Automates Processing of OLAP Cubes and Dimensions ! Lead-in DTS is a workflow Imports and Exports Data ! application that is bundled into SQL Server 2000. DTS Transforms Data ! allows you to extract, Automates Imports, Exports, and Transformations of transform, and load data to ! and from multiple Databases heterogeneous data Executes Packages sources. ! Transfers Database Objects ! DTS is a workflow application that is bundled into SQL Server 2000. DTS allows you to extract, transform, and load data to and from multiple heterogeneous data sources. By using DTS, you can: Automate the processing of OLAP cubes and dimensions. ! Import and export data between multiple data sources. ! Transform data between data sources by using simple copies or customized ! transformation scripts. Automate the imports, exports, and transformations of databases. ! Define the imports, exports, and data transformations as packages that can ! be used repeatedly. Transfer database objects, such as views, indexes, and stored procedures, ! between SQL Server 2000 databases. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  12. Module 16: Deploying an OLAP Solution 5 Working with DTS Packages Topic Objective To describe the various types of tasks in DTS Data Connection Task packages. ! Lead-in Data Transformation Tasks ! A package is a collection of database tasks created in Assorted Processing Tasks ! DTS and saved for future Precedence Constraint Tasks use. It is the primary object ! created and used in DTS. A package is a collection of database tasks created in DTS and saved for future use. It is the primary object created and used in DTS. In a package, you define the following tasks: Connections to your data source and data destination. ! Transformations defining data movement and manipulation. ! Assorted processing tasks. ! Task processing precedence constraints. ! Data Connection Task The data source is an OLE DB or open database connectivity (ODBC) database or file from which you are pulling data. The data destination is the OLE DB or ODBC database or file to which you are moving data. For databases requiring passwords and security information, you define the security information here in the connection. DTS supports many different OLE DB and ODBC data sources: SQL Server 2000 ! SQL Server 7.0 and SQL Server 6.5 ! Oracle ! Microsoft Access 2000 ! Microsoft Excel 2000 ! Text files ! dBase, Paradox, and other third-party vendors ! Each data source type has a corresponding icon in the DTS Designer. Assigning a database or file as a data source or destination is as simple as dragging the appropriate icon from the tool palette in DTS Designer to the workspace. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  13. 6 Module 16: Deploying an OLAP Solution Data Transformation Tasks In a transform task, you must define the data source, data destination, and any transformations or cleansing operations applied to the data. Tip DTS has Import and Export wizards accessed through the Enterprise Manager or through an executable file. The wizards take you step by step through the process of importing and exporting data from data sources to data destinations. For more information, refer to the DTS online documentation. DTS contains an extensive library of Component Object Model (COM) objects that give you the ability to provide custom data transformations. However, if you require only simple transformations, DTS provides built-in transformation options that require no code. The following are the transformation options available to you: Source. Defines the table containing the source of the data to be ! transformed. You can also define a SQL query whose results are the data source. Destination. Defines the destination table of your data transformation. Here ! you can define an existing table in your destination data source or you can create a new table. Transformations. Define which columns from the data source map to the ! columns from the data destination. By default, the column mapping copies the data. However, you can define a Microsoft ActiveX® script or a custom transformation that contains manipulations more complex than simple copies. Note Defining an ActiveX script is simplified by the use of the ActiveX Script Transformation Properties dialog box. In this dialog box, you select from either of two scripting languages—Microsoft Visual Basic® Scripting Edition (VB Script) or Microsoft JScript®. Advanced Transformation Settings. Define error-handling options, such as ! exception report file location, and establish settings to optimize your data transformation. Assorted Processing Tasks Other processing tasks that you can define include: The delivery of e-mail—for example, notifying the database administrator ! of a data load problem. The execution of SQL statements. ! The processing of an executable or batch script. ! The running of an ActiveX script. ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  14. Module 16: Deploying an OLAP Solution 7 DTS has a variety of built-in tasks that you can create in a package: ActiveX Script Task ! Execute Process Task ! Execute SQL Task ! Data Driven Query Task ! Transfer SQL Server Objects ! File Transfer Protocol (FTP) Task ! Execute Package Task ! Send Mail ! Bulk Insert ! Analysis Services Processing Task ! A custom task, created with languages such as Visual Basic or Microsoft ! Visual C++® An icon in the DTS Designer identifies each task. Assign a task by simply dragging the appropriate icon from the tool palette in the DTS Designer to the workspace. Precedence Constraint Tasks It is not only important to perform a series of tasks, but also to perform the tasks in an appropriate order. Some tasks are precedence constrained—for example, if a package must send an e-mail message following a successful data load, you want to ensure that the e-mail is sent after the successful data load and not before. When using DTS packages, you have the ability to create precedence constraints that define the sequential ordering of tasks. You can configure tasks to execute following the completion, success, or failure of other tasks. You create this workflow in the DTS Designer, the graphical interface used for creating and maintaining packages. The three types of precedence constraints that you can define are: On Completion. The second task waits for the first task to finish before ! processing. The second task begins after the first task finishes, regardless of whether the first task finishes successfully or fails. On Success. The second task waits for the first task to finish successfully ! before processing. Finishing with success means that no errors occurred during the processing of a task. On Failure. The second task processes only if the first task finishes with ! errors. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  15. 8 Module 16: Deploying an OLAP Solution Using the DTS Designer Topic Objective To introduce the DTS Designer and how it is used Accessing the DTS Designer in creating packages. ! Lead-in Defining Data Sources ! Creating and managing packages and tasks are Defining Transformations ! made easy by using the Adding Tasks DTS Designer. ! The DTS Designer makes creating and managing packages and tasks easy. Delivery Tip Open Enterprise Manager, After you start Enterprise Manager, you access DTS from the tree view and discuss each of the four immediately below the SQL Server node. From here, you have the ability to topics as you talk students create and manage DTS tasks. through the DTS Designer interface. Accessing the DTS Designer To access DTS Designer, create or open a package from Enterprise Manager. You will see the DTS Designer, which contains the following areas: The workspace is the area where connections, tasks, and transformations are ! created and defined. Tool palettes allow you to easily define data sources and destinations as ! well as processing tasks. Simply drag the appropriate icon to the workspace. The menu bar includes selections for general package and data ! transformation tasks. You can choose to configure the data sources and tasks of your packages from either the menu bar or the tool palettes. Defining Data Sources There are two ways to define the source and destination data sources. Drag the appropriate icons from the data tool palette to the workspace, or select the appropriate data source or destination from the Data menu. After you select the appropriate OLE DB database or file, define the type and location of the source or destination of the database. Depending on the database, you update various properties for the data source, such as user name, password, file name, and so on. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  16. Module 16: Deploying an OLAP Solution 9 Defining Transformations To transform data between data sources, click Task, click Transform Data Task, and then click the data source and the data destination. Tip Verify that your data sources have been defined and configured before creating a data transformation. After you add the transform successfully, an arrow will connect the data sources and will point in the direction that data is moving. You can right-click the transform arrow to define the data transformation properties. Adding Tasks There are two ways to define tasks in a DTS package—drag the appropriate icons from the task tool palette to the workspace, or click the appropriate task from the Task menu. Depending on the task added to the workspace, you configure the task immediately after adding it to the package. After you originally define a task, you modify its configuration by right-clicking the task and clicking Properties. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  17. 10 Module 16: Deploying an OLAP Solution Executing and Scheduling Packages Topic Objective To explain how to execute and schedule packages. Executing a Package ! Lead-in After you create and save a Scheduling a Package ! package, you define when and how often to run the package. After you create and save a package, you then define when and how often to Delivery Tip run the package. Right-click the package from Enterprise Manager to either Open Enterprise Manager execute the package immediately or schedule the package to execute at a future and discuss executing and or regularly scheduled time. scheduling packages as you talk students through the Executing a Package interface. When you execute a package, you perform all transformations and tasks defined in the package, connecting to the configured data sources, and ordering the tasks based upon any defined precedence constraints. Key Point A scheduled DTS package You execute a package in the DTS Designer by either clicking the Execute is executed by the SQL button on the toolbar or by clicking the Package menu and clicking Execute. Server Agent service as a job. The SQL Server Agent You execute a package in Enterprise Manager by right-clicking the package and must therefore be running then clicking Execute Package. The package processes at that moment and a for the scheduled packages dialog appears giving you the status of the package after processing completes. to execute. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  18. Module 16: Deploying an OLAP Solution 11 Scheduling a Package In a production environment, it is common for loading and transformation tasks to be performed the same way each time they are executed. It is often important to automate these regular tasks on an ongoing basis. Note The SQL Server Agent service executes a scheduled DTS package as a job. The SQL Server Agent must be running for the scheduled packages to execute. You automate a package in DTS by right-clicking the package and clicking Schedule Package. You see the Edit Recurring Job Schedule interface. You define the scheduling of the package in this dialog. Notice that you can define the job on a daily, weekly, or monthly basis. In addition, on the days the job is scheduled to run, you can define the frequency of the job. Finally, you can define the start and end dates of the job’s scheduling. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
  19. 12 Module 16: Deploying an OLAP Solution # The Analysis Services Processing Task Topic Objective To introduce the Analysis Services Processing Task. Defining the Analysis Services Task ! Lead-in The Analysis Services Selecting an Object to Process ! Processing task gives you the ability to create and execute DTS packages that process your OLAP databases, cubes, and dimensions. The Analysis Services Processing Task—referred to as the Analysis Services task for the rest of the module—gives you the ability to create and execute DTS packages that process your OLAP databases, cubes, and dimensions. You access the Analysis Services Processing Task in the DTS Designer. To use the Analysis Services Processing task, you must install SQL Server 7.0 or SQL Server 2000, including SQL Server Enterprise Manager, as well as Analysis Manager on the server computer on which you want to execute the package containing the task. The Analysis Services Processing Task enables you to take advantage of all the DTS functionality while integrating your OLAP databases into a production environment. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
Đồng bộ tài khoản