Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 8, 2003

MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions - Page 2

By William Pearson

The LastPeriods() Function

The LastPeriods() function, according to the Analysis Services Books Online, "returns a set of members prior to and including a specified member." Almost as simple as it sounds, the function returns (in its typical use within the context of a time dimension) the set of periods beginning with the period that follows (or lags) the specified member by a specified index value, less one, up to and including the specified member. (If the member is not specified in the function, Time.CurrentMember is defaulted.) Indeed, LastPeriods() resembles PeriodsToDate()(explored in MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions), which returns all of the members back to the beginning of a specified period, although the LastPeriods() function returns the number of members that is specified by an index value.

The index (minus one) that is specified tells the function how far to "look back" for purposes of the returned dataset. If the index is positive, the function returns the set of members whose range is headed up by the member "lagging" one less than the index value from the specified member, and that ends with the member itself. If the index is negative, then LastPeriods() returns the set of members that begins with the specified member itself, and ends with the member leading (or "ahead in time") by the negative index value, minus one, from the specified member. Finally, an empty set is returned if the index value is zero.

Discussion

The LastPeriods() function allows us to meet numerous common business needs, including (and especially) those that require that we return (individually or for accumulation) values from, for example, "several periods back," up to, and including, the specified period. That specified member might be, for example, a specific quarter for which we would like monthly values over the range of, say, two quarters back through the current quarter. A calculation can be driven for a several month's activities, as another example, whose range is determined by the beginning and ending points that result from the index we provide.

As we discussed in our last lesson, balances are maintained up to the current point in time / held at various points in time for many accounts whose activity we might wish to analyze, such as Sales, Inventory, Purchases, and various other accounts that contain values besides dollar amounts, such as shares outstanding, headcount, various quantities, and so forth, in our systems. The values most likely to be of interest in typical uses of the LastPeriods() function would most likely relate to deltas over the given periods. Ss an illustration, the total sales over each of a given group of months (itself the delta of "ending total sales" between the point in time that we establish as the specified member within the function, and the total sales value that existed, in this example, at the earlier point in the year to which we are looking back, and which is index - 1 months back). LastPeriods() is excellent for the derivation of "total activity for the last (whatever number) periods back," and, thus for a "to date" cumulative total of sorts. Utilitarian beauty appears, again, in the use of the function with .currentmember, etc. to gain a context sensitive calculation that can flex automatically as time marches on. Running averages and other derivatives of the accumulated totals are obvious easy byproducts, as well, given formulation within an expression that includes LastPeriods().

Common examples of scenarios where LastPeriods() might come in handy include monthly totals / balances, over a few quarters, for the inventories of a group of products we manufacture and sell, for perhaps comparison to the same amounts for another group of our products, as an aid in determining whether to discontinue production of a given item or items to allow us to compose a more rapidly moving product mix. Another illustration might lie in the need to analyze activity, particularly over several summer months, in total monthly quantity on hand, for a part that we stock in a Repair Parts account for use in maintaining our air conditioning plant, to ascertain, perhaps, increased demands for parts by an aging system.

MDX provides an excellent means for accumulating these time-ranged activity values over specified time frames. The LastPeriods() function affords us a ready means to navigate to the first member of the range for which one endpoint (the specified member) is defined and for which the opposite end of the range is specified by the index.

Let's look at an illustration to further clarify the operation of LastPeriods().

Syntax

Syntactically, the index and the specified member are placed within the parentheses to the right of LastPeriods(), as shown in the following illustration:

	LastPeriods(<<Index>>[, <<Member>>])

The function returns the set of members prior to (by <<Index>> - 1 "member-places"), and including, <<Member>>. The following simple example expression:

	LastPeriods (11, [Time].[1998].[Q3].[8])

would return the following set of month-members:

	{[Time].[1997].[Q4].[10],
	  [Time].[1997].[Q4].[11],
	  [Time].[1997].[Q4].[12]
	  [Time].[1998].[Q1].[1]
	  [Time].[1998].[Q1].[2]
	  [Time].[1998].[Q1].[3]
	  [Time].[1998].[Q2].[4]
	  [Time].[1998].[Q2].[5]
	  [Time].[1998].[Q2].[6]
	  [Time].[1998].[Q3].[7]
	  [Time].[1998].[Q3].[8]}
  

Practice

Let's reinforce our understanding of the basics we have covered so far, by using the LastPeriods() function in a manner that illustrates its operation. 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, again, 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 compose a simple query to gain an understanding of our data; our query will focus on the Warehouse Cost measure, a value that is captured monthly within the FoodMart organization and which is stored in the Warehouse cube.

5.  Type the following query into the Query pane:

-- MDX11-1:  Tutorial
Query No. 1
SELECT    
  
{[Measures].[Warehouse Cost]} ON COLUMNS,    
    LastPeriods(11,
[Time].[1998].[Q3].[8]) ON ROWS
FROM Warehouse

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

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


Illustration 1: Result Dataset - LastPeriods() Function

We see the total organization Warehouse Cost returned for each of the last eleven periods (our index of 11 in the query), within the range of periods that ends at August (Quarter 3), 1998, and begins ten (index of eleven minus one) months back at October (Quarter 4), 1997. As we learned in the Syntax section above, the result dataset is equivalent to

	{[Time].[1997].[Q4].[10],
	  [Time].[1997].[Q4].[11],
	  [Time].[1997].[Q4].[12]
	  [Time].[1998].[Q1].[1]
	  [Time].[1998].[Q1].[2]
	  [Time].[1998].[Q1].[3]
	  [Time].[1998].[Q2].[4]
	  [Time].[1998].[Q2].[5]
	  [Time].[1998].[Q2].[6]
	  [Time].[1998].[Q3].[7]
	  [Time].[1998].[Q3].[8]}
  

We can easily prove that the two are equivalent by taking the following steps:

7.  Type the following query into the Query pane:

-- MDX11-1 Proof:  Proof of Query No. 1
SELECT
	{[Measures].[Warehouse Cost]} ON COLUMNS,    
    	{[Time].[1997].[Q4].[10],
			[Time].[1997].[Q4].[11],
	    [Time].[1997].[Q4].[12],
	    [Time].[1998].[Q1].[1],
	    [Time].[1998].[Q1].[2],
	    [Time].[1998].[Q1].[3],
	    [Time].[1998].[Q2].[4],
	    [Time].[1998].[Q2].[5],
	    [Time].[1998].[Q2].[6],
	    [Time].[1998].[Q3].[7],
	    [Time].[1998].[Q3].[8]}
                    	ON ROWS
FROM Warehouse

8.  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 proof query delivers the results that we expected, based upon our discussion of the syntax of the LastPeriods() function: We see that the results are identical to those we obtained in the previous query, within which we used the more compact LastPeriods() function.

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 our remaining time series function, ParallelPeriod().



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date