Intrinsic Member Properties (MDX)

As a provider that is compliant with the OLAP section of the OLE DB specification dated March 1999 (2.6), Microsoft SQL Server 2005 Analysis Services (SSAS) supports the intrinsic member properties listed in this topic.

Types of Member Properties

The intrinsic member properties supported by SQL Server 2005 Analysis Services (SSAS) are of two types:

• Context sensitive member properties
  These member properties must be use in the context of a specific dimension or level, and supply values for each member of the specified dimension or level.

• Non-context sensitive member properties
  These member properties cannot be used in the context of a specific dimension or level, and supply values for all members on an axis.

Regardless of whether the intrinsic member property is context sensitive or not, the following usage rules apply:

• You can specify only those intrinsic member properties that relate to dimension members that are projected on the axis.
• You can mix requests for context sensitive member properties in the same query with non-context sensitive intrinsic member properties.
• You use the PROPERTIES keyword to query for the properties.

The following sections describe both the various context sensitive and non-context sensitive intrinsic member properties available in SQL Server 2005 Analysis Services (SSAS), and how to use the PROPERTIES keyword with each type of property.

Context Sensitive Member Properties

All dimension members and level members support a list of intrinsic member properties that are context sensitive. The following table lists these context-sensitive properties.

PROPERTIES Syntax for Context Sensitive Properties

You use these member properties in the context of a specific dimension or level, and supply values for each member of the specified dimension or level.

For dimension member properties, you precede the name of the property with the name of the dimension to which the property applies. The following example shows the appropriate syntax:

DIMENSION PROPERTIES Dimension.Property_name

For a level member property, you can precede the name of the property with just the level name or, for additional specification, both the dimension and level name. The following example shows the appropriate syntax:

DIMENSION PROPERTIES [Dimension.]Level.Property_name

To illustrate, you would like to return all the names of each referenced member in the [Sales] dimension. To return these names, you would use the following statement in a Multidimensional Expressions (MDX) query:

DIMENSION PROPERTIES [Sales].Name

Non-Context Sensitive Member Properties

All members support a list of intrinsic member properties that are the same regardless of context. These properties provide additional information that can be used by applications to enhance the user's experience.

The following table lists the non-context sensitive intrinsic properties supported by SQL Server 2005 Analysis Services (SSAS).

PROPERTIES Syntax for Non-Context Sensitive Properties

Use the following syntax to specify an intrinsic, non-context sensitive member property using the PROPERTIES keyword:

DIMENSION PROPERTIES Property

Notice that this syntax does not allow the property to be qualified by a dimension or level. The property cannot be qualified because an intrinsic member property that is not context sensitive applies to all members of an axis.

For example, an MDX statement that specifies the DESCRIPTION intrinsic member property would have the following syntax:

DIMENSION PROPERTIES DESCRIPTION

This statement returns the description of each member in the axis dimension. If you tried to qualify the property with a dimension or level, as in Dimension.DESCRIPTION or Level.DESCRIPTION, the statement would not validate.

Examples

The following example returns the count of the resellers whose sales have declined over the previous time period, based on user-selected State-Province member values evaluated using the Aggregate function. The Hierarchize (MDX) and DrilldownLevel (MDX) functions are used to return values for declining sales for product categories in the Product dimension. The Properties (MDX) function is used to return the unique name of parent members returned on the column axis.

WITH MEMBER Measures.[Declining Reseller Sales] AS 
   Count
      (Filter
         (Existing
            (Reseller.Reseller.Reseller)
            , [Measures].[Reseller Sales Amount] < 
            (
               [Measures].[Reseller Sales Amount]
                  ,[Date].Calendar.PrevMember
             )
          )
       )
MEMBER [Geography].[State-Province].x AS 
   Aggregate 
      ( 
         {[Geography].[State-Province].&[WA]&[US]
         , [Geography].[State-Province].&[OR]&[US] 
         } 
      )
SELECT NON EMPTY HIERARCHIZE 
   (AddCalculatedMembers 
      (
         {DrillDownLevel
            ({[Product].[All Products]})
         }
      )
   ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS 
FROM [Adventure Works]
WHERE 
   (
      [Geography].[State-Province].x
      , [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
      ,[Measures].[Declining Reseller Sales]
   )

The following example returns the translated caption for the product category member in the Product dimension in the Adventure Works cube for specified locales.

WITH 
MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION
MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082")
MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036")
SELECT 
{ Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0
,[Product].[Category].MEMBERS ON 1
FROM [Adventure Works]

See Also

Reference

PeriodsToDate (MDX)
Children (MDX)
Hierarchize (MDX)
Count (Set) (MDX)
Filter (MDX)
AddCalculatedMembers (MDX)
DrilldownLevel (MDX)
Properties (MDX)
PrevMember (MDX)
MDX Function Reference (MDX)

Concepts

Using Member Properties (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History