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 Mar 15, 2004

Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes

By William Pearson

About the Series ...

This is the twenty-first article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real-world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("MSAS").


In this article, we will examine Actions, a powerful feature in MSAS that allows information consumers to go beyond the robust OLAP perspective offered by the application, and to "step outside" for related information, or to generate commands or initialize programs, without leaving their current analysis focus. As we shall see, Actions can be structured into the cube by the developer, to allow users to perform these extended activities from various vantage points, with a simple right-click of the mouse. This saves analysts and other consumers time, as the complementary information can be "linked" through the Action design for them, and, perhaps more importantly, because they can perform the Actions without leaving their current position within the analysis they are performing in MSAS.

Working with MSAS at clients every day, I find Actions to be remarkably underused jewels within the MSAS goldmine. Moreover, when we take a cruise across the internet in search of documentation that surrounds the feature, we tend to come back with the same well-worn examples of very simple scenarios. I expect this vacuum to improve over time, especially if the creative users out there will contribute their ideas in public forums. The imagination is the only constraint when we consider the options that Actions practically shout to the receptive architects, developers, and kindred practitioners among us.

In this article, together with the article that follows it, we will spend a little time looking at the functionalities that Actions offer, and will explore their use in a multiple step practice example. In this manner, I hope that Actions will "speak louder than words" in exposing more of the rich analysis functionality that is MSAS.

Introduction to MSAS Actions

Within the structured regimen of the OLAP world, analysts constantly find themselves needing to reach beyond the analytical anchor point of the cube browser or, more commonly, the application that integrates with MSAS to provide the informational views with which they work. Examples of related information that does not appear within the OLAP view abound. External data stores (relational and / or OLAP) compose a significant component of "external data," but also included is documentation such as procedural manuals, organizational charts, account listings, and myriad other collateral. The web and intranets often house many of the complementary information sources to which an OLAP-focused consumer might need access at any time to crystallize a component within the OLAP view, to understand the geographical makeup of organizational units, or to understand the structures underlying considerations that might range from headcount to responsibility accounting.

Actions are a means of accessing this valuable relative information in a non-disruptive manner. Our examination of MSAS Actions in this article will include:

  • An introduction to the general types of Actions that we can exploit in the design and development of MSAS cubes;
  • A discussion of example consumer needs that might be classified within each of the general Action types;
  • A review of the points of interaction at which we can place an Action for use by an MSAS analyst;
  • An introduction to the MSAS Action Wizard;
  • A hands-on practice example of the creation of an Action within a "regular" cube, the sample Sales cube that installs with MSAS;
  • Practice in using the example Action we create, both as a general guide to the use of Actions in general, and as a means to ascertain their effectiveness from the perspective of development.

In this article, we will gain an understanding of the nature of Actions, focusing on their types, possible uses, and the choices we have for points of information consumer interaction. We will then activate what we have learned, reinforcing the concepts within a practice exercise that allows us to see clearly how the mechanics tie together within a regular cube.

In our next article, Actions in Virtual Cubes, we will extend our grasp of the key concepts to the creation and maintenance of Actions in virtual cubes (for an introduction to virtual cubes, see my article Exploring Virtual Cubes). We will highlight the differences in treatment between regular and virtual cube scenarios for Actions, and then practice the concepts within an exercise. We will also explore a particularly useful way to leverage an investment in Action design by importing an existing Action into a virtual cube.

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