MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
August 11, 2003
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 needs.
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.
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 exercise.
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, with: