Step 5. Test the Cube
Before you let end users query the cube, you should test the cube by using the browser in BIDS to simulate an end user or by creating a connection to the cube from an application such as Microsoft Excel. If you use BIDS, you might encounter an "unknown error" on certain navigation paths because of a bug in the BIDS browser.
These errors don't show up in Excel. If you're using Excel, make sure you go into the PivotTable options and select Show calculated members from OLAP server on the Display tab.
Figure 6 contains a sample pivot table that shows historical unemployment rates in the United States.
|
Figure 6: Pivot table from a cube in which a time utility dimension was added using the new approach
|
 |
The TimeCalcs dimension is displayed in the columns. The PriorPeriod and YearAgo calculations are properly scoped at yearly, quarterly, and monthly levels. If you have Excel 2007, you can open the pivot table spreadsheet (Excel_PivotTables.xlsx) and explore the various measures, date attributes, and date hierarchies.
Note that the GovtDebtAnalysis cube you just created doesn't contain role-playing dimensions. If you have time-based role-playing dimensions (e.g., OrderDate, ShipDate) in a cube, you can copy and paste all the scoped calculation definitions, then replace the dimension name with the role-playing dimension name. Because my approach uses a referenced time utility dimension rather than using the [TimeCalcs] attribute directly from the date dimension, the calculations will work across all the role-playing dimensions.
Benefits of the New Approach
So what can your end users gain from all this effort? Consider the original goal of providing end users with flexibility, simplicity, and good performance:
- Flexibility. Regardless of the measures, date attributes, or date hierarchies being queried, the [Current], [PriorPeriod], and [YearAgo] calculations are automatically computed and appropriately displayed. Other approaches to time utility dimensions don't do this.
- Simplicity. Only two calculated members were added to provide time-based analysis across the entire cube, avoiding the problem of calculated measure explosion inherit in traditional approaches. (For an example of calculated measure explosion, see "The Concepts Behind Time Calculations in SSAS".)
- Good performance. The MDX script contains no runtime-dynamic statements (e.g., CASE, IIF). Instead, the script uses static scoping to avoid potential performance issues. It also efficiently eliminates erroneous calculations at the start and tail end of the date dimension. For example, in Figure 6, notice that years without unemployment rates (1790-1989) don't appear in the pivot table and the PriorPeriod and YearAgo results for the first year containing data (1990) correctly show a null value.
Give It a Try
I encourage you to try this new time-calculation approach in your existing cubes and any new cubes you need to create. Your end users will be more productive and independent. Although the implementation involves a bit of up-front effort, it should reduce the amount of overall time spent on custom calculations.