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
About the Series ...
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").
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.
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
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:
introduction to the general types of Actions that we can exploit in the
design and development of MSAS cubes;
of example consumer needs that might be classified within each of the general Action
A review of
the points of interaction at which we can place an Action for use by an MSAS
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;
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
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.
Table 1: MSAS Action Types
| || |
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.
of this type execute an OLE DB command, with the outcome of either success or
failure, but without the return of results.
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.
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.
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
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.
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.
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.
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
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
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
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.
the Action Wizard for placement of Actions, and their subsequent triggering
by users, include:
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
2. Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
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
5. Expand the Cubes folder.
The sample cubes appear, amid other MSAS components, as shown in Illustration
1: The Sample Cubes in the FoodMart2000 Database
6. Right-click on the Sales
7. Select Edit...
from the context menu that appears, as shown in Illustration 2.
2: Select Edit ... from the Context Menu
The Cube Editor appears, exposing the construction of the Sales
cube, as depicted in Illustration 3.
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
A single-lined context menu appears, as presented in Illustration 4.
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: Action Wizard Welcome Dialog
10. Click Next to
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.
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
The Select the action type dialog of the Action Wizard,
with our setting, appears as shown in Illustration 7.
7: The Select the Action Type Dialog
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:
<div class=""title"">Local Manager:</div>
<span name=""title"">"+store.currentmember.properties("Store Manager")+"</span>
<div class=""title"">Store Type:</div>
<span name=""title"">"+store.currentmember.properties("Store Type")+"</span>
<div class=""title"">Floor Area:</div>
<span name=""title"">"+store.currentmember.properties("Store Sqft")+" Sq. Feet</span>
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
While MDX basics are beyond the
scope of this tutorial, my MDX
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: The Define the Action Syntax Dialog
19. 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.
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.
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.
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.
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.