Once you have identified potential Analysis Services bottlenecks, and have made the proper adjustments to Analysis Services server properties and perhaps added more memory/CPU power to the server, the next phase is to look into tuning the OLAP cube design and the related MDX queries used against the cube.

The sections outlined below will give you best practice insight (that I've used during customer proof of concepts) for fine-tuning your cube and query design, thus making your OLAP cube as efficient as possible for queries being sent to it for results.

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
    • No aggregation support
  • Set Materialized = true on reference dimensions
  • Use many-to-many dimensions prudently
    • Slower than regular dimensions, but faster than calculations
    • 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
    • Optional for MOLAP - server auto-detects the slice and validates against user specified slice (if any)
    • Must be specified for ROLAP
  • Manage storage settings by usage patterns
    • Frequently queried à MOLAP with lots of aggregations
    • Periodically queried à MOLAP with less or no aggregations
    • 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
    • Set rarely queried attributes to None
    • Set commonly queried attributes to Unrestricted
  • Do not build too many aggregations
    • 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
    • Filter(Customer.members, Salary.MemberValue > 100000)
  • Avoid using CalculationPassValue
    • Rely on auto recursion resolution using scopes and assignments
  • Avoid redundant use of .CurrentMember and .Value
    • (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
    • Calculation done at processing time is always better
  • Many more at:

(originally published HERE) 

 Jaime Basilico
Technology Architect
MTC: New York