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 Jul 11, 2005

Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II - Page 11

By William Pearson

At this point, we will create calculated members for the other relative time aggregations that the information consumers have requested in their business needs specifications. We will follow the steps we took in creating the Year-to-Date Total for each of the additional relative time members we need, basing our construction of the calculated members on the settings presented in the steps and tables that follow.

22.  Right-click the Calculated Members folder in the tree, once again.

23.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

24.  Create the Quarter-to-Date Total calculated member by completing the fields of the Calculated Member Builder, with the settings presented in Table 3 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Quarter-to-Date Total

Value Expression

SUM(PeriodsToDate([Time].[Quarter]),([Relative Time].[Relative Time].[Current]))


Table 4: Calculated Member Builder Settings: Month-to-Date Total Calculated Member

The Calculated Member Builder appears, with our input, as shown in Illustration 59.


Illustration 59: The Calculated Member Builder: Month-to-Date Total (Compressed View)

29.  Click OK.

The Calculated Member Builder closes, and the new Month-to-Date Total calculated member joins the other members of the Calculated Members folder. Let's create the three "comparative" calculated members requested by the information consumers next.

30.  Right-click the Calculated Members folder in the tree, once again.

31.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

32.  Create the Prior Year Total calculated member by completing the fields of the Calculated Member Builder to with the settings presented in Table 5 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Prior Year Total

Value Expression

([Measures].CurrentMember, ParallelPeriod ([Time].[Year], 1, [Time].CurrentMember), ([Relative Time].[Relative Time].[Current]))


Table 5: Calculated Member Builder Settings: Prior Year Total Calculated Member

The Calculated Member Builder appears, with our input, as depicted in Illustration 60.


Illustration 60: The Calculated Member Builder: Prior Year Total (Compressed View)

33.  Click OK.

The Calculated Member Builder closes, and the new Prior Year Total calculated member joins the other calculated members in the tree, as before.

NOTE: For more information on the ParallelPeriod() function, see my article MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions in the MDX Essentials series at Database Journal.

34.  Right-click the Calculated Members folder in the tree, once again.

35.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

36.  Create the Prior Quarter Total calculated member by completing the fields of the Calculated Member Builder, with the settings presented in Table 6 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Prior Quarter Total

Value Expression

([Measures].CurrentMember, ParallelPeriod ([Time].[Quarter], 1, [Time].CurrentMember), ([Relative Time].[Relative Time].[Current]))


Table 6: Calculated Member Builder Settings: Prior Quarter Total Calculated Member

The Calculated Member Builder appears, with our input, as shown in Illustration 61.


Illustration 61: The Calculated Member Builder: Prior Quarter Total (Compressed View)

37.  Click OK.

The Calculated Member Builder closes, and we see Prior Quarter Total appear in the Calculated Members folder within the tree. Let's create the final calculated member, Prior Month Total, to support the information consumers' expressed business requirements via the Relative Time dimension.

38.  Right-click the Calculated Members folder in the tree, as before.

39.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

40.  Create the Prior Month Total calculated member by completing the fields of the Calculated Member Builder, with the settings presented in Table 7 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Prior Month Total

Value Expression

([Measures].CurrentMember, ParallelPeriod ([Time].[Month], 1, [Time].CurrentMember), ([Relative Time].[Relative Time].[Current]))


Table 7: Calculated Member Builder Settings: Prior Month Total Calculated Member

The Calculated Member Builder appears, with our input, as depicted in Illustration 62.


Illustration 62: The Calculated Member Builder: Prior Month Total (Compressed View)

41.  Click OK.

The Calculated Member Builder closes. We see the results of our handiwork within the complete members within the Calculated Members folder that appears in Illustration 63.


Illustration 63: The Relative Time Calculated Members Set

We have created the members of the Relative Time dimension required to support the capabilities requested by the client information consumers. We will conclude this exercise with a quick verification of the operation of these structures in the section that follows. A subsequent article in my MSSQL Server Reporting Services series will focus upon the use of the structures within an enterprise reporting environment.



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