# Module 14: Using Actions, Drillthrough, and Writeback

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

0
43
lượt xem
4

## Module 14: Using Actions, Drillthrough, and Writeback

Mô tả tài liệu

Tham khảo tài liệu 'module 14: using actions, drillthrough, and writeback', 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)

Lưu

## Nội dung Text: Module 14: Using Actions, Drillthrough, and Writeback

1. Module 14: Using Actions, Drillthrough, and Writeback Contents Overview 1 Creating Actions 2 Performing Drillthrough 16 Understanding Writeback 22 Review 27
3. Module 14: Using Actions, Drillthrough, and Writeback iii Instructor Notes Presentation: This module introduces three analysis features of Microsoft® SQL Server™ 60 Minutes 2000 Analysis Services—actions, drillthrough, and writeback. All three of the features add layers of analysis to online analytical processing (OLAP) Lab: applications. 00 Minutes In this module, students create an action that opens a Web page. Students learn how to enable drillthrough to explore the underlying detailed data of a cell. In addition, students learn the importance of writeback and how to implement cube writeback. After completing this module, students will be able to: Create and view actions. ! Implement and test drillthrough. ! Understand the applications for cube writeback. ! Materials and Preparation This section lists the required materials and preparation tasks that you will need to teach this module. Required Materials To teach this module, you need the following materials: Microsoft PowerPoint® file 2074A_14.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. ! 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 14: Using Actions, Drillthrough, and Writeback 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. Are actions, drillthrough, and writeback supported in Microsoft Excel 2000? You have no built-in support for these features. To implement these features in Excel 2000 you must write custom Microsoft Visual Basic® for Applications code. 2. Is it possible for a custom application to support actions, drillthrough, and writeback? Yes. These features are exposed in the client object model. 3. Why would an action target a dimension object or level object instead of the members of the dimension or level? An action that targets a dimension object or a level object can be used to display cube metadata. For example, you can display the date and time of the most recent dimension process. 4. When you create a Data Set type action, why are you unable to test the action in the Cube Browser? The Cube Browser in Analysis Manager does not support Data Set actions. 5. How do you implement what-if analysis in Analysis Services? You can implement what-if analysis in Analysis Services by using cube writeback to store temporary cube values in memory. This allows a user, given a what-if-enabled application, to change cell values. The changes are temporary—the cube values do not persist in the cube and other users do not see the values until they are stored permanently in the cube. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
5. Module 14: Using Actions, Drillthrough, and Writeback v Module Strategy Use the following strategy to present this module: Creating Actions ! Teach students that an information system ultimately has no value unless the information causes decisions to be made, or is otherwise acted on. The action feature permits different types of actions to be designed into a cube, and subsequently executed by users. Demonstrate how to create and test actions. Performing Drillthrough ! Teach students that the purpose of drillthrough is to provide users with an added level of detailed data coming from the data warehouse. Show how to implement and test drillthrough. Understanding Writeback ! Teach students that when writeback is enabled for a given cube, cube cells can be written to from a client application. Demonstrate that this is a key feature for financial planning applications, such as budgeting and forecasting. Teach the applications for cube writeback and implement cube writeback. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
6. Module 14: Using Actions, Drillthrough, and Writeback 1 Overview Topic Objective To provide an overview of the module topics and Creating Actions objectives. ! Lead-in Performing Drillthrough ! In this module, you will learn about three features in Understanding Writeback ! Microsoft SQL Server 2000 Analysis Services—actions, drillthrough, and writeback. This module introduces three analysis features of Microsoft® SQL Server™ 2000 Analysis Services—actions, drillthrough, and writeback. All three of the features add layers of analysis to online analytical processing (OLAP) applications. In this module, you will learn how to create an action that opens a Web page. You will learn how to enable drillthrough to explore the underlying detailed data of a cell. In addition, you will learn the importance of writeback and how to implement cube writeback. After completing this module, you will be able to: Create and view actions. ! Implement and test drillthrough. ! Understand the applications for cube writeback. ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
7. 2 Module 14: Using Actions, Drillthrough, and Writeback # Creating Actions Topic Objective To introduce the concept of actions. Defining Actions ! Lead-in By building actions into a Selecting the Action Target ! cube, users can act on the information they are Choosing the Action Type ! viewing. Defining the Action Syntax ! Creating Actions in Virtual Cubes ! The purpose of an OLAP application is to provide users with valuable information to drive business decisions. Actions provide a means by which users make decisions and implement solutions based on the information that they find in cubes. In this section, you will learn about actions and how to create them in Analysis Services. You will learn about the following topics: Defining actions. ! Selecting the action target. ! Choosing the action type. ! Defining the action syntax. ! Creating actions in virtual cubes. ! BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
8. Module 14: Using Actions, Drillthrough, and Writeback 3 Defining Actions Topic Objective Describing Actions ! To define actions and show when and how to create Are user-initiated operations related to cube objects $actions. Pass cube objects as parameters to client or external applications$ Lead-in Are defined on the Analysis Server $An action is a user-initiated operation related to an Must be supported by client application$ object in a cube. Examining Action Examples ! View customer Web sites $Purchase a stock$ Generate a related report $Creating a New Action ! Use the Action Wizard within Analysis Manager$ Actions can be designed into a cube and executed by users. By using actions, users can act on the information that they are viewing. Describing Actions The following are characteristics of actions: An action is a user-initiated operation related to an object in a cube. ! A selected object, such as a member or a cell, serves as a parameter used by ! an action to initiate a specific operation defined in the action definition. Actions are defined on the Analysis Server, but are executed on the client ! computer. An action can be acted on by a client application or an external application, such as a Web browser. In order for a user to use actions, the client application, whether a third- ! party OLAP client or a custom application, must support actions. Examining Action Examples Common examples of actions include the following: Suppose you browse cube data for a certain customer. You want to visit the ! customer’s Web site. You click the customer, and then click a command that automatically starts your Web browser and navigates to the customer’s Web site. Suppose you browse a cube containing stock market data. You see a stock ! you want to buy. You click the stock, and then click a command that initiates a transaction and passes a parameter to a custom application that processes the order. Suppose you browse a cube containing sales analysis data. You click a cell, ! and then click an action that causes a custom application to generate a report based on the data in the cell. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
9. 4 Module 14: Using Actions, Drillthrough, and Writeback Creating a New Action You define an action inside a cube by using the Action Wizard. To create a new action, perform the following steps: 1. Open the Cube Editor for the cube in which you want to create an action. 2. Right-click the Actions folder, and then click New Action. The Action Wizard appears. You define the action here. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
10. Module 14: Using Actions, Drillthrough, and Writeback 5 Demonstration: Viewing Actions Topic Objective To demonstrate how users view actions. Lead-in In this demonstration, you will view an action from a user perspective by browsing the FoodMart 2000 HR cube. In this demonstration, you will view an action from a user perspective by Delivery Tips browsing the FoodMart 2000 HR cube. Encourage students to follow along with your ! To execute an action demonstration. 1. In Analysis Manager, expand the FoodMart 2000 database, and then Explain that the HR cube expand the Cubes folder. action relates to members of 2. Right-click the HR cube, and then click Browse Data. the Employees dimension. 3. Drag the Employees dimension to a row, replacing the Department Explain that no client-side dimension. programming is involved, other than generic support 4. Right-click Sheri Nowmer, and then click Show Sheri Nowmer Employee for actions built into the Card. Cube Browser. The Cube Information about Sheri Nowmer displays in your Web browser. Browser opens the Web browser, and this operation is driven entirely by the action defined on the server. Explain that, when you define an action correctly, the action is context- sensitive. The action you are demonstrating applies to any employee in the HR cube. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
11. 6 Module 14: Using Actions, Drillthrough, and Writeback ! To execute other actions 1. Return to the Cube Browser dialog box. 2. Double-click Sheri Nowmer to display the employees who report to Sheri Nowmer. 3. Right-click Donna Arnold, and then click Show Donna Arnold Employee Card. Information about Donna Arnold displays in your Web browser. 4. Return to the Cube Browser dialog box. 5. Drag the Store dimension to a row, replacing the Employees dimension. 6. Right-click USA. 7. Notice that the Show Employee Card action does not appear on the menu. USA is not a member of the Employees dimension, and no actions exist for the Store dimension. 8. Close the Cube Browser dialog box. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
12. Module 14: Using Actions, Drillthrough, and Writeback 7 Selecting the Action Target Topic Objective To explain how to target portions of the cube for the This Cube action. ! Lead-in A Dimension in this Cube ! The target of an action is the portion of the cube to which A Level in this Cube ! the action relates. Cells in this Cube ! Sets in this Cube ! The target of an action is the portion of the cube to which the action relates. The Delivery Tip target is the object that launches the action. The first step of the Action Wizard Start the Action Wizard and, prompts you to specify the target. Several types of targets that you can specify with the wizard displayed, are described below. select each of these options as you discuss them. Point This Cube out the interface elements for each target type. When you click This cube as the target, the action relates to the entire cube. When you select this option, no further inputs are required to define the target. In the client application, the action will be available for all cube objects—every member, every cell, every dimension, and every level. A Dimension in This Cube When you click A dimension in this cube, you select the cube dimension and then specify whether the target defines all members of the dimension, or the dimension object itself: If the target defines the members of the dimension, the action is available ! for all members of the dimension at all levels. For example, suppose your cube contains a Geography dimension consisting of Country, State, and City levels. You can define an action that displays the population for the selected Country, State, or City member. If the target defines the dimension object, the action is available for the ! dimension itself. For instance, the action can display the number of members belonging to the dimension, or the time at which the dimension was last processed. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
13. 8 Module 14: Using Actions, Drillthrough, and Writeback A Level in this Cube When you click A level in the cube, you select the level, and then specify whether the target defines all members of the level, or the level object itself: If the target defines the members of the level, the action is available for all ! members of the level. If the target defines the level object, the action is available for the level ! object itself. For example, the action displays all members of the level, and certain properties for those members. Cells in this Cube When you click Cells in this cube, the action relates to all cube cells. Unlike the This cube choice that includes all cube objects, the dimension and level objects are not included in the Cells in this cube target. When you select this option, no further steps are required to define the target. For example, a user can click a cell and can then click an action that passes the cell value as a parameter to a cell in a spreadsheet. Sets in this Cube When you click Sets in this cube, the action relates to all named sets in the cube. A named set is a set of defined members from the same dimension in a cube. When you select this option, no further steps are required to define the target. Note For more information on named sets, refer to SQL Server Books Online. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
14. Module 14: Using Actions, Drillthrough, and Writeback 9 Choosing the Action Type Topic Objective To introduce action types. Lead-in After you define the target, you specify the type of action you are defining. After you define the target, you specify the type of action you are defining. Delivery Tip There are several different types of actions. The Action Wizard prompts for the With the wizard displayed, action type, and displays sample syntax. select each of these options as you discuss them. Command Line A Command Line action executes an MS-DOS® command prompt. For example, you can use the path C:\Winnt\Notepad.exe to start the Notepad application in Microsoft Windows NT® 4.0 or Microsoft Windows® 2000. HTML An HTML action executes a Hypertext Markup Language (HTML) script in client Web browsers. For example, you can create an action that displays member properties in client Web browsers. A pre-existing Web page is not needed. The entire page can be defined in the action. URL A uniform resource locator (URL) action displays a Web page in client Web browsers. For example, suppose your cube contains a Customer dimension. You can define an action that displays the Web site of a customer in users’ Web browsers. Data Set A Data Set action executes a multidimensional expression (MDX) statement that returns a multidimensional cell set. The Cube Browser does not support the Data Set action type. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
15. 10 Module 14: Using Actions, Drillthrough, and Writeback Rowset A Rowset action executes an MDX or SQL statement that returns a tabular rowset. For example, a Rowset action can execute an MDX query by using the target object as a parameter. Statement Delivery Tip Point out that Statement A Statement action executes an SQL statement through OLE-DB from the and Proprietary actions are client application. created by programmers and are outside the scope of Proprietary this course. A Proprietary action executes a custom, non-generic function specific to a client application. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
16. Module 14: Using Actions, Drillthrough, and Writeback 11 Defining the Action Syntax Topic Objective To introduce action syntax. Lead-in The next step in creating an action is to define the action syntax. The next step in creating an action is to define the action syntax. You construct Delivery Tip the action syntax by using MDX. You reference the selected cube object Demonstrate a Command dynamically with the MDX syntax. In this way, the action applies to the Line action by entering selected cube object. explorer c:\ as the action syntax. When executed, the You enter the MDX directly into the Syntax box in the Action Wizard, or you action displays the contents click MDX Builder to display the MDX Builder dialog box. of the c: drive in Windows Explorer. Action syntax must adhere to the following guidelines: You enclose constant text in double quotes. ! You use the MDX CurrentMember function and other cube object ! functions to provide cube object context sensitivity. You concatenate MDX functions with constant text by using the + sign. ! The above rules apply to all types of actions regardless of the action type— URL, HTML, and so on. Beyond these basic guidelines, each type of action uses its own syntax based on its syntax requirements. Note The CurrentMember function is an important MDX function used in a variety of contexts. For more information on this function, see Module 2, "Using MDX Expressions," in course 2093A Implementing Business Logic with MDX in Microsoft SQL Server 2000. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
17. 12 Module 14: Using Actions, Drillthrough, and Writeback Command Line Example Suppose a cube has an Employee dimension. Each employee uses a disk directory on a server with the same name as the employee name. The following action will display Windows Explorer, displaying the directory for a selected employee name: “explorer ” + [Employee].[CurrentMember].Name URL Example Suppose a cube contains a Customer dimension. The members of the Customer dimension are associated with a member property named HomePage, which contains the URL for each customer’s home page. The URLs do not include the “http://www.” prefix. The following is a URL action that displays the home page for the current member of the Customer dimension: “http://www.” + [Customer].[CurrentMember].Properties(“HomePage”) BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
18. Module 14: Using Actions, Drillthrough, and Writeback 13 Demonstration: Creating an HTML Action Topic Objective To create an HTML action. Lead-in In this demonstration, you will learn how to create an HTML action that displays the name of the store manager. This demonstration shows how to create an HTML action by using the FoodMart 2000 database. In members of the Store dimension, you will display Delivery Tip the name of Store Manager, which is a member property defined in the Store Encourage students to dimension. follow along with your demonstration on their own ! To start the Action Wizard computers. 1. In Analysis Manager, expand the FoodMart 2000 database, expand the Cubes folder, right-click the HR cube, and then click Edit. 2. In the Cube Editor, right-click the Actions folder, and then click New Action. 3. Click Next at the Welcome page. The Select target page appears. ! To select target 1. In the Target box, click A level in this cube. 2. In the Dimension box, click Store. 3. In the Levels box, click Store Name. 4. Click Members of the selected level, and then click Next. ! To select the action type • In the Type box, click HTML, and then click Next. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY
19. 14 Module 14: Using Actions, Drillthrough, and Writeback ! To define action syntax 1. Type the following in the Syntax box: “” + Store.CurrentMember.Properties(“Store Delivery Tips Manager”) + “” Explain that the syntax is concatenating HTML code with a member property. Note Do not press ENTER at the end of the line—the Syntax box wraps the code for you. The tags at the beginning of the code define the syntax as HTML and indicate the beginning of a 2. Click Next. paragraph. Store.CurrentMember.Prope Note If the MDX syntax contains a syntax error, an error message displays rties(“Store Manager”) is the as soon as you click Next. If, on the other hand, an error exists in the non- MDX that returns the value MDX syntax (for example, an error exists in the HTML syntax) an error of Store Manager for the message does not display when you click Next. You must test the action to requested Store dimension member. The discover non-MDX errors. tags at the end of the code define the end of the ! To complete the action paragraph and the end of the HTML syntax. 1. Name the action Display Store Manager, and then click Finish. 2. Save the cube. Note When you define or edit an action, the cube must be saved for your changes to take effect. ! To test the action 1. Click the Data tab to browse the data in the cube. 2. Drag the Store dimension to the row area. 3. Double-click USA, double-click CA, and then double-click San Diego. 4. Right-click Store 24 and then click Display Store Manager. The store manager name for Store 24 displays in your Web browser. 5. Close the Web browser, and then close the Cube Editor. BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY