320 likes | 515 Views
Designing and Optimizing Hierarchies. Presented by: Jose Chinchilla MCP, MCTS, MCITP. Jose Chinchilla MCITP: Database Administrator, SQL Server 2008 MCTS: SQL Server 2005 & 2008 MCTS: Business Intelligence SQL Server 2008 Current Positions:
E N D
Designing and OptimizingHierarchies Presented by: Jose Chinchilla MCP, MCTS, MCITP
Jose Chinchilla MCITP: Database Administrator, SQL Server 2008 MCTS: SQL Server 2005 & 2008 MCTS: Business Intelligence SQL Server 2008 Current Positions: Sr. Business Intelligence Consultant & Data Architect, Pragmatic Works President, Tampa Bay Business Intelligence User Group & PASS Official Chapter “DBA by accident, BI Developer by chance, Geek by Choice” Blog: http://www.sqljoe.com http://www.bidn.com Twitter: http://www.twitter.com/sqljoe Linked-in: http://www.linkedin.com/in/josechinchilla Email: jchinchilla@pragmaticworks.com jchinchilla@sqljoe.com BI Savvy NOT BI Curious
Agenda • Types & classification of hierarchies • Natural & unnatural hierarchies • Attribute hierarchies vs. User hierarchies • Parent-child hierarchies • Balanced, unbalanced & ragged hierarchies • Demo • Designing & optimizing hierarchies • Dim Products • Dim Employee • Dim Date Plus Performance Tips & Best Practices
Performance Tips & Best Practices Test Test Test
Types & Classification of hierarchies Designing and Optimizing Custom Hierarchies
Analysis Services Hierarchies Q1 Jan Designing and Optimizing Custom Hierarchies
Natural vs. Unnatural Hierarchies Natural Unnatural Designing and Optimizing Custom Hierarchies
Natural vs. Unnatural Hierarchies Natural Unnatural Hierarchy tree is materialized on disk in hierarchy stores Attributes automatically considered to be aggregation candidates. Not materialized on disk Not considered as aggregation candidates. Designing and Optimizing Custom Hierarchies
Balanced, Unbalanced & Ragged Hierarchies Balanced Hierarchy Designing and Optimizing Custom Hierarchies
Balanced, Unbalanced & Ragged Hierarchies Unbalanced Hierarchy Designing and Optimizing Custom Hierarchies
Balanced, Unbalanced & Ragged Hierarchies RaggedHierarchy Designing and Optimizing Custom Hierarchies
Attribute Hierarchies vs. User Hierarchies aka User Hierarchy aka Attribute Hierarchy Designing and Optimizing Custom Hierarchies
Default Attribute Hierarchy – “ALL” • By default, an “ALL” attribute hierarchy is created for every attribute in a dimension. • Can be disabled by setting IsAggregatable= False • MDX time! Designing and Optimizing Custom Hierarchies
DEMO: Overview of an attribute hierarchy (default) Designing and Optimizing Custom Hierarchies
User hierarchies Blue Squiggly Lines • Attribute relationships are essential for significant server optimizations. Designing and Optimizing Custom Hierarchies
User hierarchies: Attribute Relationships Source of 90% of Cube Performance Gains Designing and Optimizing Custom Hierarchies
User hierarchies: Attribute Relationships Inefficient Efficient More Granular More Detail Lower Level Less Granular Less Detail Higher Level Designing and Optimizing Custom Hierarchies
DEMO: • Optimizing user hierarchies with • Attribute Relationships • Discretization • Aggregations Designing and Optimizing Custom Hierarchies
Parent-child hierarchies Self-referencing relationship or self-join Designing and Optimizing Custom Hierarchies
DEMO: Overview of a Parent-Child Hierarchy Designing and Optimizing Custom Hierarchies
Dimension and Attribute Types Dimension Types Attribute Types Designing and Optimizing Custom Hierarchies
Summary: • Query performance • Storage • Processing time Designing and Optimizing Custom Hierarchies
Impact of Data Types Designing and Optimizing Custom Hierarchies
Impact of Flexible vs. Rigid Attribute Relationship Types • Process Full will re-compute aggregations for both Flexible & Rigid Designing and Optimizing Custom Hierarchies
Impact of Attribute properties Designing and Optimizing Custom Hierarchies
Impact of Aggregations …too much of a good thing is not good. Designing and Optimizing Custom Hierarchies
Thank you for attending! Blog: http://www.sqljoe.com Twitter: http://www.twitter.com/sqljoe Linked-in: http://www.linkedin.com/in/josechinchilla Email: jchinchilla@sqljoe.com jchinchilla@pragmaticworks.com Rate my presentationhttp://www.speakerrate.com/speakers/8064-jchinchilla Questions?