Cube-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 192

NOTE
If a query uses a calculated member that is stored in the cube, after calculating its for-
mula, the calculated value can be stored in the calculation cache. Subsequent queries
will not have to recompute the formula because Analysis Services will retrieve the value
from the cache. (See Chapter 29, “Architecture of Query Execution—Calculating MDX
Expressions,” for more information.)
The CREATE MEMBER statement is intended not only to be used to define calculated
members stored in the cube; it can also be issued from a client application to create a
calculated member in a session. The calculated member is available only to queries issued
from the same user during the same session. These calculated members are session-scope
calculated members.
You can use the DROP MEMBER statement to remove a calculated member from a session.
After the DROP MEMBER statement executes, the calculated member is no longer available:
DROP MEMBER [Warehouse and Sales].Profit
To define a calculated member in the scope of a query, MDX provides the WITH clause. The
WITH clause precedes the SELECT clause in the statement, creating a section for the defini-
tion of calculated members (and other calculations that we will introduce later in this
chapter) in the query:
WITH MEMBER < calculated member > AS < calculated member formula >
SELECT < definition of the resulting multidimensional space >
FROM < source sub_space > WHERE < limiting the results to sub_space >
The WITH clause is very similar to the CREATE MEMBER statement, except it is not a stand-
alone statement, but is part of the SELECT statement. For example, you can create the
Profit calculated member with the following query:
WITH MEMBER Measures.Profit AS [Measures].[Store Sales]-[Measures].[Store Cost]
SELECT Measures.Profit ON COLUMNS FROM [Warehouse and Sales]
Common customer scenarios create a calculated member on the Measure dimension.
However, other scenarios have to create a calculated member on some other dimension.
For example, you can create a calculated member on the Time dimension to aggregate
values for the first and second quarters of a year:
WITH MEMBER [Time].[Time].[Year].[1998].[FirstHalfYear] AS
[Time].[Time].[Quarter].&[Q1]&[1998]+[Time].[Time].[Quarter].&[Q2]&[1998]
SELECT {[Time].[Time].[Year].[1998].[FirstHalfYear]} ON COLUMNS
FROM [Warehouse and Sales]