Working with Time in Microsoft SQL Server 2008 MDX

  • 2/25/2009
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

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.