SSAS - Setting Attributes as Properties for Excel

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.

 
In Excel it would look like this:
 

 

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.

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.

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.
 
 

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.

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.