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

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.

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).

	-- 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.

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.

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

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

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().