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 Apr 19, 2004

Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes - Page 6

By William Pearson

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

  • Command 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.
  • Proprietary - Custom actions that do not fit into the first six classes.

NOTE: For more detail on the Action Types, see Putting Actions to Work in Regular Cubes.

The read-only 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 Select 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

12.  Click Next.

We arrive 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.

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

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

13.  Type (or cut and paste if you like) the following into the Syntax box:

	<title>Customer Details</title>
<body BGCOLOR=""#99CCCC"">

<table CELLSPACING=""2"" 

<tr>  <td VALIGN=""TOP"" 
	<span name=""custname"">
	<h3>Customer Profile:  "+customers.currentmember.name+"</h3>
</td> </tr>

<tr><td width=""55%"">
	<div class=""title"">State:</div>
</td><td colspan=2>
	<span name=""title"">"+customers.parent.parent.name+"</span>
<tr><td width=""55%"">
	<div class=""title"">City:</div>
</td><td colspan=2>
	<span name=""title"">"+customers.parent.name+"</span>

<tr><td width=""55%"">
	<div class=""title"">Gender:</div>
</td><td colspan=2>
	<span name=""title"">

<tr><td width=""55%"">
	<div class=""Annual Income"">Annual Income:</div>
</td><td colspan=2>
	<span name=""title"">
	"+customers.currentmember.properties("Yearly Income")+"

<tr><td width=""55%"">
	<div class=""title"">Education Level:</div>
</td><td colspan=2>
	<span name=""title"">



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

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 Essentials 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 syntax.

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 Define 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

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

We arrive at the Finish the Action Wizard dialog, where we name the Action and are given an opportunity to review a summary of its setup.

15.  Type the following into the Action Name box.

Customer Details

16.  Click Finish to accept the settings and create the Action.

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

17.  Select File --> Exit to close the Virtual Cube Editor.

18.  Save the virtual cube, when prompted.

We are returned to Analysis Manager.

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