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

Monday Aug 11th 2003 by William Pearson
Share:

Join author Bill Pearson in the second of three articles surrounding the time series functions, whose purpose is to help us to meet the need for analyzing data in a time-based context. In this lesson, we will overview the OpeningPeriod() and ClosingPeriod() functions and explore the ways that they can help us to meet common business needs.

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.

Introduction

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:

  • 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;

The OpeningPeriod() Function

The OpeningPeriod() function, according to the Analysis Services Books Online, "returns the first sibling among the descendants of a specified level, optionally at a specified member." In other words, the function returns the first member that shares the current member's parent at the specified level.

When a level is specified, the dimension containing that level is used. When no level is specified, the Time dimension is used, by default. Further, if no level is specified, the level below that of the specified member is used. If neither level nor member is specified in the function, the default is Time.CurrentMember (a concept we have discussed earlier in our series), with the Level value thus being the parent level of Time.CurrentMember.

Discussion

The OpeningPeriod() function allows us to meet numerous common business needs, including (and especially) those that require that we return a balance from the first member of a given level. That level might be, for example, Year, with the first member being January. A "relative" calculation can thus be driven for a single month in the Year level of the Time dimension, without hardcoding. While other, less direct approaches exist to meet this requirement, OpeningPeriod() is an easy route to the "beginning balance" for an account, and this is one of its most common uses.

Syntax

Syntactically, the referenced level and / or member is / are placed within the parentheses to the right of OpeningPeriod, as shown in the following illustration:

OpeningPeriod([<<Level>>[, <<Member>>]])

The function returns the first member within the <<Level>>, among the descendants of <<Member>>. The following simple example expression would return January (identified simply as "1" in the Warehouse sample cube) of year 1998 or, in other words, the first member (Month 1) of the Quarter level in 1998.

OpeningPeriod ([Time].[Quarter], [1998] )

Practice

Let's get hands-on portion exposure to the OpeningPeriod() function by creating a calculated member to reinforce our understanding of the basics we have covered so far, and by using the OpeningPeriod() function in a manner that assists our ends. The MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) will, as usual, be our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

1.      Start the MDX Sample Application.

2.      Clear the top area (the Query pane) of any queries or remnants that might appear.

3.      Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.      Select the Warehouse cube in the Cube drop-down list box.

We will begin with an illustration that creates a calculated member and uses the OpeningPeriod() function in a manner similar to the example we cited earlier: our query will focus on Warehouse Cost, a value that is stored on a monthly basis within the FoodMart cube.

Let's compose and run a simple query to gain an understanding of our data. We will ascertain the values for monthly Warehouse Cost balances first (keep in mind that they are just that, balances, and that the value changes only once a month from the perspective of our cube).

5.      Type the following query into the Query pane:

	-- MDX10-1:  Tutorial Query
	No. 1
	SELECT
	{[Measures].[Warehouse Cost]} ON COLUMNS,    
	[Time].[Month].Members ON ROWS
	FROM Warehouse

Analysis Services fills the Results pane, presenting the dataset depicted in Illustration 1.


Illustration 1: Result Dataset - Monthly Warehouse Cost, Years 1997 and 1998

We see the total organization Warehouse Cost returned for each month, as we have requested. Keep these amounts in mind as we progress.

We become aware of a business requirement to present beginning monthly balances for the Warehouse Cost accounts, for each quarter of 1997 and 1998. We can see, from the month values shown above, what these totals should be. Let's use the OpeningPeriod () function to meet the business requirement, with the above results available for an easy means of verifying our results.

6.      Type the following query into the Query pane:

	-- MDX10-2:  Tutorial Query No. 2
	WITH 
	   MEMBER [Measures].[Beg Bal] AS
	  '([Measures].[Warehouse Cost], 
	    OpeningPeriod ( [Time].[Month], [Time].CurrentMember))'
	SELECT    
	   {[Measures].[Beg Bal]} ON COLUMNS,    
	     [Time].[Quarter].Members ON ROWS
	FROM Warehouse

7.      Click the Run button on the toolbar atop the Sample Application, to execute the query.

As soon as the specified cells in the Results pane are filled by Analysis Services, we see the result dataset depicted in Illustration 2.


Illustration 2: The Query Result Dataset

The query delivers the results that we expected, based upon our discussion of the syntax of the OpeningPeriod() function: We see that the value returned for each quarter coincides with the value of the first month, which we have defined as the Opening Period ("Beg Bal") in our calculated member via WITH clause of the query above. We see that the value of the first member of the month level, belonging to each quarter (which we happen to be displaying in this example) is returned.

We can verify correctness by looking again at the results we obtained in our first, exploratory query, depicted in Illustration 1. For example, the Opening Period for Q1 in each of the two years selected is 9,817.79 and 13,189.54, corresponding to each respective month 1 in Illustration 1.

The OpeningPeriod () function was designed to provide this highly useful result for business users. Coupled with Time.CurrentMember in the manner shown in our second query (the <<Member>> part of the function's argument), the logic is sound from any point in the Time dimension we happen to occupy.

Note: For a discussion of the .CurrentMember function, see my earlier articles on the members' functions within this series, as well as other series' articles I have published within the DatabaseJournal library.

If it is desirable to save this or subsequent queries, we can do so by selecting File -> Save As, and give the file a meaningful name and location.

Now let's take a look at the Newtonian "equal and opposite" of the OpeningPeriod() function, ClosingPeriod().

The ClosingPeriod() Function

The ClosingPeriod() function, according to the Analysis Services Books Online, "returns the last sibling among the descendants of a member at a specified level." In other words, it returns the last member that shares the current member's parent at the specified level.

Identically to the case of the OpeningPeriod () function, when a level is specified, the dimension containing that level is used. When no level is specified, the Time dimension is used. Further, if no level is specified, the level below that of the specified member is used. If neither level nor member is specified in the function, the default is Time.CurrentMember (a concept we have discussed earlier in our series), with the Level value thus being the parent level of Time.CurrentMember.

Discussion

The ClosingPeriod() function allows us to meet similar business needs to those for which the OpeningPeriod() function is so well adapted, including (and especially) those that require that we return a balance from the last member of a given level. To use a similar example to the one we used for OpeningPeriod(), that level might be Year, with the last member being December. A relative calculation can thus be driven for the single last month in the Year level of the Time dimension. While other, less direct approaches exist to meet this requirement, ClosingPeriod() provides an easy route to the "ending period balance" for an account; indeed, this is its most common use.

Syntax

Syntactically, the referenced member is placed within the parentheses to the right of ClosingPeriod, as shown in the following illustration:

ClosingPeriod([<<Level>>[, <<Member>>]])

The function returns the last member within the <<Level>>, among the descendants of <<Member>>. The following simple example expression would return December (identified simply as "12" in the Warehouse sample cube) of year 1998 - or, in other words, the last member (Month 12) of the Quarter level in 1998.

ClosingPeriod ([Time].[Quarter], [1998] )

Practice

Let's once again reinforce our understanding of the basics by creating a calculated member within which we will use the ClosingPeriod() function in meeting a business need.

We will begin with an illustration that creates a calculated member and uses the ClosingPeriod() function in a scenario quite similar to that in our OpeningPeriod() function. This will afford us an opportunity to compare closely the results to see the precise differences in the two functions. Our query will again focus upon Warehouse Cost, and, except for the use of the new function, be identical in every respect to our last example.

We can revisit the results obtained in our first query, and displayed in Illustration 1, to see the total organization Warehouse Cost returned for each month. Just as we did in our practice exercise for the OpeningPeriod () function, we can use these values as an easy means of verifying our results with the ClosingPeriod() function below.

Let's say, for the purposes of this exercise, that we are informed of a business requirement to present closing balances on the Warehouse Cost accounts for each quarter of 1997 and 1998. We can see, from the month values shown in Illustration 1, what these totals should be. We will use the ClosingPeriod () function to meet the stated requirement, with the month values available, as before, to help us verify our results.

8.      Type the following query into the Query pane:

-- MDX10-3:  Tutorial Query No. 3
WITH 
   MEMBER [Measures].[Closing Bal] AS
  '([Measures].[Warehouse Cost], 
    ClosingPeriod ( [Time].[Month], [Time].CurrentMember))'
SELECT    
   {[Measures].[Closing Bal]} ON COLUMNS,    
     [Time].[Quarter].Members ON ROWS
FROM Warehouse

9.      Execute the query by clicking the Run button on the toolbar atop the Sample Application.

As soon Analysis Services fills the specified cells in the Results pane, we see the result dataset shown in Illustration 3.


Illustration 3: The Query Result Dataset

The query delivers the results we anticipate from our discussion of the syntax of the ClosingPeriod() function: The value returned for each quarter coincides with the value of the last month, which we have defined as the Closing Period ("Closing Bal") in our calculated member, via the WITH clause in the query above. We see that the value of the last member of the month level, belonging to each quarter (which we again have chosen to display in the example) is returned.

We can verify correctness by looking again at the results we obtained in our first, exploratory query, depicted in Illustration 1. For example, the Closing Period for Q1 in each of the two years selected is 6285.76 and 12,128.04, corresponding to each respective month 12 in Illustration 1.

The ClosingPeriod () function thus lives up to its design to provide a means of fulfilling a common need of business users. Coupling the function with Time.CurrentMember in the argument, as shown in our query, the logic is applicable from any point in the Time dimension we happen to occupy, just as it was in the OpeningPeriod() function with which we initially practiced .

10.      Save the query as desired by selecting File -> Save As, and giving the file a meaningful name and location.

In conclusion, we can easily see the utility of the OpeningPeriod() and ClosingPeriod () functions in providing us a relative, direct route to selecting the first or last member, respectively, of a level that we target in the argument of the function.

Next in Our Series ...

In this lesson, we delved further into the time series functions group, examining two functions designed especially to focus on opening and closing periods for a given balance. After discussing the common business need to analyze data over time, and, more specifically, within a scope of the opening or closing periods of a given time window, we overviewed OpeningPeriod() and ClosingPeriod () functions in detail. In the case of each, we illustrated the syntax that is appropriate for its effective use, and then tested our understanding of how to leverage the function by undertaking a relevant practice exercise.

As has been the case with numerous functions in past lessons, we revisited the construction of a calculated member, building a calculated measure within our practice queries as a vehicle for illustrating the operation of the OpeningPeriod() and ClosingPeriod () functions. Finally, we discussed the results that each practice example generated, comparing the results of the ClosingPeriod () function with the OpeningPeriod() function to emphasize their similarity in construction and operation.

In our next lesson, we will conclude our three-article focus on the time series functions group with an examination of the LastPeriods() and ParallelPeriods() functions.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved