By: Pavel Pawlowski | Comments | Related: > 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.
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>
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.
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>
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
- You can download Adventure Works Multidimensional Models and AdventureWorksDW on Codeplex for experiments.
- Download a modified Adventure Works Multidimensional Model with changes described in this tip. The SQL 2012 version of the AdventureWorksDW and multidimensional model is used in this sample.
- Refer to Cube Basics How-to Topics (Analysis Services) for step-by-step instructions how to work with cubes in SSAS 2005 and above.
About the author
Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
