Archive

Posts Tagged ‘Properties function’

MDX #26 – SSN can only be referenced as a member property in MDX

February 21, 2013 Leave a comment

I’ve blogged about the Properties()  function before.

MDX #11 – How to get number of cars each customer owned using Properties() function?

MDX #12 – Do not forget the TYPED flag in the Properties() function 

The Properties()  function is used to explore the attribute relationships in a dimension.

If an attribute is also enabled as an attribute hierarchy, then of cause, we can also reference it as Attribute Hierarchy in MDX.

AttributeHierarchyEnabled = False

In the Employee dimension in the Adventure Works cube, employees’ SSN numbers is not enabled as an Attribute Hierarchy. Its property AttributeHierarchyEnabled is set to False.

Here are what will happen in SSAS, in MDX, and in client applications when AttributeHierarchyEnabled is set to False.

In SSAS: A disabled attribute hierarchy cannot be be used as a level in a user defined hierarchy

However, it can still be used to order the members of another attribute.

In MDX: 1) A disabled attribute hierarchy cannot be referenced as Attribute Hierarchy

This query referenced the SSN as attribute hierarchy, and no result is returned.

image

In MDX: 2) A disabled attribute hierarchy can only be referenced as a member property

This query referenced SSN as the property of current member of the employee. It shows the correct SSN for each employee.

image

In Client App: 1) Will not be used for browsing in client application

In Client App: 2) Will only be visible to client applications as a member property

In SSAS: still makes sense to order and optimize the disabled attribute

Because the members of these attribute hierarchies are still used for dimensioning fact data, ordering and optimizing the members of these attribute hierarchies can still improve performance.

Therefore, the following properties of these attributes can still be enabled.

  • AttributeHierarchyVisible = True: (the attribute will be visible to the attribute hierarchies list in client applications)
  • IsAggregatable = True: (it will still have the All top level member)
  • AttributeHierarchyOptimizedState = FullyOptimized
  • AttributeHierarchyOrdered = True
  • GroupingBehavior = EncourageGrouping

MDX #12 – Do not forget the TYPED flag in the Properties() function

August 31, 2012 Leave a comment

Continue from my previous blog, MDX #11 – How to get number of cars each customer owned using Properties() function?.

Without the TYPED flag, Employee ID is no longer a number, but a text field in Excel

A couple of months ago, I completed a small project to create a calculated measure from the Employee dimension member property Employee ID. A couple of weeks later, a user reported that the Excel PivotTable he has been using to access the cube stopped working because the Employee ID is no longer a number, but a text field now.

Without the TYPED flag, the return value is always a string

In the MSDN MDX reference library, Properties (MDX), in the Remarks section, it has clearly stated:

    By default, the value is coerced to be a string. If TYPED is specified, the return value is strongly typed.
  • If the property type is intrinsic, the function returns the original type of the member.
  • If the property type is user defined, the type of the return value is the same as the type of the return value of the MemberValue function.

Although the TYPED flag is optional, in my case, I should have used it.

Here is the syntax:

Member_Expression.Properties(Property_Name, TYPED)

To return the Number of Cars Owned as a number, I would add the TYPED flag to the properties function.

image

The original data type of Number of Cars Owned is number

Looking at the Number of Cars Owned attribute, the original data type is UnsignedTinyInt.

image

MDX #11 – How to get number of cars each customer owned using Properties() function?

August 31, 2012 1 comment

In the Adventure Works cube, this is a simple query to get just 10 customers and their Internet Sales Amount.

image

Dimensions can have many attributes (or attribute hierarchies) and user hierarchies

The Customer dimension has many attributes (or attribute hierarchies) and one user hierarchy, the Customer Geography. This is what you can see in the dimension editor.

image

One of the attribute is Number of Cars Owned. Can we add the Number of Cars Owned to the above query so that for each customer we can see how many cars they owned?

Properties() function can turn dimension attribute into measures

If you haven’t written enough MDX queries yet, you might be tempted with many possibilities, until you come across the properties() function.

Here is the query that will work.

image

It did work, but there are a couple of unanswered questions.

First, what are “properties”?

In the BIDS, under the Attribute Relationships, we can see two types of relationships.

  • attribute –> properties
  • user-defined hierarchies
    This screenshot shows that the Customer attribute has many properties, and one of them is Number of Cars Owned.

image

In the WITH statement, did we just create a calculated measure using a dimension property?

Yes. Calculated members do not need to be always from measures.

%d bloggers like this: