1 / 32

Designing and Optimizing Hierarchies

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:

teness
Download Presentation

Designing and Optimizing Hierarchies

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Designing and OptimizingHierarchies Presented by: Jose Chinchilla MCP, MCTS, MCITP

  2. 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

  3. 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

  4. Performance Tips & Best Practices Test Test Test

  5. Types & Classification of hierarchies Designing and Optimizing Custom Hierarchies

  6. Hierarchies everywhere

  7. Corporate Hierarchy

  8. Analysis Services Hierarchies Q1 Jan Designing and Optimizing Custom Hierarchies

  9. Natural vs. Unnatural Hierarchies Natural Unnatural Designing and Optimizing Custom Hierarchies

  10. 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

  11. Balanced, Unbalanced & Ragged Hierarchies Balanced Hierarchy Designing and Optimizing Custom Hierarchies

  12. Balanced, Unbalanced & Ragged Hierarchies Unbalanced Hierarchy Designing and Optimizing Custom Hierarchies

  13. Balanced, Unbalanced & Ragged Hierarchies RaggedHierarchy Designing and Optimizing Custom Hierarchies

  14. Attribute Hierarchies vs. User Hierarchies aka User Hierarchy aka Attribute Hierarchy Designing and Optimizing Custom Hierarchies

  15. 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

  16. DEMO: Overview of an attribute hierarchy (default) Designing and Optimizing Custom Hierarchies

  17. User hierarchies Blue Squiggly Lines • Attribute relationships are essential for significant server optimizations. Designing and Optimizing Custom Hierarchies

  18. User hierarchies: Attribute Relationships Source of 90% of Cube Performance Gains Designing and Optimizing Custom Hierarchies

  19. User hierarchies: Attribute Relationships Inefficient Efficient More Granular More Detail Lower Level Less Granular Less Detail Higher Level Designing and Optimizing Custom Hierarchies

  20. DEMO: • Optimizing user hierarchies with • Attribute Relationships • Discretization • Aggregations Designing and Optimizing Custom Hierarchies

  21. Parent-child hierarchies Self-referencing relationship or self-join Designing and Optimizing Custom Hierarchies

  22. DEMO: Overview of a Parent-Child Hierarchy Designing and Optimizing Custom Hierarchies

  23. Dimension and Attribute Types Dimension Types Attribute Types Designing and Optimizing Custom Hierarchies

  24. Summary: • Query performance • Storage • Processing time Designing and Optimizing Custom Hierarchies

  25. Impact of Data Types Designing and Optimizing Custom Hierarchies

  26. Impact of Flexible vs. Rigid Attribute Relationship Types • Process Full will re-compute aggregations for both Flexible & Rigid Designing and Optimizing Custom Hierarchies

  27. Impact of Attribute properties Designing and Optimizing Custom Hierarchies

  28. Impact of Aggregations …too much of a good thing is not good. Designing and Optimizing Custom Hierarchies

  29. 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?

More Related