Visual Intelligence
Microsoft Tools and Techniques for Visualizing Data
Published on 29. April 2013
Book
Paperback/Softback
400 pages
978-1-118-38803-7 (ISBN)
Description
Go beyond design concepts and learn to build state-of-the-art visualizations
The visualization experts at Microsoft's Pragmatic Works have created a full-color, step-by-step guide to building specific types of visualizations. The book thoroughly covers the Microsoft toolset for data analysis and visualization, including Excel, and explores best practices for choosing a data visualization design, selecting tools from the Microsoft stack, and building a dynamic data visualization from start to finish. You'll examine different types of visualizations, their strengths and weaknesses, and when to use each one.
* Data visualization tools unlock the stories within the data, enabling you to present it in a way that is useful for making business decisions
* This full-color guide introduces data visualization design concepts, then explains the various Microsoft tools used to store and display data
* Features a detailed discussion of various classes of visualizations, their uses, and the appropriate tools for each
* Includes practical implementations of various visualizations and best practices for using them
* Covers out-of-the-box Microsoft tools, custom-developed illustrations and implementations, and code examples
Visual Intelligence: Microsoft Tools and Techniques for Visualizing Data arms you with best practices and the knowledge to choose and build dynamic data visualizations.
The visualization experts at Microsoft's Pragmatic Works have created a full-color, step-by-step guide to building specific types of visualizations. The book thoroughly covers the Microsoft toolset for data analysis and visualization, including Excel, and explores best practices for choosing a data visualization design, selecting tools from the Microsoft stack, and building a dynamic data visualization from start to finish. You'll examine different types of visualizations, their strengths and weaknesses, and when to use each one.
* Data visualization tools unlock the stories within the data, enabling you to present it in a way that is useful for making business decisions
* This full-color guide introduces data visualization design concepts, then explains the various Microsoft tools used to store and display data
* Features a detailed discussion of various classes of visualizations, their uses, and the appropriate tools for each
* Includes practical implementations of various visualizations and best practices for using them
* Covers out-of-the-box Microsoft tools, custom-developed illustrations and implementations, and code examples
Visual Intelligence: Microsoft Tools and Techniques for Visualizing Data arms you with best practices and the knowledge to choose and build dynamic data visualizations.
More details
Persons
Mark Stacey founded Pragmatic Works South Africa (a company specializing in data management and presentation) and Aphelion Software (software for visualization). He has developed visualizations for major corporations and the South African government. Joe Salvatore is a Business Intelligence Architect with Pragmatic Works. He has been a database architect, business intelligence developer, and application developer for more than 15 years. Adam Jorgensen is President of Pragmatic Works, Director at Large for the Professional Association of SQL Server, and a Microsoft SQL Server MVP. He regularly speaks at industry events.
Content
Introduction xxiii
Part I Introduction to Data Visualization 1
1 Fundamentals of Visualization 3
Data Visualization versus Artistic Visualization 4
The Place of Infographics 7
Using 3D Effectively 7
The Illusion of Depth 8
Additional Dimensions 9
A Description of the Problem and a Proposed Solution 10
Summary 11
2 Designing a Visualization 13
Goals of Visualization 13
Human Perceptual Abilities 15
Strategic, Tactical, and Operational Views 18
Glance and Go versus Data Exploration 21
Using Color in Visualizations 23
Use of Perspective and Shape 28
Summary 31
Part II Microsoft's Toolset for Visualizing Data 33
3 The Microsoft Toolset 35
A Brief History 35
Database Tools 40
The Place of Each Front-End Tool 44
Installing the Sample Databases 46
Summary 51
4 Building Data Sets to Support Visualization 53
What Data Sets Are 53
Why We Need Them 53
How Data Sets Are Created 54
Why Data Sets Are Important 54
Common Data Set Elements 54
Data Quality 55
Metadata 55
Formatting 56
Data Volume 56
Automated Data 57
Types of Data Sets and Sources 57
Data in the Internet Age 58
Spreadsheets 58
When to Store Data in a Spreadsheet 58
SQL Tables 59
OLAP and Tabular Models 60
Reports and Data Feeds 60
Hadoop and Other Nonrelational Sources 61
Creating Data Sets for Visualization 62
Copy and Paste 62
Exporting Data from Systems 62
Import Techniques and Tools 62
Getting Started 63
Your First Data Set 63
Your First Data Set 63
Getting Data 64
Cleaning Your Data 65
Moving Your Data into a Good Format for Visualization 66
Verifying Your Data by Prototyping 67
Summary 68
5 Excel and PowerPivot 69
What are Excel and PowerPivot? 69
PowerPivot versus BISM versus Analysis Services 69
Column Stores 73
Multidimensional versus In Memory Models 75
Creating Your First PowerPivot Model 75
Step 1: Understand Your Data 76
Step 2: Create Your First Model 76
Step 3: Does Your Model Work? 81
What Does Excel Do for Me? 82
Pivot Charts and Tables 82
Summary 86
6 Power View 87
What Is Power View? 87
BISM: The First Requirement for Power View 90
Creating a Power View Report 91
Creating a Data Source 91
Creating a New Power View Report in Excel 91
Enhancing Data Models for Power View 97
Cleaning Up Your Data Model 97
Adding Metadata for Power View 98
Sharing Power View Reports 100
Publish in SharePoint 101
Exporting to PowerPoint 103
Installing the Power View Samples 103
Summary 104
7 PerformancePoint 105
Tabular versus Multidimensional Sources 105
Requirements for Running PerformancePoint 106
SharePoint Requirements 106
Authentication Issues when Using Secure Store Service 108
KPIs, Scorecards, Filters, Reports, and Dashboards 110
Creating a Data Source 110
Mapping the Time Dimension 112
KPIs 118
Scorecards 120
Filters 121
Analytic Reports 122
Dashboards 123
Combining Visualizations in PerformancePoint 124
Embedding an SSRS Report 125
Embedding Excel Reports 125
Creating Web Part Pages in SharePoint 126
Adding Web Parts 127
PerformancePoint Connections 128
Installing the PerformancePoint Samples 130
Summary 132
8 Reporting Services 133
Native versus Integrated Mode 133
Native Mode 134
SharePoint Integrated Mode 134
Shared and Embedded Data Sources 136
Authentication: A Better Solution 137
The Double Hop Problem 138
Set Execution Context: Requirements and Setup 138
Expressions in Reporting Services 140
Business Intelligence Development Studio and
Visual Studio versus Report Builder 141
Installing the Reporting Services Samples 145
Summary 146
9 Custom Code 147
Silverlight, WPF, XAML, and HTML5 147
The Future of Silverlight 150
Accessing Data from HTML5 151
Installing the HTML5 samples 152
A Web Service Sample in C# 154
Summary 167
Part III Visual Analytics in Practice 169
10 Scorecards and Indicators 171
A Quick Understanding: Glance and Go 172
KPIs 172
Drill Down 173
Drill Through 174
Drill Across 174
Tool Choices, with Examples 175
PerformancePoint 175
Excel 177
Implementation Examples 179
Implementing a Scorecard in Excel 179
PerformancePoint Services (PPS) Scorecard: Traffic Lights 182
Custom Indicators in PerformancePoint 187
Summary 189
11 Timelines 191
Types of Temporal Analysis Visualization 192
Timelines 194
Line Charts 195
Bar and Column Charts 197
Combined Charting 198
Scatter Plots and Bubble Charts 199
Tiling 200
Animation 201
Tool Choices, with Examples 202
PerformancePoint Services (PPS) 202
SQL Server Reporting Services (SSRS) 205
Excel 208
Power View 210
Implementation Examples 214
Power View Animated Scatter Plot 214
Combining Lines and Columns in Excel 218
A Drillable Line Chart in PPS 222
A Data-Driven Timeline Using SSRS and Data Bars 223
Summary 226
12 Comparison Visuals 227
Overview of Point-in-Time Comparisons 227
Explaining Perspective and Perceiving Comparisons 229
Pie Charts Versus Bar Charts 230
Bullet Charts 231
Radar Charts 232
Matrices 233
Custom Comparisons 236
Tool Choices, with Examples 237
PerformancePoint Services 237
SSRS 238
Excel 238
Power View 240
HTML5 241
Implementation Examples 242
PerformancePoint: Column Graphs 242
Excel: Multiple Axes and Scale Breaks 244
Excel: Radar Charts 252
SSRS: A Bullet Chart 254
HTML5 261
Summary 267
13 Slice and Dice: Ad Hoc Analytics 269
Explanation of Terms 270
Self-Service BI 270
The Place of PowerPivot 271
Definitions 272
Tool Choices with Examples 278
PerformancePoint: Analytic Charts 278
PerformancePoint: Drill Across 280
Excel Pivot Tables 281
SSRS Drill Down and Drill Through 282
Power View 283
Implementation Examples 284
SSRS: Dynamic Measures 284
Integrating PPS and SSRS on a Single Page 290
Power View: Exploring Data 295
Summary 298
14 Relationship Analysis 299
Visualizing Relationships: Nodes, Trees, and Leaves 299
Network Maps 301
Color Wheel 302
Tree Structures: Organization Charts and Other Hierarchies 305
Strategy Maps 306
Tool Choices 307
PPS Decomposition Tree 307
Excel and NodeXL 308
PerformancePoint Services (PPS) Strategy Maps 309
HTML5 Structure Maps 310
Implementation Examples 311
Building an Organization Chart in PerformancePoint 311
Building a Network Map in HTML5 316
Color Wheel in HTML5 317
Summary 320
15 Embedded Visualizations 321
Tabular Data: Adding Visual Acuity 322
Embedded Charts: Sparklines and Bars 323
Conditional Formatting 325
Indicators 326
Bullet Graphs 327
Tool Choices with Examples 329
Excel 329
SQL Server Reporting Services (SSRS) 330
PerformancePoint 331
Implementation Examples 331
Embedding Visualizations on a Pivot Table 331
Summary 339
16 Other Visualizations 341
Traditional Infographics 341
Periodic Tables 342
Swim Lanes 344
Transportation Maps 344
Mind Maps 347
Venn Diagram 347
CAD Drawings 348
3D Modeling 349
Funnels 349
Flow Diagrams 350
Geographic Information System Maps 352
Heatmaps 355
Summary 355
A Choosing a Microsoft Tool 357
Strengths and Weaknesses of Each Tool 357
PerformancePoint 357
Reporting Services 358
Excel/Excel Services 359
Power View 360
HTML5 361
Matching a Visualization to a Tool 362
B DAX Function Reference 369
Date and Time Functions 369
Filter Functions 371
Information Functions 372
Lookup Functions 373
Parent-Child Functions 373
Logical Functions 374
Text Functions 375
Statistical Functions 378
Math and Trig Functions 379
Time Intelligence Functions 381
Index 385
Part I Introduction to Data Visualization 1
1 Fundamentals of Visualization 3
Data Visualization versus Artistic Visualization 4
The Place of Infographics 7
Using 3D Effectively 7
The Illusion of Depth 8
Additional Dimensions 9
A Description of the Problem and a Proposed Solution 10
Summary 11
2 Designing a Visualization 13
Goals of Visualization 13
Human Perceptual Abilities 15
Strategic, Tactical, and Operational Views 18
Glance and Go versus Data Exploration 21
Using Color in Visualizations 23
Use of Perspective and Shape 28
Summary 31
Part II Microsoft's Toolset for Visualizing Data 33
3 The Microsoft Toolset 35
A Brief History 35
Database Tools 40
The Place of Each Front-End Tool 44
Installing the Sample Databases 46
Summary 51
4 Building Data Sets to Support Visualization 53
What Data Sets Are 53
Why We Need Them 53
How Data Sets Are Created 54
Why Data Sets Are Important 54
Common Data Set Elements 54
Data Quality 55
Metadata 55
Formatting 56
Data Volume 56
Automated Data 57
Types of Data Sets and Sources 57
Data in the Internet Age 58
Spreadsheets 58
When to Store Data in a Spreadsheet 58
SQL Tables 59
OLAP and Tabular Models 60
Reports and Data Feeds 60
Hadoop and Other Nonrelational Sources 61
Creating Data Sets for Visualization 62
Copy and Paste 62
Exporting Data from Systems 62
Import Techniques and Tools 62
Getting Started 63
Your First Data Set 63
Your First Data Set 63
Getting Data 64
Cleaning Your Data 65
Moving Your Data into a Good Format for Visualization 66
Verifying Your Data by Prototyping 67
Summary 68
5 Excel and PowerPivot 69
What are Excel and PowerPivot? 69
PowerPivot versus BISM versus Analysis Services 69
Column Stores 73
Multidimensional versus In Memory Models 75
Creating Your First PowerPivot Model 75
Step 1: Understand Your Data 76
Step 2: Create Your First Model 76
Step 3: Does Your Model Work? 81
What Does Excel Do for Me? 82
Pivot Charts and Tables 82
Summary 86
6 Power View 87
What Is Power View? 87
BISM: The First Requirement for Power View 90
Creating a Power View Report 91
Creating a Data Source 91
Creating a New Power View Report in Excel 91
Enhancing Data Models for Power View 97
Cleaning Up Your Data Model 97
Adding Metadata for Power View 98
Sharing Power View Reports 100
Publish in SharePoint 101
Exporting to PowerPoint 103
Installing the Power View Samples 103
Summary 104
7 PerformancePoint 105
Tabular versus Multidimensional Sources 105
Requirements for Running PerformancePoint 106
SharePoint Requirements 106
Authentication Issues when Using Secure Store Service 108
KPIs, Scorecards, Filters, Reports, and Dashboards 110
Creating a Data Source 110
Mapping the Time Dimension 112
KPIs 118
Scorecards 120
Filters 121
Analytic Reports 122
Dashboards 123
Combining Visualizations in PerformancePoint 124
Embedding an SSRS Report 125
Embedding Excel Reports 125
Creating Web Part Pages in SharePoint 126
Adding Web Parts 127
PerformancePoint Connections 128
Installing the PerformancePoint Samples 130
Summary 132
8 Reporting Services 133
Native versus Integrated Mode 133
Native Mode 134
SharePoint Integrated Mode 134
Shared and Embedded Data Sources 136
Authentication: A Better Solution 137
The Double Hop Problem 138
Set Execution Context: Requirements and Setup 138
Expressions in Reporting Services 140
Business Intelligence Development Studio and
Visual Studio versus Report Builder 141
Installing the Reporting Services Samples 145
Summary 146
9 Custom Code 147
Silverlight, WPF, XAML, and HTML5 147
The Future of Silverlight 150
Accessing Data from HTML5 151
Installing the HTML5 samples 152
A Web Service Sample in C# 154
Summary 167
Part III Visual Analytics in Practice 169
10 Scorecards and Indicators 171
A Quick Understanding: Glance and Go 172
KPIs 172
Drill Down 173
Drill Through 174
Drill Across 174
Tool Choices, with Examples 175
PerformancePoint 175
Excel 177
Implementation Examples 179
Implementing a Scorecard in Excel 179
PerformancePoint Services (PPS) Scorecard: Traffic Lights 182
Custom Indicators in PerformancePoint 187
Summary 189
11 Timelines 191
Types of Temporal Analysis Visualization 192
Timelines 194
Line Charts 195
Bar and Column Charts 197
Combined Charting 198
Scatter Plots and Bubble Charts 199
Tiling 200
Animation 201
Tool Choices, with Examples 202
PerformancePoint Services (PPS) 202
SQL Server Reporting Services (SSRS) 205
Excel 208
Power View 210
Implementation Examples 214
Power View Animated Scatter Plot 214
Combining Lines and Columns in Excel 218
A Drillable Line Chart in PPS 222
A Data-Driven Timeline Using SSRS and Data Bars 223
Summary 226
12 Comparison Visuals 227
Overview of Point-in-Time Comparisons 227
Explaining Perspective and Perceiving Comparisons 229
Pie Charts Versus Bar Charts 230
Bullet Charts 231
Radar Charts 232
Matrices 233
Custom Comparisons 236
Tool Choices, with Examples 237
PerformancePoint Services 237
SSRS 238
Excel 238
Power View 240
HTML5 241
Implementation Examples 242
PerformancePoint: Column Graphs 242
Excel: Multiple Axes and Scale Breaks 244
Excel: Radar Charts 252
SSRS: A Bullet Chart 254
HTML5 261
Summary 267
13 Slice and Dice: Ad Hoc Analytics 269
Explanation of Terms 270
Self-Service BI 270
The Place of PowerPivot 271
Definitions 272
Tool Choices with Examples 278
PerformancePoint: Analytic Charts 278
PerformancePoint: Drill Across 280
Excel Pivot Tables 281
SSRS Drill Down and Drill Through 282
Power View 283
Implementation Examples 284
SSRS: Dynamic Measures 284
Integrating PPS and SSRS on a Single Page 290
Power View: Exploring Data 295
Summary 298
14 Relationship Analysis 299
Visualizing Relationships: Nodes, Trees, and Leaves 299
Network Maps 301
Color Wheel 302
Tree Structures: Organization Charts and Other Hierarchies 305
Strategy Maps 306
Tool Choices 307
PPS Decomposition Tree 307
Excel and NodeXL 308
PerformancePoint Services (PPS) Strategy Maps 309
HTML5 Structure Maps 310
Implementation Examples 311
Building an Organization Chart in PerformancePoint 311
Building a Network Map in HTML5 316
Color Wheel in HTML5 317
Summary 320
15 Embedded Visualizations 321
Tabular Data: Adding Visual Acuity 322
Embedded Charts: Sparklines and Bars 323
Conditional Formatting 325
Indicators 326
Bullet Graphs 327
Tool Choices with Examples 329
Excel 329
SQL Server Reporting Services (SSRS) 330
PerformancePoint 331
Implementation Examples 331
Embedding Visualizations on a Pivot Table 331
Summary 339
16 Other Visualizations 341
Traditional Infographics 341
Periodic Tables 342
Swim Lanes 344
Transportation Maps 344
Mind Maps 347
Venn Diagram 347
CAD Drawings 348
3D Modeling 349
Funnels 349
Flow Diagrams 350
Geographic Information System Maps 352
Heatmaps 355
Summary 355
A Choosing a Microsoft Tool 357
Strengths and Weaknesses of Each Tool 357
PerformancePoint 357
Reporting Services 358
Excel/Excel Services 359
Power View 360
HTML5 361
Matching a Visualization to a Tool 362
B DAX Function Reference 369
Date and Time Functions 369
Filter Functions 371
Information Functions 372
Lookup Functions 373
Parent-Child Functions 373
Logical Functions 374
Text Functions 375
Statistical Functions 378
Math and Trig Functions 379
Time Intelligence Functions 381
Index 385