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
