About MDX Properties

Properties describe certain characteristics of data and metadata. MDX enables users to write queries that use properties to retrieve and analyze data. Properties can be intrinsic or custom.

Intrinsic properties are defined for members in all dimensions. In Analytic Services, the intrinsic MDX member properties defined for all members in an Analytic Services database outline are MEMBER_NAME, MEMBER_ALIAS, LEVEL_NUMBER, and GEN_NUMBER.

MDX in Analytic Services supports two types of custom properties: attribute properties and UDA properties. Attribute properties are defined by the attribute dimensions in an outline. In the Sample Basic database, the [Pkg Type] attribute dimension describes the packaging characteristics of members in the Product dimension. This information can be queried in MDX using the property name [Pkg Type].

Attribute properties are defined only for specific dimensions and only for a specific level in each dimension. For example, in the Sample Basic outline, [Ounces] is an attribute property defined only for members in the Product dimension, and this property has valid values only for the level-0 members of the Product dimension. The [Ounces] property does not exist for other dimensions, such as Market. The [Ounces] property for a non level-0 member in the Product dimension is a NULL value. The attribute properties in an outline are identified by the names of attribute dimensions in that outline.

The custom properties also include UDAs. For example, [Major Market] is a UDA property defined on Market dimension members. It returns a TRUE value if [Major Market] UDA is defined for a member, and FALSE otherwise.

Querying for member properties

Properties can be used inside an MDX query in two ways. In the first approach, you can list the dimension and property combinations for each axis set. When a query is executed, the specified property is evaluated for all members from the specified dimension and included in the result set.

For example, on the column axis, the following query will return the GEN_NUMBER information for every Market dimension member. On the row axis, the query returns MEMBER_ALIAS information for every Product dimension member.

SELECT
 [Market].Members
    DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
 Filter ([Product].Members, Sales > 5000)
    DIMENSION PROPERTIES [Product].[MEMBER_ALIAS] on rows
from Sample.Basic

When querying for member properties using the DIMENSION PROPERTIES section of an axis, a property can be identified by the dimension name and the name of the property, or just by using the property name itself. When a property name is used by itself, that property information is returned for all members from all dimensions on that axis, for which that property applies. In the following query. the MEMBER_ALIAS property is evaluated on the row axis for both Year and Product dimensions.

          
SELECT
 [Market].Members
    DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
 CrossJoin([Product].Children, Year.Children)
    DIMENSION PROPERTIES [MEMBER_ALIAS] on rows
from Sample.Basic

In a second approach, properties can be used inside value expressions in an MDX query. For example you can filter a set based on a value expression that uses properties of members in input set.

The following query returns all caffeinated products that are packaged in cans.

         
Select
Filter([Product].levels(0).members,
         [Product].CurrentMember.Caffeinated and
         [Product].CurrentMember.[Pkg Type] = "Can")
         Dimension Properties
           [Caffeinated], [Pkg Type] on columns

The following query uses the UDA [Major Market] to calculate the value [BudgetedExpenses] based on whether the current member of the Market dimension is a major market or not.

With 
   MEMBER [Measures].[BudgetedExpenses] AS
      'IIF([Market].CurrentMember.[Major Market],
       [Marketing] * 1.2, [Marketing])'
Select
   {[Measures].[BudgetedExpenses]} on columns,
   Market.Members on rows
Where
  ([Budget])

The Value Type of Properties

The value of an MDX property in Analytic Services can be a numeric, Boolean, or string type. MEMBER_NAME and MEMBER_ALIAS properties return string values. LEVEL_NUMBER and GEN_NUMBER properties return numeric values.

The attribute properties return numeric, Boolean, or string values based on the attribute dimension type. For example, in Sample Basic, the [Ounces] attribute property is a numeric property. The [Pkg Type] attribute property is a string property. The [Caffeinated] attribute property is a Boolean property.

Analytic Services allows attribute dimensions with date types. The date type properties are treated as numeric properties in MDX. When comparing these property values with dates, you need to use the TODATE function to convert date strings to numeric before comparison.

The following query returns all Product dimension members that have been introduced on date 03/25/1996. Since the property [Intro Date] is a date type, the TODATE function must be used to convert the date string "03-25-1996" to a number before comparing it.

Select
 Filter ([Product].Members, 
          [Product].CurrentMember.[Intro Date] = 
          TODATE("mm-dd-yyyy","03-25-1996")) on columns

When a property is used in a value expression, you must use it appropriately based on its value type: string, numeric, or Boolean.

NULL Property Values

Not all members may have valid values for a given property name. For example, the MEMBER_ALIAS property returns an alternate name for a given member as defined in the outline; however, not all members may have aliases defined. In these cases A NULL value would be returned for those members that do not have aliases.

In the following query:

 SELECT
  [Year].Members
     DIMENSION PROPERTIES [MEMBER_ALIAS] on columns

none of the members in the Year dimension have aliases defined for them. Therefore, the query returns NULL values for the [MEMBER_ALIAS] property for members in the Year dimension.

The attribute properties are defined for members of a specific dimension and a specific level in that dimension. In the Sample Basic database, the [Ounces] property is defined only for level-0 members of the Product dimension.

Therefore, if you query for the [Ounces] property of a member from the Market dimension, as shown in the following query, you will get a syntax error:

      
 SELECT
    Filter([Market].members,
           [Market].CurrentMember.[Ounces] = 32) on columns

Additionally, if you query for the [Ounces] property of a non level-0 member of the dimension, you will get a NULL value.

When using property values in value expressions, you can use the function IsValid() to check for NULL values. The following query returns all Product dimension members with [Ounces] property value of 12, after eliminating members with NULL values.

           
 Select
    Filter([Product].Members,
           IsValid([Product].CurrentMember.[Ounces]) and
           [Product].CurrentMember.[Ounces] = 12) on columns

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com