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.