Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 11, 2006

Actions in Analysis Services 2005: The URL Action - Page 2

By William Pearson


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM