Transcript What The Tabular?

INTRODUCTION TO TABULAR MODELS
WELCOME TO TABULAR
Dustin Ryan
@SQLDusty
WHO’S THIS GUY?
• Dustin Ryan – Senior B.I. Consultant & Trainer at
Pragmatic Works
• Data warehouse design, SSRS, SSIS, SSAS, SharePoint
BI, Power BI
• Author, contributor, technical editor
• PASS Summit, SQL Rally, SQL Saturday, Code Camp
• My family, sleep, preparing for the war against the
machines
AGENDA
• Making the right choice
• Building a Tabular Model
• Best Practices
• Resources
SSAS SHOWDOWN
• Cubes
• Tabular model
FEATURE PARITY
Multidimensional
Tabular
Power Pivot
Actions
Yes
No
No
Aggregations
Yes
No
No
Calculated Measures
Yes
Yes
Yes
Custom Assemblies
Yes
No
No
Custom Rollups
Yes
No
No
Distinct Count
Yes
Yes
(via DAX)
Yes
(via DAX)
Drill through
Yes
Yes
Yes
(detail opens in separate worksheet)
Hierarchies
Yes
Yes
Yes
KPIs
Yes
Yes
Yes
Linked objects
Yes
No
Yes
(linked tables)
Many-to-many relationships
Yes
No
No
Parent-child Hierarchies
Yes
Yes
(via DAX)
Yes
(via DAX)
Partitions
Yes
Yes
No
Perspectives
Yes
Yes
Yes
Semi-additive Measures
Yes
Yes
Yes
Translations
Yes
No
No
User-defined Hierarchies
Yes
Yes
Yes
Writeback
Yes
No
No
CONSIDERATIONS
• Scalability
• Performance
• Time to develop
• Complex business problems
• Learning curve
SCALABILITY
Tabular
Multidimensional
• In-Memory Technology (x-Velocity)
• Pre-Aggregated Data From Disk
• Can Store Large Amounts of Data
• Can Store Very Large Amounts of Data
• No Aggregations.
• Uses Aggregations to Increase Query
Column-Based Storage.
• Data Compression 10x
Performance
• Data Compression 3x
7
PERFORMANCE
Tabular
Multidimensional
• Generally Speaking Tabular will perform faster
• Pre-Aggregated Data From Disk
• Tabular Engine Does Not Require a Great Deal
• Can Store Very Large Amounts of Data
of Performance Tuning
• Best at Returning Low Granularity Data
• Need memory optimized hardware
• Uses Aggregations to Increase Query
Performance
• Often Faster Than Tabular When Pulling From
Warm Cache
• Need storage optimized hardware
8
TIME TO DEVELOP
Tabular
• By Far Less Time to Develop
• Long Planning and Development Cycles
• Can Upgrade From Power Pivot
• No Upgrade Path
• Does Not Require Dimensional Model (But it’s
• Requires Dimensional Model
still a good idea)
• Much Simpler Interface for Creating Model
9
Multidimensional
COMPLEX BUSINESS PROBLEMS
Tabular
• Can Handle Complex Relationships with DAX
Multidimensional
• Has Built-in Capabilities for Complex Relationships
• Role Playing
• Parent-Child
• Many-to-many
10
LEARNING CURVE
Tabular
• Uses DAX (Data Analysis Expressions) for Query
Language
• If You Know Excel Formulas Then DAX Will Be
Easy
11
Multidimensional
• Uses MDX (Multi-Dimensional Expressions) for
Query Language
• Difficult to Learn but has Benefits (Navigating
Hierarchies)
• Simple Drag and Drop KPI Creation
• More Complex KPI Creation
• Relationships are simple (no composite keys)
• Multiple relationship types
OTHER CONSIDERATIONS
After you Choose…
There is No Migration Path to the Other Technology
Tabular Best on its Own Machine, Not a Good Candidate for a Shared Service due
to differences in hardware requirements
12
THE BOTTOM LINE
Consider Tabular…
If You Have a Short Development Timeline
If You are Working with enough Memory (2 – 3 x more memory than required for data)
If You Data Model is Simple
If You Have Many Disparate Data Sources
If Users Need to Query Large Amounts of Detail Data
Consider Multidimensional…
If You are Using SQL Server 2008 R2 or Earlier
If You Have a Multi-Terabyte Data Source
If You Have a Complex Data Model
If You Need Multidimensional only features (Actions, Data Mining, Writeback, Translations)
13
TO THE TABULAR MODEL!
14
RESOURCES
SQL Server 2012 Analysis Services The BISM Tabular Model (link)
Practical PowerPivot & DAX Formulas for Excel 2010 (link)
Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services
Whitepaper (link)
15
CONTACT ME
@SQLDusty (link)
[email protected] (link)
Thanks for attending my session!
16