designing and optimizing hierarchies

Designing and OptimizingHierarchies

Presented by:Jose Chinchilla

MCP, MCTS, MCITP

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: jchinchilla@pragmaticworks.comjchinchilla@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

PlusPerformance Tips &Best Practices

Performance Tips & Best Practices

TestTestTest

Types & Classification of hierarchies

Designing and Optimizing Custom Hierarchies

Hierarchies everywhere

Corporate Hierarchy

Analysis Services Hierarchies

Designing and Optimizing Custom Hierarchies

Q1

Jan

Natural vs. Unnatural Hierarchies

Designing and Optimizing Custom Hierarchies

Year

Quarter

Month

Day

NaturalModel

Style

Size

Color

Unnatural

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.

Balanced, Unbalanced & Ragged Hierarchies

Designing and Optimizing Custom Hierarchies

North America

United States

Florida

Miami

Canada

Ontario

Toronto

Mexico

Yucatan

Merida

Balanced Hierarchy

Balanced, Unbalanced & Ragged Hierarchies

Designing and Optimizing Custom Hierarchies

North America

United States

Florida

Miami

Canada

Ontario

Mexico

Unbalanced Hierarchy

Balanced, Unbalanced & Ragged Hierarchies

Designing and Optimizing Custom Hierarchies

RaggedHierarchy

United States

Florida

Miami

Attribute Hierarchies vs. User Hierarchies

aka Attribute Hierarchy aka User Hierarchy

Designing and Optimizing Custom Hierarchies

• 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”

Designing and Optimizing Custom Hierarchies

DEMO:Overview of an attribute hierarchy (default)

User hierarchies

Designing and Optimizing Custom Hierarchies

• Attribute relationships are essential for significant server optimizations.

Blue Squiggly Lines

Source of 90% ofCube Performance Gains

User hierarchies: Attribute Relationships

Designing and Optimizing Custom Hierarchies

User hierarchies: Attribute Relationships

Designing and Optimizing Custom Hierarchies

Inefficient Efficient

More GranularMore DetailLower Level

Less GranularLess Detail

Higher Level

Designing and Optimizing Custom Hierarchies

DEMO:Optimizing user hierarchies with • Attribute Relationships • Discretization• Aggregations

Parent-child hierarchies

Designing and Optimizing Custom Hierarchies

Self-referencing relationship or self-join

Designing and Optimizing Custom Hierarchies

DEMO:Overview of a Parent-Child Hierarchy

Dimension and Attribute Types

Designing and Optimizing Custom Hierarchies

Dimension Types Attribute Types

Designing and Optimizing Custom Hierarchies

Summary:• Query performance• Storage • Processing time

Data Type Query Storage Processinginteger, bigint

String, GUIDs

Impact of Data Types

Designing and Optimizing Custom Hierarchies

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

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

Query Storage Processing

Impact of Aggregations

Designing and Optimizing Custom Hierarchies

…too much of a good thing is not good.

Query Storage Processing

Thank you for attending!

Blog: http://www.sqljoe.comTwitter: http://www.twitter.com/sqljoeLinked-in: http://www.linkedin.com/in/josechinchillaEmail: jchinchilla@sqljoe.com

jchinchilla@pragmaticworks.com

Rate my presentationhttp://www.speakerrate.com/speakers/8064-jchinchilla

Questions?