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

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

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.

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.

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.

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.

About these ads