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 Jun 2, 2003

MDX Essentials: MDX Member Functions: "Relative" Member Functions - Page 2

By William Pearson

Calculated Members

Calculated members, in short, allow us to define new members, based upon dimensions or measures that exist within the cube in which we create the calculated member. They are members whose values depend on an expression rather than the value of a cell within a cube. The potential uses of calculated members are limited only by the imagination and experience of the developer. An example might be the somewhat common business concept of a variance measure. We could create this measure using, say, an actual sales measure and a budgeted sales measure that exist in a given cube.

Discussion:

To create a calculated measure, at least within the context of our simple overview, we will use the WITH clause, the syntax for which might resemble the following:

WITH MEMBER dimension.name AS 'Expression'

Dimension represents the dimension into which we are creating the new member. Name is my shorthand for the name of the member we are creating, and expression is the expression from which it is created.

Syntax

Let's look at a simple example to gain a feel for the syntax.

	WITH 
	   MEMBER [Measures].[Warehouse Margin] AS
	   '[Measures].[Warehouse Sales]-[Measures].[Warehouse Cost]'
	
	SELECT
	
	   {([Time].[Quarter].Members)} ON COLUMNS,
	   {([Warehouse].[Country].USA)} ON ROWS
	
	FROM Warehouse
	
	WHERE ([Measures].[Warehouse Margin])

The result dataset returned would appear as shown in Illustration 1 below:


Illustration 1: Example Result Dataset Using WITH to Create a Calculated Member

As we can see above, the WITH section contains the definition of the calculated member Warehouse Margin. The newly created Warehouse Margin is a member of the Measures dimension, and is composed of the total of Warehouse Sales less Warehouse Cost, intersecting all members of the remaining dimensions in the cube, but displaying the combined intersects with the USA warehouses for purposes of our query. The returned dataset, therefore, represents the Warehouse Profit (which we called Warehouse Margin in our query) for the USA warehouses, by quarter (for the years 1997 and 1998, as stored in the Warehouse sample cube).

The SELECT keyword comes at the end of the WITH section, where it begins the specification of the axes / cells to which we have become accustomed in previous lessons. The SELECT statement allows us to place the members we wish to display upon the respective axes of the display, including enumeration of the quarters through our use of the .Members function. The WHERE clause, as we know from previous lessons, acts as the slicer dimension, which, in the immediate example, allows us to hold the new Warehouse Margin member constant for purposes of our query results.

We will explore many aspects of calculated members as we proceed through the series. This lesson will use them in the practice examples for purposes of illustrating the use of the "relative" functions that we have targeted for the article; in this way, we will continue to stay on track with our examination of the member functions while becoming comfortable with the rudiments of calculated member usage and behavior. Calculated members will be with us for a long time to come, and will provide many uses and topics of discussion.



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