| Downloads |
|---|
| 46157.zip |
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.
Next, the wizard sets the default member of the calculated attribute to the single noncalculated member that it specified when it created the named calculation. Cube developers often overlook the role and importance of default members. Every attribute in the cube, including Measures, has a default member. In the absence of an explicit filter that requests otherwise, the default member of each hierarchy is automatically included in every query to the cube.
In our example, setting the default member is crucial because we want to avoid returning a calculated view over time unless a user explicitly asks for it. To provide a point for comparison, we also want to provide the natural value of a given measure along with the various calculations. This is where our lone physical member comes in. Because the definition of the member exists in all rows, this is equivalent to not specifying a filter condition at all.
After creating the database attribute, the wizard analyzes the cube structure for the presence of multiple roles for the target cube dimension. As we noted earlier, each pass of the wizard targets one hierarchy in one cube dimension and results in the creation of one calculated attribute. Because the calculations won't apply in the context of any nontargeted roles, the calculated attribute is disabled for roles to which it doesn't apply.
Modeling Calculations
When the requisite cube and dimension structure is in place, you can create the actual calculations. Depending on the location in the cube, each time calculation can exist in one of three states:
- State 1: The calculation applies to the current coordinate and sufficient data exists for the calculation. In these cases, the actual expression is applied.
- State 2: Insufficient data exists for the calculation. For example, say you're comparing periods and the current period is the first one in the hierarchy (common when a period-over-period comparison is requested for the first period of available data in the cube). Not enough data exists, so the calculations explicitly return a null value.
- State 3: The calculation doesn't apply to the current coordinate. For example, when the current coordinate refers to Year to Date for the All Years member, or Month to Date when viewing data for a given year. In these cases, the calculations created by the wizard simply return the string "NA" or the locale-specific equivalent.
To determine which state the current coordinate falls into, you'd typically use the IIF function (in Analysis Services 2000), or you could use the CASE operator (new to Analysis Services 2005). However, this approach results in dynamic checks during runtime evaluation of each cell, and the resulting MDX expressions tend to quickly become cumbersome and difficult to debug as the levels of nesting increase.
The Business Intelligence Wizard provides a much more efficient and elegant approach. The wizard uses the new MDX scripting syntax constructs to define specific scopes to which each calculation applies. Because Analysis Services evaluates SCOPE statements statically (once) when the MDX script executes, this technique inherently avoids unnecessary per-cell runtime checks.
To illustrate this more efficient approach, let's assume that a user requests three calculations: Year to Date, Year Over Year Growth, and Twelve Month Moving Average. Listings 1 through 3 show fragments of the MDX script that the wizard generates for these calculations.
Listing 1 creates the calculated members. The wizard assigns a value of NA to account for State 3, the calculation doesn't apply to the current coordinate. (For those who are familiar with MDX, note the new, simplified syntax for the CREATE statement.)
The next step, which Listing 2 shows, sets the calculation scope on the user-selected measures. All measures not included within this SCOPE statement retain the value NA because the user specified that the selected time intelligence calculations don't apply.
After you specify the scope for the correct measures, you can assign more meaningful expressions to the calculated members. Listing 3 shows an example of the MDX assignments that correspond to the selected calculations. Assignments, a key MDX enhancement in Analysis Services 2005, let you apply MDX expressions to existing cells in the cube space; the resulting values are then aggregated as they would be in the absence of the expressions.
Best Practices
Now let's take a look at the MDX code that the wizard generates to see some best practices that you can use when authoring your own calculations in Analysis Services 2005.
As Listing 3 shows, best practice uses attribute hierarchies on the left side of the assignment. In Analysis Services 2005, the cube space is defined entirely by attributes, so the space to which calculations apply is best described by the attribute hierarchies contained therein. Conversely, specifying the scope for user-defined hierarchies can result in inadvertently over-restricting the calculation scope. The Year to Date calculation illustrates this best practice through the use of the Fiscal Year attribute hierarchy, which excludes the All member (where the calculation doesn't apply). This approach includes all other attributes in the hierarchy, regardless of whether or not they are All members, which is precisely what we want.
Next, note that the right side of the assignment uses multilevel user hierarchies (rather than attribute hierarchies), which lets you use hierarchy-friendly MDX functions such as ParallelPeriod and PeriodsToDate.
Another best practice uses the Aggregate (rather than the Sum) function in the Year to Date calculation. In Analysis Services 2005, the Aggregate function can process nonadditive distinct count and semi-additive measures as well as other nonadditive scenarios such as many-to-many dimensions and measure expressions. You can even apply the Aggregate function on top of some calculated measures, such as ratios.
Now that you've seen how the Business Intelligence Wizard works behind the scenes, you're ready to deploy the modified cube and look at things from the end-user's perspective. Figure 4 shows the development environment's Cube Browser with the newly created calculations for our example.
Time Analysis Has Never Been Easier
As this look at the time intelligence enhancement demonstrates, SQL Server 2005 Analysis Services, with its many features and built-in enhancements, provides automatic, intelligent solutions to common business problems. Analysis Services 2005, with these features and its greatly increased modeling and analytical capabilities, provides for rapid development (with high return on investment) of BI applications that far surpass what was previously possible.
