Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes

Monday Mar 15th 2004 by William Pearson
Share:

Exploit Actions within MSAS to extend the functionality of multidimensional cubes. Author and MSAS Consultant Bill Pearson provides a hands-on introduction to using Actions to extend regular MSAS cubes.

About the Series ...

This is the twenty-first article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real-world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("MSAS").

Introduction

In this article, we will examine Actions, a powerful feature in MSAS that allows information consumers to go beyond the robust OLAP perspective offered by the application, and to "step outside" for related information, or to generate commands or initialize programs, without leaving their current analysis focus. As we shall see, Actions can be structured into the cube by the developer, to allow users to perform these extended activities from various vantage points, with a simple right-click of the mouse. This saves analysts and other consumers time, as the complementary information can be "linked" through the Action design for them, and, perhaps more importantly, because they can perform the Actions without leaving their current position within the analysis they are performing in MSAS.

Working with MSAS at clients every day, I find Actions to be remarkably underused jewels within the MSAS goldmine. Moreover, when we take a cruise across the internet in search of documentation that surrounds the feature, we tend to come back with the same well-worn examples of very simple scenarios. I expect this vacuum to improve over time, especially if the creative users out there will contribute their ideas in public forums. The imagination is the only constraint when we consider the options that Actions practically shout to the receptive architects, developers, and kindred practitioners among us.

In this article, together with the article that follows it, we will spend a little time looking at the functionalities that Actions offer, and will explore their use in a multiple step practice example. In this manner, I hope that Actions will "speak louder than words" in exposing more of the rich analysis functionality that is MSAS.

Introduction to MSAS Actions

Within the structured regimen of the OLAP world, analysts constantly find themselves needing to reach beyond the analytical anchor point of the cube browser or, more commonly, the application that integrates with MSAS to provide the informational views with which they work. Examples of related information that does not appear within the OLAP view abound. External data stores (relational and / or OLAP) compose a significant component of "external data," but also included is documentation such as procedural manuals, organizational charts, account listings, and myriad other collateral. The web and intranets often house many of the complementary information sources to which an OLAP-focused consumer might need access at any time to crystallize a component within the OLAP view, to understand the geographical makeup of organizational units, or to understand the structures underlying considerations that might range from headcount to responsibility accounting.

Actions are a means of accessing this valuable relative information in a non-disruptive manner. Our examination of MSAS Actions in this article will include:

  • An introduction to the general types of Actions that we can exploit in the design and development of MSAS cubes;
  • A discussion of example consumer needs that might be classified within each of the general Action types;
  • A review of the points of interaction at which we can place an Action for use by an MSAS analyst;
  • An introduction to the MSAS Action Wizard;
  • A hands-on practice example of the creation of an Action within a "regular" cube, the sample Sales cube that installs with MSAS;
  • Practice in using the example Action we create, both as a general guide to the use of Actions in general, and as a means to ascertain their effectiveness from the perspective of development.

In this article, we will gain an understanding of the nature of Actions, focusing on their types, possible uses, and the choices we have for points of information consumer interaction. We will then activate what we have learned, reinforcing the concepts within a practice exercise that allows us to see clearly how the mechanics tie together within a regular cube.

In our next article, Actions in Virtual Cubes, we will extend our grasp of the key concepts to the creation and maintenance of Actions in virtual cubes (for an introduction to virtual cubes, see my article Exploring Virtual Cubes). We will highlight the differences in treatment between regular and virtual cube scenarios for Actions, and then practice the concepts within an exercise. We will also explore a particularly useful way to leverage an investment in Action design by importing an existing Action into a virtual cube.

The Many Options for Actions

As we stated in the introduction, Actions are designed into our cubes to provide analysts and other information consumers new flexibility in reaching beyond the purely OLAP presentation, to start an application, or perform other steps, within the context of the data item(s) where they trigger the Action. The idea is to act upon the results of analysis as presented in the cube, and there are several classes, or types, of Actions that we can install so that users can access external information and capabilities. Placement of the "trigger" for the Action is another consideration when creating Actions in our cubes. These target settings allow us, as part of Action design and construction, to decide the physical point from which information consumer can initialize the Action from within the cube presentation.

Within the Action Wizard, we will see that the designation of placement of an Action (its target location in Wizard parlance) occurs chronologically before the designation of the type of Action that we want to create. I find it useful in development scenarios at client locations, and elsewhere, to plan an Action from the more general concept of what it is supposed to accomplish, before thinking about the point from which it might best be accomplished. The order of planning these aspects might not be critical, but that's just the way my mind likes to arrange the furniture.

A couple of considerations that are highly important to planning, however, are the effective and complete gathering of the business requirements of the intended information consumers, for whom we are constructing the Action. This should naturally be followed up with requests for feedback, from pilot users, as to whether the intended design will actually meet their needs in a user-friendly way. The slickest functionality is undone if it does not make life easier, or worse, if it causes an obstruction to its user. Even minor irritations, on a repetitive basis, assume the characteristics of serious design gaffes.

The Action Types

MSAS offers six general types of Actions, as well as a seventh flexible option. The types of Actions we can use to link complementary information to our existing cube presentation are detailed in Table 1.

  

Command line

Actions of this type execute a command line. Can be used to start programs, as well as to pass parameters to such a program when appropriate to its operation.

Statement

Actions of this type execute an OLE DB command, with the outcome of either success or failure, but without the return of results.

HTML

Actions in this class contain HTML that opens within an Internet browser. The HTML is temporarily stored in the client application, and is displayed after the client launches a browser.

URL

An Action in this class contains a URL that constitutes a link between selected structures in the cube (the "trigger points" we discuss elsewhere in the article) and internet or intranet sites that contain complementary information. As with the HTML type, the client temporarily stores the URL information, and launches its default browser to its specifications, when the Action is executed.

Dataset

Actions in this class contain MDX queries that return data sets via OLE DB commands. These queries can be run against different cubes that reside on the same MSAS database.

Rowset

An Action in this class contains an OLE DB command that returns a rowset. The Action can be run against any data source that is OLE DB compliant.

Proprietary

Actions in this class can be virtually anything that does not fit into the first six classes. The Proprietary type offers flexibility for custom Actions to meet business needs that are not easily met via one of the other Action types.


Table 1: MSAS Action Types

An example for a command line Action type, positioned, say, to be triggered at the account name in a financial cube, might include initializing the corporate accounting application to allow an analyst to inspect the transactions in a period where an account had a higher-than-normal balance. Another example could be an Action that allows an engineering inventory analyst to call up a graphics package that contains an exploded picture of a large assembly, so as to see details about the component parts that make up the assembly. A statement Action type might be represented by an Action that allows a pension fund analyst to be able to flag balances of uncashed checks for follow-up, to determine the amounts that need to be recognized as having a potential "abandoned property" status. Statement Actions might also include the capability, from within a cube that contains information concerning plan member companies, to add or delete members from the next update of the cube.

HTML Actions might include any number of supporting lists and reports that are based upon outside data sources, or perhaps a combination of internal and external data sources, all accessible at the click of the mouse by the information consumer at the point in the cube data he is analyzing. HTML Actions might involve forms that appear when the Action is initialized from within the cube, asking for input, perhaps, of questions / comments that relate to the structural element selected input that is then accumulated and routed to the appropriate responsibility centers. HTML Actions comprise an excellent scenario from which to build an understanding of Actions within a development perspective, so we will be working with one of these, together with some basic MDX, in the practice example that follows.

URL Actions have been used in some of the documents in the slowly growing body of knowledge surrounding Actions that is currently available in print and electronic media. I have seen examples where the URL Action takes the relevant parameters from selected City and State members in a cube and, after transiting the browser to a popular travel organization's web site, inputs the parameters via the browser to obtain a map based upon the City and State information. URL Actions are easy to use and make for excellent linking from cube members, to information sources about those members, that lie outside the realm of the cube's analytics.

Similarly, a Dataset Action can be used to link users to other cubes in the same MSAS database. I have linked financial cubes with "actual" measures to cubes that contain budgeting and forecast information, to cubes containing HR / headcount measures, and to physical inventory cubes. In each of these cases, my intent was to allow financial analysts to examine the respective underlying factors, when prompted by a value or values that warrant further investigation. Another example, based upon a cube I am currently constructing for a financial services industry client, extends the perspective of the analyst beyond the measures of his organization's financial assets cube with rapid queries of a large financial ratios statistical cube I am building, to allow for context-sensitive comparisons to a wide array of industry- and instrument-specific ratios.

Like the Dataset Action type, Rowset Actions contain commands to retrieve data. Examples of uses include Actions that query external relational databases with standard SQL. Moreover, the Rowset Action might be used to perform drill through to a relational data source underneath the cube within which the Action is triggered. Triggering might be performed, as an illustration, by an information consumer who wishes to see the detail transactions behind account balances that have recently deteriorated in the corporate Accounts Receivable aging.

Finally, the Proprietary Action classification allows us to create Actions that do not fit into any of the general functional categories above. It also endows us with the flexibility to construct Actions that perhaps comprise elements of two or more of the type options above, within a more sophisticated combination that might require a more elaborate creation process.

Placement and Positioning Alternatives

The purpose of Actions, as we have seen, is to enable information consumers to extend their activities past the data contained in their cubes and to investigate or otherwise act upon discovered problems or other apparent anomalies and / or inconsistencies in the data they analyze. Actions can extend the once purely analytical review and reporting functions of the MSAS / client combination to include capabilities to perform operational and other activities from within the cube browser or MSAS client application.

To provide an intuitive, useful transition to external applications from the analysis perspective, while passing along the "coordinates" of the analysis in progress at the time of the transition, we can establish targets. Targets are points from which analysts and other information consumers can trigger the Action, within the context of the element of the cube structure upon which the Action is put into motion.

Alternatives within the Action Wizard for placement of Actions, and their subsequent triggering by users, include:

  • The cube itself
  • A dimension
  • Members within a dimension
  • A level
  • Members within a level
  • Cells
  • Named Sets

Placement of the target occurs as the first step within the Action Wizard, where we select one of the above objects for attachment to the Action. Multiple Actions can be established on a given object. It is at these objects in the analysis environment of the cube that the user can right-click, and select from the available Actions that exist, to initialize the corresponding Action. Actions can thus link parts of the cube structure to external, complementary information in an intuitive way.

Getting Started with Actions for Regular Cubes

The Action Wizard in MSAS provides us a quick means of creating and modifying Actions within our cubes. As we have already discussed, and as we shall see in the procedural steps that follow, the Action is created, or edited, based upon several settings we can make or adjust in the Action Wizard. We can learn more about these settings through a hands-on look at the wizard in action.

Create an Action

We call the Action Wizard directly from inside Analysis Manager, and then create an Action by completing several dialogs that prompt us for the required settings.

1.  Start Analysis Manager.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Expand the desired server (mine appears as MOTHER1 in the illustrations).

Our database(s) appear underneath the expanded Analysis Server.

4.  Expand the FoodMart2000 database.

5.  Expand the Cubes folder.

The sample cubes appear, amid other MSAS components, as shown in Illustration 1.


Illustration 1: The Sample Cubes in the FoodMart2000 Database

6.  Right-click on the Sales sample cube.

7.  Select Edit... from the context menu that appears, as shown in Illustration 2.


Illustration 2: Select Edit ... from the Context Menu

The Cube Editor appears, exposing the construction of the Sales cube, as depicted in Illustration 3.


Illustration 3: Cube Editor View: Inside the Sales Cube

Note: For a tutorial covering most aspects of the Cube Editor see Working with the Cube Editor, as well as other articles within this series.

In the left pane, we can see several folders. One of these, the Actions folder, will be our focus in the procedures that follow.

8.  Right click the Actions folder.

A single-lined context menu appears, as presented in Illustration 4.


Illustration 4: Context Menu at the Actions Folder

9.  Select New Action... on the context menu.

The first dialog of the Action Wizard appears, as shown in Illustration 5.


Illustration 5: Action Wizard Welcome Dialog

10.  Click Next to proceed.

The Select target dialog of the Action Wizard next appears. Here we will specify the positioning point of the Action within the user's view of the cube - the point in the cube's structure upon which a right-click, as we noted earlier, will present the option to initialize this Action. (Other Actions for the same target point will also appear on the context menu that appears with the right-click).

In the present example, we want to create an Action that allows an information consumer to access general information about a given Store. To this end, we make the following settings in the Select target dialog.

11.  Select A level in this cube in the Target Selector box.

12.  Select Store in the Dimension box.

13.  Select Store Name in the Levels box

14.  Ensure that the Members of the selected level radio button, underneath Define the target as: on the Select Target dialog, is darkened.

The Select target dialog of the Action Wizard, with our settings, should appear as shown in Illustration 6.


Illustration 6: The Completed Select Target Dialog

15.  Click Next to move to the Select the action type dialog.

16.  Select HTML in the Type box.

We note that clicking the selector arrow exposes the various types of Actions, as we discussed above, and listed in Table 1. We also note the presence of the read-only Sample display in the lower half of the dialog. This display provides a brief, context-sensitive description of, and a short sample of the syntax for, the selection we have made in the Type box above it.

The Select the action type dialog of the Action Wizard, with our setting, appears as shown in Illustration 7.


Illustration 7: The Select the Action Type Dialog

17.  Click Next.

We arrive at the Define the action syntax dialog. The appearance of this dialog depends upon our selection of Action type in the previous dialog. Here we are provided with a place to type, or cut and paste, the syntax that will appear in the browser that our Action, by its nature as an HTML-type Action, will initialize when a user calls the Action from the cube browser or another application.

We will need to do a bit more than to simply supply HTML, however, if we want to use information from the cube in our example. We will need to embed some simple MDX within the HTML to bring out some of the data elements that the information consumer sees in the launched browser display.

While we could return data from another cube in the FoodMart 2000 Analysis Services database, we will keep distractions to a minimum for purposes of this exercise, and build an HTML page that sports a handful of basic MDX expressions to populate the intended display. We will do this in such a way that the MDX can be examined independently, instead of in a single, nebulous clump that mixes HTML and MDX, without consideration for easy review.

18.  Type (or cut and paste if you like) the following into the Syntax box:

"<html><head>
<title>Store Information</title>
</head>

<body BGCOLOR=""#99CCCC"">

<ul>
<table 
	CELLSPACING=""2"" 
	CELLPADDING=""2"" 
	BORDER=""0"" 
	Bordercolor=""#FF3333"" 
	ALIGN=""LEFT"" 
	VALIGN=""TOP""  
	WIDTH=""300"">

	<tr><td 
		VALIGN=""TOP"" 
		ALIGN=""LEFT"" 
		WIDTH=""100%"" 
		height=""100%"" 
		colspan=2>
		
		<span 
			name=""storeno"">
			<h3>"+store.currentmember.name+"</h3>
		</span>
		<hr>
	</td> </tr>

	<tr><td width=""55%"">
		<div class=""title"">Location:</div>
	</td><td colspan=2>
		<span name=""title"">"+store.parent.name+"</span>
	</td></tr>

	<tr><td width=""55%"">
		<div class=""title"">Local Manager:</div>
	</td><td colspan=2>
		<span name=""title"">"+store.currentmember.properties("Store Manager")+"</span>
	</td></tr>

	<tr><td width=""55%"">
		<div class=""title"">Store Type:</div>
	</td><td colspan=2>
		<span name=""title"">"+store.currentmember.properties("Store Type")+"</span>
	</td></tr>

	<tr><td width=""55%"">
		<div class=""title"">Floor Area:</div>
	</td><td colspan=2>
		<span name=""title"">"+store.currentmember.properties("Store Sqft")+"   Sq. Feet</span>
	</td></tr>

</table>
</font>
</ul>
</body>
</html>"

I have intentionally left spaces in the syntax to separate the portions that call upon MDX (specifically the .CurrentMember and .Parent functions, as well as functions using member properties), to make them more readily understandable.

NOTE:

While MDX basics are beyond the scope of this tutorial, my MDX Essentials series is a good place to begin learning hands-on MDX. For a tutorial covering the use of the .CurrentMember function, see my article MDX Essentials: MDX Member Functions: "Relative" Member Functions. For a tutorial that focuses on the use of the .Parent function, see MDX Member Functions: The "Family" Functions.

To assist in building syntax, the MDX Builder button on the Define the action syntax dialog will initialize an MDX "helper" applet. Personally, I find such "helper" tools of little benefit, but the MDX Builder might be useful in enabling a developer to mentally instantiate the layout of the cube design, see the set of functions and other components that are available, and so on.

The expression store.currentmember.name in the second string above is intended to retrieve the Store name - upon which we right click in the client application / browser - from the cube, and to present it in the top row of a small table. This will act as the "title" of the simple HTML page that we are creating.

The third string sets up a row containing the label "Location," and presents the geographical location of the Store, which is the hierarchical level to which the Store belongs in the cube hierarchy (and is thus its parent). We are simply retrieving the name of the Store's parent with this line of the syntax.

Strings four, five and six in our syntax example exploit the retrieval of member properties. Member properties are attributes that can be valuable in just such scenarios as this; they allow the storage of data that we might want to make available for easy access, but that we do not want to house among the population of largely quantitative data for which we have designed our cube. In this case, we retrieve the name of the Manager, the Type of Store, and the square footage floor area for the store under consideration.

The top and bottom strings of the syntax we have entered construct the HTML page with a basic presentation format for the data we are retrieving.

The Define the action syntax dialog of the Action Wizard, with our newly input syntax partially shown, appears as depicted in Illustration 8.


Illustration 8: The Define the Action Syntax Dialog

19.  Click Next to accept the input and proceed to the next dialog.

NOTE:

Many syntax errors will be detected when clicking the Next button. If you experience such an error during our exercises, back up and check your typing.

We arrive at the Finish the Action Wizard dialog, where we name the Action and we are given an opportunity to review a summary of its setup.

20.  Type the following into the Action Name box.

ANSYS21 1

21.  Click Finish to accept the settings and create the Action.

The Action Wizard closes, and we see the Action appear in the Actions folder of the tree pane of the Cube Editor. Our Action is now in place and ready for use by the intended information consumers.

Use an Action

Now let's examine the operation of the Action we have created - from the perspective of an information consumer.

1.  Select File --> Save from the top menu in the Cube Editor to save our work to date.

2.  Click the Data tab at the bottom right of the Cube Editor.

The default display of the data appears in the data pane. Let's move the Store dimension to the row axis so that we can easily review the operation of the Action we have created.

3.  Drag the Store dimension bar from the top of the data pane to drop it over the Customer dimension column heading (labeled Country) in the data view, as necessary.

The Store dimension replaces the Customer dimension on the row axis, as shown in Illustration 9.

Click for larger image

Illustration 9: The Store Dimension in the Row Axis of the Data Pane (Partial View)

4.  Double-click the USA level in the row axis to drill down to the State level.

The State level appears.

5.  Double-click the OR store-state to drill down to the Oregon store-cities.

We see the Cities of Portland and Salem appear.

6.  Double-click Portland to drill to the actual Store level.

Store 11 of Portland appears. The data view appears as depicted in Illustration 10.


Illustration 10: Data View after Drill-Down to Store 11 (Partial View)

7.  Right-click on Store 11.

The context menu appears, and displays the new Action, as shown in Illustration 11.


Illustration 11: Selecting the New Action for Store 11

Recall that we set the target for the Action to be at "a level in this cube," at the Store Name level of the Store dimension,

8.  Click ANSYS21 1 on the context menu.

The default browser (mine is Internet Explorer) is launched, and the HTML page appears as shown in Illustration 12.


Illustration 12: The Action-Generated HTML Page in the Browser

We note, also, in the address bar, the temporary storage place to which the operation of the Action has directed the HTML file it has produced.

The Action we have created thus presents us with an HTML page, after launching the browser, with simple details about the Store on which we clicked from the Data view inside MSAS. The data is retrieved from the cube using simple MDX. Specifically, it returns the name of the Store (Store 11) and the location of the Store, which, in the cube structure, equates to the parent of the Store member. MDX also renders the three member properties defined for Store 11: the Manager name, the Store Type, and the square footage of the Store.

We can perform the Action for any store in our cube, although some in the sample sales cube have not been assigned square footage, or perhaps other, values.

Once you feel comfortable with your understanding of the functionality, you can leave the Cube Editor.

9.  Select File --> Exit when ready to leave the Cube Editor, saving if requested.

10.  Exit Analysis Services in similar fashion, when ready.

Summary ...

In this article, we began an exploration of MSAS Actions. We set out to first gain an understanding of the nature of Actions, focusing on their Types, possible uses, and the choices we have for points of information consumer interaction (Targets). We then activated what we learned, reinforcing the concepts within a rudimentary practice exercise that allowed us to see the end-to-end process for creating a simple Action for a regular cube.

In our next article, we will extend our examination of Actions to a discussion of creating and using Actions within virtual cubes. After considering the differences in the regular and virtual cube scenarios, we will create and import Actions into a virtual cube within illustrative practice exercises.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved