Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube - Page 3
June 13, 2005
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.
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.
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:
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() 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.
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>>.)