CREATE MEMBER Statement (MDX)
Creates a calculated member.
Syntax
CREATE [ SESSION ] [HIDDDEN] [ CALCULATED ] MEMBER CURRENTCUBE | Cube_Name.Member_Name
AS MDX_Expression
[,Property_Name = Property_Value, ...n]
......[,SCOPE_ISOLATION = CUBE]
Arguments
- Member_Name
A valid string expression that provides a member name. Specify a fully qualified name to create a member within a dimension other than the Measures dimension. If you do not provide a fully qualified member name, the member will be created in the Measures dimension.
- MDX_Expression
A valid Multidimensional Expressions (MDX) expression.
- Property_Name
A valid string that provides the name of a calculated member property.
- Property_Value
A valid scalar expression that defines the calculated member property's value.
Remarks
The CREATE MEMBER statement defines calculated members that are available throughout the session, and therefore, can be used in multiple queries during the session. For more information, see Creating Session-Scoped Calculated Members (MDX).
You can also define a calculated member for use by a single query. To define a calculated member that is limited to a single query, you use the WITH clause in the SELECT statement. For more information, see Creating Query-Scoped Calculated Members (MDX).
Property_Name can refer to either standard or optional calculated member properties. Standard member properties are listed later in this topic. Calculated members created with CREATE MEMBER without a SESSION value have session scope. Additionally, strings inside calculated member definitions are delimited with double quotation marks. This is different from the method defined by OLE DB, which specifies that strings should be delimited by single quotation marks.
Specifying a cube other than the cube that is currently connected causes an error. Therefore, you should use CURRENTCUBE in place of a cube name to denote the current cube.
For more information about member properties that are defined by OLE DB, see the OLE DB documentation.
Standard Properties
Each calculated member has a set of default properties. When a client application is connected to Microsoft SQL Server 2005 Analysis Services (SSAS), the default properties are either supported, or available to be supported, as the administrator chooses.
Additional member properties may be available, depending upon the cube definition. The following properties represent information relevant to the dimension level in the cube.
| Property identifier | Meaning |
|---|---|
SOLVE_ORDER |
Specifies the order in which the calculated member will be solved in cases where a calculated member references one other calculated member (that is, where calculated members intersect each other). |
FORMAT_STRING |
Specifies a Microsoft Office style format string that the client application can use when displaying cell values. |
VISIBLE |
Determines whether the calculated member is visible in a schema rowset. Visible calculated members can be added to a set with the AddCalculatedMembers function. A nonzero value indicates that the calculated member is visible. The default value for this property is Visible. Calculated members that are not visible (where this value is set to zero) are generally used as intermediate steps in more complex calculated members. These calculated members can also be referred to by other types of members, such as measures. |
NON_EMPTY_BEHAVIOR |
Specifies the measure or set used to determine the behavior of calculated members when resolving empty cells. |
Scope
A calculated member can occur within one of the scopes listed in the following table.
- Query scope
The visibility and lifetime of the calculated member is limited to the query. The calculated member is defined in an individual query. Query scope overrides session scope. For more information, see Creating Query-Scoped Calculated Members (MDX).
- Session scope
The visibility and lifetime of the calculated member is limited to the session in which it is created. (The lifetime is less than the session duration if a DROP MEMBER statement is issued on the calculated member.) The CREATE MEMBER statement creates a calculated member with session scope.
Scope Isolation
In SQL Server 2005 Analysis Services, when a cube Multidimensional Expressions (MDX) script contains calculated members, by default the calculated members are resolved before any session-scoped calculations are resolved and before any query-defined calculations are resolved. This is different from SQL Server 2000 Analysis Services behavior, where solve order can explicitly be used to insert a session-scoped or query-defined calculation in between two cube-level calculations. This difference in behavior might result in query or session-scoped calculated members not returning the desired results in SQL Server 2005 Analysis Services.
Note
In certain scenarios, the Aggregate (MDX) function and the VisualTotals (MDX) function do not exhibit this behavior.
The behavior in SQL Server 2005 Analysis Services allows generic client applications to work with cubes that contain complex calculations, without having to take into account the specific implementation of the calculations. However, in certain scenarios, you might want to execute session or query-scoped calculated members before certain calculations in the cube, and neither the Aggregate function nor the VisualTotals function are applicable. SQL Server 2000 Analysis Services enables this behavior through careful settings of the SOLVE_ORDER property on both cube and session or query-scoped calculated members. To enable the same behavior in SQL Server 2005 Analysis Services, use the SCOPE_ISOLATION calculation property.
Note
The SCOPE_ISOLATION calculation property is new in SQL Server 2005 Analysis Services Service Pack 2 (SP2).
Example
The following script is an example of a scenario where the SCOPE_ISOLATION calculation property is required to produce the correct result.
Cube's MDX Script:
CREATE MEMBER CURRENTCUBE.Measures.ProfitRatio AS 'Measures.[Store Sales]/Measures.[Store Cost]', SOLVE_ORDER = 10
MDX Query:
WITH MEMBER [Customer].[Customers].[USA]. USAWithoutWA AS
[Customer].[Customers].[Country].&[USA] - [Customer].[Customers].[State Province.&[WA], SOLVE_ORDER=5
SELECT {USAWithoutWA} ON 0 FROM SALES
WHERE ProfitRatio
The desired result of the previous query is the ratio of sales for USA without WA, to store cost for USA without WA. While the previous query returns the desired result in SQL Server 2000 Analysis Services, it does not return the desired result in SQL Server 2005 Analysis Services. In SQL Server 2005 Analysis Services, it returns the ratio of USA minus the ratio of WA, which is a meaningless result. To achieve the desired result, you can use the SCOPE_ISOLATION calculation property.
MDX Query using the SCOPE_ISOLATION calculation property:
WITH MEMBER [Customer].[Customers].[USA]. USAWithoutWA AS
[Customer].[Customers].[Country].&[USA] - [Customer].[Customers].[State Province.&[WA], SOLVE_ORDER=5
,SCOPE_ISOLATION=CUBE
SELECT {USAWithoutWA} ON 0 FROM SALES
WHERE ProfitRatio
See Also
Other Resources
MDX Data Definition Statements (MDX)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|
12 December 2006 |
|