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 ...
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").
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.
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.
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:
introduction to Actions from the perspective of MSAS virtual
Creation of a
virtual cube through the "cloning" of a FoodMart2000 sample, to save
A review of
the characteristics of virtual cubes, where appropriate to the current
practice example of the creation of an Action within our newly created
A review of
the use of Actions within the Cube Browser;
practice example of the import of an Action within our newly created virtual
Create a Clone of a Virtual Cube for this Article
Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
Mine is shown in various illustrations as MOTHER1.
in much the same manner as shown in Illustration 1.
Illustration 1: Databases
Displayed within Analysis Manager
Expand the FoodMart
Expand the Cubes
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.
the Warehouse and Sales sample cube.
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.
from the context menu that appears, as shown in Illustration 3.
Illustration 3: Select
Copy from the Context Menu
the Cubes folder.
from the context menu that appears, as shown in Illustration 4.
Name dialog appears.
have two cubes of the same name in a given MSAS database.
following into the Name box of the Duplicate Name dialog:
Virtual Cube w Actions
Name dialog appears, with our modification, as depicted in Illustration 5.
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.)
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.
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.
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."
Cube w Actions.
from the context menu that appears, as shown in Illustration 6.
Illustration 6: Select Edit
from the Context Menu
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
--> Process Virtual Cube from the main menu, atop the Cube
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
to close the Processing viewer.
returned to the Virtual Cube Editor, where data is immediately
retrieved, and appears on the Data tab.
--> Exit to close the Virtual Cube
to Analysis Manager, where we will await our next activities.
a quick review of virtual cubes, and then put an illustrative Action to
Review of Virtual Cubes
As many of us are aware (see,
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!
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
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.
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
4. Select New Action from the
context menu, as depicted in Illustration 9.
Illustration 9: Select New Action (Circled)
dialog of the Action Wizard appears, as shown in Illustration 10.
Illustration 10: Action
Wizard Welcome Dialog
5. Click Next to continue.
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).
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.
level in this cube in the Target Selector box.
in the Dimension box.
in the Levels box
the Members of the selected level radio button, underneath Define the
target as: on the Select Target dialog, is darkened.
target dialog of the Action Wizard, with our settings, should appear
as shown in Illustration 11.
Illustration 11: The
Completed Select Target Dialog
to move to the Select the action type dialog.
in the Type box.
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:
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.
- Custom actions that do not fit
into the first six classes.
more detail on the Action Types, see Putting Actions to Work in Regular Cubes.
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 action type dialog of the Action Wizard, with our setting,
appears as shown in Illustration 12.
Illustration 12: The Select
the Action Type Dialog
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.
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.
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.
Type (or cut
and paste if you like) the following into the Syntax box:
<tr> <td VALIGN=""TOP""
<h3>Customer Profile: "+customers.currentmember.name+"</h3>
<div class=""Annual Income"">Annual Income:</div>
<div class=""title"">Education Level:</div>
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
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
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
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 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
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 are given an opportunity to review a summary of its setup.
following into the Action Name box.
to accept the settings and create 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
--> Exit to close the Virtual Cube
virtual cube, when prompted.
returned to Analysis Manager.
Use the New Action
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
Cube w Actions.
Select Browse Data from the context menu that
appears, as shown in Illustration 14.
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.
the USA row to drill down to the States
USA States appear.
to drill down to the Oregon Cities.
within OR appear.
5. Double-click Corvallis, near the top of the OR
Von Strahl, the third customer listed under Corvallis Total.
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)
Details from the context menu.
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)
also, in the address bar, the temporary storage place to which the operation of
the Action has directed the HTML file it has produced.
to retire the Data Browser.
to Analysis Manager.
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.
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.
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.