Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes

Monday Apr 19th 2004 by William Pearson

Leverage the power of Actions within virtual cubes. MSAS Consultant Bill Pearson provides a hands-on introduction to using Actions to extend virtual MSAS cubes.

About the Series ...

This is the twenty-second 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").


In our last article, we began an exploration of MSAS Actions. Our initial objective was to gain an understanding of the Types of Actions, and the options that are available as points of interaction (Targets) by information consumers. We then activated what we learned, reinforcing the concepts within a hands-on practice exercise that allowed us to see the end-to-end process for creating a simple Action for a regular cube.

In this article, we will extend our examination of Actions to their creation and use within virtual cubes (see Exploring Virtual Cubes in this series for my introduction to virtual cubes). After considering the differences in the regular and virtual cube scenarios, we will create an Action in a sample virtual cube. We will then perform the import of an example Action into a virtual cube. We will accomplish these steps within illustrative practice exercises, commenting upon the results obtained to reinforce our understanding of the concepts involved.

As I remarked in our last session, Actions are a powerful feature that allows information consumers to go beyond the robust OLAP perspective offered by MSAS, and to "step outside" for related information, to generate commands or to initialize programs, without leaving their current analysis focus. We also saw how 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. The power of Actions lies in their potential to save the consumers time and focus, precious considerations within the context of analysis, which often loses much value when it falls victim to distraction and interruption.

I mentioned that I find Actions to be remarkably underused jewels within the MSAS goldmine. Indeed, virtual cubes are also underutilized, and are often greeted with excitement by clients who, until I explain their potential for performance enhancement and other efficiencies in design and implementation, "know them not" in any apparent way. I expect the vacuums that exist for Actions, virtual cubes, and other innovative structures and capabilities of MSAS to improve over time, especially, as I continue to point out, if the creative users out there will contribute their ideas in public forums.

MSAS Actions in Virtual Cubes

We learned in our last article that an MSAS Action is an operation upon a selected cube (or portion of a cube), initiated by an information consumer, while performing analysis within the cube in which the Action exists. We discovered that, for the item upon which it is initiated, the Action can retrieve information about the item that is selected, can start an application with the selection as a parameter that is fed to the application to accomplish its function, and so forth.

Actions afford the consumer-analyst the capability of accessing valuable relative information in a non-disruptive manner, by allowing him / her to access the information without leaving the point of analysis at which the Action is initialized (the "target," in the parlance of MSAS architecture). In this article, our examination of MSAS Actions will include:

  • A brief introduction to Actions from the perspective of MSAS virtual cubes;
  • Creation of a virtual cube through the "cloning" of a FoodMart2000 sample, to save time;
  • A review of the characteristics of virtual cubes, where appropriate to the current objectives;
  • A hands-on practice example of the creation of an Action within our newly created virtual cube;
  • A review of the use of Actions within the Cube Browser;
  • A hands-on practice example of the import of an Action within our newly created virtual cube;

Create a Clone of a Virtual Cube for this Article

1.  Open Analysis Manager.

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

Our server(s) appear. Mine is shown in various illustrations as MOTHER1.

3.  Expand the desired server.

Our database(s) appear, in much the same manner as shown in Illustration 1.

Illustration 1: Databases Displayed within Analysis Manager

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder.

The sample cubes appear, as shown in Illustration 2.

Illustration 2: The Sample Cubes in the FoodMart 2000 Database

NOTE: Your databases / cube tree may differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online.

6.  Right-click on the Warehouse and Sales sample cube.

Because our lesson will involve making changes to the cube we use, let's make a copy of the Warehouse and Sales cube. This sample that accompanies installation of MSAS is a virtual cube. Working with the copy will allow us to maintain our existing sample cubes in their original condition.

7.  Select Copy from the context menu that appears, as shown in Illustration 3.

Illustration 3: Select Copy from the Context Menu

8.  Right-click on the Cubes folder.

9.  Select Paste from the context menu that appears, as shown in Illustration 4.

Click for larger image

Illustration 4: Select Paste from the Context Menu

The Duplicate Name dialog appears.

We cannot have two cubes of the same name in a given MSAS database.

10.  Type the following into the Name box of the Duplicate Name dialog:

Virtual Cube w Actions

The Duplicate Name dialog appears, with our modification, as depicted in Illustration 5.

Click for larger image

Illustration 5: The Duplicate Name Dialog, with New Name

TIP: As I have mentioned elsewhere in the series, this is also an excellent way of renaming a cube (a "rename" capability is not available here, as it is in many Windows applications). Simply create a duplicate, give it the name to which you wish to rename the old cube, and then delete the old cube. (This also works for MSAS databases.)

11.  Click OK to save the name change.

The new cube appears in the cube tree, among those already in place. We now have a copy of the Warehouse and Sales cube, Virtual Cube w Actions, with which we will proceed through the steps of our lesson. There is, however, another series of steps we need to perform to prepare the new cube for the steps ahead.

When we made a copy of the Warehouse and Sales cube, we copied the structure exactly as it existed for the original sample. One component critical to our ability to browse the cube, as well as to perform some edit actions however, could not be a part of our cloning process - the Query log entries that relate to our use of the Warehouse and Sales cube cannot accompany our new cube, because they are identified in the Query log database (see Simple Cube Usage Analysis for details regarding the log) as belonging to the cube that generated them.

We will process the cube, although we might have avoided doing so, if simply to obtain full browse and edit capabilities to make our work with the clone more instructive. To do this, we need first to process the cube, as the copy we have created also brought no history of processing along - for the same reasons it is "log-less."

12.  Right-click Virtual Cube w Actions.

13.  Select Edit from the context menu that appears, as shown in Illustration 6.

Illustration 6: Select Edit from the Context Menu

The Virtual Cube Editor opens, where we are greeted with a right-hand pane that is blank, save for a warning message (instead of the typically Data tab) as shown in Illustration 7.

Illustration 7: Virtual Cube Editor, for Pre-Processed Cube, Showing Warning Message

14.  Select Tools --> Process Virtual Cube from the main menu, atop the Cube Editor.

Processing begins, and runs rapidly, as evidenced by the Process viewer's presentation of processing log events in real time. The Processing cycle ends and the success of the evolution is indicated by the appearance of the Processing Completed Successfully message (in green letters) at the bottom of the viewer, as shown in Illustration 8.

Illustration 8: Indication of Successful Processing of the Virtual Cube

15.  Click Close to close the Processing viewer.

We are returned to the Virtual Cube Editor, where data is immediately retrieved, and appears on the Data tab.

16.  Select File --> Exit to close the Virtual Cube Editor.

We return to Analysis Manager, where we will await our next activities.

Let's do a quick review of virtual cubes, and then put an illustrative Action to work.


A (Quick) Review of Virtual Cubes

As many of us are aware (see, Exploring Virtual Cubes, earlier in this series) a virtual cube is a logical (versus physical) cube, composed of one or more regular cubes. An analogy I hear and read often, in varied attempts to explain the "logical cube" concept, is a view, within the context of a relational database. In a view, as most of us know, tables and / or other views are combined to embody a single "source" from which data can be presented. Virtual cubes are flexible, as they allow us to "consolidate" cubes, and it is flexibility that makes the virtual cube attractive to architects and developers.

When we construct a virtual cube, we can pick and choose the measures and dimensions we want to present from the population of dimensions and measures in the constituent, underlying cubes. "Consolidated" is a concept that is particularly meaningful in scenarios I frequently design and implement for clients. An example might be a group of single P & L cubes, one each for standalone entities that are identical in structure otherwise. In this case, a virtual cube makes for an excellent "consolidated" financial statements reporting tool (the intercompany balances can be housed within one or more additional standalone cubes, which become part of the group that is "consolidated," with the intercompany cube(s) obviously netting to zero, due to mutual offsets therein). It's the kind of stuff CPA's fantasize about!

The information consumers, of course, see the virtual cube as a single cube from the perspective of the reporting package, browser, or other tools they use. As we noted in our introductory article, virtual cubes can also be used to control the information (measures and / or dimensions) that consumers can see in a single cube, working again like a view in "hiding" that which we want to keep away from them. This, too, can become quite useful as a mechanism for controlled presentation, particularly when we combine a virtual cube, designed to present a restricted view, with an underlying, unrestricted cube; we then exercise control over access using security roles that group users by which cube we want them to access.

As many clients have been delighted to learn (and I, of course, delighted to relay), virtual cubes require practically no physical storage space. We can combine and / or control the data in multiple cubes that are already in place without a material increase in storage demands. This is because virtual cubes physically contain no data - only their own definitions. A potential downside is the need to process a virtual cube before it can be browsed the first time: While the linking that occurs between the virtual cube and its underlying components is rapid enough, the virtual cube's processing triggers the processing of any underlying cube that it determines to need it. This can be mitigated with some fairly straightforward planning and scheduling, but it is important to note that, even with virtual cubes, there is no totally free lunch

Create a New Action in the Virtual Cube

We will begin with the creation of a simple Action within our virtual cube, and then take a look at an alternative means for placing an Action there. We will briefly review assorted facts about Actions as we proceed; to obtain an in-depth introduction, see our previous article, Putting Actions to Work in Regular Cubes.

Beginning in Analysis Manager, where we left off after creating our clone virtual cube in the section above, take the following steps:

1.  Right-click the new Virtual Cube w Actions.

2.  Click Edit from the context menu that appears.

The Virtual Cube Editor launches.

3.  Right-click the Actions folder in the tree pane to the left. (I like to close the Dimensions and Measures folders first, to give myself more space).

Note that two options exist here, versus the single-lined context menu that appeared in the Cube Editor in the subsequent article. The difference is that we can import, as well as create, an Action in a virtual cube.

4.  Select New Action from the context menu, as depicted in Illustration 9.

Illustration 9: Select New Action (Circled)

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

Illustration 10: Action Wizard Welcome Dialog

5.  Click Next to continue.

The Select target dialog of the Action Wizard next appears. Here we 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 by the consumer 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.

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

7.  Select Customers in the Dimension box.

8.  Select Name in the Levels box

9.  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 11.

Illustration 11: The Completed Select Target Dialog

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

11.  Select HTML in the Type box.

We note that clicking the selector arrow exposes the various Types of Actions, as we discussed in Putting Actions to Work in Regular Cubes. The types again, are as follows:

  • Command line - Executes a command line. Can start programs, pass parameters to such a program, etc., when appropriate to its operation.
  • Statement - Executes an OLE DB command, with outcome of either success or failure, but no results are returned.
  • HTML - Contains HTML that opens within an Internet browser.
  • URL - Contains a URL that constitutes a link between cube trigger points and internet / intranet sites.
  • Dataset - Contains MDX queries; returns data sets via OLE DB commands.
  • Rowset - Contains an OLE DB command; returns a rowset.
  • Proprietary - Custom actions that do not fit into the first six classes.

NOTE: For more detail on the Action Types, see Putting Actions to Work in Regular Cubes.

The read-only display in the lower half of the dialog 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 12.

Illustration 12: The Select the Action Type Dialog

12.  Click Next.

We arrive at the Define the action syntax dialog. The appearance of this dialog is based 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 simple 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.

We will keep things simple and build an HTML page that uses a handful of basic MDX expressions to populate a simple browser 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.

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

	<title>Customer Details</title>
<body BGCOLOR=""#99CCCC"">

<table CELLSPACING=""2"" 

<tr>  <td VALIGN=""TOP"" 
	<span name=""custname"">
	<h3>Customer Profile:  "+customers.currentmember.name+"</h3>
</td> </tr>

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

<tr><td width=""55%"">
	<div class=""title"">Gender:</div>
</td><td colspan=2>
	<span name=""title"">

<tr><td width=""55%"">
	<div class=""Annual Income"">Annual Income:</div>
</td><td colspan=2>
	<span name=""title"">
	"+customers.currentmember.properties("Yearly Income")+"

<tr><td width=""55%"">
	<div class=""title"">Education Level:</div>
</td><td colspan=2>
	<span name=""title"">



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: I have mentioned in past articles that, while MDX basics are beyond the scope of most the tutorials in this series, the Database Journal 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.

The expression customers.currentmember.name in the second string above is intended to retrieve the Customer 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, appended to a label of "Customer Profile:". This will appear as the "header" or "title" of the simple HTML page that we are creating.

The third and fourth strings set up rows containing location information - actually State and City details, respectively. The City is the level to which the Customer belongs in the cube hierarchy (and is thus its parent); by contrast, the State is, in a manner of speaking (and coding) the "parent of the parent," hence the MDX that we see used in its generation. (The Ancestor() function, etc., might have been used to generate the State name, as well, but this is only a simple example). We are simply retrieving the name of the customer's "grandparent" with this line of the syntax.

Strings five, six and seven in our syntax example exploit the retrieval of member properties. Member properties 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 Gender, Annual Income, and Education Level for the Customer for whom we launch the Action.

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 13.

Illustration 13: The Define the Action Syntax Dialog

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


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 are given an opportunity to review a summary of its setup.

15.  Type the following into the Action Name box.

Customer Details

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

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

17.  Select File --> Exit to close the Virtual Cube Editor.

18.  Save the virtual cube, when prompted.

We are returned to Analysis Manager.

Use the New Action

Let's take a quick look at the results of our work, calling the Action into play from the Browse Data viewer for Virtual Cube w Actions. Using the Action in our virtual cube is no different than using it in a regular cube, as we did in Putting Actions to Work in Regular Cubes, so we will abbreviate the process here to keep moving.

1.  Right-click Virtual Cube w Actions.

2.  Select Browse Data from the context menu that appears, as shown in Illustration 14.

Click for larger image

Illustration 14: Select Browse Data

Data is retrieved and the Cube Browser viewer opens. The Browser presents data as partially depicted in Illustration 15.

Illustration 15: All Customers in the Column Axis in Browse Data (Compacted View)

NOTE: Ensure that All Customers appears in the column axis of the Cube Browser, as shown above.

3.  Double-click the USA row to drill down to the States underneath.

The three USA States appear.

4.  Double-click OR to drill down to the Oregon Cities.

The Cities within OR appear.

5.  Double-click Corvallis, near the top of the OR Cities.

The Corvallis Customers appear.

6.  Right-click Annette Von Strahl, the third customer listed under Corvallis Total.

The context menu appears, displaying our newly added Action, Customer Details, as shown in Illustration 16.

Illustration 16: Customer is in the Column Axis in Browse Data (Compacted View)

7.  Select Customer Details from the context menu.

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

Illustration 17: The Action-Generated HTML Page in the Browser (Compressed View)

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.

8.  Select Close to retire the Data Browser.

We return to Analysis Manager.

Next, let's take a look at another way to bring a new Action aboard that is unique to the virtual cube - as an Import.

Import an Action from another Cube to the Virtual Cube

We will import the Action that we created in the previous article into the Virtual Cube W Action clone we created, to get a handle on the process. For this purpose, we will select the Action we created, named ANSYS21 1, within the Sales sample cube in our previous lesson, Putting Actions to Work in Regular Cubes. We will focus for now upon the Import process itself.

1.  Right-click the Virtual Cube w Actions virtual cube we cloned earlier.

2.  Click Edit from the context menu that appears.

The Virtual Cube Editor launches.

3.  Right-click the Actions folder in the tree pane to the left.

This time we are going to select the option that is unique to the virtual cube, the Import Action option.

4.  Select Import Action from the context menu, as depicted in Illustration 18.

Illustration 18: Select Import Action (Circled)

The Import Action dialog appears. We can select to import Actions from other cubes that are within the same database - although, in my case, at least, only one Action for a common data source appears.

5.  Select the check box beside the ANSYS21 1 Action, housed within the Sales cube.

The Import Action dialog appears, as shown in Illustration 19.

Illustration 19: Import Action Dialog

NOTE: We might deselect the Show source cubes only checkbox to make all Actions and their cubes visible. Leaving the checkbox selected means we only see those that relate to a common data source.

6.  Click OK.

The ANSYS21 1 Action is imported into the cube without any errors noted.

7.  Select File --> Save to save the virtual cube with the imported Action.

If you wish to test the ANSYS21 1 Action, be sure to drill to the lowest level in the Store dimension, then to select it from the context menu that appears from right-clicking the individual store. ANSYS21 1 appears as an option when you perform the right-click - but only at the Store Name level, as is appropriate. (ANSYS21 1 was fully functional when I tested it on my machine).

An error message upon the attempt to import an Action typically means that the definition of the Action includes a measure, dimension, or other object that is not in the virtual cube, or that syntax errors have been detected. The Action is not included in the virtual cube data displayed to end users as long as the error exists. To correct the error, we can add the missing component to the virtual cube, or edit the action to remove the offending syntax (from within the Cube Editor and Action Wizard, within the cube that houses the original Action).

Once we feel comfortable with our understanding of the functionality, and have tested the imported Action, if desired, we can leave the Virtual Cube Editor.

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

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

Summary ...

In this article, we extended the examination of Actions that we began in the previous article, Putting Actions to Work in Regular Cubes, to the creation and use of Actions within virtual cubes. After considering the differences between regular and virtual cube scenarios, we created an Action in a sample virtual cube we cloned from an existing sample. We then performed the import of an example Action into our cloned virtual cube. Throughout the hands-on practice exercises, we commented upon the results we obtained, to reinforce our understanding of the concepts involved.

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

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

Mobile Site | Full Site