Designing and optimizing hierarchies
Download
1 / 32

Designing and Optimizing Hierarchies - PowerPoint PPT Presentation


  • 164 Views
  • Uploaded on

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:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Designing and Optimizing Hierarchies' - yuri-thompson


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


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

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



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

[email protected]

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

Questions?


ad