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.