Actions in Analysis Services 2005: The URL Action

Monday Dec 11th 2006 by William Pearson
Share:

BI Architect Bill Pearson continues in-depth coverage of Analysis Services 2005 Actions. This month’s 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 ...

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:

Make a Copy of an Analysis Services Sample Project within a New Solution

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:

Ascertain Connectivity of the Relational Data Source

Procedure

Work with a URL Action

Create a URL Action

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

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

Our 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 Services 2005.

We 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:

1.  Inside the Solution Explorer, right-click the AdventureWorks cube within the Cubes folder of our project.

2.  Select Open 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.

3.  Click the Actions tab.

The Actions tab opens.

4.  Select Cube -> New Action from the main menu, as shown in Illustration 2.


Illustration 2: Select Cube -> New Action

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

5.  Replace the default Name with the following:

Parameterized Internet Search

6.  Select Hierarchy Members in the Target type selector.

Here we are simply selecting, among several options, to make our Action available within a single hierarchy within a dimension of our cube.

7.  Select the downward pointing selector arrow on the right side of the box labeled Target object, immediately below the Target type selector.

8.  Within the selector that appears, expand the Product dimension by clicking the “+” sign to its immediate left.

9.  Select the Product Categories hierarchy that appears (the second item under the newly expanded Product dimension).

10.  Click OK to accept the selection.

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

11.  Type, or cut and paste, the following into the box labeled Condition (Optional) immediately underneath the Target object selector:

[Product].[Product Categories].CURRENTMEMBER <> 
 
 [Product].[Product  Categories].[Category].[Accessories]

Here 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.“

12.  Select URL in the Type selector, in the Action Content section of the form that appears below the Condition input box.

13.  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)
"http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGIC,GGIC:2006-38,GGIC:en&q=" 
-- 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.

The Actions tab – Action Form Editor appears, with our input, as depicted in Illustration 3.


Illustration 3: Actions Tab – Action Form Editor with Our Settings

14.  If required, click the down arrows to the immediate left of Additional Properties, the section just beneath the Action expression box, to expand the properties into view.

15.  Ensure that Interactive is selected within the Invocation selector atop the Additional Properties.

16.  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: " + 
   [Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION

17.  In the bottom selector in the Additional Properties section, labeled Caption is MDX, select True.

The setting 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 our handiwork.

18.  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:

Deploy the Analysis Services Project

Verification

Inspect the Operation of the URL Action within the Cube Browser

Once 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:

1.  Click the Browser tab to initialize the Browser for the Designer, as depicted in Illustration 5.


Illustration 5: Click the Browser Tab ...

2.  Once the Browser loads, click the Reconnect button, as shown in Illustration 6.


Illustration 6: Click the Reconnect Button ...

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

3.  Within the cube tree appearing inside the Metadata pane, expand Measures.

4.  Expand the Reseller Sales folder, as depicted in Illustration 7.


Illustration 7: Exposing Measures within the Cube Tree

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


Illustration 8: Dragging the Measures to the Browser Canvas ...

6.  Once again inside the Metadata pane, expand the Product folder.

7.  Right-click the Product Categories hierarchy that appears in the expanding tree.

8.  Select Add to Row Area from the context menu that appears, as depicted in Illustration 9.


Illustration 9: Adding the Product Categories Hierarchy to the Cube Browser Row Axis

9.  Expand the Date folder in the Metadata pane,

10.  Expand the Calendar folder that appears.

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.


Illustration 10: Adding Calendar Quarters to the Cube Browser Column Axis

The available Calendar Quarters appear within the browser’s 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, let’s 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 axis).

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


Illustration 12: The URL Action is Appropriately Absent (and Unavailable) for Accessories ...

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

We will 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 successful.

18.  Close the web browser, when ready.

19.  Further inspect the availability and operation of the URL Action, performing additional browses, as desired.

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.

Conclusion

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

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

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved