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
Online and Samples.
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
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
- a brief discussion of the MDX results we obtain in each
- an ongoing review of the use of calculated members,
primarily as a means of exploring the time functions of this lesson;