Introduction

Visualization, both in the form of traditional business intelligence (BI) and as infographics (such as those of The New York Times) has become a vital part of communication in business, in the media, and in government.

Much work has been done on the art and science of visualization, and just as much has been done on specific technology implementations. However, less work has been done on the intersection between these, making it difficult to choose the best tool to implement a specific visualization. Visual Intelligence: Microsoft Tools and Techniques for Visualizing Data bridges this gap, helping you to choose the right visualization and the right tool to implement the visualization, and then to guide you in the best way to implement that visualization.

Overview of the Book and Technology

This book focuses on the Microsoft platform. With the ubiquity of Microsoft products such as Excel, and to a smaller extent SQL Server and SharePoint, almost everyone has access to a Microsoft tool. Indeed, anyone working on a Windows PC can freely download SQL Server Express and gain access to Reporting Services.

The biggest challenge in the Microsoft BI platform is the spread of the capabilities across the platform. With products from the SQL team and the Office team often overlapping, it can be challenging to know when to use one tool as opposed to another, and there is often a tendency to try to make just one tool (most commonly Excel!) fit all use cases. In this book, you find out how to avoid this pitfall and choose the correct tool for the task at hand.

How This Book Is Organized

In order to visualize data, you need to understand why visualization is a good representation of the data. This involves a good understanding of the data you are analyzing, a good understanding of which visualizations are appropriate, and an in-depth knowledge of what your toolset is capable of representing visually. Part 1 of this book starts with a review of the history and science of visualization, explaining what research has discovered about human perception, and how this science can be applied to building a visualization.

Part 2 explains how to bring data into the platform to do visualizations. Building on the basics covered in Part 1, we give you an overview of the capabilities of the Microsoft platform and dive into the shortcomings that can be solved using custom code. We take some time to explain the place of Silverlight and how it is being changed with the advent of HTML5, and we show you an approach that enables you to build for either platform. We also discuss the future of Microsoft user interface (UI) technologies using XAML.

The real value of the book is in Part 3. Each chapter in the part explains a particular family of visualizations, starting with the science behind the visualization and what types of data it is appropriate for. This section of the book is appropriate for any person regardless of the technology he is using. The part continues by helping you choose the best technology on the Microsoft stack; we provide practical real-world implementations along with code samples.

Part 1: Introduction to Data Visualization

Chapter 1 introduces the concept of visualization, and differentiates between data-driven visualization and infographics. It also discusses the place of 3D.

Chapter 2 talks about designing a visualization, and what techniques need to be applied as you decide what effects to use.

Part 2: Microsoft's Toolset for Visualizing Data

Chapter 3 discusses the Microsoft toolset and introduces you to the various tools.

Chapter 4 explains the basics of building a data set to support your visualization.

Chapter 5 discusses the tool everyone loves (or loves to hate)—Excel. It also covers the data-processing tool embedded in Excel 2013: PowerPivot,

Chapter 6 introduces the new data exploration tool called Power View.

Chapter 7 covers the dashboarding and performance management tool called PerformancePoint.

In Chapter 8 you get an introduction to Reporting Services.

Chapter 9 covers the custom code development tools—the future of Silverlight (or lack thereof) is covered, followed by HTML5, and building web services to supply datasets.

Part 3: Visual Analytics in Practice

Chapter 10 focuses on scorecards and indicators; it explains how you can use them to monitor your business.

In Chapter 11, you find out how to analyze data that changes over time.

Comparing data points is one of the fundamentals of visualization, and in Chapter 12 you work through the different ways of doing so.

Chapter 13 covers a subject that has been at the core of many BI tools—slice and dice, also called ad hoc analytics.

Chapter 14 covers a visualization that has become much more important in the modern world of social media: relationship analysis.

In Chapter 15, we cover the art of embedding visualizations in tables.

Chapter 16 talks about visualizations that are not covered in depth in this book. Many of the visualizations are very useful, but they are not built into the Microsoft toolset.

Appendix A is a cheat-sheet to help you choose a visualization tool based on the visual you want to achieve.

Appendix B is a DAX (Data Analysis Expressions) function reference. DAX is used by both PowerPivot and the Tabular mode of Analysis Services, and you will find it is greatly useful for data analysis.

Who Should Read This Book

The target reader for this book is a data professional who needs to present data. By data professional we mean anyone who works with data—from sales managers and accountants to BI professionals.

The primary audience is people with a moderately technical inclination who are comfortable learning new technologies and/or adding to the understanding they already have of the technologies. A basic knowledge of Excel will suffice for many of the implementation examples, and this book starts by introducing you to new tools.

The secondary audience is those people who, despite not implementing any visualizations themselves, need to understand how to choose a visualization, such as journalists who need to present data to their readers. This audience includes business analysts who need to know what visualizations to choose for development, and anybody who reads modern publications, consumes infographics, and needs to know how to interpret them.

Industry and Technology Trends

BI, and its front-facing discipline visualization, is changing from a back-office activity of mega-corporates to a mainstream activity conducted by small companies, bloggers, journalists, and departments in a company. This has been partly driven by a change in the world to a data-driven culture and is driven partly by an improvement in the tools—both in capabilities and in affordability.

There are two major changes in the technology space that inspired this book: One is Microsoft specific, and the other is a general sea change. The release of Microsoft SQL Server 2012 is a driving force for end-user adoption. Data integration visualization tools, in the form of PowerPivot and Power View, are coming to the less technical end user for the first time. In the world as a whole, the adoption of a standard web-based technology, HTML5, for the presentation of highly interactive user interfaces, has led to the wholesale adoption of more interactive data graphics, which are automatically cross-platform.

Both of these changes allow more people to implement data visualization, but they also require some re-education on the part of existing implementers.

Tools You Need

This book covers the entire Microsoft stack, and different tools are shown throughout. You don’t need any of the tools listed in the following sections to understand the material that’s presented in the book, but the implementation examples do require them.

Versions

Excel is the most important tool, and the examples shown are all built in Excel 2013. Most of the implementation samples work with Excel 2010, and a good portion also work with Excel 2007.

The PowerPivot add-in is required for Excel 2010. Excel 2007 requires an Analysis Services instance from SQL 2012 because PowerPivot is not available for that version of Excel. Excel 2013 requires the Professional Plus Edition to use PowerPivot.

SQL Server 2012 is used for the Reporting Services component, as well as for the databases that contain the data—both SQL databases and Analysis Services. SQL Server 2008 R2 Reporting Services can do most of the visualizations listed, but 2012 is required to install any of the samples.

The PerformancePoint examples require SharePoint 2010 Enterprise Edition, but they work without modification in SharePoint 2013.

Excel and PowerPivot

Excel has always been the world’s foremost data-analysis tool for the end user, and with the advent of the PowerPivot add-in it has become even stronger. We work primarily on Excel 2013 (with and without PowerPivot), but we highlight when a particular visualization also works on earlier versions. Most of the work in PowerPivot will be possible in Excel 2010 as well.

Power View

Power View is part of the SQL Server 2012 release, and it’s also embedded in Excel 2013. PowerView’s greatest attraction is its integration of animation components to show changes over time.

Reporting Services

Reporting Services is the reporting tool provided with SQL Server. It focuses primarily on the developer market. With rich customization abilities, in some ways it’s the most powerful of the Microsoft visualization tools, but it comes with a commensurately steep learning curve.

SQL Server Analysis Services (Multidimensional and Tabular Models)

Writing SQL queries to access data is not something the average person wants to do. Analysis Services provides a user-friendly layer as well as query acceleration that makes developing visualizations a quick and easy process.

SQL Server Analysis Services (SSAS) comes in two flavors: multi-dimensional (MDM) and tabular (BISM or Business Intelligence Semantic Model). The multi-dimensional model has not changed significantly from SQL 2008 to 2008R2 and 2012, so all code samples provided work in SQL 2008. Tabular is a new version released with 2012, and so SQL 2012 is required for those examples.

SharePoint Technologies: PerformancePoint and Excel Services

Excel Services is the web-based version of Excel, and as such all Excel Services discussions are the same as Excel.

PerformancePoint is the dedicated monitoring and analysis tool provided in SharePoint, and forms a large component of this book.

Custom Code: C#, Silverlight, XAML, and HTML5

There are some places where the Microsoft toolset falls short. In those cases, you have to write some code. The book covers the use of non-Microsoft HTML5 libraries such as InfoVis. These sections are explicitly highlighted, and you can skip them if you’re part of the non-developer audience.

The custom code samples used in the book cover several different technologies, and we include a discussion of the strengths and weaknesses of each technology. A chapter on the future of Silverlight (and the presentation language called XAML that is used both for Silverlight and other Microsoft presentation technologies) with the advent of HTML5 is included in the book, along with guidance to help you future-proof against the changeover from Silverlight to HTML5.

C# is covered as a way of providing a data provider to front end-only technologies such as Silverlight/HTML5, and for a code sample we provide a generic version that you can use for any of the visualizations in this book.

What's on the Website

Data for the implementation examples that you will work through have all been obtained from the Organization for Economic Development (OECD) at http://stats.oecd.org/. The data samples have been provided in SQL database form, as Analysis Services backups, and PowerPivot workbooks.

We have provided finished versions of all implementation examples.

In addition, code samples for the C# web service and the HTML5 visualizations are included in more detail than covered in the book.

The data sets, implementation examples, and code samples are available from this book's page at www.wiley.com/go/visualintelligence.