Print this page Share

Visual Intelligence: Microsoft Tools and Techniques for Visualizing Data

ISBN: 978-1-118-38803-7
400 pages
April 2013
Visual Intelligence: Microsoft Tools and Techniques for Visualizing Data (1118388038) cover image


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.

See More

Table of Contents

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

See More

Author Information

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.

See More


Download TitleSizeDownload
Complete code download for Visual Intelligence
Updated 6/25/13
32.39 MB Click to Download
See More

Related Titles

Back to Top