Figure 2 shows an excerpt from the results created with the PTPower add-on.
Figure 2: Excerpt from Query 3a's results |
 |
The calculated member [UnemploymentAvg_PP_Change] uses the ParallelPeriod function to calculate the difference in unemployment rates by retrieving the unemployment rate from 1 year ago for a given member in the [H-Year] hierarchy. The ParallelPeriod function takes three arguments: a level, a number representing the number of periods to lag, and a member upon which to operate. Due to the level argument, ParallelPeriod operates only on user-defined hierarchies. To calculate same-period-last-year results on dimension attributes, you can use the Lag function instead.
Other than the erroneous results for the years of 1990 and 2010, this is a pretty nifty calculation. No matter where you navigate in the H-Year hierarchy (whether year, quarter, or month), the change from the same period 1 year ago is returned. For example, the UnemploymentAvg_PP_Change result for 1991-Feb is 1.27%, which represents the difference between the current value of 6.56% and the value 1 year ago (5.29%) in 1990-Feb.
The Need for a Systematic Approach to Time-Based Calculations
The built-in MDX functions (e.g., PrevMember, ParallelPeriod) might seem in of themselves sufficient for time-based calculations. Add a few calculated members to a cube (or even let users do it themselves with PTPower) and problem solved, right? Not exactly. There are serious shortcomings and complexities that can and should be addressed in order to truly empower end users. The shortcomings include:
- Erroneous or misleading results. When there are missing values along part of a time dimension (or the end user is looking at the first set of members in the dimension), calculated members that use functions such as PrevMember or ParallelPeriod will return misleading results. The calculated member definition needs to account for these situations. Typically, cube designers resort to fairly complicated conditional logic (e.g., IIF, CASE) in their MDXs, which can introduce performance problems along with code that's tricky to develop and maintain.
- Accounting for both attribute dimensions and dimension hierarchies. SSAS 2005 introduced the notion of dimension attributes which, from a time perspective, means end users can look at values by attribute (e.g., flat list of years) or by hierarchy (e.g., Year-Month-Day). For example, look at the DimDate dimension in our example database. End users can view measures by several attributes, including Century, Calendar Year, and Calendar Quarter. Or, they may choose to leverage a hierarchy (e.g., H-Year). The calculated member examples we've reviewed are specific to either a hierarchy or to a level. Ideally, period-to-period and same-period-last-year calculations should work regardless of what attribute or level the end user selects. (Note that a Microsoft design "best practice" recommends hiding attributes that participate in a hierarchy, but this is more of a general guideline. It doesn't address situations in which there is one or more time attributes that don't participate in a hierarchy, which is the situation in our case.)
- Calculated measure explosion. There are seven visible measures in the GovtDebtAnalysis cube. If you want to provide a period-to-period and same-period-last-year calculation for each visible measure, you now have a minimum of 21 visible measures in the cube (7 measures + 14 calculated members). However, you also have three time hierarchies. This means you'll actually need 42 (3 x 14) calculated members, which brings a new total of 49 measures (7 measures + 42 calculated members). But this total still doesn't account for visible dimension attributes. There are five in the cube, so you need an additional 70 calculated members. Finally, end users might want to see period-to-period and same-period-last-year calculations in three different forms (e.g., prior period value, change in prior period value, and change represented as a percent). You now have a cube that's bloated and difficult to understand and query.
To address these shortcomings, Microsoft and SSAS practitioners have developed various approaches to time-based calculations. My article "Optimizing Time-Based Calculations in SSAS" discusses the various approaches, including a new one that provides end users with calculations that are flexible, simple to use, and offer good performance.