Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Custom Attribute Names in Role Playing Dimensions for SSAS


By:   |   Last Updated: 2013-09-27   |   Comments   |   Related Tips: > Analysis Services Dimensions

Problem

SQL Server introduced a great feature for improving the processing time and reducing storage requirements in the form of Role Playing Dimensions. Unfortunately one of the drawbacks is that those dimensions do not support custom attribute names for different roles. Often the business requirement is to use custom attribute names, but many reporting solutions (including Excel) use only the attribute names for column names.  In this tip we look at how this can be achieved using hierarchies.

Solution

To solve the problem many BI developers fall back to standard non role playing dimensions. This is not a big problem for relatively small dimensions, but in the case of large dimensions with many members it could mean a big impact on processing time.

Creating SSAS Custom Attribute Names Using Custom Hierarchies

Instead of falling back to individual dimensions there is a work-around by using custom hierarchies. We will take a closer look at this approach using a Date dimension in the Adventure Works sample SSAS database.

Role Playing Date Dimension

Creating Custom Hierarchies in SSAS

As we can create multiple hierarchies in a dimension and we can assign custom level names to these hierarchies, we simply create a separate set of hierarchies for each role of the dimension. Simply make copies of the hierarchies and provide appropriate attribute names for different roles. If you do not need all the hierarchies, copy only the ones you need for a particular dimension role. If you need custom attribute names not only in hierarchies but also for individual attributes simply create custom hierarchies for individual attributes (for example Date -> Ship Date. You can see the result on the image below./P>

Custom Dimension Hierarchies

Disabling Dimension Hierarchies in Cube Dimensions (Dimension Roles)

Once we have created all the necessary hierarchies, we have to alter the cube dimensions (dimension roles) and disable the unwanted hierarchies. If we do not disable the hierarchies in the cube dimensions, than each dimension will show all the hierarchies when browsing and this is not what we want.

In our example this means that for the [Date] cube dimension we disable the [Fiscal Delivery], [Calendar Delivery], [Delivery Date], [Calendar Ship Date] and [Ship Date] hierarchies. For the [Ship Date] cube dimension we disable the [Fiscal], [Calendar], [Fiscal Weeks], [Calendar Weeks], [Fiscal Delivery], [Calendar Delivery] and [Delivery Date] hierarchies. Similarly for the [Delivery Date] cube dimension we disable [Fiscal], [Calendar], [Fiscal Weeks], [Calendar Weeks], [Calendar Ship Date] and [Ship Date] hierarchies.

As we have separate hierarchies for the [Date] attribute ([Delivery Date] and [Ship Date] hierarchies), we have to hide the [Date] attribute in the [Ship Date] and [Delivery Date] hierarchies. We cannot disable the [Date] attribute hierarchy in the [Delivery Date] and [Ship Date] hierarchies of the cube dimension, because the [Date] attribute is a granularity attribute used in the custom hierarchies.

Of course, you can disable other not needed custom hierarchies and hide not needed attributes in particular roles.

You can disable and hide attributes for cube dimensions in the Cube Structure tab of the cube designer.

Cube Hierarchies

Deploying and Checking Results for Custom Attribute Names in SSAS

Once we have made all the necessary changes, we can deploy the project to a SSAS server and reprocess the database. Once reprocessed, we can try to connect using SSMS or Excel to check the results./P>

SSMS Check


Excel Check

Conclusion

Although this solution is not the most ideal approach (the ideal option is missing direct support in SSAS) for the custom attribute names in the role playing dimension, because you have to create additional hierarchies in the dimensions, using this approach you will save significant processing time and storage space especially for large dimensions.

The storage and processing savings are due to the fact that SSAS instance does not need to read and store all the attributes as in the case of non-role playing dimensions, because it only builds additional hierarchies.

Next Steps


Last Updated: 2013-09-27


next webcast button


next tip button



About the author
MSSQLTips author Pavel Pawlowski Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools