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.
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 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.
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 |