Summary for "Common SSAS Design Mistakes" By Chris Webb

The following is a summary for the very good session held by Chris Webb for SQLBits and titled Common Analysis Services design mistakes and how to avoid them:

• Don’t ignore the blue squiggly lines in BIDS, they sometimes make useful recommendations about what you should better do.
• Try your best to avoid ETL and Named Queries in your Data Source View, because it can really slow down processing.
• Use friendly names for your dimensions, attributes and hierarchy.
• When you design your dimensions get rid of the surrogate key attribute or merge it with another attribute. Try to review all the attributes and see if they deserve to live in the cube or not. Removing unnecessary attribute will improve your dimension processing, also it is easier to come up with an effective aggregation design.
• Set AttributeHierarchyEnabled to false for attributes which are simply a kind of ‘property’ (for example email address or customer mobile number), attribute we never want to be able to drive queries by (like we never want to see sales by phone number!). Doing this will still allow to show those attributes as properties for other attributes.
• Even Parent/Child hierarchies are very user friendly and flexible, there are still some drawbacks for them:
• They can lead to slow query performance.
• No aggregations can be built at levels inside the hierarchy.
• They can also be a nightmare for scoping advanced MDX calculations.
• And dimension security on parent/child hierarchy is too complex.
• If there is any alternative to using a parent/child hierarchy then use it. And if you can know or assume what will be the maximum depth of your parent/child hierarchy then try to convert it to ‘Ragged’ hierarchy with using the HideMemberIf property. BIDS Helper can do this automatically for you.
• “One cube with multiple measure groups” or “multiple cubes with one measure group” each has its own pros and cons: in one cube, it could be complicated for the users (but you can overcome usability issues using Perspectives), hard to maintain and develop, and query performance may suffer if there are few common dimensions between measure groups and many calculations. In multiple cubes, it’s hard to analyse data from many cubes. Generally speaking, one cube is better but you have to analysis the situation.
• Try to the calculations in your ETL and DWH rather than in your cube because it’s always will perform better.
• Aggregations are the most important SSAS feature for performance, but you have to make sure they are being used, and to do so, run a Profiler trace and look at “Get Data From Aggregation” event. If you see values for that event in your Profiler trace then aggregation is being used. To fix that your aggregation is not being used, go to the aggregations tab, to the advance view, there you can see the granularity of the aggregation. If no checks beside the attribute you want to be included in the aggregation, redesign your aggregation using Aggregation Design Wizard and choose deferent Aggregation Usage for that attribute, keep in mind that:
• Full: means that every aggregation design must include this attribute.
• None: means that this attribute will never be included in aggregation.
• Unrestricted: leaves it to the Wizard to decide whether to use it or not.
• Default: means the same as Unrestricted if the attribute is the key attribute or the dimension or it’s involved in natural user hierarchy.