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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 13, 2005

Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube - Page 3

By William Pearson

Hands-On Procedure

Basic Approach: Adding a Relative Time Calculated Member to an Analysis Services Cube

We will begin our examination of relative time periods in Analysis Services with a "periods to date" scenario, one of several relative time structures we have cited as an example in the introduction. The PeriodsToDate() function, according to the Analysis Services Books Online, "returns a set of periods (members) from a specified level starting with the first period and ending with a specified member." Within the scope of a level that is specified within the function, the function returns a set of periods that exist on the same level as the member that is also specified in the expression. In other words, it returns the number of periods within the specified level, up to and including the specified member.

The PeriodsToDate() function allows us to meet very common business needs, including the calculation of a year-to-date total. The calculation of this total requires accumulation over a range of members of the Time dimension. This set of Time members is easily assembled using the PeriodsToDate() function, although other, less direct approaches exist to meet this requirement.

To set the scene for our practice session, let's say that a group of information consumers in the Finance department at a client, the FoodMart organization, has encountered a need wherein they are seeking our assistance. The company has embarked on a conversion from an existing enterprise BI system to the integrated Microsoft BI solution, a move that is expected to trim hundreds of thousands of dollars from the annual IT budget. Moreover, moving to the Analysis Services / Reporting Services combination will offer the added benefit of allowing the organization to defer earlier plans to offshore approximately ten percent of their development staff to accommodate budget considerations. FoodMart has evaluated Analysis Services, and has engaged us for the conversion, based upon our experience with the both Analysis Services and with the system that it is replacing.

The consumers have noted that the previous application provided "prefabricated" relative time structures that do not automatically appear in the Warehouse cube, their first Analysis Services development effort. Their requirement for a relative time aggregation is straightforward: they wish to be able to generate year-to-date totals for Warehouse Sales within the Cube Browser, and ultimately within the report environment, (they have chosen Reporting Services to replace the existing high-cost system they currently use).

Let's jump right into creating a calculated member in Analysis Services to provide a Year-to-Date value for the designated measure, Warehouse Sales, discussing the syntax we employ along the way.

1.  Open Analysis Manager.

2.  Expand the Analysis Servers folder in the management console.

3.  Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder inside the FoodMart 2000 database.

6.  Right-click the Warehouse cube

7.  Select Edit ... from the context menu that appears, as depicted in Illustration 3.

Illustration 3: Select Edit from the Context Menu

The Cube Editor opens.

8.  Right-click the Calculated Members folder within Cube Editor.

9.  Select New Calculated Member ... from the context menu that appears, as shown in Illustration 4.

Illustration 4: Select New Calculated Member from the Context Menu

The Calculated Member Builder opens.

10.  Type the following into the Member name box:

YTD Warehouse Sales

While the name of the calculated member can obviously be anything that is useful in the environment in which we are creating it, we need to make the name intuitive to the users. We used the term "YTD" here simply because that was the name employed in the previous application.

11.  Type the following MDX into the Value Expression section of the Calculated Member Builder:

Sum(PeriodsToDate([Time].[Year]),[Measures].[Warehouse Sales])

Let's examine the syntax in the MDX PeriodsToDate() function, which we employ in the Value expression section of the Calculated Member Builder above, together with, and within the context of, the Sum statement, to get an understanding of how it helps us to aggregate a measure over time.

The PeriodsToDate() function consists of the following:

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

PeriodsToDate() returns a set of periods, which are members within a hierarchical level in the Time dimension, beginning with the first period within that level and ending with the member that we specify. Simple examples of its use appear in Table 1.


Returned Set


The days from the beginning of Quarter 4 to December 12, 1997.

PeriodsToDate([Time].[Year], [Time].[Month].[March]) 

January, February, March

    - level specified, but no member specified

The members from the beginning of the year that is the ancestor of Time.CurrentMember, through Time.CurrentMember.

PeriodsToDate() - no level or member specified

The set of members from the beginning of the level containing the period of Time.CurrentMember to Time.CurrentMember. All the returned members are at the same level as Time.CurrentMember.

Table 1: Basic Examples of PeriodsToDate() Expressions and the Respective Sets Returned

The <<Level>> supplies the scope of the function (in our example it is [Time].[Year], or the Year level of the Time dimension). Because the function we constructed for our calculated member above has a level specified, but no member (like the third expression from the top in Table 1 above), <<Member>> becomes [Time].CurrentMember (an unspecified member assumes the same dimension as that of <<Level>>.)

MS SQL Archives

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