Designing and optimizing hierarchies
This presentation is the property of its rightful owner.
Sponsored Links
1 / 32

Designing and Optimizing Hierarchies PowerPoint PPT Presentation


  • 77 Views
  • Uploaded on
  • Presentation posted in: General

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:

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Designing and optimizing hierarchies

Designing and OptimizingHierarchies

Presented by:

Jose Chinchilla

MCP, MCTS, MCITP


Designing and optimizing hierarchies

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


Designing and optimizing hierarchies

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


Designing and optimizing hierarchies

Performance Tips & Best Practices

Test

Test

Test


Designing and optimizing hierarchies

Types & Classification of hierarchies

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Hierarchies everywhere


Designing and optimizing hierarchies

Corporate Hierarchy


Designing and optimizing hierarchies

Analysis Services Hierarchies

Q1

Jan

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Natural vs. Unnatural Hierarchies

Natural

Unnatural

Designing and Optimizing Custom Hierarchies


Designing and optimizing 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


Designing and optimizing hierarchies

Balanced, Unbalanced & Ragged Hierarchies

Balanced Hierarchy

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Balanced, Unbalanced & Ragged Hierarchies

Unbalanced Hierarchy

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Balanced, Unbalanced & Ragged Hierarchies

RaggedHierarchy

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Attribute Hierarchies vs. User Hierarchies

aka User Hierarchy

aka Attribute Hierarchy

Designing and Optimizing Custom Hierarchies


Designing and optimizing 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


Designing and optimizing hierarchies

DEMO:

Overview of an attribute hierarchy (default)

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

User hierarchies

Blue Squiggly Lines

  • Attribute relationships are essential for significant server optimizations.

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

User hierarchies: Attribute Relationships

Source of 90% of

Cube Performance Gains

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

User hierarchies: Attribute Relationships

Inefficient

Efficient

More Granular

More Detail

Lower Level

Less Granular

Less Detail

Higher Level

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

  • DEMO:

  • Optimizing user hierarchies with

    • Attribute Relationships

    • Discretization

    • Aggregations

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Parent-child hierarchies

Self-referencing relationship or self-join

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

DEMO:

Overview of a Parent-Child Hierarchy

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Dimension and Attribute Types

Dimension Types

Attribute Types

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

  • Summary:

    • Query performance

    • Storage

    • Processing time

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Impact of Data Types

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Impact of Flexible vs. Rigid Attribute Relationship Types

  • Process Full will re-compute aggregations for both Flexible & Rigid

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Impact of Attribute properties

Designing and Optimizing Custom Hierarchies


Designing and optimizing hierarchies

Impact of Aggregations

…too much of a good thing is not good.

Designing and Optimizing Custom Hierarchies


Designing and optimizing 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: [email protected]

[email protected]

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

Questions?


  • Login