Working with Time in Microsoft SQL Server 2008 MDX

2/25/2009

Contents

Analysis Services provides a number of time-based MDX functions. Using these functions, powerful metrics can be assembled. In this chapter from Microsoft SQL Server 2008 MDX Step by Step, you learn how to employ the time-based MDX functions to calculate some of the more frequently requested of these metrics.

After completing this chapter, you will be able to:

Explain the requirements for effective time-based analysis in Analysis Services
Employ MDX functions to calculate common time-based metrics
Combine time-based expressions to assemble complex metrics

Time is a critical component of business analysis. Analysts interpret the state of the business now, often in relation to what it was in the past, with the goal of understanding what it might be in the future.

To support this, Analysis Services provides a number of time-based MDX functions. Using these functions, powerful metrics can be assembled. In this chapter, you learn how to employ the time-based MDX functions to calculate some of the more frequently requested of these metrics.

Understanding the Time Dimension

Analysis Services has no inherent awareness of the concept of time. Although at first glance this may seem like a shortcoming of the tool, it actually affords you the flexibility to define your time dimension in a way that reflects how time is managed in your specific organization.

At the heart of the time dimension is one or more user-hierarchies referred to as calendars. Calendars allow you to drill down in time from higher levels of granularity, such as years, into lower levels of granularity, such as quarters, months, and days. Figure 9-1 illustrates one such calendar hierarchy based on the standard calendar we employ in everyday life.

When employed against calendar hierarchies, the time-based MDX functions give the appearance of time awareness. However, most time-based functions are simply exploiting the basic structure of the hierarchy to return the set or member required. In fact, SQL Server Books Online goes so far as to provide the navigational equivalents of each of the time-based functions. If you require slightly different functionality, you can use the navigational functions to implement it yourself.

Figure 9-1 A user-hierarchy based on the standard calendar

The reliance on the calendar hierarchies for time-based functionality imposes two critical constraints on the attributes of the time dimension. First, the members of the attributes comprising the calendar hierarchies must be ordered in time-based sequence from the past to the present because many time-based functions assume this order. Second, complete sets of members for each attribute should be provided because missing members throw off position-based navigation.

Each of these issues is addressed through cube and ETL-layer design. As an MDX developer, you may not have the responsibility or the access required to ensure that these are addressed in a manner appropriate to your needs. However, if you intend to successfully make use of the time-based functions, you must make sure those responsible for assembling the time dimension are aware of these issues.

Determining the Current Value

A very common request is to return the current value of a metric. Although determining the current value is a seemingly simple request, it can be quite challenging.

First, you need to determine the granularity of the request. We often think of time as continuous, but in Analysis Services time is recorded as discrete members representing ranges of time. Between attributes, these members overlap so that the current date member of one attribute is associated with the current month member of another and the current quarter and year members of still others. Each of these represents quite different ranges of time, but each represents the current time.

Once you know the grain, the next challenge is to determine which member represents the current time. A key characteristic of any data warehouse is latency. The time it takes for changes to data in source systems to be reflected in the data warehouse varies from implementation to implementation, but some degree of latency is always present. Because of this, the data warehouse is only current as of some point in the past. Knowing this simply shifts the challenge from identifying the member associated with the current time to identifying the member associated with the time at which the data is current.

One technique for identifying the time at which the data is current is to employ the VBA time functions Date, Time, or Now to retrieve the current time, and then use the VBA date math functions DateAdd or DateDiff to adjust the time for latency. You can then use the adjusted value or parts of it extracted by using the VBA DatePart function to locate the current time member.

Although effective, this technique requires certainty in the amount of latency in the data. Try as you might, you may not be able to always accurately reflect this in the calculation. Considering the potential complexity of the expression logic as well, other alternatives should be explored.

A preferred alternative is to incorporate a property or attribute within the time dimension identifying a member at an appropriately low level of granularity as current. Relationships between attributes can then be employed to identify current time members at higher levels of granularity. The particulars of this design-time solution to the problem of identifying the current time member vary with the circumstances of your data warehouse, but the approach allows the data warehouse to tell you how up to date it is rather than you telling it how up to date it should be.

Save to your account