BI Architect Bill Pearson continues in-depth coverage of Analysis
Services 2005 Actions. This months session focuses upon the URL action, and
includes exploitation of the MDX Condition expression as a means of restricting
URL Action availability.
About the Series ...
article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to
provide hands-on application of the fundamentals of MS SQL Server Analysis
Services (Analysis Services), with each installment progressively
presenting features and techniques designed to meet specific real-world needs.
For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools
needed to complete the hands-on portion of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this
About the Mastering Enterprise BI Articles
The purpose of the Mastering
Enterprise BI subset of my Introduction to MSSQL Server
Analysis Services series
is to focus on
techniques for implementing features in Analysis Services that parallel
or outstrip - those found in the more mature enterprise OLAP packages. In many cases, which I try to
outline in my articles at appropriate junctures, the functionality of the OLAP
solutions within well-established, but expensive, packages, such as Cognos
PowerPlay Transformer and Cognos PowerPlay, can be met and
typically exceeded in most respects by the Analysis Services / Reporting
Services combination at a tiny fraction of the cost.
vacuum of documentation comparing components of the Microsoft BI solution to
their counterparts among other enterprise BI vendors, to date, represents a
serious undersell of both Analysis Services and Reporting Services,
particularly from an OLAP reporting perspective. I hope, within the context of
the Mastering Enterprise BI articles, to demonstrate that
the ease of replicating popular enterprise BI features in Analysis Services
will be yet another reason that the Microsoft integrated solution will
commoditize business intelligence.
more information about the Mastering Enterprise BI articles,
see the section entitled About the
Enterprise BI Articles in my article Relative
Time Periods in an Analysis Services Cube, Part I.
in Analysis Services 2005: An Introduction,
we overviewed the general types of Actions with which we,
as Analysis Services developers, can support information consumers. We
discussed examples of consumer needs that might be classified within each of
the general Action types, as well as reviewing the points of interaction
at which we can place triggers to allow analysts and information consumers a
means of accessing valuable, but often external, information in a
non-disruptive manner. We stated in Actions
in Analysis Services 2005: An Introduction that we would extend
our introduction to individual examinations of specific Action types in
separate articles. Individual articles, we reasoned, would afford us the time
and focus required to examine each type in detail, from a perspective of
creation and maintenance. This article will focus upon the URL Action,
which, although it existed in Analysis Services 2000, has witnessed
enhancement in Analysis Services 2005.
In this article, we will
gain some familiarity with the URL Action how it works and some of its capabilities - and then get some hands-on exposure to
creating a URL Action within the sample AdventureWorks
development environment, which can be installed with MSSQL Server 2005. Our examination of the URL
Action in this article will include:
introduction to the URL Action, including an overview of
its uses and the data it presents;
practice exercise, wherein we set up a working URL Action, based
upon the sample Adventure Works Analysis Services database;
regarding the various settings that we exploit in the design and creation of our
upon the use of an MDX Condition expression to restrict the availability
of a URL Action;
of the effectiveness of our new URL Action, from the tandem
perspectives of availability of the Action and operation
of the Action, in meeting the business requirements of a
The URL Action
Overview and Discussion
As we noted in Actions in Analysis Services
2005: An Introduction, a URL
Action returns a
URL that constitutes a link between selected structures in the cube and internet or intranet sites that
contain complementary information. We noted that the client temporarily stores
the URL information, and launches its default browser to the URLs
specifications, when the Action is executed. As we mentioned, URL
Actions have been the most commonly chosen (since Analysis Services
2000), as Action type examples within the slowly growing body of
knowledge surrounding Actions that is currently available in print and
electronic media. Many of us have seen examples where the URL Action
takes the relevant parameters from selected City and State (and sometimes
postal code, etc.) members in a cube, transits the browser to a popular
mapping web site, and inputs the parameters via the browser to obtain a map
based upon the provided information.
URL Actions are easy to use, and afford us an
opportunity to offer intuitive links from cube members, to information sources about
those members that lie outside the realm of the cubes analytics. Indeed, Report Actions, which debut in Analysis
Services 2005, are, in essence, URL Actions that are enhanced to
construct a URL string specifically for Report Server access,
based upon properties we input about the Report Server, its location,
and additional report, and report format, parameters that we can supply.
At heart, the URL
Action simply presents a page via a web browser, based upon the relative
position of the target from which we launch the Action. (As we
noted in Actions
in Analysis Services 2005: An Introduction, targets are points from which analysts and
other information consumers can trigger an Action, within the context
of the element of the cube structure upon which the Action is put into
As a part of
preparation for our practice session with URL Actions, we will create an
Analysis Services Project within the Business Intelligence Development Studio, which provides the environment and the
tools that we need to design and develop business intelligence solutions based
upon Analysis Services 2005. As we have noted in other articles
of this series, the Analysis Services Project that we create within the BI
Development Studio will assist us in organizing and managing the
numerous objects that we will need to support our work with an Analysis
Considerations and Comments
For purposes of the
practice exercises within this series, we will be working with samples that are
provided with MSSQL Server 2005 for use with Analysis Services.
The samples with which we are concerned include, predominantly, the Adventure
Works DW Analysis Services database (with member objects). The Adventure
Works DW database and companion samples are not installed by default in MSSQL
Server 2005. The samples can be installed during Setup, or at any
time after MSSQL Server has been installed.
The topics Running
Setup to Install AdventureWorks Sample Databases and Samples in SQL
Server Setup Help or Installing AdventureWorks Sample Databases and Samples in the Books Online (both of which are included on
the installation CD(s), and are available from www.Microsoft.com and other sources), provide
guidance on samples installation. Important information regarding the rights /
privileges required to accomplish samples installation, as well as to access
the samples once installed, is included in these references.
We will get some
hands-on experience with our subject matter in a practice session. We will
first need to prepare for our exercises by creating an Analysis Services
project, within which to work from the Business Intelligence Development
Studio. We will rely heavily upon samples that ship with MSSQL Server
2005, to minimize the preparation time required to create a working
practice environment. This shortcut will afford anyone with access to the
installed application set and its samples an opportunity to complete the steps
in the practice session.
If you prefer to work
within an existing copy of the Adventure Works Analysis Services
project (perhaps you have already made a copy for work with previous articles),
or you intend to create a new, pristine copy of the original from the CDs or
another source for this (and possibly other) purposes, please feel free to skip
the related preparatory sections.
Create a New
Analysis Services Project within a New Solution
For purposes of our
practice session, we will create a copy of the Adventure Works Analysis
Services project, one of several samples that are available with (albeit
installed separately from) the Microsoft SQL Server 2005 integrated
business intelligence solution. Creating a clone of the project means we can
make changes to select contents (perhaps as a part of later exploration with
our independent solution), while retaining the original sample in a pristine
state for other purposes, such as using it to accompany relevant sections of
the Books Online, and other documentation, as a part of learning more
about Analysis Services and other components of the Microsoft integrated
business intelligence solution in general.
To create a copy of
the sample Adventure Works Analysis Services project, please see
the following procedure in the References section
of my articles index:
Connectivity of the Relational Data Source
ensure we have a working data source. Many of us will be running side-by-side
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced as a server
/ instance combination, versus a server name alone (the default for
the Adventure Works DW project samples connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default).
If you do not know how
to ascertain connectivity of the relational data source, please perform the
steps of the following procedure in the References section of my
Work with a URL Action
Create a URL Action
first create a URL Action within our new environment, to get some
experience with the process. As is the case with other Action types, we create and
maintain URL Actions via the Actions tab of the Cube Designer.
For purposes of our practice session, we will say that we have been asked by
the Adventure Works organization to add a URL Action to the Adventure
Works cube. The client representatives with whom we are working, who
comprise a team of developers and report authors for the integrated Microsoft
Business Intelligence solution, have told us that they would like to create a URL
Action to use in conjunction with a pilot they are creating. They plan to
use various prototypes within the pilot as an aid in the collection of business
requirements from information consumers throughout their organization.
client colleagues tell us that the business requirement is for the Action
to take the consumer who selects it to a basic, pre-parameterized Google search
summary, based upon the target (a member of a dimension hierarchy
level) from which the Action is launched within the cube browser.
For purposes of this prototype, they tell us that such a search, based upon the
selected target within the cube browser, will serve the purpose of
illustrating the kinds of things that such an Action can be designed to
do, its overall characteristics (such as to its general context sensitivity,
etc.), and so forth. The Action is to be called Parameterized Internet
Search (the name displayed at runtime will be a context sensitive,
user-friendly title) to make clear that the intended objective of the Action
is to afford an exit from the Unified Dimension Model (UDM): By
triggering this Action, the information consumer will be able to perform
extended, general browses, based upon the various Product Categories that AdventureWorks sells,
without having to leave their concurrent position within Analysis Services.
client colleagues ask that we disable the URL Action for the Accessories Category of their Products
offerings, to show how we might place conditional limitations upon the use of a
URL Action, much as we can with other Actions available in Analysis
listen carefully to the requirements, and, once we confirm our understanding of
the need, we set out to create a URL Action, taking the following steps:
Inside the Solution
the AdventureWorks cube within the Cubes folder of our project.
from the context menu that appears, as depicted in Illustration 1.
Illustration 1: Opening
the Cube Designer ...
The Cube Designer opens for Adventure Works Cube,
defaulting to the Cube Structure tab.
Click the Actions
-> New Action from the main menu, as shown in Illustration
Illustration 2: Select
Cube -> New Action
tab changes to the settings for a standard Action, as the URL Action
Form Editor opens. A default Name of Action appears in the
top and center portion of the tab.
default Name with the following:
Parameterized Internet Search
Members in the Target type selector.
are simply selecting, among several options, to make our Action
available within a single hierarchy within a dimension of our cube.
downward pointing selector arrow on the right side of the box labeled Target
object, immediately below the Target type selector.
selector that appears, expand the Product dimension by clicking the +
sign to its immediate left.
Select the Product
Categories hierarchy that appears (the second item under the newly
expanded Product dimension).
to accept the selection.
now established the members of the Product Categories hierarchy as the target
points of the Action, we need to consider the restriction imposed by
the client representatives: our colleagues have indicated that they want to
exclude the Accessories Product Category from being designated as a target.
Type, or cut
and paste, the following into the box labeled Condition (Optional)
immediately underneath the Target object selector:
[Product].[Product Categories].CURRENTMEMBER <>
we are entering an MDX expression, which will act to further restrict availability
of the Action (not to restrict the URL details returned, in
any way, as we shall see). What we are basically saying with the condition is
make the Action available for selection, when the Product Category
is not Accessories, for the measures defined.
in the Type selector, in the Action Content section of the form
that appears below the Condition input box.
Type (or cut
and paste) the following expression into the Action expression box, just
underneath the Type selector:
-- General URL for Google Search (may differ based upon individual browsers)
-- Append Product Category name to the above (MDX string)
+ [Product].[Product Categories].CURRENTMEMBER.NAME
Note: The above was used on PC with Internet Explorer 7
installed as the browser. The commented lines - those preceded by the double
dashes (--) - explain the parts of the expression.
tab Action Form Editor appears, with our input, as depicted in Illustration
Illustration 3: Actions
Tab Action Form Editor with Our Settings
click the down arrows to the immediate left of Additional Properties, the
section just beneath the Action expression box, to expand the properties
Ensure that Interactive
is selected within the Invocation selector atop the Additional
Type (or cut
and paste) the following expression into the Caption box, the second
from the bottom in the Additional Properties section:
"General Search for Product Category: " +
In the bottom
selector in the Additional Properties section, labeled Caption is MDX,
of Interactive that we have made above (in the first of the Additional
Properties settings, Invocation), directs that the Action will
be initiated by the information consumer. By indicating True in the
bottom setting, Caption is MDX, we have directed that the MDX expression
that we have provided in the Caption box is to be used to assemble the
title for the browser (making it context-sensitive for the selected target)
when the Action is triggered. The Additional Properties section
of the Action Form
Editor appears, with our input, as partially shown in Illustration 4.
Illustration 4: Additional
Properties Section of the Form (Partial View
We are ready to accept our settings, deploy the project,
and then move to the Cube Browser, where we can inspect the results of
Deploy the DBJ
AdventureWorks DW project.
NOTE: If you do not know how to ascertain
alignment of the project to the destination server, and / or to deploy the
project, please perform the steps of the following procedure in the References
section of my articles index:
the Operation of the URL Action within the Cube Browser
the deployment of the project has been accomplished, we can fully leverage the Browser
that the Cube Designer makes available to us. We will do so by taking
the following steps:
Click the Browser
tab to initialize the Browser for the Designer, as depicted
in Illustration 5.
Illustration 5: Click
the Browser Tab ...
Once the Browser
loads, click the Reconnect button, as shown in Illustration 6.
Illustration 6: Click
the Reconnect Button ...
Lets perform a Browse
scenario to ascertain that our URL Action functions as expected. The Analysis
Services Browser is an extremely convenient place to accomplish this and
other examinations. (More in-depth details about use of the Browser, a
formidable tool within itself, are developed within other articles of this
series, in sections where the objectives, typically within verification
procedures, are to examine or analyze values, and other details, within the
cube tree appearing inside the Metadata pane, expand Measures.
Expand the Reseller
Sales folder, as depicted in Illustration 7.
Illustration 7: Exposing
Measures within the Cube Tree
Drag the Reseller
Sales Amount measure, followed by the Reseller Order Quantity measure,
to the area of the Browser canvas marked Drop Totals or Detail Fields
Here, as shown in
Illustration 8: Dragging
the Measures to the Browser Canvas ...
inside the Metadata pane, expand the Product folder.
the Product Categories hierarchy that appears in the expanding tree.
Add to Row Area from the context menu that appears, as depicted in Illustration 9.
9: Adding the Product Categories Hierarchy to the Cube Browser Row Axis
Expand the Date
folder in the Metadata pane,
10. Expand the Calendar folder
11. Expand the Date.Calendar hierarchy
that appears within the expanded Calendar folder.
12. Right-click the Calendar
Quarter level that appears.
13. Select Add to Column Area
from the context menu that appears, as shown in Illustration 10.
10: Adding Calendar Quarters to the Cube Browser Column Axis
The available Calendar
Quarters appear within the browsers column axis.
Having set up the axes as
desired, we are ready to take a look at two primary considerations for our new URL
Action, availability and operation. First, lets examine for
which members of the Product Categories hierarchy the Action is available,
and for which it is disabled, to ascertain that its behavior is in accordance
with the client business requirements we have defined.
14. In the row axis, occupied by the Product
Categories, right-click the row labeled Clothing.
The context menu that
appears contains two Actions, one of which is the URL Action we
have added in preceding steps, named General Search for Product Category:
Clothing, as depicted in Illustration 11.
Illustration 11: The
Newly Added URL Action Appears for the Clothing Category ...
Next, we will repeat the
same test for the Accessories Category, for which we have restricted
selection as a Target for the new URL Action.
15. In the row axis, occupied by the Product
Categories, right-click the row labeled Accessories (the top row in
The context menu that
appears contains only a single Action. This is the one of the Actions
that pre-existed in the AdventureWorks sample, and not the URL Action
that we added in the steps of our exercise. The context menu appears as
shown in Illustration
Illustration 12: The URL
Action is Appropriately Absent (and Unavailable) for Accessories ...
that we do not see our new URL Action in the context menu for Accessories
provides instant confirmation that our Condition within the Action
definition is effective. Recall that the purpose of the Condition
was to render the URL Action unavailable for the Accessories Product Category.
We imposed this Condition in response to the expressed business
requirement of our client colleagues to disable the new URL Action for
the Accessories Category.
next verify that the newly added URL Action is operational, by taking
the following steps.
16. In the axis row, once
again, right-click the row labeled Bikes.
The context menu that
appears this time contains our new URL Action. The name is context
sensitive once again: General Search for Product Category: Bikes, as depicted
in Illustration 13.
Illustration 13: The New
URL Action is Available for the Bikes Product Category ...
17. Click General Search for
Product Category: Bikes on the context menu.
Our click initiates the URL
Action, based upon the Bikes Product Category, and launches an
instance of the web browser (Internet Explorer 7.0, in my case). It
then enacts a general Google search, based upon the term Bikes,
as we specified (via the MDX expression we provided) in our definition of the Action
in the steps above. The browser appears similar to that shown in Illustration 14.
Illustration 14: The URL
Launched Search in Internet Explorer 7.0
We verify, therefore,
that our efforts to meet the requirements of our client colleagues have been
18. Close the web browser, when ready.
19. Further inspect the availability
and operation of the URL Action, performing additional browses, as
20. Within the Business
Intelligence Development Studio, select File -> Save All from the main menu, to save our
work through this point, as depicted in Illustration 15.
Illustration 15: Saving All Work
from Our Session
21. Select File -> Exit, when ready, to leave the Business
Intelligence Development Studio.
In this article, we
explored URL Actions, another mechanism by which information consumers
can extend their efforts beyond the Analysis Services 2005 UDM
structure for analysis and exploration. Our focus upon the URL Action included a brief introduction to
its features, capabilities and possible uses. We then obtained some hands-on
exposure to this standard Action type, creating a URL Action within
the sample AdventureWorks development environment that can be installed
with MSSQL Server 2005.
As a part of our practical
examination of the URL Action, after an overview of its uses and the
data it presents, we focused upon the use of an MDX Condition expression
to restrict its availability within the Cube Browser, and, by extension,
within client applications which a URL Action might be created to
support. Having concluded an examination of the settings involved in creating
our working URL Action, we verified, within the Cube Browser, the
effectiveness of the new URL Action, from the tandem perspectives
of availability of the Action and operation of the
Action, in meeting the business requirements of a hypothetical
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.