Actions in Analysis Services 2005: The URL Action

About the Series …

This
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
series.

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.

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

For
more information about the Mastering Enterprise BI articles,
see the section entitled “About the
Mastering
Enterprise BI
Articles” in my article Relative
Time Periods in an Analysis Services Cube, Part I
.

Introduction

In Actions
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:

  • An
    introduction to the URL Action, including an overview of
    its uses and the data it presents;

  • A hands-on
    practice exercise, wherein we set up a working URL Action, based
    upon the sample Adventure Works Analysis Services database;

  • A discussion
    regarding the various settings that we exploit in the design and creation of our
    URL Action;

  • Special focus
    upon the use of an MDX Condition expression to restrict the availability
    of a URL Action;

  • Verification
    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
    hypothetical client.

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 URL’s
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.

NOTE:
For a general introduction to Actions in Analysis Services 2000,
see my articles Putting
Actions to Work in Regular Cubes
and Actions
in Virtual Cubes
, both members
of this series.

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 cube’s 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
motion.)

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
Services
database.

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

Hands-On Procedure

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.

Preparation

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:

Ascertain
Connectivity of the Relational Data Source

Let’s
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 sample’s 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
articles index:

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles