designing and optimizing hierarchies
DESCRIPTION
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: - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/1.jpg)
Designing and OptimizingHierarchies
Presented by:Jose Chinchilla
MCP, MCTS, MCITP
![Page 2: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/2.jpg)
Jose Chinchilla MCITP: Database Administrator, SQL Server 2008 MCTS: SQL Server 2005 & 2008MCTS: Business Intelligence SQL Server 2008
Current Positions:Sr. Business Intelligence Consultant & Data Architect, Pragmatic WorksPresident, Tampa Bay Business Intelligence User Group & PASS Official Chapter
“DBA by accident, BI Developer by chance, Geek by Choice”
Blog: http://www.sqljoe.comhttp://www.bidn.com
Twitter: http://www.twitter.com/sqljoe
Linked-in: http://www.linkedin.com/in/josechinchilla
Email: [email protected]@sqljoe.com
BI Savvy NOT BI Curious
![Page 3: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/3.jpg)
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
PlusPerformance Tips &Best Practices
![Page 4: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/4.jpg)
![Page 5: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/5.jpg)
Performance Tips & Best Practices
TestTestTest
![Page 6: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/6.jpg)
Types & Classification of hierarchies
Designing and Optimizing Custom Hierarchies
![Page 7: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/7.jpg)
Hierarchies everywhere
![Page 8: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/8.jpg)
![Page 9: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/9.jpg)
![Page 10: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/10.jpg)
Corporate Hierarchy
![Page 11: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/11.jpg)
Analysis Services Hierarchies
Designing and Optimizing Custom Hierarchies
Q1
Jan
![Page 12: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/12.jpg)
Natural vs. Unnatural Hierarchies
Designing and Optimizing Custom Hierarchies
Year
Quarter
Month
Day
NaturalModel
Style
Size
Color
Unnatural
![Page 13: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/13.jpg)
Natural vs. Unnatural Hierarchies
Designing and Optimizing Custom Hierarchies
Natural Unnatural
1. Hierarchy tree is materialized on disk in hierarchy stores
2. Attributes automatically considered to be aggregation candidates.
1. Not materialized on disk
2. Not considered as aggregation candidates.
![Page 14: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/14.jpg)
Balanced, Unbalanced & Ragged Hierarchies
Designing and Optimizing Custom Hierarchies
North America
United States
Florida
Miami
Canada
Ontario
Toronto
Mexico
Yucatan
Merida
Balanced Hierarchy
![Page 15: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/15.jpg)
Balanced, Unbalanced & Ragged Hierarchies
Designing and Optimizing Custom Hierarchies
North America
United States
Florida
Miami
Canada
Ontario
Mexico
Unbalanced Hierarchy
![Page 16: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/16.jpg)
Balanced, Unbalanced & Ragged Hierarchies
Designing and Optimizing Custom Hierarchies
RaggedHierarchy
United States
Florida
Miami
![Page 17: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/17.jpg)
Attribute Hierarchies vs. User Hierarchies
aka Attribute Hierarchy aka User Hierarchy
Designing and Optimizing Custom Hierarchies
![Page 18: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/18.jpg)
• 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
Default Attribute Hierarchy – “ALL”
![Page 19: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/19.jpg)
Designing and Optimizing Custom Hierarchies
DEMO:Overview of an attribute hierarchy (default)
![Page 20: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/20.jpg)
User hierarchies
Designing and Optimizing Custom Hierarchies
• Attribute relationships are essential for significant server optimizations.
Blue Squiggly Lines
![Page 21: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/21.jpg)
Source of 90% ofCube Performance Gains
User hierarchies: Attribute Relationships
Designing and Optimizing Custom Hierarchies
![Page 22: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/22.jpg)
User hierarchies: Attribute Relationships
Designing and Optimizing Custom Hierarchies
Inefficient Efficient
More GranularMore DetailLower Level
Less GranularLess Detail
Higher Level
![Page 23: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/23.jpg)
Designing and Optimizing Custom Hierarchies
DEMO:Optimizing user hierarchies with • Attribute Relationships • Discretization• Aggregations
![Page 24: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/24.jpg)
Parent-child hierarchies
Designing and Optimizing Custom Hierarchies
Self-referencing relationship or self-join
![Page 25: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/25.jpg)
Designing and Optimizing Custom Hierarchies
DEMO:Overview of a Parent-Child Hierarchy
![Page 26: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/26.jpg)
Dimension and Attribute Types
Designing and Optimizing Custom Hierarchies
Dimension Types Attribute Types
![Page 27: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/27.jpg)
Designing and Optimizing Custom Hierarchies
Summary:• Query performance• Storage • Processing time
![Page 28: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/28.jpg)
Data Type Query Storage Processinginteger, bigint
String, GUIDs
Impact of Data Types
Designing and Optimizing Custom Hierarchies
![Page 29: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/29.jpg)
Relationship Type Process Option Query Storage ProcessingFlexible Incremental
Full
Rigid Incremental
Full
Impact of Flexible vs. Rigid Attribute Relationship Types
Designing and Optimizing Custom Hierarchies
• Process Full will re-compute aggregations for both Flexible & Rigid
![Page 30: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/30.jpg)
Property Query Storage ProcessingIsAggregatable = FalseNo default “All” attribute hierarchy
AttributeHierarchyOptimizedState = False No Aggregations
AttributeHierarchyOrdered = False No Sorting
Estimated Count <> 0Member count stats
OrderBy = Key (integer)Order column
DiscretizationMethod <> None
Impact of Attribute properties
Designing and Optimizing Custom Hierarchies
![Page 31: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/31.jpg)
Query Storage Processing
Impact of Aggregations
Designing and Optimizing Custom Hierarchies
…too much of a good thing is not good.
Query Storage Processing
![Page 32: Designing and Optimizing Hierarchies](https://reader037.vdocuments.us/reader037/viewer/2022102818/56812b0c550346895d8ef5a4/html5/thumbnails/32.jpg)
Thank you for attending!
Blog: http://www.sqljoe.comTwitter: http://www.twitter.com/sqljoeLinked-in: http://www.linkedin.com/in/josechinchillaEmail: [email protected]
Rate my presentationhttp://www.speakerrate.com/speakers/8064-jchinchilla
Questions?