Let me build a case for integrating time-based calculations into a cube. Consider the following questions:
- What are the total sales this quarter, and how does that compare to the same quarter last year?
- How much did inventory levels increase or decrease last month?
- Is the number of male student applicants starting to decline?
- When did we achieve the highest donations?
- What is our likely tax obligation in 2010?
All of these questions have something in common—time. That's not a surprise as time applies to virtually every organization. You see this manifested in SQL Server Analysis Services (SSAS); almost every cube you design or query will contain at least one dimension based on time. While certain time-oriented questions (e.g., what are the total sales this quarter) are answered by simply browsing a cube, other questions (e.g., how do sales this quarter compare to the sales in the same quarter last year) need to be calculated. This is where time-based calculations come into play. By integrating common time-based calculations into a cube, end users become empowered and more effective in their jobs.
Overview of MDX Functions Used for Time-Based Calculations
If you haven't already done so, download the accompanying samples for this article by clicking the 103356.zip link near the top of the page. The underlying data, which compares economic trends across different countries, comes from the Organisation for Economic Co-operation and Development.
Start by restoring the SQL Server 2008 relational database from the DebtAnalysis.bak backup file. Next, open up the OLAP_GovtData.dwproj project in Business Intelligence Development Studio (BIDS). Edit the project's target server property to match the location of your SSAS instance. Finally, after editing the DebtAnalysis.ds data source to reflect the location of the DebtAnalysis relational database, process the entire database using the Database menu's Process option.
Period-to-Period Calculations
For this article, I'll limit the focus to two common sets of time-series calculations: period-to-period and same-period-last-year. Period-to-period calculations (also called prior period calculations) return or use data from a prior period. To illustrate, open the file MDXQueryExamples_1.mdx (which is located in the MDX_Queries folder in the OLAP_GovtData project) in SQL Server Management Studio (SSMS) and establish a connection to the GovtDebtAnalysis cube in the OLAP_GovtData database. Scroll down and execute Query 2b, which Listing 1 shows.
Listing 1: Query 2b |
 |
The results for this query can been see in Figure 1. Note that if you'd rather see these results in Excel 2007, you can download PTPower. This free Excel 2007 add-on lets you create calculated members for pivot tables based on SSAS 2008 and SSAS 2005 cubes.
Figure 1: Query 2b's results |
 |