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

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.

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.

About these ads
  1. Mark Wojciechowicz
    September 1, 2012 at 1:03 pm

    Nice post, Sherry! This is a great tip

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 166 other followers

%d bloggers like this: