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

The Many Options for Actions

As we stated in the introduction, Actions are designed into our cubes to provide analysts and other information consumers new flexibility in reaching beyond the purely OLAP presentation, to start an application, or perform other steps, within the context of the data item(s) where they trigger the Action. The idea is to act upon the results of analysis as presented in the cube, and 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 target settings allow us, as part of Action design and construction, to decide the physical point from which information consumer can initialize the Action from within the cube presentation.

Within the Action Wizard, we will see that the designation of placement of an Action (its target location in Wizard parlance) occurs chronologically before the designation of the type of Action that we want to create. I find it useful in development scenarios at client locations, and elsewhere, 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.

A couple of considerations that are highly important to planning, however, are the effective and complete gathering of the business requirements of the intended 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 will actually meet 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.

The Action Types

MSAS offers six general types of Actions, as well as a seventh flexible option. The types of Actions we can use to link complementary information to our existing cube presentation are detailed in Table 1.

An example for a command line Action type, positioned, say, to be triggered at the account name in a financial cube, might include initializing the corporate accounting application to allow an analyst to inspect the transactions in a period where an account had a higher-than-normal balance. Another example could be an Action that allows an engineering inventory analyst to call up a graphics package that contains an exploded picture of a large assembly, so as to see details about the component parts that make up the assembly. 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, to determine the amounts that need to be recognized as having a potential "abandoned property" status. Statement Actions might also include 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.

HTML Actions might include any number of supporting lists and reports that are based upon outside data sources, or perhaps a combination of internal and external data sources, all accessible at the click of the mouse by the information consumer at the point in the cube data he is analyzing. HTML Actions might involve forms that appear when the Action is initialized from within the cube, asking for input, perhaps, of questions / comments that relate to the structural element selected input that is then accumulated and routed to the appropriate responsibility centers. HTML Actions comprise an excellent scenario from which to build an understanding of Actions within a development perspective, so we will be working with one of these, together with some basic MDX, in the practice example that follows.

URL Actions have been used in some of the documents in the slowly growing body of knowledge surrounding Actions that is currently available in print and electronic media. I have seen examples where the URL Action takes the relevant parameters from selected City and State members in a cube and, after transiting the browser to a popular travel organization's web site, 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.

Similarly, a Dataset Action can be used to link users to other cubes in the same MSAS database. I have 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 factors, when prompted by a value or values that warrant further investigation. Another example, based upon a cube I am currently constructing for a financial services industry client, extends the perspective of the analyst beyond the measures of his organization's financial assets cube with rapid queries of a large financial ratios statistical cube I am building, 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 drill through 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 in the corporate Accounts Receivable aging.

Finally, the Proprietary Action classification allows us to create Actions that do not 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 a more sophisticated combination that might require a more elaborate creation process.

Placement and Positioning Alternatives

The purpose of Actions, as we have seen, is to enable information consumers to extend their activities past the data contained in their cubes and to investigate or otherwise act upon discovered problems or other apparent anomalies and / or inconsistencies in the data they analyze. Actions can extend the once purely analytical review and reporting functions of the MSAS / client combination to include capabilities to perform operational and other activities from within the cube browser or MSAS 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 the Action, within the context of the element of the cube structure upon which the Action is put into motion.

Alternatives within the Action Wizard for placement of Actions, and their subsequent triggering by users, include:

• The cube itself
• A dimension
• Members within a dimension
• A level
• Members within a level
• Cells
• Named Sets

Placement of the target occurs as the first step within the Action Wizard, where we select one of the above objects for attachment to the Action. Multiple Actions can be established on a given object. It is at these objects in the analysis environment of the cube that the user can right-click, and select from the available Actions that exist, to initialize the corresponding Action. Actions can thus link parts of the cube structure to external, complementary information in an intuitive way.