Mastering Enterprise BI: Introducing Actions in Analysis Services 2005

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, if appropriate, 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 upon 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 often exceeded – by the Analysis Services / Reporting Services combination, at a tiny fraction of the cost. I know this to be a fact because I convert once dominant enterprise business intelligence implementations to the Microsoft solution on a regular basis.

The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among the offerings of the dominant 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 Putting Actions to Work in Regular Cubes, and in Actions in Virtual Cubes (both members of the Database Journal series Introduction to MSSQL Server Analysis Services), I introduced Actions as they existed within Analysis Services 2000. In this and the related articles, we will overview Actions in Analysis Services 2005. Much as they did in the previous version of Analysis Services, Actions allow information consumers to go beyond the robust OLAP perspective offered by Analysis Services, and to “step outside” for related information, or to generate commands or initialize programs, without leaving their current analysis focus. Most of the enhancements that appear in Analysis Services 2005 involve increased ease of development and a higher degree of functional specialization.

As we shall see in this and the related articles, Actions can be structured into the cube by the developer, to allow users to perform these extended activities from various vantage points within the Cube Browser. As an illustration, Actions can be triggered by a consumer from cube cells, as well from dimensional members and levels, among other locations that we will examine in the section that follows. Actions are composed of metadata that we define as a part of cube design, and they typically send operational commands to client applications – operations that are contextually based upon the selections within the Cube Browser from which we trigger the Action. Because Actions can link complementary, extended information that exists outside the cube, and because consumers can perform Actions without leaving their current position within the analysis they are performing in Analysis Services, much time can be saved in “stepping outside” for additional information to complete the job at hand.

As I noted was the case in Putting Actions to Work in Regular Cubes, I find Actions to be remarkably underused jewels within the Analysis Services goldmine, in working with Analysis Services at client sites. Moreover, when we search the Web for documentation surrounding Actions, we tend to come back with the same, well-worn examples of very simple scenarios. I expect this situation to improve over time, as the creative users out there continue to contribute their ideas in public forums. The imagination is the only constraint when we consider the options that Actions offer receptive architects, developers and kindred practitioners among us.

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 Analysis Services 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 sources. The web, as well as organizational and other intranets, often serves as home(s) to many of the complementary information sources. It is these sources that an OLAP-focused consumer might need to access during analysis, 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 offer a means of accessing this valuable related information in a non-disruptive manner. Our examination of Analysis Services Actions in this article will include:

  • An introduction to the general types of Actions that we can exploit in the design and development of our Analysis Services models;
  • A discussion of example consumer needs that might be classified and managed 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 Analysis Services consumer.

In this article, we will gain an understanding of the nature of Actions in general, focusing on their types, possible uses, and the choices we have for points of information consumer interaction. We will activate much of what we discuss within this article in the hands-on practice sessions within the articles devoted to the individual Action types. In these articles, we will extend our grasp of the key concepts to the creation and maintenance of specific Action types, to afford the time and focus required to examine each of the chosen types in detail.

Introducing Actions in Analysis Services 2005

As stated in the introduction, we design Actions into our cubes to provide analysts and other information consumers new flexibility in reaching beyond the purely OLAP presentation, to start an application, or to perform other steps, within the context of the data item(s) from whence they trigger the Action. (Microsoft states in the Books Online that “An action is just a Multidimensional Expressions (MDX) statement that is stored in Analysis Services and which can be incorporated into client applications and started by a user.”) The idea is to support further exploration based upon the results of analysis as presented in the cube.

There are several classes, or types, of Actions that we can install so that users can access external information and capabilities. Placement of the “trigger” for the Action is another consideration when creating Actions in our cubes. These launch settings allow us, as part of Action design and construction, to decide the physical points within the cube presentation from which information consumers can initialize the Action.

Analysis Services 2000 provided a wizard as the means of creating Actions. In Analysis services 2005, the Cube Designer offers an Action tab, where we design the Action, specifying its target, type and expression. While the apparent order of setup might appear to dictate the designation of placement of an Action (its target) before the designation of the type of Action that we want to create, I find it useful in client development scenarios to plan an Action from the more general concept of what it is supposed to accomplish, before thinking about the point from which it might best be accomplished. The order of planning these aspects might not be critical, but that’s just the way my mind likes to arrange the furniture.

Considerations that are highly important to planning, however, include the effective and complete gathering of the business requirements of the information consumers for whom we are constructing the Action. This should naturally be followed up with requests for feedback, from pilot users, as to whether the intended design actually meets their needs in a user-friendly way. (The slickest functionality is undone if it does not make life easier, or, worse, if it causes an obstruction to its user. Even minor irritations, on a repetitive basis, assume the characteristics of serious design gaffes.) Actions will offer new capabilities to many, and their effective, user-friendly introduction into the client application requires input from representative information consumers in both the design and the piloting phases.

The Action Types

Analysis Services offers six general types of Actions, as well as a seventh “flexible” option, to extend our analysis capabilities beyond the drilling up, drilling down and pivoting to which most of us have become accustomed within OLAP applications. The types of Actions we can use to link complementary information to our existing cube presentations are detailed in Table 1.

Action Type:

Returns:

URL

A URL that constitutes a link between selected structures in the cube (the “targets” we discuss elsewhere in the article) and internet or intranet sites that contain complementary information. The client temporarily stores the URL information, and launches its default browser to its specifications, when the Action is executed.

Statement

A statement that can be executed as an OLE DB command, with an outcome of either success or failure, but without the return of results.

Dataset

A dataset to a client application.

Rowset

A rowset to a client application.

Drillthrough

A drillthrough statement to a client application. A Drillthrough Action is defined by a Rowset Action.

Reporting

A report to a client application

Proprietary

An Action in this class can be virtually anything that does not fit into the first six classes. The Proprietary type offers flexibility for custom Actions to meet business needs that are not easily met via one of the other Action types.

Table 1: Analysis Services Action Types

URL Actions have been the most commonly chosen, 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 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 City and State information. URL Actions are easy to use, and make for excellent linking from cube members, to information sources about those members that lie outside the realm of the cube’s analytics.

A Statement Action type might be represented by an Action that allows a pension fund analyst to be able to flag balances of uncashed checks for follow-up, within a client application, to determine the amounts that need to be recognized as having a potential “abandoned property” status. A Statement Action might also support the capability, from within a cube that contains information concerning plan member companies, to add or delete members from the next update of the cube.

A Dataset Action can be used to link users to data outside the cube, such as that housed within other cubes in the same Analysis Services database. I’ve linked financial cubes with “actual” measures to cubes that contain budgeting and forecast information, to cubes containing HR / headcount measures, and to physical inventory cubes. In each of these cases, my intent was to allow financial analysts to examine the respective underlying, or surrounding, factors, when prompted by a value or values that warrant further investigation. Another example, based upon a cube I constructed for a financial services industry client, extended the perspective of the analyst beyond the measures of his organization’s financial assets cube. Rapid queries were then possible of a large financial ratios statistical cube I had built, to allow for context-sensitive comparisons to a wide array of industry- and instrument-specific ratios.

Like the Dataset Action type, Rowset Actions contain commands to retrieve data. Examples of uses include Actions that query external relational databases with standard SQL. Moreover, the Rowset Action might be used to perform drillthrough to a relational data source underneath the cube within which the Action is triggered. Triggering might be performed, as an illustration, by an information consumer who wishes to see the detail transactions behind account balances that have recently deteriorated, with respect to aging, in corporate Accounts Receivable.

Drillthrough Actions provide a means for an information consumer to view the fact table rows that constitute an aggregated value within a cube cell. This special Action type debuts in Analysis Services 2005, improving the more rudimentary MDX approach to drillthrough that we knew in Analysis Services 2000. (See my article Drilling Through to Details: From Two Perspectives for a discussion of drillthrough options in the previous version). Drillthrough Actions targets are always cells (single or multiple), in combination with specific Measure Groups. (For more on Measure Groups, and how they are associated, see my article Mastering Enterprise BI: Working with Measure Groups, also a member of the Introduction to MSSQL Server Analysis Services series.) The Drillthrough Action is based on the Rowset Action, in that it returns fact table details in a rowset. We provide in-depth coverage of the Drillthrough Action in another article of this series, where we gain hands-on practice in creating and using a Drillthrough Action.

Report Actions are also new in Analysis Services 2005. Report Actions 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. Report Actions are triggered within the Cube Browser from the targets we establish, much as other Actions are triggered. We can offer a selection from multiple reports (if we design Report Actions for each) from the target point, to allow consumers to obtain the information specifically needed while browsing OLAP data. This might consist of detail data (indeed, a drillthrough report could be generated in this manner), as well as related, but wholly external, data that we might want to make available from another data source, for examination alongside the summary data in the cube. We provide in-depth coverage of the Report Action in another article of this series.

Finally, the Proprietary Action classification allows us to create “custom” Actions that don’t fit into any of the general functional categories above. It also endows us with the flexibility to construct Actions that perhaps comprise elements of two or more of the type options above, within sophisticated combinations that might require the implementation of a more elaborate design than is supported by the other Action types.

Placement and Positioning Alternatives

The purpose of Actions, as we have seen, is to enable information consumers to extend their activities beyond the data contained in their cubes, and to amplify and extend their analysis. Actions might, for example, allow consumers to investigate or otherwise act upon discovered problems or other apparent anomalies and / or inconsistencies in the data they analyze. Actions can extend the largely analytical review and reporting functions of the Analysis Services / client combination to include capabilities to perform operational and other activities from within the Cube Browser or Analysis Services client application.

To provide an intuitive, useful transition to external applications from the analysis perspective, while passing along the “coordinates” of the analysis in progress at the time of the transition, we can establish targets. 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.

Alternatives within the Action tab for placement of Actions, and their subsequent triggering by users, include the details presented in Table 2.

For Target Type:

Select:

Actions Associated with:

Cube

Current Cube

The Cube with which we associate the Action.

Dimension Members

A single Dimension

All members of the selected Dimension.

Hierarchy

A single Hierarchy

The Hierarchy only.

Hierarchy Members

A single Hierarchy

All members of the selected Hierarchy.

Level

A single Level

The Level only.

Level Members

A single Level

All members of the selected Level.

Attribute Members

A single Attribute Hierarchy

All members of the selected Attribute.

Cells

“All Cells”

All Cells, restricted by Condition expression supplied.

Table 2: Action Placement Options and Associations

We place targets, as well as establishing numerous other settings for Actions, within the Actions view (accessed via the Actions tab), inside the Business Intelligence Development Studio. The Action becomes available to consumers once it is created and the cube is reprocessed.

In other articles of the Introduction to MSSQL Server Analysis Services series, as well as elsewhere, we create and work with various Action types, examining the features of each in detail.

Summary …

In this article, we began an exploration of Analysis Services 2005 Actions, including the two new special Actions that have debuted in this version of Analysis Services, Drillthrough Actions and Report Actions. Our objective was to gain an understanding of the nature of Actions, focusing upon their Types, possible uses, and the choices we have for points of information consumer interaction (targets). We activate what we have learned in detailed, hands-on articles devoted to the respective individual Action types (also members of the Introduction to MSSQL Server Analysis Services series). In each of these individual articles, we discuss the Action under examination in detail, and then reinforce the concepts surrounding it within a rudimentary practice exercise that allows us to see the end-to-end process for creating and using an Action of its type.

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

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

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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles