Analysis Services Actions

Take action to make better decisions more quickly

SQL Server 2000 Analysis Services is a full-featured OLAP engine that supports many of the latest multidimensional tricks and features. But in business analysis, OLAP isn't enough. To be truly effective, a business analyst needs access to a variety of business information that's linked by context, not just a standalone OLAP cube. The ability to link detailed or related information to an OLAP cube is one new aspect of Analysis Services. "Analysis Services Drillthrough" (June 2000) covered one such capability, drillthrough, which lets analysts see the detailed transactions that make up any cell value in an OLAP cube. Another new Analysis Services feature, actions, can link structured or unstructured data or commands to almost any part of an OLAP cube. If you want to improve the quality and timeliness of decisions in your organization, use actions to link relative information in an intuitive way. Decision makers will spend less time searching for information and can make more informed decisions.

You can think of actions as context-sensitive commands that an end user initiates. The administrator who built the OLAP cube usually defines actions, but end users can also define them. Actions have many uses for integrating information or applications; for example, suppose you have an OLAP cube that contains market-share information. You can browse the cube to compare the sales of your company's products with those of any competitor. When you see that a competitor is gaining market share, you might want more information about that competitor's product line and marketing messages. Here, an action that links the competitor's name to a document or Web site with a profile of that competitor's products could help you determine what change to make in your business strategy.

You can also link product information to product names, employee profiles to employee names, and so on. But actions can do more than just link soft information such as Web sites or documents to dimension members. Actions can run commands against a cube, run command-line programs, or contain embedded HTML. An action can even execute a proprietary command that only certain analysis applications understand. You can link actions to cube cells, sets, dimension levels, dimensions, or the whole cube.

The Analysis Services administrative program, Analysis Manager, includes a wizard to help you create actions. Start the Analysis Manager and expand the explorer tree on the left until you have the name of a cube—for instance, FoodMart 2000 Sales—then right-click the cube and select Edit. In the cube editor, drop down the Insert menu and select Action, or click the action toolbar button, which shows a yellow cube with two red arrows coming out of it. Either method will start the Action Wizard.

As its first step, the wizard identifies a portion of the cube to attach the action to. As Figure 1 illustrates, I chose to attach an action to members of the Store Name level of the Store dimension. The next step lets you choose the action type, as Figure 2 shows. The sidebar "Action Types," page 64, describes the different types of actions. Each type of action contains textual information that is based on the action type. You can parameterize this text; for example, you can fill in the name of the dimension member that the action is associated with.

After you choose the action type and click Next, you can enter the text to associate with the action. Figure 3, page 64, shows some example text for an HTML action. The final page of the wizard lets you name the action. For this example, I used the name HTML Sample. Click Finish to finish creating the action. Next, click the diskette icon on the cube editor toolbar to save the change.

You can test the action by clicking the data tab at the bottom of the cube editor window to switch the cube editor from displaying the schema to browsing the data. Drag the Store dimension onto the grid rows and expand the dimension all the way to the bottom. (You expand dimension members by double-clicking the plus sign next to the member name.) When you can see store names, right-click one of them, and you'll see the HTML Sample action in the menu that appears. Selecting this menu option will initiate the action; Figure 4 shows the results.

Using Actions from Visual Basic

If you want programmatic access to the new features of Analysis Services, you can find documentation for most of the features in the OLE DB for OLAP specifications on Microsoft's Web site at http://www.microsoft .com/data/oledb/olap. ActiveX Data Object, Multi-Dimensional (ADO MD) offers a higher-level way to access OLE DB for OLAP—a way that works well with Visual Basic (VB). Microsoft created the OLE DB for OLAP API to improve interoperability between OLAP clients and OLAP servers, although Microsoft didn't document actions in this standard API. You can access actions through OLE DB for OLAP and from ADO MD by using Microsoft proprietary extensions, as SQL Server 2000 Books Online (BOL) documents.

To give you an idea of how you can use actions from your client application, I've created an example VB program that searches for any HTML action attached to the Store dimension member Store 9. If the program finds such an action, it displays the action name in a message box, then launches your Internet browser to display the action's HTML code. If you use this source code in your own application, you can extend the code to support all the action types and cube meta data types.

To run the example, first create the HTML Sample action I outlined earlier. Then, download the VB project from the Related Info box that accompanies this article on the SQL Server Magazine Web site. If you type in the example, you'll need to set up your VB project references correctly. Be sure to include references to the ADO MD, ADO, and Microsoft Scripting Runtime libraries. (If you download the VB project, the references are already set up.)

The first part of the VB code declares the constants necessary to access the Actions schema rowset through ADO MD. The Form_Load subroutine starts by establishing an ADO MD connection to the FoodMart 2000 database. The next step sets up an array of restrictions on accessing the Actions schema rowset. For this example, I limited the rows to the action type HTML in the Sales cube for the dimension member Store 9. The values in the restrictions array map to the fields in the Actions rowset. If you specify Empty in the restrictions array, that column won't restrict the rowset. Table 1 contains a list of the action rowset fields.

To request the rowset from ADO MD, you call the same OpenSchema method that you use to access a Dimension rowset or Member rowset. In this case, though, you pass in the parameter adSchemaProviderSpecific to specify that the rowset is specific to the Microsoft OLE DB for OLAP provider. The third parameter is the globally unique identifier (GUID) that specifies which proprietary rowset you want.

The final step of this example displays the name of the action and executes it. The example saves the HTML content to a temporary file and launches an Internet browser to display the content.

The combination of existing knowledge and the available, relevant data limits the effectiveness of analysis. Actions in SQL Server increase the relevant data by exposing a variety of data in a context-sensitive manner. If you have OLAP cubes deployed in your organization, or you plan to deploy some soon, consider using actions to link other sources of information. Linking related information for business analysts will improve overall decision making because analysts can make decisions more quickly with relevant information. If you're developing an OLAP client application, I recommend that you implement actions; the value proposition is high for the amount of programming work necessary. Until next time, you can reach me at OLAPMasters @sqlmag.com. (For answers to the June MDX Puzzle, see "June MDX Puzzle Solution Revealed." For the July puzzle, see "MDX Puzzle.")