Working with Time in Microsoft SQL Server 2008 MDX

2/25/2009

Contents

Combining Time-Based Metrics

Throughout this chapter, you have explored the various time-based functions and how they can be used to enhance business analysis and solve business problems. Although each of these functions is valuable on its own, they are often used in combination to provide even greater insight and clarity into the analysis of business data. These may seem like very challenging metrics to assemble, but in reality they are no more complex than most other metrics calculated throughout this book. The trick is to remember tuple and expression basics.

Calculate year-to-date and prior period year-to-date sales

Open the MDX Query Editor to the MDX Step-by-Step database.

Enter the following query to retrieve reseller sales for the months of calendar year 2003:

SELECT
    {
        ([Measures].[Reseller Sales Amount])
        } ON COLUMNS,
    {
        Descendants(
            [Date].[Calendar].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
        } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

The query returns reseller sales by month for calendar year 2003. Using the PeriodsToDate function, you can calculate year-to-date sales just like before.

Alter the query to calculate a year-to-date sales:

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
    Aggregate(
       PeriodsToDate(
           [Date].[Calendar].[Calendar Year],
           [Date].[Calendar].CurrentMember
            ),
       ([Measures].[Reseller Sales Amount])
       )
    ,FORMAT="Currency"
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Year to Date Reseller Sales])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
        } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

Using the Year To Date Reseller Sales calculated member in a tuple, you can easily calculate year-to-date sales for the prior period.

Alter the query to calculate the prior period year-to-date sales:

WITH
MEMBER [Measures].[Prior Period Year to Date Reseller Sales] AS
    (
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
            ),
        [Measures].[Year to Date Reseller Sales]
        )
    ,FORMAT="Currency"
MEMBER [Measures].[Year to Date Reseller Sales] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Year],
            [Date].[Calendar].CurrentMember
            ),
        ([Measures].[Reseller Sales Amount])
        )
    ,FORMAT="Currency"
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Year to Date Reseller Sales]),
        ([Measures].[Prior Period Year to Date Reseller Sales])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
        } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

This exercise demonstrates a very simple approach to combining calculated members that use time-based functions. When formulating complex metrics, you can easily lose sight of the basic techniques allowing logic in one calculated member to be leveraged for another. As easily as you combined a period-to-date calculation with a prior period calculation, you could extend this query to include the difference, variance, or percent growth of the current year year-to-date values compared to the prior year year-to-date values or any flavors thereof.

The OpeningPeriod and ClosingPeriod Functions

We would be remiss if we did not mention the OpeningPeriod and ClosingPeriod functions. The introduction of expanded support for semi-additive measures in the 2005 release of Analysis Services has diminished the role of these functions, which return the first and last members of a period:

OpeningPeriod( [Level [, Member]] )
ClosingPeriod( [Level [, Member]] )

The OpeningPeriod and ClosingPeriod functions return the first or last member, respectively, of the descendants from a given level and a specified member. If no level is specified, Analysis Services assumes the topmost level of the time hierarchy. If no member is specified, Analysis Services assumes the current time member. As with the other time-based functions, you are encouraged to supply both arguments to ensure clarity.

As previously mentioned, both the OpeningPeriod and ClosingPeriod functions have seen their use diminished with recent releases of Analysis Services. Historically, these functions have been used to calculate values now returned by the FirstChild, FirstNonEmpty, LastChild, and LastNonEmpty aggregate functions. These aggregate functions are frequently employed with finance facts, exchange rates, and other snapshot facts to identify period starting and ending values.

For example, the end-of-day exchange rate employs the LastNonEmpty aggregate function to provide access to the last available value within a given period. But what if you needed to determine the end-of-day exchange rate at the start of a period? The following query illustrates the use of the OpeningPeriod function to calculate this value:

WITH
MEMBER [Measures].[First Child Rate] AS
    (
        OpeningPeriod(
            [Date].[Calendar].[Date],
            [Date].[Calendar].CurrentMember
            ),
             [Measures].[End of Day Rate]
            )
       ,FORMAT="Standard"
SELECT
    {
        ([Measures].[First Child Rate]),
        ([Measures].[End of Day Rate])
        } ON COLUMNS,
    {[Date].[Calendar].Members} ON ROWS
FROM [Step-by-Step]
WHERE ([Destination Currency].[Destination Currency].[Euro])

This query provides both the first and last available end-of-day exchange rates for the specified period. The former is provided through the MDX OpeningPeriod function; the latter is provided through a cube aggregate function. You could further extend the query to identify the difference or variance in exchange rates across the opening and closing of the period.

Save to your account