Month: April 2012

Something’s to look for when you are building your next SSAS cube

There are a number of things which you should be aware of when you are building your SSAS cube. Here I am listing some of the very trivial things that have been particularly painful to me. I am hoping that this will be a live post and I will add more as and when I find them.

 1. Create views in the DW and then build your DSV from them

The biggest benefit is that it will add an abstraction layer between DW and SSAS.  Metadata columns from the DW such as StartDate, EndDate, Source, DateAdded, ModifiedBy etc. which are not required in the cube can be easily filtered out. Another benefit is that any changes in the DW which you don’t want to propagate to cube can be absorbed in views. For example minor data type changes, adding new column, new relationships etc. Make sure that the views are simple SELECT statements over DW tables with minimal joins.

2. Avoid NULLs

This is more of a DW thing than SSAS. SSAS behaves slightly weird when it finds a NULL, especially when the column has NULL and an empty string(”). SSAS converts NULLs to blanks or zeros and chances are that you will get Duplicate Key Error. You can avoid NULLs in the views as mentioned above. If you do have NULLs then consider setting  NullProcessing property toUnknownMember.

3. Religiously define and maintain relationships between tables in DSV

This is very important since SSAS will be able to help you more actively once you define relationships. Take snow-flaking for example,when building snow-flaked dimension, SSAS can tell you which other tables from DSV are related to it to help you out.

4. Beware of the datatypes and trimming

I have had a fair share of trouble because of these. If the datatype of a column changes in DSV, this change does not reflect automatically in dimension attribute. This is specially troublesome for varchar,char or nvarchar datatypes because sooner or later you will start getting  the error “The size specified for a binding was too small, resulting in one or more column values being truncated.”  Dimension Data Type Discrepancy Check  from BIDS Helper is a life saver in this situation

5. Use folders to categories dimension attributes

For dimensions with large number of attributes, use AttributeHierarchyDisplayFolder property to display them in specific folders. This will make browsing the cube in excel for end users simple. Same holds true if you have large number of measures in a measure group.

6. Keep the attributes in the dimension to bare minimum

Too many attributes in too many dimensions will create too much confusion. Give users what they absolutely need and will be most useful to them. Disable or hide the hierarchies which are not needed.

7. Create partitions or atleast plan for them (Update:03 August 2012)

This may not be an immediate concern but thinking about partitioning in the beginning will help you in the longer run. Partitioning can dramatically reduce the cube processing and performance.

8. Partition slices are important, don’t ignore them!! (Update: 19 December 2012)

Without slices, chances are that your MDX query is scanning all the partitions. There are many many blogs out there who confess to this and recently I had a very similar experience. This post from Mosha is a good indication of importance of setting Slices.

AttributeHierarchyVisible in SSAS and a small observation


In Analysis Services 2008 to hide an attribute hierarchy, you set the AttributeHierarchyVisible property to False. The hierarchy will still be available but will be hidden from the user. We can set the flag to True if we want to show it again except there is a small catch which you need to be aware of. There are two places where we can set AttributeHierarchyVisible.

First place is our normal dimension properties window and the other is in the cube designer window. Open a cube in BIDS. If you haven’t changed the default view in cube design window, you should have Measures window on the right and Dimension window just below it.  Expand any of the dimensions in the Dimensions window, right click on an attribute and select properties. You will find another AttributeHierarchyVisible property which you can set.

It seems like the two act like an OR operation. Both need to be True for attribute to be visible. If either of them is False, the attribute will not be visible. In my case the property from cube designer window got changed to False (I don’t know how?) and then no matter what I did the attribute was not visible.

If you are confused like me as to why we need to change the same property twice, I am sorry I don’t have the answer. But at least we know the solution.