MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions

About the Series …

This is the tenth article of the
series, MDX Essentials. The primary focus of this series is an
introduction to MDX. The series is designed to provide hands-on application of
the fundamentals of the Multidimensional Expressions (MDX) language,
with each tutorial progressively adding features designed to meet specific real-world

For more information about the series in general, as well as
the software and systems requirements needed for getting the most out of the
lessons included, please see the first article, MDX at
First Glance: Introduction to MDX Essentials

Note: Service Pack 3 updates are assumed for MSSQL
Server 2000
, MSSQL Server 2000 Analysis Services, and the related Books
and Samples.

What We
Accomplished in our Last Article

In the ninth article of the series, MDX
Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
we introduced the time series functions group, a specialized group of
functions that MDX provides to perform analysis within the context of time.
After discussing the common business need to analyze data over time, we
overviewed the PeriodsToDate() function in detail, illustrated the
syntax for its effective use, and then tested our understanding of how to
leverage this important time series function by undertaking a relevant practice

As part of our use of the PeriodsToDate() function,
we revisited the construction of a calculated member, using a calculated
measure as a vehicle for illustration of the operation of the PeriodsToDate()
function. We introduced the Sum statement as a part of building the
calculated member, and discussed its constituent parts, as well, in a preview
of a later article to come. Finally, we discussed the specialized "shortcut"
functions that are based upon PeriodsToDate(), including the YTD(),
QTD(), MTD(), and WTD() functions.

In this lesson, we will introduce two additional time series
functions, OpeningPeriod() and ClosingPeriod(), and explore the
use of these specialized functions to meet further business needs to analyze
data within the context of time.


We reflected, in our
last lesson, upon the fact that most of us become acquainted, early in our
careers, with the need to perform data analysis we with reference to time.
Examples abound, including the analysis of growth (for instance, in revenues or
expenses) over periods in time (say, between the years 1997 and 1998). Another
common example within accounting and finance circles is the requirement to
present year-to-date totals, averages and numerous other accumulations. The
common factor in these analytical needs is time, and the primary mission
of the time functions is to provide us a way to easily navigate within
the time dimension.

The time series
functions are specifically designed to support time-based analysis. They are
largely applied to dimensions of the "time" type, but, as we noted in
our last lesson, can be applied to other dimensions as well. The general idea
is, again, to allow us to navigate within the time dimension to such places as "this
month last year," "the first month in the quarter," or "our
current point in the year, last year."

For many accounts, such
as Fixed Assets, Inventory and Purchases, as well as with other non-dollar quantities
that we might maintain in our systems, such as headcount, item or event
quantities, and so forth, balances are maintained up to the current point in
time / held at various points in time. A common example is an end of the month
balance for a department’s employee population, or a total quantity on hand for
a part, say, which we stock in a Repair Parts account for use in maintaining
our manufacturing equipment. Another common instance might be a balance we wish
to monitor in our inventory of goods held for sale, to allow us to ascertain
potentially declining turnover, which might indicate declining market share and
obsolescence concerns.

These measures are
often analyzed in terms of "opening" and ‘closing" balances for various
reasons, particularly at the close of a financial year, but under other time
parameters as well. MDX provides an excellent means for referring to opening
and closing periods that lie within the dimensional structure of our cubes.
The OpeningPeriod() function affords us a ready means to navigate to the
first member of the day level (as an example) from our position at any time
member / level coordinate. The ClosingPeriod() function provides
similar access to, as an illustration, the last month-level member and the
balance assigned to that member within a given year. In the many accounting
and financial systems that maintain an opening and closing period outside the
standard twelve operating months, these functions can be used to great
advantage to identify the period(s) upon which we wish to focus, by virtue of
that period (or periods) relative place within a level of a properly
architected cube structure.

In this lesson we will
introduce the OpeningPeriod() and ClosingPeriod() functions,
addressing some of the general considerations of time functions as a part of
our exploration. Our lesson will include an introduction to these functions,

  • an examination of the syntax surrounding the use of each;
  • an illustrative example, putting each to work in a
    practice exercise;
  • a brief discussion of the MDX results we obtain in each
    practice example;
  • an ongoing review of the use of calculated members,
    primarily as a means of exploring the time functions of this lesson;
William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles