SQL Server Analysis Services (SSAS) provides built-in MDX functions to facilitate time-based calculations. For example, these functions are often used for period-to-period and same-period-last-year comparisons. (If you're unfamiliar with these MDX functions and calculations, see the web-exclusive article "The Concepts Behind Time Calculations in SSAS". Although approaches to leverage the MDX functions exist, they have shortcomings. I developed an approach that overcomes the shortcomings and strives to provide end users with calculations that are flexible, simple to use, and offer good performance.
I'll begin by describing commonly used approaches to time-based calculations (which involve a time utility dimension) and the shortcomings of those approaches. I'll then walk you through the approach I developed. Although I use SQL Server 2008 in the examples, my approach also works with SQL Server 2005.
Commonly Used Approaches That Involve Time Utility Dimensions
As I discuss in my web-exclusive article "The Concepts Behind Time Calculations in SSAS," adding distinct time-based calculations for each measure (and applicable time-based dimension attribute/hierarchy) can result in bloated, unwieldy cubes. A time utility dimension can be used instead.
In 2002, I came across the concept of a time analysis utility dimension (now typically referred to as time utility dimension) while reading the first edition of George Spofford's MDX Solutions (John Wiley & Sons, 2001). In Spofford's approach, you manually add a new single-member dimension (with a static value such as [Current]), then add a calculated member to the dimension for each type of desired analysis (e.g., period-to-period, same-period-last-year, year-to-date). The result is a flexible cube that has a small set of calculated member definitions.
For example, look at the pivot table in Figure 1.
|
Figure 1: Pivot table from a cube in which a time utility dimension was manually added (click to enlarge)
|
 |
This pivot table is tracking two economic indicators: unemployment and consumer price index (CPI). A Time dimension hierarchy is displayed along the rows of the pivot table, and a Time utility dimension is displayed along the columns. The two columns underneath the Current member display the actual measure values for a given date, while the PriorPeriod and YearAgo columns represent calculated members.
The clever part of this approach is that the Current, PriorPeriod, and YearAgo members are generic (i.e., not explicitly defined against any particular measure) and defined only once. Measure values can be switched in and out of this pivot table. When you do so, the Current, PriorPeriod, and YearAgo values will update accordingly.
Let's walk through an implementation of a time utility dimension built directly into SSAS: the Business Intelligence Wizard's "time intelligence" option. (The Business Intelligence Wizard was first introduced in SSAS 2005.)
If you'd like to follow along, you can download the files in Chessman_TimeAnalysis_CodeSamples.zip by clicking the 103355.zip hotlink near the top of the page. The underlying data, which comes from the Organisation for Economic Co-operation and Development, compares economic trends across different countries.