Cube Design Best Practices - Dimensions
• Consolidate multiple hierarchies into single dimension (unless they are related via fact table)
• Avoid ROLAP storage mode
• Use role playing dimensions (e.g. OrderDate, BillDate, ShipDate) - avoids multiple physical copies
• Use parent-child dimensions prudently
o No aggregation support
• Set Materialized = true on reference dimensions
• Use many-to-many dimensions prudently
o Slower than regular dimensions, but faster than calculations
o Intermediate measure group must be “small” relative to primary measure group
Cube Design Best Practices – Attributes/Hierarchies
• Define all possible attribute relationships!
• Remove redundant attribute relationships
• Mark attribute relationships as rigid where appropriate
• Use integer (or numeric) key columns
• Set AttributeHierarchyEnabled to false for attributes not used for navigation (e.g. Phone#, Address)
• Set AttributeHierarchyOptimizedState to NotOptimized for infrequently used attributes
• Set AttributeHierarchyOrdered to false if the order of members returned by queries is not important
• Use natural hierarchies where possible
Cube Design Best Practices – Measures
• Use smallest numeric data type possible
• Use semi-additive aggregate functions instead of MDX calculations to achieve same behavior
• Put distinct count measures into separate measure group (BIDS does this automatically)
• Avoid string source column for distinct count measures
Cube Design Best Practices – OLAP Partitions
• No more than 20M rows per partition
• Specify partition slice
o Optional for MOLAP – server auto-detects the slice and validates against user specified slice (if any)
o Must be specified for ROLAP
• Manage storage settings by usage patterns
o Frequently queried MOLAP with lots of aggregations
o Periodically queried MOLAP with less or no aggregations
o Historical ROLAP with no aggregations
• Alternate disk drive - use multiple controllers to avoid I/O contention
• Remote partitions for scale out – VLDB
Cube Design Best Practices – Aggregations
• Define all possible attribute relationships
• Set accurate attribute member counts and fact table counts
• Set AggregationUsage to guide aggregation designer
o Set rarely queried attributes to None
o Set commonly queried attributes to Unrestricted
• Do not build too many aggregations
o In the 100s, not 1000s
• Do not build aggregations larger than 30% of fact table size (agg design algorithm doesn’t)
MDX Query Design Best Practices
• Use calculated members instead of calc cells where possible
• Use .MemberValue for calculations on numeric attributes
o Filter(Customer.members, Salary.MemberValue > 100000)
• Avoid using CalculationPassValue
o Rely on auto recursion resolution using scopes and assignments
• Avoid redundant use of .CurrentMember and .Value
o (Time.CurrentMember.PrevMember, Measures.CurrentMember ).Value can be replaced with Time.PrevMember
• Avoid LinkMember, StrToSet, StrToMember, StrToValue
• Replace simple calculations with computed columns in DSV
o Calculation done at processing time is always better
• Many more at:
o
http://sqljunkies.com/weblog/mosha
o
http://sqlserveranalysisservices.com
Source:
http://blogs.msdn.com/b/jbasilico/archive/2008/04/01/analysis-services-2005-design-best-practices-cubes-mdx.aspx
MSBI (SSIS/SSRS/SSAS) Online Training:
Exam Profile : 
