Analysis Services has been redesigned from the ground up in SQL Server 2005 to provide the most capable business intelligence (BI) platform in the world. Analysis Services 2005 has many features and built-in enhancements that provide automatic, intelligent solutions to common business problems. Even if you're less-than-expert in OLAP or Multidimensional Expressions (MDX) coding, you can now create rich analytical applications.
The new Analysis Services 2005 time intelligence enhancement lets you easily create time-based calculations. First, let's look at how the new Business Intelligence Wizard makes implementing time intelligence easy. Next, we'll explain the "why" and "how" behind the wizard. Then, we'll analyze the modeling techniques used in time intelligence and examine the code that the wizard generates to show you several best practices for modeling both metadata and MDX. Because this article provides only a brief overview, you'll find this information most useful if you already have experience with SQL Server 2000 Analysis Services.
Time Intelligence
Businesses commonly need to conduct time-series analyses for a given point over a specific set of time periods. Analysis Services lets you create time-based calculations—calculated views of a given measure over time—such as periods-to-date and various forms of moving averages. You can use multiple methods for modeling solutions to this problem, but each method requires you to write potentially complex MDX code and to make important metadata modeling decisions. Implementing a less-than-optimal design can have serious consequences; for example, you can end up with a significantly less user-friendly model (through overexpansion of the measures dimension) and poor calculation performance (through excessive runtime computations).
Now implementing good design is easy. The Business Intelligence Wizard provides a collection of enhancements that you can apply to extend and enrich a given model. With Analysis Services 2005, creating time-based calculations is as simple as running the time intelligence enhancement of the Business Intelligence Wizard.
Using the BI Wizard
To set up time intelligence for a specific cube, you launch the Business Intelligence Wizard from the Business Intelligence Developer Studio's context menu in Solution Explorer of an Analysis Services 2005 development cube or dimension. The wizard's Choose Enhancement screen, which Figure 1 shows, lists the enhancements available for the current object.
In most cases, you'll add time intelligence late in the development cycle, after you've finalized the core metadata structure and the calculations not related to time. It's important that you finalize the date (or time) database dimension structure before adding time intelligence because it will subsequently be referenced in wizard-generated calculations. It's also important that you decide whether to include one or more instances (roles) of the date dimension (e.g., Ship Date, Order Date) in the target cube because the wizard modifies the structure of these roles. And because time intelligence can be applied to both physical and calculated measures, you'll also want to finalize the design of the measures dimension (including calculated measures) so that a complete list of options is available when you run the wizard.
Before applying time intelligence, you must first decide which hierarchy to target and what calculations to generate. Figure 2 shows the Choose Target Hierarchy and Calculations screen, where you specify the target cube dimension hierarchy for the time-series analysis and the time calculations you want. Typically, you'll specify user or multilevel hierarchies in the dimension to support analysis at multiple levels of time (e.g., Years, Semesters, Quarters). First choose the hierarchy, then choose the calculations that you want the wizard to create. Note that the calculation template the wizard uses is fully extensible, meaning that you can easily customize the template to suit the needs of any customer, locale, or industry.
The first choice, deciding which hierarchy to use as the basis for analysis of time calculations, deserves more explanation because it differs significantly from the Analysis Services 2000 model. In Analysis Services 2000, each dimension contains a single hierarchy; in Analysis Services 2005, one dimension can (and likely will) contain many hierarchies.
Because we're dealing with time, odds are good that the target cube will have more than one role. Each role is based on one database dimension but carries a unique meaning through a different foreign key relationship to the fact table. For example, a database dimension such Date can play multiple roles (e.g., Ship Date, Bill Date, Order Date) in the context of a single cube. A dimension can (and often will) have multiple hierarchies that span various calendar types, such as Fiscal, Reporting, Manufacturing, and ISO 8601. In such cases, you run the wizard multiple times to target multiple hierarchies across multiple dimension roles. In Analysis Services 2005, the Dimension Wizard can, in one pass, automatically generate a dimension that contains any or all of the aforementioned calendar types.
Next, as Figure 3 shows, you define the scope of calculations, specifying the measures for which you want to apply the calculations. The wizard lists all the cube's measures (both physical and calculated). When you're deciding about the measures, be sure to consider both logical applicability and the business user's expectations. After you decide which measures to include, click Next to review your selections, then click Finish to apply the changes. Now you can process the cube and browse it to see the added analytical richness.
Changes to Database Structure and Metadata
Now let's go behind the scenes to see the "how" and "why" of the Business Intelligence Wizard, the underlying structural and noncalculation metadata changes that correspond to the wizard's various steps.
First, the wizard creates a new named calculation within the time-dimension table in the Data Source View (DSV). (A named calculation is a calculated column that resides purely within the DSV; no changes are written to the underlying data source.) The named calculation serves as the source for a calculated attribute hierarchy. The calculated attribute will contain a single noncalculated value (based on the named calculation) in addition to all the user-selected calculations. The wizard uses a constant as the basis for the calculation so that it applies to all rows in the dimension table. This column returns the natural values over time, both as the default value and to allow comparison to the calculated views.
Next, after creating the column, the wizard creates a dimension attribute that points to the calculated column. Because this attribute's hierarchy primarily contains calculations, we can't logically aggregate these members. To mark a hierarchy as being nonadditive, we disable the system-generated All level. The IsAggregatable property of a dimension attribute controls the existence of the All level; because we don't want to aggregate the members in the calculated hierarchy, we set it to False.