Actions in Analysis Services 2005: The Drillthrough Action

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, 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 on 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 – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost.


The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among other 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 Actions in Analysis Services 2005: An Introduction we overviewed the general types of Actions with which we, as Analysis Services developers, can support information consumers. We discussed examples of consumer needs that might be classified within each of the general Action types, as well as reviewing the points of interaction at which we can place “triggers” to allow analysts and information consumers a means of accessing valuable, but often external, information in a non-disruptive manner. We stated in Actions in Analysis Services 2005: An Introduction that we would extend our introduction to individual examinations of specific Action types in separate articles. Individual articles, we reasoned, would afford us the time and focus required to examine each type in detail, from a perspectives of creation and maintenance. This article will focus upon the new Drillthrough action that makes its debut, along with many other new features, in Analysis Services 2005.


In this article, we will gain some familiarity with the Drillthrough action – how it works and some of its capabilities – and then get some hands-on exposure to creating a Drillthrough action within the sample AdventureWorks development environment, which can be installed with MSSQL Server 2005. Our examination of the Drillthrough Action in this article will include:



  • An introduction to the Drillthrough Action, including an overview of its uses and the data it presents;
  • A hands-on practice exercise, wherein we set up a working Drillthrough Action, based upon the sample Adventure Works Analysis Services database;
  • A discussion regarding the various settings that we exploit in the design and creation of our Drillthrough Action;
  • Special focus upon the use of an MDX Condition expression to restrict the availability of a Drillthrough Action;
  • Verification of the effectiveness of our new Drillthrough Action, from the tandem perspectives of availability of the Action and operation of the Action, in meeting the business requirements of a hypothetical client.

The Drillthrough Action


Overview and Discussion


As we noted in Actions in Analysis Services 2005: An Introduction, Drillthrough Actions provide a means for an information consumer to view the fact table rows that underpin 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 conjunction 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 upon the Rowset Action, in that it returns fact table details in a rowset. (It is important to keep in mind that the rows come from the fact table, and not from the originating relational database – when we say that Drillthrough makes it possible to see the transactions underlying a given aggregation, we don’t mean the original transactions per the OLTP. There are other ways to accomplish a “drillthrough to the OLTP.”)


We will create an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005. As we have noted in other articles of this series, the Analysis Services Project that we create within the Studio will assist us in organizing and managing the numerous objects that we will need to support our work with an Analysis Services database.


Considerations and Comments


For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 for use with Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.


The topics “Running Setup to Install AdventureWorks Sample Databases and Samples” in SQL Server Setup Help orInstalling AdventureWorks Sample Databases and Samples” in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.


Hands-On Procedure


We will get some hands-on experience with our subject matter in a practice session. We will first need to prepare for our exercises by creating an Analysis Services project, within which to work from the Business Intelligence Development Studio. We will rely heavily upon samples that ship with MSSQL Server 2005, to minimize the preparation time required to create a working practice environment. This “shortcut” will afford anyone with access to the installed application set and its samples an opportunity to complete the steps in the practice session.


Preparation


If you prefer to work within an existing copy of the Adventure Works Analysis Services project (perhaps you have already made a copy for work with previous articles), or you intend to create a new, pristine copy of the original from the CDs or another source for this (and possibly other) purposes, please feel free to skip the related preparatory sections.


Create a New Analysis Services Project within a New Solution


For purposes of our practice session, we will create a copy of the Adventure Works Analysis Services project, one of several samples that are available with (albeit installed separately from) the Microsoft SQL Server 2005 integrated business intelligence solution. Creating a “clone” of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Analysis Services and other components of the Microsoft integrated business intelligence solution in general.


To create a copy of the sample Adventure Works Analysis Services project, please see the following procedure in the References section of my articles index:



Ascertain Connectivity of the Relational Data Source


Let’s ensure we have a working data source. Many of us will be running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone (the default for the Adventure Works DW project sample’s connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default).


If you do not know how to ascertain connectivity of the relational data source, please perform the steps of the following procedure in the References section of my articles index:


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.

Latest Articles