Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
April 10, 2006
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, 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.
Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:
To successfully replicate the steps of the article, you may also need to have:
Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples, as appropriate. 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 2005 and its component applications.
About the Mastering Enterprise BI Articles ...
Having implemented, and developed within, most of the major enterprise BI applications for over for fourteen years, and having developed an appreciation for the marriage of ease of use and analytical power through my background in Accounting and Finance, I have come to appreciate the leadership roles Cognos and other vendors have played in the evolution of OLAP and enterprise reporting. As I have stated repeatedly, however, I have become convinced that the components of the Microsoft integrated BI solution (including MSSQL Server, Analysis Services, and Reporting Services) will commoditize business intelligence. It is therefore easy to see why a natural area of specialization for me has become the conversion of Cognos (and other) enterprise BI to the Microsoft solution. In addition to converting formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects / Crystal, MicroStrategy, and others, to the Reporting Services architecture, I regularly conduct strategy sessions about these conversions with large organizations in a diverse range of industries the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five-to-six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.
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 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 (as well as their Cognos 8 incarnations), 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 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 to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves and, 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 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.
In this, the second half of a two-part article, we continue the examination of Named Calculations we began in Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I. In Part I, by way of introduction, we recalled ways of "extending" the data source tables underlying our Analysis Services 2000 cubes which we had examined in past articles of this series, as an introduction to this article, where we explore yet another new feature where Analysis Services 2005 offers us more flexibility in this area. We noted that we were limited, in the previous version of Analysis Services, to using SQL expressions within the Member Key and Member Name columns (in the case of dimension structures), and in the Source column (in the case of measures) to achieving similar extensions. We referred to my article Mastering Enterprise BI: Create Aging "Buckets" in a Cube, where I proposed the use of an IIF / CASE scenario to build the necessary dimensional structure into a sample cube to support aging buckets, as an example of such an extension, and we got a glimpse of how, although the approach might work to help us deliver desired results in our business environments, the use of SQL expressions within these rather limited selectors might become cumbersome in many situations.
At this point in Part I, we noted that, among many overall improvements, and added conveniences in the design arena, Analysis Services 2005 offers us far more flexibility in this area, as well. We stated that the advent of the Data Source View represents a significant design and development enhancement within Analysis Services, pointing to my article Introduction to MSSQL Server Analysis Services: Introducing Data Source Views, where we first introduced this new abstract layer within the design environment. We noted, in review, that the Data Source View contains the logical model of the schema used by database objects, including cubes, dimensions, and so forth, and that it forms a central, unified view of the metadata within our Analysis Services Project.
We recalled that, in addition to being capable of representing one or more Data Sources (allowing us to integrate data from multiple data stores within a single cube, or even dimension), another of the many advantages offered by the Data Source View layer is its capacity to contain logical objects, such as queries, relationships, and calculated columns, that do not exist within (and, indeed, are entirely separate from) the underlying data sources. This factor, as we discovered, lies at the heart of our current focus upon Named Calculations, which become quite useful to us when we cannot create, for whatever reason, these "extending" objects within the data sources upon which we are constructing our Analysis Services Projects
In the first half of this article, we laid out our objective to examine Named Calculations, and to get hands-on exposure to the process of adding them to a basic cube we construct within the Business Intelligence Development Studio. We overviewed the creation of Named Calculations, and discussed ways in which they can offer flexibility in general cube design and development. We then prepared Analysis Services, and our environment, by creating an Analysis Services Project (which we called ANSYS045 Named Calculations), to house our development steps, and to serve as a platform for the design of a quick cube model, within which to perform subsequent procedures in our session. Next, we created a Data Source containing the information Analysis Services needs to connect to a database, and then we created a Data Source View containing schema information. Finally, as the last step of Part I, we added examples of Named Calculations within the Data Source View.
As a part of our continuing examination of Named Calculations in this article, we will: