SSAS - Setting Attributes as Properties for Excel


Comments (6)

Forward to a friend

Print

Rating: 3/5 1 Votes

change text size: A A A

Published 12/8/2010 03:31 PM by MikeDavis

When a user is browsing your cube with excel they may not understand how to slice the data and end

up using things like “first name” as a hierarchy to slice the data. This does not make for good analysis,

since grouping all of the people with the same name would not be useful (Unless your business is in

analyzing names).

So a better practice is to set the “Attribute Hierarchy Enabled” property of the attributes to false, for

attributes you don’t want the user slicing on, like First name, Last name, Email Address, and phone

number. Then the user can see the properties in excel without having to do any slicing.

Below are examples using the employee dimension in Adventure Works. You can ignore the parent

child relationship on the dimension.

Here is an example of what NOT to do:

In the below image you can see all of the attributes of the dimension are available and there are no

hierarchies built. So if an end user wanted to slice by marital status and see the first and last names of

the people in the different statuses, they would have to slice by all three of these attributes.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

1 of 7 4/6/2011 6:42 PM

In Excel it would look like this:

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

2 of 7 4/6/2011 6:42 PM

This is confusing and is very bad for query performance.

To fix this you will need to build a hierarchy and let the user see the properties of the employee.

To do this:

1. Drag marital status into the hierarchies window in the center of the dimension editor

2. Drop the employee key under martial status

3. Rename the hierarchy to Marital Hierarchy

Now you can disable the attributes you don’t want the user slicing on like Phone, Login ID, Last name,

First Name, and Email Address.

To do this:

1. Hold CTRL and click on each of the attributes listed above

2. In the properties window change the Attribute Hierarchy Enable Property to false

The Dimension Structure should look like the image below.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

3 of 7 4/6/2011 6:42 PM

Click on the Attribute relationships tab and it should look like the below image.

Notice the Grey next to the disabled attributes in the bottom window. Now process and deploy the

dimension and the cube.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

4 of 7 4/6/2011 6:42 PM

The user will only need to slice by the Marital Hierarchy in Excel as seen in the image below.

The user can slice the information by marital status and then right click to see the properties of the

employee as seen in the image below.

Note: The employee Key is showing in this example instead of the login ID because the name property

of the employee key was changed to the login Id.

The end user can now get the properties of each of the employees by right clicking on the employee

and selecting “Show Properties in Report” and selecting the information they want to see.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

5 of 7 4/6/2011 6:42 PM

Now selecting the first and last names do not slice the cube by the names and just show the names on

the report as seen in the below image.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

6 of 7 4/6/2011 6:42 PM

This should make your queries faster and allow your users to see the information they want without

doing and lot of extra slicing.

To give you a deeper understanding of what is occurring here, imagine that you have millions of rows of

data on your fact table and you drag out first name like in the example above of what NOT to do. This

divides all of the data into groups by the first names. So anyone with the name Mike would be grouped

together. Grouping the Mikes together is not useful. The only reason the user is doing this is because

they want to see the name once they get down to the employee level.

By changing this behavior in your user and teaching them how to get the properties, your SSAS queries

will run much smoother and give your users a better experience.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

7 of 7 4/6/2011 6:42 PM