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 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,
Note: Current Service Pack 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, within which I have also implemented MS Office 2003, 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 ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.
About the Mastering Enterprise BI Articles …
Having implemented, and developed within, most of the major enterprise BI applications for over ten 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 became convinced, from their earliest appearance, 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 enterprise BI to the Microsoft solution. In addition to converting formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, 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, 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
Introduction
In this article, we will continue our examination of the design and creation, within Analysis services, of relative time periods. As we stated in our previous article, Relative Time Periods in an Analysis Services Cube, Part I, a popular feature found in Cognos PowerPlay Transformer, is a set of relative time periods, which can be generated automatically or manually for reporting in the Cognos PowerPlay application. Because of the vast market share currently possessed by Cognos in the business intelligence space, Cognos PowerPlay Transformer (the cube design component) and Cognos PowerPlay (the OLAP reporting component) are pervasive in industry. The debut of the Microsoft integrated BI solution, including MSSQL Server, MSSQL Server Analysis Services, and, most recently, Reporting Services, has driven a high level of interest in adopting the solution, in part or in whole, and a resulting demand to evaluate the tool set against the leading vendors in the BI arena, including Cognos.
It is in this rapidly moving environment of change that I receive many requests centering upon the replication, within the Analysis Services / Reporting Services combination, of features found within popular enterprise BI applications. Because the relative time periods are so popular, I constantly receive requests for assistance in setting up a similar functionality in Analysis Services, examples of which include current "period," (meaning month, quarter, year, or other levels of the Time / Date dimension), prior period, period to date, and others. An increasing number of these requests have begun to originate from organizations which have already converted from Cognos, among other enterprise BI leaders, and who are seeking to replicated functionality they enjoyed prior to converting.
In our last article, we introduced relative time periods, discussing their general importance in analysis and reporting. We described how dominant enterprise business intelligence vendor Cognos has provided these easy-to-use relative time structures within the Cognos PowerPlay Transformer application for reporting in Cognos PowerPlay. After discussing the frequent request for replicating similar capabilities, we began with a straightforward approach to meeting the requirement for relative time periods, highlighting differences in operation inherent in the use of a calculated member to achieve the capabilities offered in Cognos PowerPlay Transformer.
We then continued our exploration of this simple approach, within a practice exercise whereby we added an example relative time structure. As a part of constructing a Year-to-Date calculated member for a given measure within our sample cube, we discussed the manner in which the combined PeriodsToDate() and Sum() MDX functions could be used to support our relative time period. We then verified the adequacy of our solution through the use of the Cube Browser in the Analysis Services Cube Editor, discussing the use of the new calculated member in browses of the cube, reports and other queries. We demonstrated that the calculated member operates in a "contextually sensitive" way, from the perspective of the time dimension levels at which it is employed, leveraging the power of OLAP beyond the capabilities of a simple fixed calculation.
In this article, we will develop a somewhat more sophisticated approach that closely replicates relative time period functionality in Cognos PowerPlay Transformer, and which thereby provides a solution even more user friendly for reporting specialists and information consumers. Having examined the creation of such time aggregations in Analysis Services, we will later take a look at putting that solution into use in the reporting component of the Microsoft integrated BI solution, Reporting Services, much as we would use Cognos PowerPlay as the application to report from a cube created in Cognos PowerPlay Transformer.
In this, the second half of a two-part discussion surrounding relative time periods in an Analysis Services cube, we will briefly re-examine the capabilities found in Cognos PowerPlay Transformer and other enterprise cube design applications, and then::
- Discuss a more sophisticated approach to meeting the requirement for relative time periods;
- Highlight the differences between our expanded approach and the simple approach we examined in our previous article, where we created a standalone calculated member to meet a narrower need;
- Perform a practice exercise, whereby we add relative time capabilities with this more evolved method, creating the structural members in Analysis Manager that we require to implement the solution;
- Verify adequacy of our solution by demonstrating the use of the new capabilities from the perspective of the Cube Browser in Analysis Manager;
- Generally discuss the use of our new structures in the ultimate reporting application. We will also look forward to a subsequent article where we employ the relative time structures that we create in this article within Reporting Services. This will effectively demonstrate that the functionality provided by Cognos PowerPlay Transformer to Cognos PowerPlay can be duplicated within the Microsoft BI solution.
