SSAS Standards
Data Source Name:
Data source name should be application specific.
Data Source View:
All Diagrams should be arranged / organized with two ways
• Separate view
• All in one view
Aggregations:
• All facts must be aggregated
Source binding:
Fact must have proper source approach by using bindings.
• Table binding
• Query binding
Perspective:
As per the requirements we should create perspective to easy understand and to make things easy and secure.
Role:
All roles should be specified clearly.
Hierarchy:
Need to setup proper Hierarchy setups for better performance.
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
