In the Real WorldVisualizing Data

In the Real World Visualizing Data

Database developers, who deal with tables and query result sets on a daily basis, tend to forget that consumers of their products aren't necessarily fond of tabular data. Having to scan let alone digest reams of tabular data, whether on paper or PC monitor, is one of the curses of the cubicle.

Management executives primarily are interested in trends and exceptions. Only when trends go the wrong way or exceptions hit the bottom line are suits (middle management) or pinstripes (top execs, bankers, eastern venture capitalists) likely to be interested in detail data. Well-designed PivotTables let users control the amount of detail displayed, subject to the performance issues that result from using very large data sets to provide multiple drill-down levels. PivotChart's Drill Into and Out feature makes drill-down simple enough for management types to use.

The Perils of Pivot Manipulation

Before the advent of PivotTables and PivotCharts, application programmers had to design individual forms or write complex VBA code to let users slice and dice data according to their preferences. PivotTables and PivotCharts in Access forms and DAP let users choose the fields to display, change category hierarchies, and swap axes. The problem with this approach, of course, is that users who aren't familiar with PivotTables and PivotCharts get into trouble and call for help. After a few calls from users who've lost field buttons, it's tempting to lock down the view by clearing all the check boxes except ScreenTips in the Show/Hide page of the PivotChart's Properties dialog.

A better alternative to lockdown is user education. PivotTables are the cornerstone of Microsoft's approach to delivering crosstabs from SQL Server and other client/server tables linked to SQL Server databases. As Digital Dashboards and Web Parts for delivering Web-based information become more popular, you can expect PivotCharts to play an important role in all data-intensive presentation formats. Design an illustrated Web page that shows users how to work with PivotTables and PivotCharts in general. Add a link to the page on your DAP or forms that use these views. Writing a Web page is far easier and faster than designing a full-fledged online help file.

Meaning, Significance, and Visualization

Data becomes information when one grasps the meaning and significance of the data. Well-designed charts and graphs based on summary queries make the data contained in millions of rows of transactional tables meaningful. The significance of information is in the eye of the beholder. If your bonus is based on sales, trends in sales determine much of your income; if you're a profit-sharing participant, the bottom line counts the most. As noted at the beginning of the chapter, time-series graphs and charts are most common, because spotting trends and taking action based on trends is one of management's primary responsibilities. Trends inherently are historical in nature; regression analysis and other statistical methods enable projecting historical performance to the future with varying degrees of risk. In many cases, the experienced eye of a seasoned executive can better project future trends than the most sophisticated statistical algorithms.

Data visualization, the foundation of graphs based on queries, is more of an art than a science. Edward R. Tufte's self-published 1983 classic, The Visual Display of Quantitative Information, is still a bestseller at least by computer book standards. Tufte's sequel, Envisioning Information (1990), deals primarily with cartography. The final volume of the trilogy, Visual Explanations: Images and Quantities, Evidence and Narrative (1997), covers presentation of dynamic data. Tufte describes his three books as "pictures of numbers, pictures of nouns, and pictures of verbs." Anyone designing Access graphs and charts for any purpose other than entertainment should own a copy of The Visual Display of Quantitative Information. After you become acquainted with Tufte's seminal work, you're very likely to acquire his other two books.

Management by Trend Exception

Most managers and executives suffer from information overload. One of the approaches to making information delivered to management more effective is to flag situations in which performance falls outside of the expected or budgeted range. Multiline graphs, which present actual versus projected performance, are especially useful for flagging poor or exceptional results at the region, department, division, or corporate level. Regression methods often are more useful in actual-versus-budgeted graphs, because extrapolated trend lines that cross budget lines in the wrong direction are immediately visible to the most harried executive. Adding budgetary data usually requires nested queries, views, or functions to combine summary results from transaction data and presummarized budget data.