The evidence for big data is scant at best. To date, large fields of data have generated meaningful insights at times, but not on the scale many have promised…Yet, for years now, corporations and public organizations have been busy buying huge servers and business intelligence software, pushed by technology providers and consultants armed with sales pitches with colorful anecdotes such as the Moneyball story in which general manager Billy Beane triumphed by using player statistics to predict the winning strategies for the Oakland A’s baseball team. If it worked for Billy Beane, it will work for your global multinational, too, right? Well, no.

The worship of big data is not new. Twenty-five years ago, technology salespeople peddled data using an old story about a retailer that spotted a correlation between diaper purchases and beer drinking, allowing a juicy cross-promotion of the two products for young fathers. Today, most data warehouses are glorified repositories of transaction data, with very little intelligence.

Working with multinationals as a management consultant, I have chased big data insights all my life and have never found them. What I have learned, however, is that local data has a lot of value. Put another way, big data is pretty useless, but small data is a rich source of insights. The probability of discovering new relationships at a local, highly contextual level and expanding it to universal insights is much higher than of uncovering a new law from the massive crunching of large amounts of data.

Read Gouilart’s article in full and pass it on. It’s time to usher in a quiet voice of sanity in this noisy, naive world of “more is better.”

Take care,

Predictive analytics is a computer-based application of statistics that has grown out of an academic discipline that is traditionally called machine learning. Yes, even though computers can’t think, they can learn (i.e., acquire useful knowledge from data). Siegel defines predictive analytics as “technology that learns from experience (data) to predict the future behavior of individuals in order to drive better decisions.” (p. 11)

I appreciate the fact that Siegel doesn’t gush about the wonders of data and technology to the hyperbolic degree that is common today; he keeps a level head as he describes what can be done in realistic and practical terms. Here’s what he says about data:

As data piles up, we have ourselves a genuine gold rush. But data isn’t the gold. I repeat, data in its raw form is boring crud. The gold is what’s discovered therein. (p. 4)

And again here:

Big data does not exist. The elephant in the room is that there is no elephant in the room. What’s exciting about data isn’t how much of it there is, but how quickly it is growing. We’re in a persistent state of awe at data’s sheer quantity because of one thing that does not change: There’s always so much more today than yesterday. Size is relative, not absolute. If we use the word big today, we’ll quickly run out of adjectives: “big data,” “bigger data,” “even bigger data,” and “biggest data,” The International Conference on Very Large Databases has bee running since 1975. We have a dearth of vocabulary with which to describe a wealth of data…

There’s a ton of it—so what? What guarantees that all this residual rubbish, this by-product of organizational functions, holds value? It’s no more than an extremely long list of observed events, an obsessive-compulsive enumeration of things that have happened.

The answer is simple. Everything is connected to everything else—if only indirectly—and this is reflected in data…

Data always speaks. It always has a story to tell, and there’s always something to learn from it…Pull some data together and, although you can never be certain what you’ll find, you can be sure you’ll discover valuable connections by decoding the language it speaks and listening. (pp. 78 and 79)

Siegel demonstrates that you can embrace technology without becoming a drooling idiot sitting around the campfire singing Kumbayah and toasting the imminence of the Singularity while chugging homemade wine produced by an algorithm:

I have good news: a little prediction goes a long way. I call this The Prediction Effect, a theme that runs throughout the book. The potency of prediction is pronounced—as long as the predictions are better than guessing. The Effect renders predictive analytics believable. We don’t have to do the impossible and attain true clairvoyance. The story is exciting yet credible: Putting odds on the future to lift the fog just a bit off our hazy view of tomorrow means pay dirt. In this way, predictive analytics combats financial risk, fortifies healthcare, conquers spam, toughens crime fighting, and boosts sales. (p. XVI)

This is a great introduction to predictive analytics. It won’t teach you how to develop predictive models, but it surveys the territory, explains why it’s worthwhile, and points you in the right direction if you want to claim some of this territory as your own.

Take care,

Dear Sir,

I am writing to ask you to please compose a post specifically addressing the new ‘data scientist’ hype, as this term is filtering down into organizations and causing all kinds of havoc. As a data analyst with a solid combination of education and experience, I am used to having terms thrown about by management who think ‘data mining’ is using VLOOKUP in Excel and love to present all their metric results as ’significant’. Usually we can ignore it; they’ll find some poor kid to do what they want, and hopefully someone will point out that the Emperor has no clothes before they present their latest innovative analysis to a VP.

The Data Scientist buzz is powerful right now—perhaps it’s the exposure of Nate Silver’s election predictions. Managers who have zero statistical or data understanding are now expecting their analysts to produce inferential statistics believing this to be the solution to all their problems (or at least a career builder). These same managers don’t even know the difference between descriptive and inferential statistics or that they are asking for a research project that requires methodological rigor and time. (Research design to these guys means searching the Internet.)

Sometimes I wonder if your courses shouldn’t be specifically directed to middle and upper management. Most of the people data analysts are managed by have no real understanding of data, analysis, or statistics. This is why they are such easy marks for BI Vendors and Consultants.

I personally am no longer willing to educate my bosses. Organizations restructure so frequently now, who has time to train 3 managers a year? Besides, they often just grab on to terminology and use it inappropriately in meetings to sound knowledgeable. But I believe if you were to do a post explaining what a data analyst is and what the ‘data scientist’ hype is all about, we analysts could quietly forward it to our bosses in hopes they could learn something other than what they hear from Vendors and Consultants.

By the way, I love the Aptitudes and Attitudes of Effective Analysts section in your book Now You See It—you nailed it. Nowhere have I read a better description—it should be used for job descriptions. I just wish I could have gotten one boss to read it.

Thank-you

Kelly Martin

Kelly raises a legitimate concern, shared by many. She articulated it so well and with so much feeling, I couldn’t ignore her plea.

Similar to the term Big Data, which was coined in 1997, the term data science isn’t new. Gil Press did a great job of tracing its historical roots in a recent Forbes article titled “A Very Short History of Data Science.” Precursors of the term can be found in the writings of Princeton statistician John Tukey dating back to the 1960s. The precise term first appeared in a book by Peter Naur’s titled Concise Survey of Computer Methods in 1974 and it was later used in the title of a conference for the first time in 1996 (”Data science, classification, and related methods” at the biennial conference of the International Federation of Classification Societies).

It was in a paper written in 2001, however, by statistician William S. Cleveland, one of data visualization’s great pioneers, that the term was first used in a manner that’s fairly consistent with its current use: “Data Science: An Action Plan for Expanding the Technical Areas of the Field of Statistics.”

This document describes a plan “to enlarge the major areas of technical work of the field of statistics. Because the plan is ambitious and implies substantial change, the altered field will be called “data science.” The focus of the plan is the practicing data analyst. A basic premise is that technical areas of data science should be judged by the extent to which they enable the analyst to learn from data.

Cleveland wanted to expand the perspective and toolkit of statisticians and apply their efforts to a broader range of real-world problems. He spoke of data science as a multidisciplinary effort, rooted in historical precedents.

The single biggest stimulus of new tools and theories of data science is the analysis of data to solve problems posed in terms of the subject matter under investigation. Creative researchers, faced with problems posed by data, will respond with a wealth of new ideas that often apply much more widely than the particular data sets that gave rise to the ideas. If we look back on the history of statistics—for example, R. A. Fisher inventing the design of experiments stimulated by agriculture data, John Tukey inventing numerical spectrum analysis stimulated by physical science and engineering data, and George Box inventing response surface analysis based on chemical process data—we see that the greatest advances have been made by people close to the analysis of data.

[Data science] carries statistical thinking to subject matter disciplines. This is vital. A very limited view of data science is that it is practiced by statisticians. The wide view is that data science is practiced by statisticians and subject matter analysts alike, blurring exactly who is and who in not a statistician.

Cleveland believed that statistics should be integrated more intimately into the real world and embrace a broader range of tools to explore and make sense of data. His perspective echoed that of Tukey who many years earlier expressed concern that the term statistician reflected a narrow approach to data sensemaking, so he encouraged use of the term data analyst to promote a more open-minded statistics that embraced the use of software (still in its infancy at the time, long before personal computers) and data visualization. Tukey’s use of the term data analysis and Cleveland’s use of the term data science encouraged statisticians to embrace all of the technologies, techniques, and skills that were needed to derive greater value from a rapidly growing body of data. In the same spirit, today we can work to free data sensemaking from compartmentalization into separate disciplines of statistics, financial analysis, computer programming, data warehousing, data mining, business intelligence, data storytelling, data visualization, predictive analytics, and any other confined specialty. We can accomplish more by collaborating, tearing down the boundaries that have traditionally and in many respects artificially separated these disciplines, sometimes creating conflict and competition between them.

When defined in this way, the term data science is meaningful and useful. Understood in this way, the work of data science is not new; it is what good data analysts by any name have been doing for quite awhile. As the term is used by technology vendors and technology analysts, or on resumes, however, data science is seldom more than marketing hype—a new name for old technologies and skills that rarely rise to the level of science.

When uttered from the lips of technology vendors, technology analysts, and suddenly rebranded data practitioners, like many marketing terms, data science is an ill-defined miasma of confusion. They usually use the term synonymously with Big Data. Here’s the description that appears on the website www.datasciencecentral.com: “Data Science Central is the industry’s online resource for big data practitioners.” New York University’s initiative in data science intimately joins these terms as well: “In order to unlock the powerful potential of this big data, the world needs researchers and professionals skilled in developing and utilizing automated methods of analyzing it. These individuals are called ‘data scientists’…” (http://datascience.nyu.edu/about/). Insight Data Source, which offers a training program that in six weeks equips people to “succeed as data scientists,” enthusiastically describes the term more broadly, but no more meaningfully.

Nowhere has the benefits of analyzing data been felt more strongly than at top technology companies. Silicon Valley companies are not only leading in the production of data, they are also on the cutting edge of using insights from that data to benefit their users. In fact, the role of data scientist, now used throughout industry to describe highly specialized analysts with deep quantitative abilities, was coined by the heads of the early data teams at Facebook and LinkedIn. They realized the process of asking questions about product use cases, taking measurements, verifying hypotheses and building upon those results closely mirrored the process by which science is done. The individuals, therefore, who apply their curiosity, quantitative skills and intellect toward understanding big data are now known as data scientists—a job title that is one of the most in-demand job roles at today’s leading technology companies.

Imagine developing the opportunity to become “highly specialized analysts with deep quantitative abilities” in six weeks. Despite the fact that it has been obvious to data sensemakers all along that their work is scientific in nature and method, applying the term “data scientist” to those who do this work exploded in the IT industry’s consciousness as a revelation: one that it could harness to make money.

In some cases, vendors offer incoherent descriptions of data science beyond an association with Big Data that seem intentionally designed to keep the term nebulous. It is often to a vendor’s advantage to define what they sell in broad, vague strokes to seemingly fit the diverse expectations of customers. Here’s how data science is described on IBM’s website:

So what does a data scientist do?

A data scientist represents an evolution from the business or data analyst role. The formal training is similar, with a solid foundation typically in computer science and applications, modeling, statistics, analytics and math. What sets the data scientist apart is strong business acumen, coupled with the ability to communicate findings to both business and IT leaders in a way that can influence how an organization approaches a business challenge. Good data scientists will not just address business problems, they will pick the right problems that have the most value to the organization.

The data scientist role has been described as “part analyst, part artist.” Anjul Bhambhri, vice president of big data products at IBM, says, “A data scientist is somebody who is inquisitive, who can stare at data and spot trends. It’s almost like a Renaissance individual who really wants to learn and bring change to an organization.”

Whereas a traditional data analyst may look only at data from a single source—a CRM system, for example—a data scientist will most likely explore and examine data from multiple disparate sources. The data scientist will sift through all incoming data with the goal of discovering a previously hidden insight, which in turn can provide a competitive advantage or address a pressing business problem. A data scientist does not simply collect and report on data, but also looks at it from many angles, determines what it means, then recommends ways to apply the data.

Data scientists are inquisitive: exploring, asking questions, doing “what if” analysis, questioning existing assumptions and processes. Armed with data and analytical results, a top-tier data scientist will then communicate informed conclusions and recommendations across an organization’s leadership structure.

So, according to IBM, a data scientist is someone who exhibits the following characteristics:

• An evolved state of business analysis or data analysis expertise
• Business knowledge
• An ability to communicate to technical and business people alike
• Inquisitiveness
• Influence
• A focus on problems that matter
• Artistry
• Pattern detection
• A Jack of all trades (Renaissance individual)
• A lifelong learner
• A facilitator of change
• A multi-source data integrator
• A seeker of hidden gems
• An multi-perspective observer
• A person of action (insight applier)
• Skepticism
• Someone who asks “what if?”
• Someone to whom leaders listen

Have I missed any? These are all great qualities, but they don’t constitute a useful definition of a data scientist. In fact, we could replace the title “data scientist” with “data analyst,” “statistician,” “BI professional,” or any of the other job titles that have been used over the years for expert data sensemakers and the list would work as well.

According to Wikipedia, data scientists are more narrowly defined: they must be trained in a specific scientific discipline and work as members of a team.

Data scientists solve complex data problems through employing deep expertise in some scientific discipline. It is generally expected that data scientists are able to work with various elements of mathematics, statistics and computer science, although expertise in these subjects are not required. However, a data scientist is most likely to be an expert in only one or two of these disciplines and proficient in another two or three. There is probably no living person who is an expert in all of these disciplines—if so they would be extremely rare. This means that data science must be practiced as a team, where across the membership of the team there is expertise and proficiency across all the disciplines.

There is no general agreement about the meaning of the term. Despite the good intentions of some who use it, such as William Cleveland when he first introduced it in 2001, it has led to confusion through freewheeling, self-serving use. When all is said and done, deriving value from data comes down to effective data sensemaking, resulting in greater understanding and better decisions. Regarding those who do the work, it only matters that they’re qualified to do it well. If you’re a hiring manager looking for someone to glean meaningful insights from your data, keep in mind that the title “data scientist” on a resume means nothing. IT titles are notoriously inflated.

In a recent article (”Data scientists don’t scale“, ZDNet, May 22, 2013) Andrew Brust described the situation poignantly:

There’s a risk that many technologists will become “data scientists” in the name of finding a better gig, in exactly the same way that happened with other lofty titles in technology (”architect,” for example). Title inflation happens in any field, but in the tech field, terms and titles are in any case viewed as metaphors, more than literal descriptions. Tech folks tend to take poetic license with titles, and those who don’t do so find themselves at a disadvantage compared to those who do.

If you manage people who work as data sensemakers, take the time to understand what they do and what’s required to do it well. Learn enough to see past the hype that vendors attach to this work to get you to buy their products and hire their consultants. Know enough to support your employees in their work, and then get out of their way. Most of all, never forget that only people can make sense of data. At best, tools can augment the abilities of talented people. There is indeed a science to data sensemaking, but data science by any other name (and there are many) would smell as sweet.

Take care,

New chapters have been added that focus on the following topics:

• Fundamental considerations while assessing requirements
• In-depth instruction in the design of bullet graphs
• In-depth instruction in the design of sparklines
• Critical steps that you should take during the design process

Examples of graphics and dashboards have been updated throughout the book and many new examples have been added, including a few more well-designed dashboards. In total, approximately 30% more content has been added to the book.

To give you a sense of this new edition, here’s the preface:

When I finished writing the first edition of this book in early 2006, I could not find a single example of a well-designed dashboard to illustrate the principles and practices that I advocate. Prior to this book, no specific guidelines for dashboard design existed. Not only did no good examples exist at the time, but no software could easily produce them. For example, in early 2006 no products supported data visualization expert Edward Tufte’s sparklines, which often work ideally on dashboards to provide an abbreviated view of history. No products supported bullet graphs either; shortly before this book was first published, I had introduced the bullet graph as a better alternative to typical dashboard gauges. I took a risk by writing a book that urged people to do what exceeded the capabilities of existing technology at the time. The risk paid off in that dashboard software has come a long way since then (although it still has a long way to go). However, had I worked within the boundaries of existing products at the time, the book would have not been worthwhile.

One complaint that I received about the first edition of this book was that it didn’t include enough examples of well-designed dashboards. Given the technological limitations that I’ve just described, I had to create, using Adobe Illustrator, the few good examples that appeared in the final chapter of the first edition. One of the main reasons that I’ve now written this second edition is to respond to this legitimate but unavoidable complaint by adding several more good examples, most of which were created by others.

In the years since 2006, another minor gap has developed between the book that I initially wrote and this second edition: the dashboard examples are somewhat dated. What’s surprising, however, is the fact that most of the dashboards that people create today using the latest technology are no better than their early predecessors. Almost every software vendor that claims to support dashboards features a hall of shameful examples on its website. I had a wealth of poorly designed dashboards to choose from vendor websites; I’ve included those examples throughout this new edition to illustrate bad but typical design practices.

Now that copies of the first edition are no longer available for purchase, Amazon.com is now accepting pre-orders for the new edition. I hope you find it useful. It has certainly been a labor of love.

Take care,

If George Peck has his way (Peck wrote the only authorized book about Tableau 8 and has also served as the featured speaker in many of the Tableau 8 Roadshow events), the next version of Tableau will add flashy gauges to its library of charts. Here are his thoughts on the matter, as recorded in the latest newsletter from Peck’s consultancy The Ablaze Group:

Why Tableau Should Add a Gauge to Version 9

We’re wrapping up the Tableau 8 Roadshow (having now been shut out of two cities, including our own hometown of Denver, by airport weather cancellations). Tableau 8 is available and is enjoying rave reviews. And, while I was just getting around to fully digesting the old Tableau controversy about removing WikiLeaks visualizations, I just now heard about the new one that erupted when Stephen Few dissed Tableau about version 8. Despite my behind-the-time-ness, I simply must offer a contrast to Mr. Few’s thoughts.

There’s a place for visualization “experts.” Varying points of view are good. Educated opinions on visual best practices contribute to improved toolsets. But, can we all remember that there’s not any one person who knows all, or sees all, about any particular topic? My philosophy about “informed opinions,” including mine, is “Put this in your bucket of thoughts, shake or stir thoroughly, and benefit from the mix.” With this spirit of “mix of opinions” in mind, add the following to your bucket and shake it up.

Tableau should add a gauge mark type to Version 9.

We had an existing SAP BusinessObjects customer (Stephen Few’s never-ending scorn for this product is legendary) who approached us a while back inquiring about Tableau. “Our existing BI system has some issues. Some parts of it are slow and difficult to maintain. Can you give us an idea of where Tableau might improve this?” My reply was, “Sure… let me know where you have particular issues — where are your most painful areas?” But, before we could even begin to address these basic salient points, the prospect took it upon themselves to download a Tableau demo and begin to explore the product. The first follow-up was almost immediate; “How do I create a gauge in Tableau?”

I tried to move the customer back to the initial issues that had, theoretically, been the impetus for their initial inquiry. “Well, we can explore that. But, rather than just trying to mirror your current visuals, can we talk about where you have problems? Maybe there’s a better way, such as use of a bullet chart, to analyze those types of metrics. When can we talk?” The response, “Yeah, let me see when I can work a call into my schedule. But, for now, can you tell me how to create a gauge in Tableau?” I responded with a fairly extensive comparison of gauges versus bullet charts to analyze actual/goal data (this particular customer’s application of gauges). The next, final response: “Sorry, Tableau isn’t right for us.”

Yes, we can talk all day about this customer’s lack of insight, inability/unwillingness to look at anything other than “the way we’ve always done things,” and their refusal to sit down for even a basic discussion of their issues. In other words, this was a fairly normal situation (so, I’m admittedly now adopting the Stephen Few approach of “A few insults never hurt anybody”). In the final evaluation, Tableau’s lack of this ubiquitous mark type immediately prevented this (admittedly uninformed) prospect from discovering the beautiful, blazingly fast, tool that’s Tableau. Once they would have gotten over their gauge-itis, they too would have come on board.

Here’s the bottom line (expressed with an appropriate metaphor relating to New York Mayor Bloomberg’s recent come-uppance from a State Supreme Court): It’s not Tableau’s place to keep the 18 ounce sugary drink off the Mark Type Cafe menu. Visualization Seat Belts may help at the time of the Data Discovery collision, but Stephen Few simply doesn’t have the authority to mandate self-protection by all BI passengers.

Even if they choose to add the following confirmation dialog:

Tableau should offer a gauge in their next major release.

So many wise lessons can be found in this short article. Here’s what I’ve learned from Mr. Peck:

1. “There’s a place for visualization ‘experts.’” Thank God for this. Previously, I had my doubts. (I have the impression, however, that my place, according to Peck, is in one of the rings of Dante’s Inferno.)
2. To my great disappointment, I have now discovered that I do not know all or see all. At best, I must be satisfied with the status of semi-omniscient demigod.
3. All opinions are of equal value. We needn’t vet them, but should just put them all in a hat, shake them up, and then…well I don’t actually know what we’re supposed to do with them next, but somehow we will “benefit from the mix.”
4. Software vendors should give customers what they want, even when those things don’t work and are potentially harmful. If you give your customers crap, they will eventually figure out that it’s crap and reject it in favor of the other stuff that you gave them that isn’t crap.
5. Teaching data visualization best practices is an attempt to make it illegal for people to do otherwise. Apparently, I don’t have the right to make people do what I want. Damn! Once again my divine self-image has been dismissed as an illusion.
6. Visualizing data ineffectively isn’t such a big deal. It’s a lot like using a dirty word. At worst you might offend some prickly data Nazi like me, so go right ahead and do your worst.

You might think that I shouldn’t need to be taught these same lessons over and over again. How many times have I been told that I should shut up and stop caring about visualizing data in ways that actually work? Too many times to count. Chances are, I’ll never learn. I suspect that I will once again toss these lessons into that round object (no, not a hat) that keeps my life uncluttered by nonsense.

As Peck so graciously concedes, everyone has the right to an opinion, even pesky “informed opinions,” and he certainly has a right to his. What concerns me, however, is the degree to which his opinion reflects the perspective of Tableau. I recently learned that when my review of Tableau 8 was published, Tableau employees were forbidden from responding publicly. That makes this brief article by Peck the closest thing to a public response from Tableau that I’ve received.

Not all that long ago, I would have said that George Peck, someone who has supported SAP BusinessObjects for many years and continues to do so today, couldn’t possibly represent the position of Tableau. Now, I’m no longer sure. Unless the ban at Tableau on responding to me publicly is perpetual, I’d love to find out to what extent Tableau is still committed to best practices, which is what once made the product great and unique. Tableau’s customers, especially those who fell in love with the product because it avoided the silly stuff and made it easy to derive real value from data, deserve to know if Tableau has changed course. Who is calling the shots at Tableau these days: sales and marketing, from a near-term perspective of the quick win, or the information visualization experts such as Chris Stolte, Pat Hanrahan, and Jock Mackinlay who built the product with a clear vision rooted in best practices?

Take care,

(You’ll see in a moment why there is a vertical line in 2005.) Things get a little more interesting if you split population by age groups. You can see that population is getting old at the top (more elderly people) and at the bottom (fewer children):

In population studies, there is a very useful dependency ratio, a ratio between Young and Adult population (Young Dependency) and between Old and Adult population (Old Dependency). Now, think about Jon, a baby-boomer. He was born in 1961. You can see him in the orange series. And you can see him again 65 years later:

 A strange scenario

It is clear that, while the young dependency ratio remains flat, the old dependency ratio sharply increases as soon as the first baby boomers enter old age, by 2011. But what happens if you plot a ratio against the other?

Here is our L-shaped trend, where the red dot marks 2005. There are several things at play here. The first one is, obviously, the impact of baby boomers. But you must see it in the context of the long-term demographic transition (from high birth and death rates to low birth and death rates). And there is a model of the projected fertility rate (in this case, the so called “medium-fertility variant”).

There are basically three scenarios. The line always goes up. If you assume that fertility rate remains stable then you get a vertical line. If you you assume that fertility rate keeps declining, the line bends to the left, and if  you are an incorrigible optimist and you believe that couples will start having more than two children again, then the line bends to the right:

I don’t like L-shaped trends, so I wanted to see how well the model is predicting the actual ratios. Here is a zoomed-in chart with estimates from the US Census and actual data from the latest census (2010).

Apparently, things are getting worse than predicted by the model: the old dependency ratio is going up (you can’t do much to change that) but the young dependency ratio is not frozen at the 2005 level (Americans are making fewer babies than expected). The US is not alone. Here are a few countries with a similar pattern:

And the largest ones:

So, unless a country is planning to kill or export its older citizens, its old dependency ratio is likely to keep increasing over the next years. Assuming that you don’t have much control over the number of elderly people, your only option is to play with fertility rates. Given current economic and financial uncertainty, I don’t know how fertility rate can increase.

The incredibly shrinking country

If the model is right, many countries will have to deal with an over-sized elderly population followed by a dramatic decline of total population. Here is the UN data for Portugal (in 1990 the 85+ age group is split):

______________________

Post from: Excel Charts Blog.

Strange L-shaped trends

The original post is titled Strange L-shaped trends , and it came from The Excel Charts Blog .

Big Data: A Revolution That Will Transform How We Live, Work, and Think
Viktor Mayer-Schönberger and Kenneth Cukier
Houghton Mifflin Harcourt, 2013

I figured that if anyone had something useful to say about Big Data, these were the guys. What I found in their book, however, left me convinced even more than before that Big Data is a ruse, and one that should concern us.

What Is Big Data?

One of the problems with Big Data, like so many examples of techno-hype, is that it is ill-defined. What is Big Data exactly? The authors address this concern early in the book:

There is no rigorous definition of big data. Initially the idea was that the volume of information had grown so large that the quantity being examined no longer fit into the memory that computers use for processing, so engineers needed to revamp the tools they used for analyzing it all. (p. 6)

Given this state of affairs, I was hoping that the authors would propose a definition of their own to reduce some of the confusion. Unfortunately, they never actually define the term, but they do describe it in various ways. Here’s one of the descriptions:

The sciences like astronomy and genomics, which first experienced the explosion in the 2000s, coined the term “big data.” The concept is now migrating to all areas of human endeavor. (p. 6)

Actually, the term “Big Data” was coined back in 1997 in the proceedings of IEEE’s Visualization conference by Michael Cox and David Ellsworth in a paper titled “Application-controlled demand paging for out-of-core visualization.” Scientific data in the early 2000s was not our first encounter with huge datasets. For instance, I was helping the telecommunications and banking industries handle what they experienced as explosions in data quantity back in the early 1980s. What promoters of Big Data fail to realize is that data has been increasing at an exponential rate since the advent of the computer long ago. We have not experienced any actual explosions in the quantity of data in recent years. The exponential rate of increase has continued unabated all along.

How else do the authors define the term?

[Big Data is]…the ability of society to harness information in novel ways to produce useful insights or goods and services of significant value. (p. 2)

On the contrary, we’ve been finding “novel ways to produce” value from data forever, not just in the last few years. We haven’t crossed any threshold recently.

How else do they define it?

At its core, big data is about predictions. Though it is described as part of the branch of computer science called artificial intelligence, and more specifically, an area called machine learning, this characterization is misleading. Big data is not about trying to “teach” a computer to “think” like humans. Instead, it’s about applying math to huge quantities of data in order to infer probabilities. (pp. 11 and 12)

So, Big Data is essentially about “predictive analytics.” Did we only in recent years begin applying math to huge quantities of data in an attempt to infer probabilities? We neither began this activity recently, nor did data suddenly become huge.

Is Big Data a technology?

But where most people have considered big data as a technological matter, focusing on the hardware or the software, we believe the emphasis needs to shift to what happens when the data speaks. (p. 190)

I wholeheartedly agree. Where I and the authors appear to differ, however, is in our understanding of the methods that are used to find and understand the messages in data. The ways that we do this are not new. The skills that we need to make sense of data—skills that go by the names statistics, business intelligence, analytics, and data science—have been around for a long time. Technologies incrementally improve to help us apply these skills with greater ease to increasingly larger datasets, but the skills themselves have changed relatively little, even though we come up with new names for the folks who do this work every few years.

So what it is exactly that separates Big Data from data of the past?

One way to think about the issue today—and the way we do in this book—is this: big data refers to things one can do at a large scale that cannot be done at a smaller one, to extract new insights or create new forms of value, in ways that change markets, organizations, the relationship between citizens and governments, and more.

But this is just the start. The era of big data challenges the way we live and interact with the world. Most strikingly, society will need to shed some of its obsession for causality in exchange for simpler correlations: not knowing why but only what. This overturns centuries of established practices and challenges our most basic understanding of how to make decisions and comprehend reality. (pp. 6 and 7)

To “make decisions and comprehend reality” we no longer need to understand why things happen together (i.e., causation) but only what things happened together (correlation). When I read this an eerie feeling crawled up my spine. The implications of this line of thinking are scary. Should we really race into the future satisfied with a level of understanding that is relatively superficial?

According to the authors, Big Data consists of “things one can do at a large scale that cannot be done at a smaller one.” And what are these things and how exactly do they change how “we live and interact with the world?” Let’s see if the authors tell us.

Does Big Data Represent a Change of State?

One claim that the authors make, which is shared by other promoters of Big Data, is that data has grown so large and so fast that the increase in quantity constitutes a qualitative change of state.

Not only is the world awash with more information than ever before, but that information is growing faster. The change of scale has led to a change of state. The quantitative change has led to a qualitative one. (p. 6)

The essential point about big data is that change of scale leads to change of state. (p. 151)

All proponents make this claim about Big Data, but I’ve yet to see anyone substantiate it. Perhaps it is true that things can grow to such a size and at such a rate that they break through some quantitative barrier into the realm of qualitative change, but what evidence do we have that this has happened with data? This book contains many examples of data analytics that have been useful during the past 20 years or so, which the authors classify as Big Data, but I believe this attribution is contrived. Not one of the examples demonstrates a radical departure from the past. Here’s one involving Google:

Big data operates at a scale that transcends our ordinary understanding. For example, the correlation Google identified between a handful of search terms and the flu was the result of testing 450 million mathematical models. (p. 179)

I suspect that Google’s discovery of a correlation between search activity and incidents of the flu in particular areas resulted, not from 450 million distinct mathematical models, but rather a predictive analytics algorithm making millions of minor adjustments during the process of building a single. If they really created 450 million different models, or even if they actually made that many tweaks to an evolving model to find this relatively simple correlation, is this really an example of progress. A little statistical thinking by a human being could have found this correlation with the help of a computer much more directly. Regardless of how many models were actually used, the final model was not overly complicated. What was done here does not transcend the ordinary understanding of data analysts.

And now, for the paradigm-shattering implications of this change of state:

Big data is poised to reshape the way we live, work, and think. The change we face is in some ways even greater than those sparked by earlier epochal innovations that dramatically expanded the scope and scale of information in society. The ground beneath our feet is shifting. Old certainties are being questioned. Big data requires fresh discussion of the nature of decision-making, destiny, justice. A worldview we thought was made of causes is being challenged by a preponderance of correlations. The possession of knowledge, which once meant any understanding of the past, is coming to mean an ability to predict the future. (p. 190)

Does any of this strike you as particularly new? Everything that the authors claim as particular and new to Big Data is in fact old news. If you’re wondering what they mean by the “worldview we thought was made of causes is being challenged by a preponderance of correlations,” stay tuned; we’ll look into this soon.

Who Is the Star of Big Data?

Who or what in particular is responsible for the capabilities and potential benefits of big data? Are technologies responsible? Are data scientists responsible? Here’s the authors’ answer:

The real revolution is not in the machines that calculate data but in data itself and how we use it. (p. 7)

What we can do with data today was not primarily enabled by machines, but it is also not intrinsic to the data itself. Nothing about the nature of data has changed. Data is always noise until it provides an answer to a question that is asked to solve a problem or take advantage of an opportunity.

In the age of big data, all data will be regarded as valuable, in and of itself. (p. 100)

With big data, the value of data is changing. In the digital age, data shed its role of supporting transactions and often became the good itself that was traded. In a big-data world, things change again. Data’s value shifts from its primary use to it potential future uses. That has profound consequences. If affects how businesses value the data they hold and who they let access it. It enables, and may force, companies to change their business models. It alters how organizations think about data and how they use it. (p. 99)

God help us. This notion should concern us because most data will always remain noise beyond its initial use. We certainly find new uses for data that was originally generated for another purpose, such as transaction data that we later use for analytical purposes to improve decisions, but in the past we rarely collected data primarily for potential secondary uses. Perhaps this is a characteristic that actually qualifies as new. Regardless, we must ask the question, “Is this a viable business model?” Should all organizations begin collecting and retaining more data in hope of finding unforeseen secondary uses for it in the future? I find it hard to imagine that secondary uses of data will provide enough benefit to warrant collecting everything and keeping it forever, as the authors seem to believe. Despite their argument that this is a no-brainer based on decreasing hardware costs, the price is actually quite high. The price is not based on the cost of hardware alone.

Discarding data may have been appropriate when the cost and complexity of collecting, storing, and analyzing it were high, but this is no longer the case. (p. 60)

Every single dataset is likely to have some intrinsic, hidden, not yet unearthed value, and the race is on to discover and capture all of it. (p. 15)

Data’s true value is like an iceberg floating in the ocean. Only a tiny part of it is visible at first sight, while much of it is hidden beneath the surface. (p. 103)

Imagine the time that will be wasted and how much it will cost. Only a tiny fraction of data that is being generated today will ever be valuable beyond its original use. A few nuggets of gold might exist in that iceberg below the water line, but do we really need to collect and save it all? Even the authors exhibit concern for the persistent value of data.

Most data loses some of it utility over time. In such circumstances, continuing to rely on old data doesn’t just fail to add value; it actually destroys the value of fresher data. (p. 110)

Collecting, storing, and retaining everything will make it harder and harder to focus on the little that actually has value. Nevertheless, the authors believe that the prize will go to those with the most.

Scale still matters, but it has shifted [from technical infrastructure]. What counts is scale in data. This means holding large pools of data and being able to capture even more of it with ease. Thus large data holders will flourish as they gather and store more of the raw material of their business, which they can reuse to create additional value. (p. 146)

If this were true, wouldn’t the organizations with the most data today be the most successful? This isn’t the case. In fact, many organizations with the most data are drowning in it. I know, because I’ve tried to help them change this dynamic. Having lots of data is useless unless you know how to make sense of it and how to apply what you learn.

Attempts to measure the potential value of data used for secondary purposes have so far been little more than wild guesses. Consider the way that Facebook was valued prior to going public.

Doug Laney, vice president of research at Gartner, a market research firm, crunched the numbers during the period before the initial public offering (IPO) and reckoned that Facebook had collected 2.1 trillion pieces of “monetizable content” between 2009 and 2011, such as “likes,” posted material, and comments. Compared against its IPO valuation, this means that each item, considered as a discrete data point, had a value of about five cents. Another way of looking at it is that every Facebook user was worth around $100, since users are the source of the information that Facebook collects.

How to explain the vast divergence between Facebook’s worth under accounting standards ($6.3 billion) and what the market initially valued it at ($104 billion)? There is no good way to do so. (p. 119)

Indeed, we are peering at tea leaves, trying to find meaning in drippy green clumps. Unforeseen uses for data certainly exist, but they are by definition difficult to anticipate. Do we really want to collect, store, and retain everything possible on the off chance that it might be useful? Perhaps, instead, we should look for ways to identify data with the greatest potential for future use and focus on collecting that? The primary problem that we still have with data is not the lack of it but our inability to make sense of it.

Does Correlation Alone Suffice With No Concern for Causation?

The authors introduce one of their strangest claims in the following sentence:

The ideal of identifying causal mechanisms is a self-congratulatory illusion; big data overturns this. (p. 18)

Are they arguing that Big Data eliminates our quest for an understanding of cause altogether?

Causality won’t be discarded, but it is being knocked off its pedestal as the primary fountain of meaning. Big data turbocharges non-causal analyses, often replacing causal investigations. (p. 68)

Apparently science can now take a back seat to Big Data.

Correlations exist; we can show them mathematically. We can’t easily do the same for causal links. So we would do well to hold off from trying to explain the reason behind the correlations: the why instead of the what. (p. 67)

This notion scares me. Correlations, although useful in and of themselves, must be used with caution until we understand the causal mechanisms related to them.

We progress by seeking and finding ever better explanations for reality—what is, how it works, and why. Explanations—the commitment to finding them and the process of developing and confirming them—is the essence of science. By rebelling against authority as the basis of knowledge, the Enlightenment began the only sustained period of progress that our species has ever known (see The Beginning of Infinity, by David Deutsch). Trust in established authority was replaced by a search for testable explanations, called science. The information technologies of today are a result of this search for explanations. To say that we should begin to rely on correlations alone without concern for causation encourages a return from the age of science to the age of ignorance that preceded it. Prior to science, we lived in a world of myth. Even then, however, we craved explanations, but we lacked the means to uncover them, so we fabricated explanations that provided comfort or that kept those in power in control. To say that explanations are altogether unnecessary today in the world of Big Data is a departure from the past that holds no hope for the future. Making use of correlations without understanding causation might indeed by useful at times, but it isn’t progress, and it is prone to error. Manipulation of reality without understanding is a formula for disaster.

The authors take this notion even further.

Correlations are powerful not only because they offer insights, but also because the insights they offer are relatively clear. These insights often get obscured when we bring causality back into the picture. (p. 66)

Actually, thinking in terms of causality is the only way that correlations can be fully understood and utilized with confidence. Only when we understand the why (cause) can we intelligently leverage our understanding of the what (correlation). This is essential to science. As such, we dare not diminish its value.

Big data does not tell us anything about causality. (p. 163)

Huh? Without data we cannot gain an understanding of cause. Does Big Data lack information about cause that other data contains? No. Data contains this information no matter what its size.

According to the authors, Big Data leverages correlations alone in an enlightening way that wasn’t possible in the past.

Correlations are useful in a small-data world, but in the context of big data they really shine. Through them we can glean insights more easily, faster, and more clearly than before. (p. 52)

Big data is all about seeing and understanding the relations within and among pieces of information that, until very recently, we struggled to fully grasp. (p. 19)

What exactly is it about Big Data that enables us to see and understand relationships among data that were elusive in the past? What evidence is there that this is happening? Nowhere in the book do the authors answer these questions in a satisfying way. We have always taken advantage of known correlations, even when we have not yet discovered what causes them, but this has never deterred us in our quest to understand causation. God help us if it ever does.

Does Big Data Transform Messiness into a Benefit?

Not only do we no longer need to concern ourselves with causation, according to the authors we can also stop worrying about data quality.

In a world of small data, reducing errors and ensuring high quality of data was a natural and essential impulse. Since we only collected a little information, we made sure that the figures we bothered to record were as accurate as possible…Analyzing only a limited number of data points means errors may get amplified, potentially reducing the accuracy of the overall results…However, in many new situations that are cropping up today, allowing for imprecision—for messiness—may be a positive feature, not a shortcoming. It is a tradeoff. In return for relaxing the standards of allowable errors, one can get ahold of much more data. It isn’t just that “more trumps some,” but that, in fact, sometimes “more trumps better. (pp. 32 and 33)

Not only can we stop worrying about messiness in data, we can embrace it as beneficial.

In dealing with ever more comprehensive datasets, which capture not just a small sliver of the phenomenon at hand but much more or all of it, we no longer need to worry so much about individual data points biasing the overall analysis. Rather than aiming to stamp out every bit of inexactitude at increasingly high cost, we are calculating with messiness in mind…Though it may seem counterintuitive at first, treating data as something imperfect and imprecise lets us make superior forecasts, and thus understand our world better. (pp. 40 and 41)

Hold on. Something is amiss in the authors’ reasoning here. While it is true that a particular amount of error in a set of data becomes less of a problem if that quantity holds steady as the total amount of data increases and becomes huge, a particular rate of error remains just as much of a problem as the data set grows in size. More does not trump better data.

The way we think about using the totality of information compared with smaller slivers of it, and the way we may come to appreciate slackness instead of exactness, will have profound effects on our interaction with the world. As big-data techniques become a regular part of everyday life, we as a society may begin to strive to understand the world from a far larger, more comprehensive perspective than before, a sort of N = all of the mind. And we may tolerate blurriness and ambiguity in areas where we used to demand clarity and certainty, even if it had been a false clarity and an imperfect certainty. We may accept this provided that in return we get a more complete sense of reality—the equivalent of an impressionist painting, wherein each stroke is messy when examined up close, but by stepping back one can see a majestic picture.

Big data, with its emphasis on comprehensive datasets and messiness, helps us get closer to reality than did our dependence on small data and accuracy. (p. 48)

Will impressionist data provide a more accurate and useful view of the world? I love impressionist paintings, but they’re not what I study to get a clear picture of the world.

Now, if you work in the field of data quality, let me warn you that what’s coming next will shock and dismay you. Perhaps you should sit down and take a valium before reading on.

The industry of business intelligence and analytics software was long built on promising clients “a single version of the truth”…But the idea of “a single version of the truth” is doing an about-face. We are beginning to realize not only that it may be impossible for a single version of the truth to exist, but also that its pursuit is a distraction. To reap the benefits of harnessing data at scale, we have to accept messiness as par for the course, not as something we should try to eliminate. (p. 44)

Seriously? Those who work in the realm of data quality realize that if we give up on the idea of consistency in our data and embrace messiness, the problems that are created by inconsistency will remain. When people in different parts of the organization are getting different answers to the same questions because of data inconsistencies, no amount of data will make this go away.

So what is it that we get in exchange for our willingness to embrace messiness?

In return for living with messiness, we get tremendously valuable services that would be impossible at their scope and scale with traditional methods and tools. According to some estimates only 5 percent of all digital data is ’structured’—that is, in a form that fits neatly into a traditional database. Without accepting messiness, the remaining 95 percent of unstructured data, such as web pages and videos, remain dark. By allowing for imprecision, we open a window into an untapped universe of insights. (p. 47)

The authors seem to ignore the fact that most data cannot be analyzed until it is structured and quantified. Only then can it produce any of the insights that the authors applaud in this book. It doesn’t need to reside in a so-called structured database, but it must at a minimum be structured in a virtual sense.

Does Big Data Reduce the Need for Subject Matter Expertise?

I was surprised when I read these two authors, both subject matter experts in their particular realms, state the following:

We are seeing the waning of subject-matter experts’ influence in many areas. (p. 141)

They argue that subject matter experts will be substantially displaced by Big Data, because data contains a better understanding of the world than the experts.

Yet expertise is like exactitude: appropriate for a small-data world where one never has enough information, or the right information, and thus has to rely on intuition and experience to guide one’s way. (p. 142)

This separation of subject matter expertise on the one hand from what we can learn from data on the other is artificial. All true experts are informed by data. The best experts are well informed by data. Data about things existed long before the digital age.

At one point in the book the authors quote Hal Varian, formerly a professor in the computer science department at UC Berkeley and now Google’s chief economist: “Data is so widely available and so strategically important that the scarce thing is the knowledge to extract wisdom from it. (p. 125)” What they seem to miss is the fact that Varian, in the same interview from which this quotation was derived, talks about the need for subject matter experts such as managers to become better informed by data to do their jobs. People become better subject matter experts when they become better acquainted with pertinent data. These experts will not be displaced by data; they will be enriched by it as they always have, hopefully to an increasing degree.

As we’ve seen, the pioneers in big data often come from fields outside the domain where they make their mark. They are specialists in data analysis, artificial intelligence, mathematics, or statistics, and they apply those skills to specific industries. (p. 142)

These Big Data pioneers don’t perform these wonders independent of domain expertise but in close collaboration with it. Data sensemaking skills do not replace or supplant subject matter expertise, they inform it.

To illustrate how Big Data is displacing the subject matter experts in one industry, the authors write the following about the effects of Big Data in journalism:

This is a humbling reminder to the high priests of mainstream media that the public is in aggregate more knowledgeable than they are, and that cufflinked journalists must compete against bloggers in their bathrobes. (p. 103)

“Cufflinked journalists”? From where did this characterization of journalists come? Perhaps the author Cukier, the data editor of the Economist, has a bone to pick with other journalists who don’t understand or respect his work. Whatever the source of this enmity against mainstream media, I don’t want to rely on bloggers for my news of the world. While bloggers have useful information to share, unless they develop journalistic skills, they will not replace mainstream journalists. This is definitely one of those cases in which the amount of information—noise in the blogosphere—cannot replace thoughtful and skilled reporting.

Perhaps the strangest twist on this theme that the authors promote is contained in the following paragraph:

Perhaps, then, the crux of the value is really in the skills? After all, a gold mine isn’t worth anything if you can’t extract the gold. Yet the history of computing suggests otherwise. Today expertise in database management, data science, analytics, machine-learning algorithms, and the like are in hot demand. But over time, as big data becomes more a part of everyday life, as the tools get better and easier to use, and as more people acquire the expertise, the value of the skills will also diminish in relative terms…Today, in big data’s early stages, the ideas and the skills seem to hold the greatest worth. But eventually most value will be in the data itself. (p. 134)

The value of skills and expertise will not diminish over time. When programming jobs started being offshored, the value of programming wasn’t diminished, even though the value of individual programmers was through competition. No shift in value will occur from skills and expertise to data itself. Data will forever remain untapped, inert, and worthless without the expertise that is required to make sense of it and tie it to existing knowledge.

What Is a Big Data Mindset and Is It New?

Data represents potential. It always has. From back when our brains first evolved to conceive of data (facts) through the development of language, followed by writing, the invention of movable type, the age of enlightenment, the emergence of computers, and the advent of the Internet until now, we have always recognized the potential of data to become knowledge when understood and to be useful when applied. Has a new data mindset arisen in recent years?

Seeing the world as information, as oceans of data that can be explored at ever greater breadth and depth, offers us a perspective on reality that we did not have before. It is a mental outlook that may penetrate all areas of life. Today, we are a numerate society because we presume that the world is understandable with numbers and math. And we take for granted that knowledge can be transmitted across time and space because the idea of the written word is so ingrained. Tomorrow, subsequent generations may have a ‘big-data consciousness”—the presumption that there is a quantitative component to all that we do, and that data is indispensable for society to learn from. The notion of transforming the myriad dimensions of reality into data probably seems novel to most people at present. But in the future, we will surely treat it as a given. (p. 97)

Perhaps this mindset is novel for some, but it is ancient in origin, and it has been my mindset for my entire 30-year career. Nothing about this is new.

In a big-data age, we finally have the mindset, ingenuity, and tools to tap data’s hidden value. (p. 104)

Poppycock! We’ve always searched for hidden value in data. What we haven’t done as much in the past is collect everything in the hope that it contains a goldmine of hidden wealth if we only dig hard and long enough. What has yet to be determined is the net value of this venture.

What Are the Risks of Big Data?

Early in the book the authors point out that they are observers of Big Data, not evangelists. They seem to be both. They certainly promote Big Data with enthusiasm. Their chapter on the risks of Big Data does not negate this fact. What’s odd is that the risk that seems to concern them most is one that is and will probably always remain science fiction. They are concerned that those in power, such as governments, will use Big Data to predict the bad behavior of individuals and groups and then, based on those predictions alone, act preemptively by arresting people for crimes that have not yet been committed.

It is the quintessential slippery slope—leading straight to the society portrayed in Minority Report, a world in which individual choice and free will have been eliminated, in which our individual moral compass has been replaced by predictive algorithms and individuals are exposed to the unencumbered brunt of collective fiat. If so employed, big data threatens to imprison us—perhaps literally—in probabilities. (p. 163)

Despite preemptive acts of government that were later exposed as mistakes (e.g., the invasion of Iraq because they supposedly had weapons of mass destruction) and those of insurance companies or credit agencies that deny coverage or loans by profiling certain groups of people as risky in the aggregate, the threat of being arrested because an algorithm predicted that I would commit a crime does not concern me.

Big data erodes privacy and threatens freedom. But big data also exacerbates a very old problem: relying on the numbers when they are far more fallible than we think. (p. 163)

This is indeed a threat, but the authors’ belief that we can allow messiness in Big Data exacerbates this problem.

The threat is that we will let ourselves be mindlessly bound by the output of our analyses even when we have reasonable grounds for suspecting something is amiss. Or that we will become obsessed with collecting facts and figures for data’s sake. (p. 166)

This obsession with “collecting facts and figures for data’s sake” is precisely what the authors promote in this book.

In Summary

The authors of this book are indeed evangelists for the cause of Big Data. Even though one is an academic and the other an editor, both make their living by observing, using, and promoting technology. There’s nothing wrong with this, but the objective observer’s perspective on Big Data that I was hoping to find in this book wasn’t there.

Is Big Data the paradigm-shifting new development that the authors and technology companies claim it to be, or is the data of today part of a smooth continuum extending from the past? Should we adopt the mindset that all data is valuable in and of itself and that “more trumps better”? Should we dig deep into our wallets to create the ever-growing infrastructure that would be needed to indiscriminately collect, store, and retain more?

Let me put this into perspective. While recently reading the book Predictive Analytics by Eric Siegel, I learned about the research of Larry Smarr, the director of a University of California-based research center, who is “tracking all bodily functions, including the scoop on poop, in order to form a working computational model of the body as an ecosystem.” Smarr asks and answers:

Have you ever figured how information-rich your stool is? There are about 100 billion bacteria per gram. Each bacterium has DNA…This means that human stool has a data capacity of 100,000 terabytes of information stored per gram.

This is fascinating. I’m not being sarcastic; it really is. I think Smarr’s research is worthwhile. I don’t think, however, that everyone should continuously save what we contribute to the toilet, convert its contents into data, and then store that data for the rest of our lives. If we did, we would become quite literally buried in shit. Not all data is of equal importance.

The authors the book Big Data: A Revolution That Will Transform How We Live, Work, and Think, in a thoughtful moment of clarity, included the following note of realism:

What we are able to collect and process will always be just a tiny fraction of the information that exists in the world. It can only be a simulacrum of reality, like the shadows on the wall of Plato’s cave. (p. 197)

This is beautifully expressed and absolutely true. Data exists in a potentially infinite supply. Given this fact, wouldn’t it be wise to determine with great care what we collect, store, retain, and mine for value? To the extent that more people are turning to data for help these days, learning to depend on evidence rather than intuition alone to inform their decisions, should we accept the Big Data campaign as helpful? We can turn people on to data without claiming that something miraculous has changed in the data landscape over the last few years. The benefits of data today are the same benefits that have always existed. The skills that are needed to tap this potential have changed relatively little over the course of my long career. As data continues to increase in volume, velocity, and variety as it has since the advent of the computer, its potential for wise use increases as well, but only if we refine our ability to separate the signals from the noise. More does not trump better. Without the right data and skills, more will only bury us.

Take care,

My spirit rose when the video began with the words of Edward Tufte and his image filled the screen.

I nearly swooned as Tufte calmly and eloquently uttered statements such as “Style and aesthetics cannot rescue failed content” and “There are enormously beautiful visualizations, but it’s as a byproduct of the truth and the goodness of the information.” At last, I thought, a professionally made video that features the best of data visualization. Within seconds, however, I found that Tufte served only as the bookends of this video and that much of the content in between conflicted with his statements.

Here are a couple samples:

And, of course, a video about data visualization is not complete without at least one of the infamous monstrosities created by David McCandless.

I can only imagine how Tufte must have felt when he saw the final product and discovered how his statements were contradicted by much of the other content that PBS chose to include. It is because of this possibility that I turn down invitations to participate in projects like this video that don’t allow contributors to control the content. Unless you have a contract that grants you the right to review and approve the final product, great harm can be done to your reputation and you can unwittingly participate in a project that undermines your work.

Wouldn’t it be wonderful if PBS or some other respected media provider decided to work closely with leaders in the field of data visualization to present it at its best and most useful? I’m betting that I could get many of my colleagues—several of the best and brightest in the field—to participate in this project with enthusiasm if we were given the right to work closely with the production team and then review and approve the final content. Perhaps we could even use Tufte’s portions of “The Art of Data Visualization,” but fill the middle with a consistent message about the true potential of data visualization to enlighten with beauty as ”a byproduct of the truth and the goodness of the information.”

Take care,

1. Know your data. Until you understand the stories that live in your data, you can’t begin to tell them.
2. Know your audience. Unless you understand what matters to your audience, you won’t know what is of interest and use to them.
3. Determine your message. Every dataset contains multiple stories. You can’t tell them all at once. Before you present quantitative information, you must determine the specific message or messages that you want to communicate. Start by writing a sentence or two or three to express the message before moving on to determine the ideal means of expression.
4. Reduce the data to what’s needed to communicate the message. Pare the data down to the essence of what your audience must see to understand the message. What’s essential usually involves more than a simple set of primary values (e.g., monthly sales figures), for without context in the form of comparisons, numbers mean little. For example, monthly sales figures compared to target values or to values for the same months last year are more meaningful than sales figures alone.
5. Determine the best means of expression. Some quantitative messages are best communicated with words, some with tables of numbers, some with graphs, and some with a combination. Some messages are best displayed in a bar graph, some in a line graph, some in a scatter plot, and so on. Knowing which form of expression works best for the message that you’re trying to present requires a little training into how our eyes and brains process visual information. The principles are easy to learn, but they aren’t intuitive. I wrote the book Show Me the Numbers, in part, to teach these principles.
6. Design the display to communicate simply, clearly, and accurately. Include nothing that isn’t data unless it’s needed to support the data. Unnecessary color variation and visual effects, or even grid lines in a graph when they aren’t needed, will detract from the message. Non-data elements that are needed should only be visible enough to do their job and never so visible that they call attention to themselves. Non-data elements should sit politely in the background so the information stands out clearly in the foreground. If some information is more important to the message than other information, do something visual to feature it. For example, a brighter color or thicker stroke would make a particular line in a line graph stand out more than the others.
7. Suggest a way to respond. Whenever possible, make it easy for your audience to respond with appropriate action by suggesting specific steps. Most quantitative messages aren’t presented merely to inform, but also to motivate a useful response.

Take care,

I learned about straw men as an undergraduate majoring in communication studies. I loved the course that I took in argumentation and debate back then because I found the rules of logic elegant, interesting, and easy to understand. I vividly remember, however, that most of my classmates didn’t take so naturally to these principles and frequently struggled to make their case. I’m ashamed to admit that I took far too much pleasure in tying my opponents into logical knots and luring them into logical traps.

Since those bygone days of youth, I have expanded what I learned in college by keeping up with work in the fields of critical thinking and brain science. I am now familiar not only with the rules of rational argument but also with many causes of flawed thinking. I have found, to my great disappointment, that this is not common knowledge, even among scientists and analysts. I am no longer surprised when academics in the field of information visualization—doctoral students and professors—conduct studies that are flawed in obvious ways.

I was prompted to think about straw men recently when I encountered a couple on the Web that were apparently constructed to fault the work of people like me who teach data visualization best practices. The first appeared in a recent series of articles about data visualization on the Harvard Business Review’s (HBR) website. I was invited to contribute an article to this series, but unfortunately didn’t have the time. I wish I could have participated, however, to correct the portrayal of business-related data visualization as skewed toward elaborate infographics rather than the simple uses of quantitative graphics that make up around 99% of the data visualizations created in organizations. The straw man that I noticed was constructed by Amanda Cox of the New York Times. I greatly admire the data graphics of the New York Times, including Amanda’s work in particular. Cox is an articulate spokesperson for journalistic uses of data visualization. For this reason, I was surprised when I read the following interaction in HBR’s interview with Amanda (emphasis mine):

[HBR]: It seems like there’s more focus on trying to get data viz to go viral than to make it “matter.”

[Amanda Cox]: There’s a lot where not much actionable comes out of it. I don’t know if the ratio is different from the ratio of bad writing to good, or bad restaurant openings to good, but I think it’s an important idea to focus on. There’s a strand of the data viz world that argues that everything could be a bar chart. That’s possibly true but also possibly a world without joy.

I appreciated almost everything that Amanda said except the two sentences that I’ve highlighted above, which appear to be a jab at data visualization practitioners who promote the use of simple graphs over some of the elaborate (but often ineffective) infographics that routinely appear on the Web. Amanda’s statement is a straw man. No one “argues that everything could be a bar chart.” Anyone who did would not only be robbing the world of joy but also of meaning. Bar graphs are one effective means of displaying data among several, and they are only appropriate for particular data sets and purposes. I’m not sure why Amanda felt compelled to insert this little goad of a comment in the interview. If she has an actual case to make, she can surely do better than this.

On April 17^th, I encountered a similar straw man constructed by Nathan Yau in his blog (emphasis mine):

Data is an abstraction of something that happened in the real world. How people move. How they spend money. How a computer works. The tendency is to approach data and by default, visualization, as rigid facts stripped of joy, humor, conflict, and sadness—because that makes analysis easier. Visualization is easier when you can strip the data down to unwavering fact and then reduce the process to a set of unwavering rules.

The world is complex though. There are exceptions, limitations, and interactions that aren’t expressed explicitly through data. So we make inferences with uncertainty attached. We make an educated guess and then compare to the actual thing or stuff that was measured to see if the data and our findings make sense.

Data isn’t rigid so neither is visualization.

Are there rules? There are, just like there are in statistics. And you should learn them.

However, in statistics, you eventually learn that there’s more to analysis than hypothesis tests and normal distributions, and in visualization you eventually learn that there’s more to the process than efficient graphical perception and avoidance of all things round. Design matters, no doubt, but your understanding of the data matters much more.

I agree with everything that Nathan says here, but not with what he implies in the text that I’ve highlighted. His comment about “efficient graphical perception and avoidance of all things round” appears to be a direct reaction to my position, but one that he’s morphed into a straw man. No one argues that there isn’t more to data visualization than perceptual efficiency and circle avoidance. (I suspect that Yau’s phrase “all things round” refers to an article that I wrote in 2010, “Our Irresistible Fascination with All Things Circular.”) No one who promotes the importance of efficient and accurate graphical perception argues that design matters more than understanding. In fact, it is our concern that people understand data clearly, accurately, and as fully as possible that leads us to teach people how to present data graphically in ways that work for human perception and cognition. There is indeed much more to data visualization than a rigid set of design rules, which is why, when I teach design principles, I do so in a way that enables my students to understand how and why these principles work so they can apply, bend, and sometimes break the rules intelligently.

What’s ironic about Yau’s claim is that he often features infographics as exemplary that are beautiful or otherwise eye-catching, but yield little understanding. Such examples can easily be found in his lists of the best data visualizations of the year. Given his training as a statistician, I’ve always found this puzzling.

Making data visualizations perceptible is not all there is, but it is certainly an essential requirement if we want people to understand what we’re trying to say. I’m sure that Cox and Yau agree, but they seem willing at times to sacrifice perceptual effectiveness for visual allure. When they do, understanding is diminished. There is no reason why perceptual effectiveness and visual allure cannot coexist. Leaders in the field of data visualization don’t always agree, but when we disagree and wish to state our case, we should build it on solid evidence and sound reason. Dismissive remarks and thinly veiled insinuations that aren’t accurate or backed by evidence don’t qualify as useful discourse.

Take care,

If you want to make a choropleth/thematic map in Excel without programming perhaps conditional formatting is all you need. Here is how to do it:

1. Select a few hundred columns and rows;
2. Set width and height to 3 (more or less, depending on the resolutions you want);
3. Set font size to 1;
4. Place a map file over those cells and make it transparent (if possible, use a WMF file) so that you can see the cells and the map borders;
5. In a new sheet, add a table with all the regions and some random data;
6. Get back to the first sheet and for each cell within a region enter a lookup function and change cell background (the lookup must include the region ID);
7. Save your work and make a copy of this sheet;
8. Select a rectangle around your map and add a border;
9. Choose a conditional formatting rule for the entire rectangle;
10. Clear the existing cell background color;
11. You should see a colored map using the conditional formatting rule.
12. Inside the rectangle, merge a few cells and create a legend (in this case, I used percentiles to automatically update it);
13. You can delete the overlapping file, but the maps will look nicer with the borders;
14. Use the camera tool to copy the map to a new sheet.

Here is the sheet for the example above:

Make Excel choropleth/thematic map without programming.

Enjoy!

______________________

Post from: Excel Charts Blog.

Making Excel maps without VBA

The original post is titled Making Excel maps without VBA , and it came from The Excel Charts Blog .

My most recent experience was with the academic publisher Taylor & Francis. They publish several academic journals, including one that I was asked to write for over a year ago: the “Journal of Computational and Graphical Statistics.” Specifically, I was asked to write a response to an academic paper entitled “Infovis and Statistical Graphics: Different Goals, Different Looks” by Andrew Gelman and Antony Unwin. When approached by Richard Levine of San Diego State University, the journal’s editor, I had grave reservations. Back in 2008 I wrote in this blog about an experience that I had with IEEE’s journal Computer Graphics and Applications. The article that I wrote in response to the editor’s request was pulled from publication at the very last minute, when without warning they sent me a contract that demanded the right to alter my work, without approval, however they wished. An author would be insane to grant this right. With this experience in mind, I expressed my concerns to Levine before agreeing to write for his statistics journal:

If the journal requires authors to transfer their copyrights to it, I’ll pass on the opportunity. I’m happy to grant exclusive rights of distribution to the journal, which is all that should matter. I never grant others the right to revise my work without permission, which is what can happen when copyrights are given away.

In response, Levine checked with Taylor & Francis and assured me that my terms were acceptable.

With this commitment in writing, I proceeded with confidence to write my response, which involved roughly two days of effort. Following the submission of my response in March of 2012, I began to navigate the arduous process that one inevitable faces when writing for an academic journal: rushed spurts of activity (peer reviews, copyedits, layout reviews, etc.) separated by months of inactivity. After all was complete, guess what Taylor & Francis asked me to sign? A standard contract that ignored our prior agreement. I quickly redlined the inappropriate sections of the contract and offered to sign it with my redlines intact. I didn’t hear back from them for a while, but finally received a new agreement that I assumed was a replacement for the original contract, which I signed and immediately returned. I was then informed that what I signed was an addendum to the original contract, not a replacement, and that I would still need to sign the original. I replied that I would gladly sign my redlined version of the original contract, as previously promised. At this point our correspondence was kicked upstairs to Eric Sampson, the Journals Manager at Taylor & Francis, who informed me that I must sign a copy of the original contract without redlines. You can imagine my dismay. I told Sampson that I never sign contracts that contain errors. He responded that Taylor & Francis did not have the right to publish my work unless I signed the original contract without alteration. Further, he said that I was preventing the journal from going to press and that they would therefore remove what I’d written from the journal. I couldn’t believe what I was hearing. I replied to Sampson with the following:

This is rather absurd. I agreed to sign the original agreement back when it was first sent to me provided that I could redline the parts that you agreed to negate in the addendum. In other words, there is no disagreement between us and you absolutely have the right to publish the article as originally promised. I abhor this kind of dysfunctional bureaucracy, which I’ve encountered far too often among publishers. I’ve spent a great deal of time working on this and I expect my article to be published as originally promised. Otherwise, why did I do all of this work? Before I wrote the article, I made it clear that the copyright would remain in my name and that I would not allow revisions to the article without my permission. I have honored my agreement; I would appreciate it if you would honor yours.

The following excerpts from Sampson reveals the extent to which Taylor & Francis is dysfunctional (un-italicized comments in brackets are mine):

We have done our absolute best to meet your requests. Our original agreement was that you would retain copyright provided we received a signed, unmodified license to publish. [Not true. I was never told when we forged our agreement that I would be required to sign an "unmodified license to publish"—a license that I had never seen.] When you objected to the license, T&F [Taylor & Francis] went above and beyond to create a special addendum to that license, which you signed, but the addendum is just that—the signed license to publish is still required, which you have refused to provide without significant modification. [The only modifications that I required were the removal or redlining of those sections that Taylor and Francis agreed would not apply to me.]

Our bureaucracy is required to make the publication process as smooth as possible, and we ask for copyright transfer so that, in the event of a copyright dispute, the ASA [American Statistical Association] and T&F protect authors’ rights instead of leaving them to fend for themselves. [So they're just trying to help me? That's what all this fuss is about? What an egregious lie!] The ASA publishes hundreds of articles a year. If we negotiated each and every one as we’ve done here, it would be all we do. [If they respected the rights of authors in their standard agreement, negotiations would seldom be necessary.]

The ASA and T&F have one of most permissive copyright agreements in publishing, and we take great pride in taking care of the authors who contribute to our journals. I’m sorry that you’ve found our efforts so lacking. We’ve genuinely done our best. [If this is their best effort, their routine efforts must be pitiful.]

At this point, as you might imagine, I was getting angry, so I wrote the following reply:

Contrary to your claim, you have not done your best. You are currently breaking an agreement that we made in the beginning. You waited until the last minute to send me a license that you knew was in conflict with my requirements. Now, despite the fact that we have agreed in principle to the terms, you are insisting that I sign a form that miss-states those terms. Please explain to me how this makes any sense.

In all of our correspondence, Sampson wrote only one sentence in response to my request that he “explain…how this makes any sense.” Here it is: “I have no idea why we can’t just edit the license, but the society and publisher prefer the addendum approach.” He, the Journals Manager, did not know why the original contract could not be redlined or revised to remove errors. After days of consultation with his superiors, he sent a final written response that still provided no explanation. Again and again in our correspondence I pointed out that there was absolutely no legal requirement that the original contract be signed without redlines or revisions. I invited him to have his legal department explain otherwise, if they disagreed. No explanation was ever provided because no rational explanation exists.

Taylor & Francis is an academic publisher. I would expect that any publisher, and especially one that serves the academic community, could respond to a clear and reasonable question with an answer that is better than the traditional response of tyrants, “Because we said so.”  How do they get away with this behavior when dealing with the academics who write papers for their journals? Academics must publish in journals to advance in their careers. When academic journals demand of authors the right to alter their work without permission, students and professors feel that they have no choice but to surrender their rights, because they have no other avenue for publication to which they can turn. I think it time for this to change.

Take care,

what you really want to do is to compare each slice to the whole, like this:

because, if you want to compare them you must do this:

I hope that you find this pretty obvious. If you don’t, let’s add one more series.

When you do this:

what you really want is to compare categories for each year, so you use a bar chart:

or, even better, a slope chart:

You never ever compare proportions, only a single proportion to the whole. The moment you change your question and want to compare data points you have to use a different chart. That’s why the optimal number of categories in a pie chart is one.

Capisci?

______________________

Post from: Excel Charts Blog.

Finally revealed: the optimal number of categories in a pie chart

The original post is titled Finally revealed: the optimal number of categories in a pie chart , and it came from The Excel Charts Blog .

(click to enlarge)

I have a single and very simple resolution for 2013: make more charts. Simple charts, just to play with the data. Here is the first one. I like scatter plots with a time dimension, even though data points often look like drunken sperm.

Ah, by the way, I decided to use a dotted line to denote a break in a series. We often forget these things.

So, how would you improve it?

______________________

Post from: Excel Charts Blog.

Chart: Education vs. GDP in Europe

The original post is titled Chart: Education vs. GDP in Europe , and it came from The Excel Charts Blog .

Fortunately, life is not that simple. Many people dislike charts and a good table with the latest data is more than enough to get the insights they need. This kind of puzzles us. Haven’t they seen the light yet?

Is context overrated?

Let me put it this way: external visual context may be overrated.

Suppose you’re a programmer. You want to solve a very specific problem, like making a routine run faster. You search your favorite tips & tricks site and find what you were looking for. End of story. Your knowledge and experience provide all the context you need. Let’s call it internally supplied context.

If you are a beginner things are a bit different. You probably don’t even know that your routine can run faster. If you do, you don’t exactly know what to look for. And if you do, you don’t know how that new piece of code works and you need help. You need explicit externally supplied context.

Journalists and graphic designers love external context (hence they love charts) because they are not subject-matter experts. They work with other people’s data. If I say “an unemployment rate of 12.1%” a subject-matter expert can easily provide internal context, while the journalist (and her audience) need a more explicit external context.

Are patterns overrated?

I’d like to know more about table-based decision-making process, but I suspect that people are less aware of data patterns and more interested in some kind of fluctuation bands, and they compare data points against them. This is an interesting alternative data reduction technique.

The dangers of a consolidated knowledge

Internally supplied context and fluctuation bands are two by-products of a mature and consolidated knowledge. They can be very effective in decision-making and help seeing beyond short-term trends. On the other hand, in a rapidly changing environment they can (dis)miss relevant but unexpected changes.

So, what do we do?

We may not like it, but we must accept the fact that some people are less visual that others and that they can get the information they need from a table or a written report. They already know what they need to know and they can provide the necessary context to deal with a few data points. A chart is useless and redundant.

How can we convince them that a chart is a good thing? Well, try this:

• Don’t tell them quantitatively what they already know qualitatively.
• Show them more complex relationships.
• Try to find unexpected patterns.
• Try to find their pain points and solve them (all processes can be improved).

So, what do you think? How can you convince a table person to become a chart person?

______________________

Post from: Excel Charts Blog.

Context comes in many ways and shapes

The original post is titled Context comes in many ways and shapes , and it came from The Excel Charts Blog .

Clearly and Simply proudly presents a new guest article: Michael Martin of Business Information Arts, Tableau Partner, Tableau Certified Consultant and leader of the Toronto Tableau User Group shows us how to visualize Network Graphs using Tableau Software. Enjoy.

Network Graphs can help us see and measure relationships and connections between people, places, and things over time. This can be expressed as identifying, measuring and understanding process flows, the mix of products in shopping carts, social network and email traffic, affinities and interests people share (or don’t share), and the “hierarchies of influence” in business and / or social systems by identifying who or what triggers events, and the impacts they have on others.

Today’s post describes how you can build Network Graphs using Tableau Software versions 6 or 7, including a detailed how-to tutorial and some information on the background of Network Theory.

What is a Network Graph? A Picture says more than 1,000 words

What are Network Graphs for? Here are just a few practical examples:

Contacts Between Philanthropic Twitter Users

The Organization of Hierarchical Communities

The Path to Products People Buy

Last, but not least a Network Graph built in Tableau:

Association of Food Groups, Brands and Flavors

Tableau’s Out of the Box Network Graphs

Tableau Desktop is one of my favorite data analysis and reporting tools. Other excellent products such as Visokio Omniscope support network graphs as one of a wide number of supported view types. But what I have always found so impressive about how Tableau is engineered is how various “loosely coupled” features can be re-assembled to create new ones. Examples of this include double axis graphs, bullet charts, and the support for bubble graphs and tree maps in the upcoming Tableau 8 release (Q1 of 2013). Tableau is a fabulous “Swiss Army Knife” for visualizing data.

Build Network Graphs with Tableau – The How to

My implementation of network graphs in Tableau leverages features that have been around since version 1, the circle and the line mark types, and support for scatter plots – and ability to draw double axis graphs (hackable for years before being officially supported as “combo charts” in version 6). With a little bit of data preparation, this is all you need to draw a network graph in Tableau.

For me, the fun really starts when other great Tableau functionalities (actions, parameters, page field animation, filtering, highlighting, size by, color by, table calculations to name just a few) are brought into play.

Data Preparation

Key to my implementation is data preparation given the requirement to connect elements in the form of a transaction and lay out the design of the network graph in the Tableau view:

If you want to follow the step-by-step below using my example data, here is the Excel workbook with the data for free download:

Download Network Graphs Example Data (Microsoft Excel 2007/2010, 14.3K)

Demo – A simple Network Graph

Here’s a simple network graph based on the example data.

The Step-by-Step Tutorial

Step 1 – The Basic Set Up

To get started, put the “Line Y” field to the Row Shelf and “Line X” field on the Column shelf. Tableau will automatically set the mark type to circle and render a basic scatterplot. The “Line Y” and “Line X” co-ordinates in the source data are visible via the field value headers.

Step 2 – Dual Axis

Add the “Circle Y” to View on the rows shelf as a double axis, and synchronize the two Y axes (right click on the axis, and click on “Synchronize axis”).

Step 3 – Multiple Mark Types

The next step is to format the Tableau Marks Card to show "Multiple Mark Types“. Then cycle to the "Circle Y” mark and set mark type as "Pie“. Drag the "Node Name" field to the Label Pill. You can optionally color the “Node Name” field by ID by dragging ID to Level of Detail Shelf Resize the Pie mark to make larger – each pie slice represents a Transaction ID.

Step 4 – Connect the Dots

Then cycle to the "Line Y" Mark. Drag the “ID” and “Relationship” fields to Level of Detail shelf. Set the mark type to “Line”. Tableau will connect the dots – and you have a simple Network Graph. Resize the Line Y series to make lines thinner and color the lines as desired.

Optionally, you can format the canvas to include grid lines and turn brushing on in the Color Legend. Uncheck the “Show Header” in the “Line X”, “Line Y”, and “Circle Y” fields in the row and column shelves.

Step 5 – The Tooltips

If you hover over nodes in the view with the mouse, you’ll see Tableau generated tooltip text:

We can do a few things to make the Tooltips more meaningful. With the “Line Y” Mark selected in the Marks Card, place the “Relationship” and “Sales vs Sales For Display” fields in the Level of Detail Shelf.

The “Sales For Display” field is a calculated field that I will describe shortly.

Then cycle to the “Circle Y” field in the Marks Card and place the “Total Sales”, “InDegree”, “OutDegree”, “Node Name”, and “ID” fields in the Level of Detail Shelf.

Step 6 – Calculated Fields and the Tooltips again

The next step is to define a simple calculation which I named “Sales vs Sales For Display” in my workbook:

IF Sum([Sales])=0 THEN Sum([Sales For Display]) ELSE Sum([Sales]) END

The output of this calculation is the value of either the “Sales” or the “Sales For Display” data fields associated with a single transaction. My implementation needs this calculation because without this calculation the value of the “Sales” field will change to zero (or change from zero to the value of the sale) as you pass the halfway point between two connected nodes when traversing the line between connected nodes with the mouse.

If you take another look at the source data, you’ll see that the value of the “Sales For Display” field is the same as the value for “Sales” in the previous row for a given transaction ID. As Tableau aggregates the “Sales” and “Sales For Display” metrics by Transaction ID, the value of the calculation will change as you pass the halfway point in the line connecting the nodes in the transaction with the mouse.

Then edit the tooltip text is shown in the figure below.

The first two lines will appear when the mouse hovers over a line connecting two nodes (the “Line Y” field). The remaining four lines will display when the mouse hovers over a Node (the “Circle Y” field).

Step 7 (optional) – A Summary Table

You can optionally make a summary sales table that sums the “Sales” field by the “Node Name” field which includes creating a calculation named “Sales Label” that suppresses the display of zero values in the “Sales Field”.

If you look at the source data, you’ll see that the “Relationship” field is encoded to show who the seller and buyer were. The value Ken à Bill describes a transaction where Ken was the seller and Bill was the buyer. Ken is listed as the “Initiating Person” and Bill is listed as the “Secondary Person”. The “Direction” field explains this in another way; from Ken’s point of view as the “Initiating Person”, this is an “Out Degree” connection. From Bill’s point of view as the buyer, this is an “In Degree” connection.

Step 8 (optional) – Filter Actions

You can optionally define a Tableau Filter Action to filter data that will appear in the “Summary Sales Table” based in which transactions in the view are selected with the mouse. In my implementation, the Action is set to run “On Select” based on the values of the “ID” and “Node Name” data fields.

For more information on how to use Actions in Tableau, have a look at this how-to tutorial: The Power of Tableau Actions.

Step 9 (optional) – Animated View

You can optionally animate the view by dropping the “ID” field into the Pages Shelf and inserting the Pages Shelf into the Dashboard by selecting “Current Page”.

After you start the Page Player, transactions will come into the view sorted by the Transaction ID number. With the use of calculations and “Page History” settings, you can create very interesting animated views of transaction oriented data.

The Result

Here is the example packaged Tableau workbook for free download:

Download Prototype Scene Graph (Tableau 7 Packaged Workbook, 62.3K)

Network Metrics

The “Network Density” metric is commonly calculated as the number of actual possible connections divided by the number of possible connections. There are 9 actual connections and 56 possible connections in the example data, resulting in a Network Density value of .1607 which depending on the context could be considered to be low or high.

The “Network Centralization” metric tells us how “centered” the network is around the member(s) of the network with the highest number of connections. In a network with three members, this metric is of little value – but in a network with thousands or millions of connections, knowing the people or persons the network is centralized around is meaningful to our understanding of the network. In the data driving my implementation, Jane is involved in four of the nine transactions which would be commonly calculated as (4 / 9) = .444. This would be considered a high value in most cases, so you could say that the total network is highly centralized (around Jane).

The “Network Homophily” metric describes the degree that connected nodes share similar characteristics - i.e. are connected nodes largely alike? The richer the source data is, the more important and interesting this metric can be as the row count increases. This metric is of particular interest to marketers.

Switching to Node specific metrics; the “In Degree” metric is the count of in-coming connections to a Node from other nodes in the network. The “Out Degree” metric is the count of outgoing connections from a single node to other nodes in the network. These two metrics are often used to help analysts and marketers understand how “social” products within particular retail categories are with products in similar or different retail categories.

The “Betweeness” metric helps us understand how important a particular node is to the overall “performance” of the network from the perspective of a particular metric or class of metrics. The example data describes connections through “Sales”. If Sally and Roger had made huge sales to each other or to Jane, removing Jane from the network would lower the “total value” of the network because Roger and Sally are in the network by virtue of their relationships to Jane.

The “Closeness” metric helps us understand how useful a given network member is for getting a message from outside the network circulated within the network as soon as possible. If an outside person wanted to circulate a message within the network described in the example data, the go-to person is Jane because she is directly connected (one hop away) to five other network members, who in turn are a hop away from the remaining network members (Roger and Ken).

Although the “Betweenness” and “Closeness” metrics are important, they don’t necessarily predict the ranking of members in a network by the governing metric (in this example, sales). The top seller in the network is Wayne by virtue of a 20k sale to Marjory. If you size the “Node ID” field by “Sales”, you would immediately realize how important Wayne is to the network from a sales performance point of view.

The “Eigenvector Centrality” metric explains the degree to which a given node is connected to the most important node in the network. In a given network, an “introverted” member with low “in degree” and “out degree” metrics and has little or no “betweenness” or “closeness” could in fact be quite important due to its influence on members who are very well connected. If Jane is heavily influenced by Sally’s purchasing recommendations, Sally’s role in shaping the profile of the network is important given Jane’s position in the network as the most important buyer in the network.

Recommended Further Resources

There are many great resources on and off the web for learning about network theory and metrics. Here are a few that I’ve found helpful, with apologies to other great resources that I haven’t encountered yet.

University of Maryland Human / Computer Interaction Lab contains links to many interesting data visualization projects and white papers related to network data visualizations.

Node XL is an Excel add-in for visualizing network graphs.

Analyzing Social Media Networks With NodeXL by Derek Hansen, Ben Schneiderman and Mark Smith, published by Morgan Kaufman.

Gephi is an open source tool for visualizing network graphs.

Aaron Koblin provides a great visualization of airline flight patterns over North America.

About Michael

Michael Martin (email Michael) works internationally in a variety of business sectors that include market research, consumer packaged goods and retail, banking, hospitality, commercial construction, entertainment, governmental, and non-profit.

His project deliverables include business performance forecasts, strategic and operational case study white papers, operational dashboards and scorecards, associative and neural networks, customer / product segmentation and market-basket analyses.

Michael is a Tableau Partner, a Certified Tableau Consultant and leads the Tableau Toronto User Group.

Robert’s Note

A big time thank you very much to Michael for contributing this fantastic article. If you enjoyed what you have read, please drop Michael a line to say thank you by email (email link see above) or leave him a comment here.

Stay tuned.

On several occasions I thought (and stated) that I already published everything I have to say about Choropleth Maps with Excel.

Having said that, it seems as if “the ghosts I called I can’t get rid of now”. Recently someone asked me if I could provide an Excel Choropleth Map of Germany by zip codes. Careless and stupid boy I am, I answered “sure this is no problem”. What a misjudgment.

If you followed my previous Excel Choropleth Map articles, you know that it takes either an Enhanced Metafile of the map you can ungroup in Excel to get the shapes or at least an SVG file to transform it to an EMF file using e.g. Inkscape. 

And here is the roadblock I encountered: I simply couldn’t find a map of Germany by zip codes in one of the required formats. All I could find were ESRI shape files. After hours of searching for EMF and SVG files, I gave up, simply reversed my thinking and looked for a tool to convert shape files into SVG. 5 minutes later I had the solution.

Today’s post describes how to use Indiemapper, a free online tool, to transform shape files into SVG which can then be used for Microsoft Excel Choropleth Maps in the well-known way.

The Challenge

The Choropleth Map approach I used in all articles here requires ungrouped shapes in Excel. The easiest way of getting there is an EMF file you simply import into Excel and ungroup it until you have one shape for each region you want to visualize. If you don’t have an EMF file, this article shows you how to transform an SVG file into EMF format.

But what if you can find the map you need neither in EMF nor in SVG format. Global Administrative Areas, for one, provides the administrative regions of all countries of the world. The problem: they are ESRI shapes files, not EMF or SVG.

Today’s challenge is to find an easy way of transforming ESRI shape files into EMF which can be directly used in Excel for creating Choropleth Maps.

The Solution - Indiemapper

Indiemapper is a free service provided by Axis Maps making static, thematic maps from geographic data. It also as an export feature to transfer shape files into SVG, JPG and PNG.

Step 1: Load your Shape Files

After launching Indiemapper, click on Shapefile and browse for the file on your computer.

If available, also upload the .dbf file in the following dialogue.

Step 2: Skip New Layer Option

Skip the next dialogue window to add a new layer by clicking on Cancel:

Step 3: Turn off the Graticule

If necessary (like it was in my case), uncheck the strokes checkbox in the Graticule window.

Step 4: Export 

Click on export at the top right of the website, choose “layered SVG”, select a filename and click on Generate File:

Finally export the generated SVG file to your computer:

That’s it. In 4 simple steps we transformed an ESRI Shape File into SVG, i.e. the file format we can now transform into an EMF file and prepare it for being used as a Choropleth Map in Excel, exactly the way I already described it here: Build your own Choropleth Maps with Excel.

The Show Case – Germany by Zip Codes

With the few steps described above combined with the technique to transform SVG files into Excel Choropleth Maps (here), I was able to produce the following 2 maps of Germany by zip codes in almost no time.

For your understanding: German zip codes have 5 digits and there are more than 8,200 of them (only the ones which represent a geographical region). Very often, geographical visualizations use only the first 2 digits of the zip code, dividing Germany into 95 regions. 95? Shouldn’t that be 99? No. the remaining 4 are unused or do not represent a geographical area.

So, here is a map of Germany by zip code 2 (what we call PLZ 2):

And here is the comprehensive one: Germany by zip code 5 (PLZ 5) with more than 8,200 shapes:

The Disadvantages

Let’s call a spade a spade: using Choropleth Maps in Microsoft Excel is nothing else than the poor man’s geographical visualization tool. It takes some time and know-how to set them up and they come with a couple of disadvantages.

The zip code 2 map with 95 shapes works pretty well. With the zip code 5 map, however, the technique is definitely reaching its limits:

• More than 8,200 shapes bloat the file size to almost 9 MB
• Although I used the optimized VBA code provided here, the map takes around 10 seconds to update on my machine. Everything else than a good performance and user experience

Still, if you do not have a professional geographical visualization tool available, Microsoft Excel can be a reasonable workaround, even for a very detailed map like Germany by zip codes.

The Download Links

Germany by zip code 2 (PLZ 2):

Download Choropleth Map Germany by Zip Code 2 (Microsoft Excel 2007/2010, 965.7K)

Germany by zip code 5 (PLZ 5):

Download Choropleth Map Germany by Zip Code 5 (Microsoft Excel 2007/2010, 8910.4K)

The data used in these example workbooks is made up.

Acknowledgements

Many thanks go to the developers of Indiemapper, Zachary Johnson, Andy Woodruff, Dave Heyman, Ben Sheesley and Mark Harrower for creating this great online tool and to Axis Maps for providing it for free. Thank you!

Stay tuned.

The recent post described how to use an ActiveX Spreadsheet Control as a tooltip on Excel worksheets. At first sight this seemed to be a nifty little idea to spice up tooltips on Microsoft Excel worksheets. In the meantime, however, I had to learn that there is a major pitfall:

The heart of the solution, the ActiveX Spreadsheet Control is part of the so called Office Web Components. So far, so good. The problem is that Microsoft does not ship the Office Web Components with Office 2007 and later anymore. In other words, in a clean, new Office 2007/2010 installation, the ActiveX Spreadsheet Control is not available anymore.

Microsoft still provides the Office Web Components for free download (Office 2003 Add-in: Office Web Components), but there is still a major drawback: if you want to use the described technique, you have to make sure that every user of your workbook either uses Excel 2003 or has the Office Web Components installed.

At the end of the previous post I announced a follow up article with more practical examples using the technique. Knowing now what I didn’t know before, I pondered if this article would still make sense. To cut a long story short, I finally decided to publish it despite the limitation described above. Maybe some of you will still find it interesting and useful.

Today’s post provides 3 different use cases of how to take advantage of the ActiveX Spreadsheet Control in a tooltip:

• a scrollable tooltip
• side calculations within a tooltip
• an improved version of the Summary Card tooltip

As always the articles provides all Excel workbooks for free download.

Example 1 – Details on the selected cell or row in a scrollable list

Let’s assume we have an Excel workbook with unemployment rates from 2008 to 2012 by state on the first worksheet and by county on the second sheet. An ActiveX Spreadsheet Control can provide the county details for the active row (i.e. the selected state) in a scrollable tooltip:

The technique for setting up the Spreadsheet Control is exactly the same as described in the how-to post: Interactive Tooltips on Excel Worksheets.

What’s different?

Firstly, you need 2 named ranges for the data (e.g. “myStates” and “myCounties”).

Secondly, the VBA is different. The code in the sheet module (sub Worksheet_SelectionChange) is even simpler than before.  However, you need another function retrieving the county details for the selected state (function GetCountyDetails in module modGetCountyData). The code isn’t very complicated. Basically it is working like a filter, i.e. it loops through all counties and returns only those belonging to the current state (selected row).

Here is the workbook for free download:

Download Interactive Tooltip Lookup Details (Microsoft Excel 2003, 670.5K)

Example 2 – Side calculations based on the value of the active cell

In this example, we have data including some currency values (like prices or margins) and a second worksheet containing the exchange rates for other currencies updated via a data connection to a financial website.

The idea of this use case is to provide a list of other currencies, including the exchange rate and the conversion of the USD amount of the active cell into each currency. The amounts in all currencies are not calculated by the VBA code but rather by a simple formula in the ActiveX Spreadsheet Control (e.g. = B4*$C$1). Hence, the user has also the option to overwrite the USD value in cell C3 of the tooltip (top right) and immediately gets the results for the entered value:

Again we need a second range name (“myExchangeRates”) and a few modifications in the VBA: the code updates the exchange rates in the ActiveX Spreadsheet Control, checks if the active cell has a currency format, writes the value of the active cell to cell C3 of the Spreadsheet Control and finally displays and positions the tooltip:

Download Interactive Tooltip Exchange Rates (Microsoft Excel 2003, 123.5K) 

Example 3 - Enhanced Summary Card

The how-to post used the example of a Summary Card in the tooltip, i.e. showing aggregation results of the selected range, like sum, average, maximum, etc.

A helpful little tooltip, but it comes with 2 limitations:

1. It does not provide much more information than Excel’s status bar displays already by default
2. It aggregates all data of the selected range. This is fine as long as the selected range only covers one column of the data, but it doesn’t make much sense to display e.g. the sum of order quantity and order prices.

The Summary Card of Tableau, for one, shows the aggregations per measure used in the view:

Having said that, we can enhance our Summary Card spreadsheet tooltip to do something very similar, i.e. to show the aggregations in different columns, like this:

The VBA code is a bit longer (55 lines of code compared to 37 in the simple version), but it isn’t really complex.

In a nutshell it loops through the columns of the selected range, performs the calculations column by column, writes it to the according columns of the ActiveX Spreadsheet Control and finally adjusts the viewable range of the tooltip.

There is a lot of room for optimization (e.g. the count is always the same for all measures, so it should be displayed only once above the table, see also the Tableau Summary Card screenshot above), but I am sure you get the idea. For the details please have a look at the workbook:

Download Interactive Tooltip Summary Card Enhanced (Microsoft Excel 2003, 86.5K) 

Conclusion

As mentioned in the update at the bottom of the previous post and in the introduction to this article, the entire technique has one major limitation: The ActiveX Spreadsheet Control is not available by default if you are running Excel 2007 / 2010. However, if you are still using Office 2003 or don’t mind to install the Office Web Components, a tooltip including the ActiveX Spreadsheet Control might still be an interesting interactive feature for your models.

Stay tuned.

Tooltips are one of the most helpful features when prospecting large data sets.

We already had a couple of articles talking about tooltips, on charts and on worksheets:

Tooltips on Microsoft Excel Tables

Charts in Excel Table Tooltips

Display all Fields of a Row in large Excel Tables

Better Chart Tooltips with Microsoft Excel 2010

Today’s post is trying to take this to the next level: an interactive tooltip on a Microsoft Excel worksheet. You may ask:

“What the heck is an interactive tooltip?”

What I am thinking of is a tooltip enabling you not only to read additional information but also interact with it. E.g. copy data from the tooltip and paste it somewhere else, do side calculations based on the currently selected data, browse through a longer list of data in the tooltip, etc.

Impossible? No it isn’t. Microsoft provides a very useful ActiveX control which enables us to do exactly this: the Microsoft Office Spreadsheet Control. Today’s post shows how to take advantage of this ActiveX control in order to provide an interactive tooltip on Excel worksheets - as always including the Excel workbook for free download.

The Idea

Microsoft Office / Excel comes with a lot of very interesting and powerful ActiveX controls. One of them is the Microsoft Spreadsheet ActiveX Control. It may sound a bit weird to use a spreadsheet control within a spreadsheet, but it offers a lot of interesting options.

The basic idea of this post is using the spreadsheet control as an interactive tooltip on Excel worksheets. In order to keep things “as simple as possible, but not any simpler” (Albert Einstein), I will describe the idea by displaying a “Summary Card” (see also Tableau Quick Tip #1 - The Summary Card) of the selected range in a tooltip on simple list of data in Excel:

The Summary Card displays sum, average, minimum, maximum, count, median and standard deviation of the values in the selected range. Agreed, all of this information (except for median and standard deviation) is available in Excel’s status bar at the bottom right already (in Excel 2007/2010, in Excel 2003 and earlier you only have one of them). However, this is just the first step to show the technique in general. I will provide more useful examples in the next article.

Let’s get started. Here is the how to:

The Step-by-Step Tutorial

Step 1 – Bring your Ducks in a Rows

Insert a data table into a Microsoft Excel worksheet and define a name for the range containing the data (“myData” in my example).

Step 2 – Insert a Microsoft Spreadsheet ActiveX Control

On the Developer tab click on Insert and More Controls:

In the following dialogue window scroll down to the Microsoft Spreadsheet 11.0 control and click on Ok:

The cursor changes to a cross. Simply click somewhere on your worksheet and the Spreadsheet Control will be inserted.

Step 3 – Change the Properties of the Microsoft Spreadsheet ActiveX Control

On the Developer Tab click on Design Mode, select the ActiveX Control and click on Properties:

You do not have to change much here. Simply define the caption (“Summary Card” in this example), set DisplayOfficeLogo and DisplayToolbar to False and define an appropriate height and width of the control object.

Step 4 – Change the Options of the Microsoft Spreadsheet ActiveX Control

Right click on the control and select Microsoft Office Spreadsheet 11.0 Object and Command and Options:

First tab we go to is Workbook:

Uncheck the scrollbars and the sheet selector, delete all sheets except for Sheet1 and rename the sheet (Summary Card).

Next we go to the Sheet tab:

Uncheck row headers, column headers and gridlines and define the viewable range. For the summary card example, we need only 2 columns and 7 rows, so we set the viewable range to $A$1:B$7.

Finally we define the column width and row height on the Advanced tab:

Step 5 – Format the Cells on the Microsoft Spreadsheet ActiveX Control

You can use the Format tab in the Command and Options window (see step 4) to format the cells on the worksheet. However it is much easier and faster if you simply create a range on the Excel worksheet, format it as you like (fill colors, borders, number formats, etc.) and simply copy this range and paste it into the ActiveX control. Before you do so, you have to turn off Design Mode on the Developer tab.

Step 6 – The VBA

You guessed it: to make this work we need some VBA. But don’t worry, it is a really small code snippet (2 subs, 37 lines of code). The main part is the event driven procedure Worksheet_SelectionChange. There is one additional function checking if all cells in the selected range are numeric.

Here is what the code does in a nutshell:

1. Check if the active cell is in the data table, i.e. within the defined range name (“myData”)
2. If so, check if the selected range has more than one cell and all cells in the selected range are numeric
3. If so, make the ActiveX control visible, position it at the bottom right of the selected range and insert the results (sum, average minimum, maximum, count, median and standard deviation) for the selected range using the Application.WorksheetFunction method
4. If not, hide the ActiveX control
5. Save the workbook as an Excel Macro Enabled Workbook

That’s it.

The Pros

Well the pros are obvious, aren’t they?

The first one is the ease of implementation: 6 steps and a small piece of code and you are good to go.

Even more important is the interactivity. The ActiveX control is a limited spreadsheet in terms of its functionality, no doubt about it. Having said that, it offers many options you would not expect to have in a tooltip: users can do their own side calculations by entering values into the cells of the tooltip, you natively have the scrolling options for larger tables to be shown, you can easily copy content from the tooltip and paste it somewhere into the Excel workbook or elsewhere, and many more.

Last but not least, the technique offers many possibilities to create a highly interactive and responsive tooltip to prospect data tables. Maybe not suitable for all of your models, but definitely a very interesting option in some use cases.

The Cons

Well, there are not many cons I have encountered so far except for this one: if you change the zoom factor of the worksheet, the ActiveX control resizes too, but the content (i.e. the cell range of the spreadsheet tooltip) doesn’t. You will see something like this:

Not really nice, but a minor drawback in my humble opinion.

The Download Link

Download Interactive Tooltip Summary Card (Microsoft Excel 2003, 73.5K)

Acknowledgements

Special thanks go again to Jordan Goldmeier of the Option Explicit VBA Blog for reviewing my workbook, for his feedback and for encouraging me to write this article. Thanks, Jordan!

What’s next?

As mentioned above, this technique offers a variety of possibilities. Agreed, the Summary Card example does not really take advantage of the interactive features (except for the option to copy the content of the tooltip and paste it somewhere else).

The next post will provide a small selection of examples of what you can do with the ActiveX Spreadsheet control and how to make a really interactive tooltip.

Stay tuned.

Update on Sunday, November 11, 2012 – A serious limitation

In the cons section of the article I claimed the zooming issue would be the only drawback of this technique. It turned out that unfortunately this is only half the story. The ActiveX Spreadsheet Control is part of the so called Office Web Components. In his comments to this post (see below), reader "Sg" pointed to the fact that Microsoft discontinued the shipping of the Office Web Components with Office 2007 and later. This one was new to me. Although I am running Office 2010, the Spreadsheet Control is available on my computer, probably because I have also installed Microsoft Project 2003.

Microsoft still provides the Office Web Components for free download (Office 2003 Add-in: Office Web Components) and if you install it, I suspect the ActiveX Spreadsheet should also work with Excel 2007/2010.

However, this is a serious limitation of the technique: you can’t easily distribute the workbook to other users running Excel 2007/2010. They would have to install the Web Components first and this considerably decreases the applicability of the ActiveX Spreadsheet Control for professional Excel models.

Recently we had an article visualizing the beer prices and beer price development at Oktoberfest with a Tableau dashboard: O'zapft Is!

Oktoberfest is long over already and there is peace in the valley of Munich again. Having said that, I would like to come back to this visualization once more. We had a couple of posts here on Clearly and Simply showing how to bluff Tableau’s great interactive features with Microsoft Excel:

Bluffing Tableau Actions with Microsoft Excel

Bluffing a Visual Cross-tab with Excel

The Next Level of Interactive Microsoft Excel Dashboards

Microsoft Excel Site Catchment Analysis

Drawing on this tradition, today’s article presents an Excel workbook emulating the Tableau Oktoberfest Dashboard. The technique behind this bluff is a very clever approach my blogging colleague Jordan Goldmeier published in several articles over at his Option Explicit VBA Blog.

Stealing Jordan’s idea, today’s post shows how to recreate the Oktoberfest Beer Price Tableau dashboard with Microsoft Excel, as always including the Excel workbook for free download.

The Challenge

O'zapft Is presented a Tableau Dashboard visualizing the prices of beer and other beverages and the beer price trends at Oktoberfest, including

• a map displaying tooltips with beverage prices and additional information about each beer tent and a selected year in the tooltips
• a URL action opening a link to the tent’s website on muenchen.de
• a band chart visualizing the beer price development of the selected tent since 2002 within the total range of the beer prices across all tents

The Tableau folks liked my little dashboard and selected it as the “Tableau Viz of the Day” on September 26, 2012: Beer Prices at Oktoberfest.

Today’s challenge is re-creating this dashboard with Microsoft Excel. In the end, the Excel dashboard will look like this:

To be more precise, the Excel Dashboard should not only look like the Tableau Viz, it should also have the main interactive features Tableau provides more or less natively:

Tooltip when hovering over a tent

Open a link in a web browser after clicking on a tent

And of course the option to select a year and a tent in a dropdown (combo box) as well as the band chart.

The Technique

The heart of this solution is Jordan Goldmeier’s “Roll Over Macro Execution” technique described in this blog post: How to Create a Rollover Effect in Excel.

The basic idea is stellar: Jordan uses a User Defined VBA Function inside a Excel HYPERLINK function like this:

=HYPERLINK(myUserDefinedFunction(),"")

There are a few interesting things which were totally new to me

• I didn’t know you can use a UDF within a hyperlink
• A UDF can usually not change values or other properties of other cells. However, if they are used within hyperlinks, they can
• The UDF is not only fired when clicking on the cell, but also when hovering over it with the mouse

An impressive and very innovative idea by Jordan which offers a variety of new possibilities. In his latest post, for one, Jordan used this technique for an excellent visualization of an Interactive Electoral Scoreboard of the United States:

Fantastic work, Jordan!

If you want to learn more about the details of this technique, please refer to one of Jordan’s articles:

The Excel Rollover Mini FAQ

and / or to Chandoo’s post featuring this technique:

Interactive Dashboard in Excel using Hyperlinks

Now let’s try to apply this technique to the Oktoberfest Dashboard. Here is the step-by-step how-to:

Step 1 – The Data

This is the easy part. We already have the data available from the original Tableau article (O'zapft Is!). We simply insert 2 worksheets ([data tents] and [data_prices] into a new Excel workbook. The sheet [data_tents] contains the names of the tents, the seats, the breweries and the URLs of the tents. The sheet [data_prices] holds all the prices for all tents and all beverages from 2002 to 2012.

Step 2 – Prepare the Roll Over

First we create a new worksheet (the dashboard) and format it to have a squared cell grid, i.e. cells which have approximately the same width and height. Next, we insert the image of the Oktoberfest:

The Interactive Electoral Scoreboard uses a map of the United States consisting of freeform shapes (one shape per state). To determine the current state (i.e. the shape the mouse is currently hovering over), Jordan developed a very clever technique combining Excel worksheet formulas with some VBA User Defined Functions. I will not go into the details here. To find out more about the approach, please refer to Jordan’s article and workbook.

The “map” of the Oktoberfest does not consist of several shapes. It is just one single image inserted into the Excel worksheet. Hence, we have to replace Jordan’s technique of calculating the correct shape / state by a manual setup.

First we number all 14 (large) tents visible on the map:

Next step is the laborious part of the implementation: we manually insert the numbers of the tents according to their positions on the map into the cells of the worksheet:

The result is a lookup matrix we will later use to identify which cell of the grid belongs to which tent. Since we will need the cells on the dashboard for the HYPERLINK formulas, we move this lookup matrix of tent positions to another worksheet ([control]):

Agreed, this isn’t 100% exact, since the cell grid is not detailed enough to exactly cover the sizes of the tents. However, it is close enough for our purposes. 

Step 3 – The Control Worksheet

Besides the tent position matrix (see step 2 above), some more preparation work is necessary on the [control] worksheet, like

• lists and target cells for the drop downs (year and tent) on the dashboard
• a formula to calculate the position of the selected year and tent in the data
• a formula to retrieve the URL of the selected tent
• a cell range with all labels and formulas we want to show in the tooltip
• a cell range to calculate the relevant data series for the band chart

INDEX, VLOOKUP, MATCH and a couple of  more complex array formulas, but nothing really new under the sun.

Finally we define a couple of names for some cells on worksheet [control], like the index and name of the current tent, the URL and the range containing the tooltip content.

Step 4 – The Dashboard

Step 4.1 – The Hyperlinks

First step of setting up the dashboard is defining a name for the cell range with the tents beneath the image. This range has exactly the same size as the lookup matrix for the tent positions we created in step 2. In my example workbook the name of this range is “myWiesn” (for your understanding: we Bavarians call the Oktoberfest “Wiesn”…).

This is the point where the hyperlink trick of Jordan’s rollover technique comes into play. We insert the following formula in the first cell of “myWiesn”:

=IFERROR(HYPERLINK(Define_HyperLink(ROW(P9),COLUMN(P9),control!I6),""),"")

where control!I6 is the reference to the upper left cell of the tent position matrix on worksheet [control] and Define_Hyperlink is a User Defined Function we will create in step 5. Finally we copy this formula to all cells of “myWiesn.”

Step 4.2 – The Shapes

We need two textboxes on the dashboard:

1. The first one (called “shpClickBox”) has the same size as the cells and is formatted to be invisible (no fill color, no line color). It is used to handle the web actions when clicking on them
2. The second one (called “shpToolTip”) is linked to the cell range on worksheet [control] containing all information to be displayed in the tooltip

Step 4.3 – Everything else on the Dashboard

The usual suspects: 2 combo boxes to select a year and a tent and a band chart (see also: An Underrated Chart Type: The Band Chart) using the data series we calculated on the sheet [control] in step 3. I guess I do not have to explain this in detail.

Step 5 – The VBA

You may expect a lot of very complicated VBA code to make this work. However, the VBA is pretty simple. In fact it only consists of 5 subs / functions and only 39 (!) lines of code in total.

Here is a short description of those 5 subs:

1. In the event driven Worksheet_SelectionChange sub of the worksheet [dashboard], the tooltip shape is set to be invisible if the user clicks some cell outside of the map.
2. The User Defined Function called in the HYPERLINK formulas (see step 4.1) checks first if the cell value is greater than 0 (i.e. the cell beneath the image is a tent). If so, it updates the cell containing the current tent index, positions the click box shape above the cell and positions and shows the tooltip shape. If not, it makes the tooltip invisible. Finally it calls another procedure: SetHyperlink
3. SetHyperlink adds / updates the hyperlink to the current cell at the top right of the dashboard
4. ChangeTentDropDown is hooked up to the combo box for selecting a year and simply makes the tooltip invisible and calls SetHyperlink
5. OpenURL is called when the the user clicks on the click box shape and simply opens the browser with the URL of the current tent

If you are interested in more details, please have a look at the VBA of the workbook (download link see below). If you have any questions, please leave me a comment.

That’s it.

With Tableau, creating the dashboard only took a few minutes. It is by far more work to implement this in Microsoft Excel, but it isn’t impossible. If you can’t get your hands on Tableau, you can still create create an interesting and highly interactive dashboard with Microsoft Excel.

The Download Link

Here is the workbook for free download:

Download Beer Prices at Oktoberfest (Microsoft Excel 2007/2010 workbook, 1,042.4K)

Acknowledgements

Special thanks go of course to Jordan Goldmeier of the Option Explicit VBA Blog for inventing and sharing this technique.

Excellent work. Kudos, Jordan!

What’s next?

That’s it with Oktoberfest. I promise. At the very least for this year. I am planning a couple of new posts on something completely different in November for both tools, Excel and Tableau.

Stay tuned.

You make charts all the time, but you don’t really know if this new “data visualization” is the same thing with a pompous name or is something bigger and more complex. Perhaps you could start with a quick image search in Google for “data visualization“. Here is what you’ll get in the first page:

Wow! Fascinating! But… is it relevant for you, for your work? Let’s check the prerequisites:

• Data types that you don’t have in your work;
• Artistic talent you are not born with;
• programming skills you are not interested in;
• tools your company will never buy.

What’s left? Aesthetic enjoyment as a consumer and little inspiration for a chart maker.

Is data visualization for you?

Well, don’t feel excluded from the fascinating world of data visualization just because these results don’t help you in your work. There is a vast silent majority equally frustrated!

Before you return to your simple and boring charts, let me ask you this: do you think that a poetry soiree is a good sample of how people use their mother tongue? Of course not! These search results are like a poetry soiree (in the sense of a very specialized use of language). If you just need a functional use of language, poetry may not be your best role model (don’t get me wrong: we need poetry too).

Is there information visualization without aesthetics?

One of the data visualization founding fathers, Jacques Bertin, believed that data visualization is an objective set of signs and rules, with no place for aesthetics. I actually disagree, but back then the world was in black and white and it was easier to accept it. You can’t avoid aesthetics. It is embedded in every single one of your choices when making a chart.

You can approach data visualization from multiple perspectives, depending on your background, skills or goals. Given the same data set,  a designer, a product manager or a statistician will come up with very different visualizations. The problem, today, is that we are witnessing some kind of “over-aesthetization“. The more visible perspective of what data visualization is about is defined by the results returned by search engines. They obviously emphasize design (beauty tends to be linked and shared more often), not visualizations made by engineers or statisticians. And it all started with Edward Tufte. He must be proud (I always wanted to write this). This is bad, because many people may start to equate data visualization and pretty charts, nice to look at but not terribly useful for their job and their skill sets.

Occam’s razor, or How can we avoid an aesthetic disaster?

To tell you the truth, I’m glad most corporate charts are not listed in the search engine results. Many are ugly, useless and childish (yes, childish). But if you can’t even choose the right tie for your shirt how can you avoid a disaster when making a chart?

Glad you ask. There are no easy answers, but let me put it this way: if your goal is to facilitate chart reading, remove everything that cannot be justified and justify all the other options. That’s probably the safest way to avoid major disasters.

It’s easier to explain with an example. Compare the first chart, using the embarrassing defaults in Excel 2003 (the smiling cow adds the childish touch) with the second chart, also made using Excel 2003.

Let me justify my options:

• Why is the title not centered? Because left aligned text is easier to read;
• Why is the vertical axis line not displayed? Because most of the time is unnecessary, specially when you have axis labels and grid lines;
• Why don’t you use a legend? Because direct labeling minimizes eye movement and working memory usage;
• Why are grid lines light gray? Because they are less relevant than the data itself;
• Why are grid lines dashed? To differentiate them from the horizontal axis line;
• Why is the line Chicken wider than the others? Because it is the series that interests me the most and I want to draw your attention to it;
• Why there seems to be three colors with varying tones? For you to realize that the series in each group have something in common (red meat, poultry, fish) ;
• Why was the happy cow removed? Because its childish, distracts and adds no benefit whatsoever.

I suggest you to continue the exercise with some more questions and answers (why don’t you label Lamb and Veal? Why is Year on the right?). At first, not all your answers will be coherent and consistent with your goal but you’ll get there.

Color is one of the major components of aesthetics and very hard to manage. The colors above were not chosen at random. The reddish tones are associated with red meat, orange with poultry and blue with fish. Is this the best possible color combination (assuming it exists)? Far from it. This is just a choice that I can justify, and that in general will be better than a different one base on personal taste alone.

This chart will not win a beauty contest in the near future but, because your goal is to facilitate reading and you are forced to justify all your options, the risk of an arbitrary solution based on a not-to-be-trusted aesthetic sensibility is greatly minimized. Gradually your answers will become more consistent, the process more natural and you’ll find your own style.

There are multiple aesthetics

Newton’s Binomial is as beautiful as Venus de Milo. What happens is that few people notice it.

Perhaps we could follow Fernando Pessoa and write:

Minard’s map of Napoleon’s March is as beautiful as the Sistine Chapel.

Beauty in a chart lies not in its ability to provide a moment of aesthetic enjoyment but in helping us understand the world, elegantly. So, start making elegant charts and aesthetics be damned!

P.S.: If you’re interested to find the right balance between aesthetics and function in the context of data visualization, you cannot avoid Alberto Cairo’s The Functional Art, probably the best data visualization book published in 2012.

______________________

Post from: Excel Charts Blog.

Aesthetics be damned

The original post is titled Aesthetics be damned , and it came from The Excel Charts Blog .

I have work orders on each row with an open and (possibly) a close date on each row. I want to plot a chart showing dates and the number of open cases.

This is a queue problem – items enter the queue and at some point leave it. Or we have:

I have a number of treatment rooms in my clinic. Each row of data has the time the patient entered and the time the patient left. I want to understand by hour of the day what rooms are busiest.

This is a utilization problem. Both can be solved in different ways and have multiple solutions depending what the end result needs to be and what the data looks like. This post looks at queues – the essential difference is that you only care about dates where something happens and don’t care about dates with no data. Hopefully that will become clearer as they are subtlety different.

Here’s my data on Sheet 1. You can see we have some cases that were not closed.

At the moment there’s no way of getting to that chart of open cases across the year. The Single Table SQL generated by Tableau is simply:

SELECT [Sheet1$].[Closed] AS [Closed],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 [Sheet1$].[Opened] AS [Opened]
FROM [Sheet1$]

We cannot plot the queue because open dates and closed dates are separate dimensions. We could reshape the data in Excel (using the add-in if we wanted) to look like this:

But that’s not always practical if the data is more than a one-off or isn’t coming from Excel. It’s easier to reshape the data using Custom SQL. Instead of doing a single table connection to the data we choose Custom SQL:

And then edit the SQL that Tableau created for the single table connection so that we pull open and closed dates onto separate lines but under one dimension ([Date]) and have a counting mechanism we’ll call “Number Open”:

SELECT [Sheet1$].[Opened] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Opened" as [Date Type],
 1 as [Number Opened]
FROM [Sheet1$]
union all
SELECT [Sheet1$].[Closed] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Closed" as [Date Type],
 -1 as [Number Opened]
FROM [Sheet1$]

We don’t really need both [Date Type] and [Number Opened] as we could create calculations in Tableau if we only had one of them, but I like to keep them there for my sanity. Now the data looks much like our reshaped data – we run though the data set once collecting all of the open dates as a date, and marking them Open ([Date Type]) and increment the number open by one, then run through the data again (bottom paragraph), add the new rows on (union all), mark them as close dates and decrease the count of open by 1. Note when you union the columns must be in the same order, so I collect date first, regardless of it being open or closed.

Now we’re beginning to get somewhere. I can create this chart by using our date, and the quick table calculation “Running Total”. Because we have the Number Open measure, the running total takes the line up and down as we need it:

It’s not great, but all of the data I need is there. I can see that in early Feb there were a max of 5 cases opened, and that by the last date I still have 2 cases open. Note I had to filter null dates out to get the chart to show correctly. Tableau cannot draw stepped charts; the lines are connected by slopes. We can rectify this by adding some more points to create the steps. We need to add a point just before each date (start or finish) to correctly draw each step. As I’m using Excel I can use the dateadd in the custom SQL. For other sources you may have to use a simple subtraction or different function. We’ll add a point one second before each date:

SELECT [Sheet1$].[Opened] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Opened" as [Date Type],
 1 as [Number Opened]
 FROM [Sheet1$]
 union all
 SELECT [Sheet1$].[Closed] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Closed" as [Date Type],
 -1 as [Number Opened]
 FROM [Sheet1$]
 union all
 SELECT DATEADD('s',-1,[Sheet1$].[Opened]) AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Padding" as [Date Type],
 0 as [Number Opened]
 FROM [Sheet1$]
 union all
 SELECT DATEADD('s',-1,[Sheet1$].[Closed]) AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Padding" as [Date Type],
 0 as [Number Opened]
 FROM [Sheet1$]

Our datasource is now four times as large, but we can now draw a chart in exactly the same way as before but with the steps:

I can add Department or the IT Person to the view on rows (or both to color and size) and break the chart correctly. This may be the end point for many people and the chart they need. However, because of the running sum it’s difficult to reuse this data elsewhere and get the view you want. When I’ve done this before I’ve copied the summary data (view data) out, pasted it into Excel, and brought it in as a new source. This is not an ideal solution. Even if I do this, what if I want know about the open cases on Feb 20th? There are no cases that open or close on that date so there is no data point for it. This has now become a utilization problem, and needs to be solved in a different way.

Here’s the data file: Work orders and the Tableau Workbook: Queue

I would love to take credit for these methods, but they are a combination of solutions created on the forums  by Joe Mako, Richard Leeke, and Jonathan Drummey. 

Interactive version available here.

As explained by Michael:

This display is a perfect example of why we need dashboards in today’s world. Behind this colorful showing of charts and sliders are thousands upon thousands of data points. Spreadsheets are not easy to read – dashboards are.

The entire design philosophy of a dashboard is based on speed.

Consolidating data into a purely visual format allows users to identify trends and make informed decisions far quicker than analyzing numbers.

This dashboard gives a complete overview of a fictional construction agency. It tracks all the metrics related to running that company, using a series of input and output elements.

The selection lists, drop down menu, and calendar allow the user to modify the outputs on the charts. If, for example, a user would like to know how many accidents there were in California between July and August, they can tweak the dashboard appropriately.

Try this dashboard for yourself at this link here or see our professional design tools at http://www.inetsoft.com.

Becoming a Data Visualization Anarchist

I think things can be changed from the inside, improving the way people use Excel. I write for Excel users because I’m one of them. That’s not going to change soon. But I love data visualization, not the tools that make it happen. I specially like interaction, multiple charts and making them available on the web. And I need to manage more data (not big data, just more data). Some things can’t be done in Excel or require too much effort.

The Portuguese poet Fernando Pessoa has a funny short story called The Anarchist Banker. The idea is that only a banker can be a true anarchist, because only a banker can be free from “social fictions”, specially money… In data visualization, this means getting the tools out of the way, by learning them or avoiding them.

I chose the learning path and I’m learning R now. I always wanted to make those scatterplot matrices. And I want to play with the ggplot2 package. A programming language is covered but R is not going to pay the bills.

Enter Tableau and Qlikview

I will not complicate matters  by discussing how I chose Tableau and Qlikview and not Spotfire, for example. I just want to choose one. Qlikview vs Tableau. Comparison articles like this and this are very helpful, but a man is a man with his circumstances. Each starting point is different from everyone else. Let me tell you what I think I know about these tools in this early stage.

Tableau

I like Tableau, I have to admit it. I like the fact that you don’t have to fight stupid defaults in design and formatting, because I share the same data visualization principles.

I like its enthusiastic and knowledgeable community. Let me give you two simple examples. I spent a lot of time making this horizon chart in Excel, and Joe Mako quickly came up with a better version in Tableau.

Then I tried to be creative with the bamboo charts and Joe Mako strikes again, with a better implementation. I’m starting to get nervous… (kidding)

I know and respect many Tableau users, not because of Tableau but because I share the same views regarding data visualization.

Apparently, maps in Tableau are good enough, so that’s a good point.

And as a blogger, I want to make my work available to the web, and Tableau Public is a nice option (my population pyramid).

The Guardian often publishes Tableau visualizations. I’d like to try that with the local newspapers here.

On the other hand, we know how stubborn some datavis experts are. Is Tableau that stubborn? Can clear vision and the right principles become a straitjacket? I really hate straitjackets (“the idea of”, never actually tried one…)

In my country, Tableau is virtually unknown and I am not sure if I want to sell shoes in Africa.

Qlikview

I know even less about Qlikview. The first chart I see in its video is the  pie chart above. Not exactly a shiny example of good data visualization principles.

Apparently there is a very active Qlikview community on Linkedin but not so much on Twitter. Probably this is meaningful.

I keep reading that Qlikview is better than Tableau when it comes to making dashboards, while you should explore the data with Tableau. It’s a good point in favor of Qlikview (that’s what I need now). Extensions and the market seem to be interesting too.

Qlikview has several business partners here. Actually, I was invited to work in some Qlikview projects in 2013 (obviously I have to learn the basics until then). They can pay a lot of bills.

Its not always about features

Not everything is black and white, not everything can be decided based on feature-by-feature comparison. Not everything is heart, not everything is reason. If I choose Tableau, my data visualization skills will improve a lot. Qlikview is harder to predict. I’m sure there are many users that dislike the pie above. If not, Qlikview can be more, hummm, challenging.

What I’m going to do

I mentioned those Qlikview projects, but I’ll try to remove them from the equation, at least for now.

I have a simple dashboard in Excel and I’d like to create Tableau and Qlikview versions. That’s probably one best ways to evaluate a tool, using my own work.

So, can you help me?

I’d love to learn from you. Can you answer questions like:

• How do they compare regarding maps? Is it simple to add your own maps?
• Is it true that it’s easier to make a a centrally designed dashboard in Qlikview, while Tableau has a more exploratory nature?
• How can I share a Qlikview chart in my blog?

______________________

Post from: Excel Charts Blog.

Qlikview vs Tableau? I have to choose and I’m not sure

The original post is titled Qlikview vs Tableau? I have to choose and I’m not sure , and it came from The Excel Charts Blog .

You already know that a bar chart helps you to compare data points, while a line chart is better at displaying trends, right? But you keep staring at them, not knowing which one to choose.  Suddenly, your little guardian angel whispers: “the bar chart is wrong”.

The bar chart? What’s wrong with the bar chart? And why is the line chart OK?

Then you realize that the vertical scale starts at 30, and apparently it should start at zero, so you change it in both charts:

That leaves a lot of white space under the line. The experts say you don’t have to start scales at zero if you are using line charts. So you revert the changes in the line chart:

Ah, yes! Now you can choose one of them. Your guardian angel agrees that both charts are correct. So, do it, pick one!

What’s the problem? Is there something bothering you?

Humm, I see. They don’t look that similar. You believe that people may draw different conclusions depending on the chart you choose.

OK, let’s discuss this a bit. Let’s talk about resolution.

What is chart resolution, anyway?

Higher resolution is usually a good thing. It means that you can see more clearly the difference between data points. To improve resolution in a chart, you zoom in, using the numeric scales:

How much you can zoom in? Well, the lower limit should be the first nice round number below the minimum value in your data set, and the upper limit should be (you’ve guess it) the first nice round number above the maximum value.

You have to make a little change to the rule when it comes to bar charts. In a bar chart, people compare heights, so if the bars are not proportional to the data they encode, you are misleading your audience. So, the charts above are both correct, but the one below is not:

That’s why the lower limit in a bar chart should always be the value that maintains the right proportions (usually zero). So, the take-away message is, improve resolution byt changing the scale, but in a bar chart you must keep proportions aligned with the data.

What about slopes?

Line charts are more subtle. Both charts below are correct:

The only difference between them is that the one on the left has a higher resolution, and in general is a better option considering Cleveland’s suggestions for banking to 45º (slopes should average around 45º). You can do it by changing the numeric scales and/or the chart aspect ratio. This is a suggestion (an excellent suggestion), but it also tells you that there is no strict rule to obey.

To be completely honest, I don’t care much about scales or aspect ratio in line charts, as long as they do not go overboard. What really matters is to have something to compare with. In the post Weltanschauung, Lies and Charts, I use these politically biased charts…

… and argue that only when you have more than one series you can learn anything from a line chart, like this:

So it doesn’t matter much what you do with scales or  aspect ratio, as long as you have two or more series and your goal is to compare them.

You can learn more about scales in Naomi Robbin’s Creating More Effective Graphs and she often writes about it, like in here and here.

Perhaps you could use this as a rule of thumb: use a bar chart when you have a single series and a line chart when you have two or more series. It will not always work, but it’s a good starting point, don’t you think?

By the way: “méfiez-vous des morceaux choisis” is a lovely French expression that roughly translates to ”beware of selected pieces”.

______________________

Post from: Excel Charts Blog.

Bars and lines: méfiez-vous des morceaux choisis

The original post is titled Bars and lines: méfiez-vous des morceaux choisis , and it came from The Excel Charts Blog .

One little thing bothered me, though. Explicitly or not, many stories ended with the words “and now you know why”.

And now you know why.

We, the data visualization community, love to talk about visualization as a kind of storytelling but can we honestly say that our visualizations can deliver a good story? Can our audience say, “now we know why”?

We can argue that this “visual storytelling” is nothing more than new-agey mumbo jumbo. After all, we just need to process data, get insights and act. We don’t have to mimic a folktale.

If we do agree that storytelling can help us creating better visualizations, I have a humble suggestion: from now on, each datavis conference should be required to invite a renowned storyteller and let him/her show us what storytelling is really about. I liked Ben Haggarty and Jan Blake by the way.

If you want to improve your charts/infographics/dashboards, here is a simple trick: write, below them, the words “and now you know why”. But first make sure they deserve it.

______________________

Post from: Excel Charts Blog.

And now you know why

The original post is titled And now you know why , and it came from The Excel Charts Blog .

We are going to resist the urge to identify them and use our preconceptions against them. Let’s dig deeper instead. Some groups are more exposed to the risk of poverty. Let’s see what happens when we split the data by sex:

How do you read this? For each sex, at the top you have the national average and at the bottom you have the group value. If the group has a lower risk, the line points to the left; otherwise, it points to the right. So, in this case the data shows very consistently that females have a higher risk of poverty or social exclusion than males.

Perhaps there are other factors. with higher variability.  Let’s try age:

Lots of interesting stuff here: leaving your parents (16-24) is risky, while if you are 65 or over it can be heaven or it can be hell, depending on the country you live in. Let’s now add education:

As expected, there is a strong correlation between education level and risk of poverty. What about income and household type?

As you can see in the income, poor people run an extremely high risk of poverty (Monsieur de Lapalisse dixit). Explore the household type. Very interesting stuff too.

If you want to focus on the leaves only:

Here is the complete chart (click to enlarge):

Here is a comparison between two countries, Norway and Romania.

The bamboo chart:  the bastard son of Mr. Slopegraph and Madame Parallel Coordinates.  Do you like it? Do you find it useful? Confusing, perhaps? Do you know of a similar chart (link, please)?

How would you represent the same data? Here is the dataset (xls) for you to play.

______________________

Post from: Excel Charts Blog.

Bamboo charts: People at risk of poverty or social exclusion

The original post is titled Bamboo charts: People at risk of poverty or social exclusion , and it came from The Excel Charts Blog .

The video show how to set up various widgets and reports that allow drill down examination of the data. Pay particular attention to the Executive Dashboard setup and how the left side displays the marketing funnel statistics (unique pageviews, unique visitors, engaged visitors and RSS subscribers).

Here is the pdf of the full size infographic:

http://www.boostlabs.com/wp-content/uploads/2012/05/top-gear-infographic-9.pdf

This excellent panel discussion focuses on what they DON’T teach you about entrepreneurship in business school. This group of successful entrepreneurs reveal what they learned in real life business that they never could have been taught in school. They also discuss what courses were actually most helpful to them and also what courses they wish they took but didn’t.

A strategy map is an extension of the Balanced Scorecard and was created by Kaplan and Norton.

Reasons for using strategy mapping include advantages across several categories. When properly executed, strategy maps:

Create a line of sight between individual efforts and organizational objectives.

Translate strategy to operational terms.

Align people and action.

Help put a value on things traditionally viewed as “hard to measure”.

Here’s a quick look at the overall process of building a balanced scorecard:

The process consists of seven steps over three phases:

• Phase I: The Strategic Foundation
• Step 1: Communicate and align the organization around a clear and concise strategy. This is the fundamental starting point behind everything else. Your strategy is what“feeds” the Balanced Scorecard.
• Step 2: Determine the major strategic areas or scope for getting the organization focused on those things the organization can actually do.
• Step 3: Build a strategic grid for each major strategic area (step 2) of the business. Out of all the steps in the entire process, this can be the most difficult since we must take our entire strategy (step 1) and transform it into specific terms that everyone can understand. And everything must be linked to form one complete strategic model.
• Phase II: Three Critical Components
• Step 4: Establish Measurements: For each strategic objective on each strategic grid, there needs to be at least one measurement. Measurement provides the feedback on whether or not we are meeting our strategic objectives.
• Step 5: Set Targets for each measurement: For each measurement in your scorecard,establish a corresponding target.
• Step 6: Launch Programs: Things will not happen unless the organization undertakes formal programs, initiatives or projects. This effectively closes the loop and links us back to where we started – driving the strategy that was formulated in phase I.
• Phase III: Deployment
• Step 7: Once the Balanced Scorecard has been built, you need to push the entire process into other parts of the organization until you construct a single coherent management system. This pulls everything together, allowing successful execution of your strategy.

And here’s a look at some of the tools in the course:

Sometimes a very straight forward approach in data visualization is all you need. Let’s take a look at this comparison of the cost of having lunch at the best restaurants in Great Britain as ranked by Michelin. Well, maybe all you really need to do is to stack a really big bunch of bar graphs and call it a day!

Read more about this Michelin infographic here: http://www.coolinfographics.com/blog/2012/6/4/eat-at-the-best-british-restaurants-for-less-than-you-think.html

This one in particular makes for a good example. You don’t just have the challenge of translation, but all the left to right, right to left parsing issue. It’s almost twice the work!

You can read more about this infographic at this link: http://www.coolinfographics.com/blog/2012/6/5/market-and-competitive-intelligence-trends.html

This first one is from CapGemini and it titled “Business Analytics and Big Data: The Battleground for Competitive Advantage”.

And here is one from IBM called Designed for Data. It’s part of their i for Business series.

This one is in a super wide screen format and is also from IBM. It’s called Business Analytics: Turning Data into Insight. It starts off with a very interesting fact: All the data in the world that existed up to 2003, we generated now in two days.

And this pitch for support, training, and consulting services for IBM Business Analytics from Inca.

The first video below is the music video. The one below that is “The Making of Java Rap”.

And here’s a bonus video:

Plus, of course, you know what this is:

2011 was a bad year for tornadoes, and 2012 has started with some dangerous ones as well. I’ve been wanting to visualize them for a long time and finally found a source of clean data – the Tornado History Project which allows you to download data that was cleaned from the Storm Prediction Center. There is some really interesting information in there:

• Time of day peaks and lows for tornadoes
• Most dangerous time of day (there are fewer storms at night, but they are just as dangerous)
• Southern states have a completely different set of peak months than the ‘tornado alley’ states
• The sheer number of tornadoes per year and on aggregate
• The increase in number of tornadoes recorded (lots of smaller ones are now recorded that were never before)

It also makes you think about population growth as state and once-rural population increases and how deadly encounters with tornadoes are likely to increase. Equally, how the advent of better weather radar and emergency warning systems can help save lives (excluding 2011, for tornadoes 4 or more on the Fujita – deaths have dropped from between 5 and 10 per storm to 1 to 2). Finally, there are some interesting questions around data quality, especially with historical data. The Fujita scale was only created in 1970 and changed again in 2007, so this brings into question equivalent comparison between the years. Also as the scale is somewhat subjective, based on damage to houses and crops, the data will be affected as our ability to measure damage has improved, but also as the increased likelihood that obvious damage to structures occurs (as there are more structures). And of course, there’s the fact that today we are much more likely to observe weak tornadoes than 50 years ago. Click here to go the viz.

Played properly, the house advantage will typically be somewhere around 0.5% depending on the rules at the table. In other words, for 20 bets of $10, you can expect to lose only $1. Of course that’s unlikely, and you only approach the house advantage after hundreds or thousands of rounds of cards. In the short run, you’ll be up or down with much bigger swings.

The idea behind basic strategy is simple; you know what cards you have, and the up card of the dealer. That allows you to make a decision based on that information. Here’s the strategy table for a typical ruleset:

And here’s the workbook for testing your basic strategy:

Given what cards you have, you can predict the return. The blue squares show the positive return and the red an expected loss. A negative 45% means that by using basic strategy, on average you will lose 45c per dollar bet. This is data from Wizards of Odds.

Blah blah, standard disclaimer. You can’t sue me if I screwed up.

Therefore, given a minimum you want to bet, some tables and casinos will offer better deal. I found a list of blackjack tables with the house advantage calculated at the excellent Wizard of Odds site. I used this list with permission to generate the interactive visualization below. Hover over the top right button for more information on how to interact with it.

Disclaimer: Don’t be suing me when you’ve lost your money and the house advantage was wrong…

• For the same level of drunkenness, what beer has most calories per 100 ml? (hint, sort by descending calories per alcohol %)
• Which has the least?
• What has the most calories per alcohol percent of the light beers?
• Which brewery has the widest spread of alcohol percent and calories (separate the list by brewery, then highlight each brewery on the scatter plot)
• Which beer would get you the most wasted?

To answer the question about light beers, alcohol, and calories: light beer has 35% less calories, but 24.5% less alcohol. To reach the same level of drunkenness (I know that’s not necessarily the goal…) you are only reducing your calorie intake by 14%.

When I say dearth that’s no exaggeration, there is literally one – Stephen and Eileen McDaniel’s Rapid Graphs with Tableau. As with Tableau, the book is highly visual, I would estimate that half of the page space is taken up with charts and screenshots. The first 50 pages takes you on a mission that most new users will go on – your manager wants you to tell the story hidden behind rows of data. The book is designed so that you follow along in Tableau, using one of the sample datasets that Tableau provides as part of the install.

The rest of the book is also meant for you to read the book with Tableau open, and delves into topics beyond the basics that are critical to understanding data and guiding the end users of the data – other chart types, maps, sorting, calculations, color, and methods of distribution of the end result. Despite considering myself an advanced user of Tableau, I learned some very useful things.

I couldn’t find fault with the book – it would be interesting to watch how a completely novice user works through it – with the “curse of knowledge” I knew where to click, what to do, and I couldn’t force myself into pretending I didn’t know. I suspect they’d be fine. The book is subtitled “Create Intuitive, Actionable Insights in Just 15 Days”. I think that’s a reasonable expectation, but don’t take my word for it, read the first few chapters here.

Disclaimer: I asked Stephen and Eileen if they would like me to review the book. They accepted and sent me a complimentary copy which I thank them for. However, this was not in exchange for a good review – this honestly is a great book for new and more advanced Tableau users.

Creating them however is more of a challenge. While it would seem that with some creative label calculations combined with a line chart would suffice, in this data set (and many I would suspect) the line ends are too close together (see first tab below). In fact Tufte has manipulated the positioning of the line ends and labels manually to provide some spread on the axis. This, of course, provides a challenge if you want to automate the process in any way. I approached this by calculating the start and end positioning of each country using a table calculation (see tab 2), and using this to position the countries (tab 3). Of course, this loses the slope information and only shows the position change. To add the slope back in I use a calculation that combines both the index and original value to ensure that the lines cannot end too close to each other (tab 4). I then manually moved the labels to a better position to create the final slopegraph (tab 5).

Comments on the workbook: 1. Note that the label calculation is has the left() statements because of issues when you use extracts – 46.8 turns into 46.8000000001 or similar. 2. I spent ages trying to get this to work, but as soon as I put the label (country plus value) on the label shelf, it would alter the table calculation (I don’t know why as it’s a string). Making the label an attribute stopped that. 3. This method affects the slope of the lines to some degree. Another option would be shift the correct slopes up and down, thus making the vertical positioning slightly wrong (EDIT: This method now appears on sheet 5a. It turns out that this is really hard to get right – the order messes up, and you end up moving everything around – try changing the parameters yourself and getting the spacing and order correct. Looking back at the original, Tufte changes the slope as per my first method).

Below is the seasonally adjusted data for March 2011, going back over two decades. The index is set so that all cities (regardless of median selling price) are indexed at 100 for the year 2000. There are two tabs on the visualization.

The first shows the location of the 20 metropolitan areas covered by the index with marks encoded for size by the absolute percent change of the index from the year prior, and color by the magnitude of the change (big green = big increase from last year in house prices, big red = big decrease from last year). Use page control to cycle through the years looking at the booms and busts. The sparklines below show the index and the percent change month to month, also encoded by color.You can click on a city on the map and it will filter the sparklines to cities in that region. The year on year value compares values from March 2011 to March 2010. Note the only bright spot of Washington D.C. with a positive gain. Detroit is interesting with one of the smallest decreases – sign of a recovery or that prices just can’t fall any more?

On the second tab are the indexes, and Year on Year charts with all of the cities overlaid. Select a city from the list to highlight it. There are some interesting things here such as Dallas which has had a very shallow change. Compare that to Las Vegas or Phoenix. Unsurprisingly, those that rose the fastest dropped the most.

This workbook has some interesting hacks for Tableau concerning using Measure Values – for example because of my method of flipping between the aggregations means that I can’t show the axis as percent when required, I multiply the field by 100 and put a custom number format of 0.0″%” to fake a percent label. Using this Measure Values method is also useful when you need want the ability to flip between different window partitions.

To understand who is using the oil, where it’s coming from, and what would happen if oil exports ground to a stop I retrieved data from US Energy Information Administration. The visualization is held on 3 tabs. The first allows you to explore where the oil is coming from, and where it goes. Sparklines and sorted tables allow for quick understanding of the data. The second concerns reserves of oil, and the third allows us to see how countries’ use is changing as their population changes over time. Some highlights:

• Many countries decreased the amount of oil imported in the last year of complete data (2009), probably due to the global recession. China and India are among the top importers bucking this trend.
• You can normalize imports/exports by population; this creates some interesting anomalies such as the Virgin Islands which have massive per capita imports of crude (and massive per capita exports of refined) due to the Hovensa oil refinery on St. Croix.
• On the second tab – everyone knows about the Middle East, but Canada, Venezuela, Libya and Nigeria have very significant reserves – selecting just a specific region allows exploration of each country
• If countries had to depend on their own reserves (assuming all oil is accessible, and consumption doesn’t change …) the results are surprising – the US has under three years of oil, whereas Chad has over 2050 left. Select the drop down to switch between years left and total reserves
• The third tab shows how consumption has changed with time and population. You can use the page controls to cycle through the years. Note the general upwards trend. Select just Asia and look at the tracks – how China’s population hasn’t grown as quite quickly as India’s, but consumption has increased much more. The size of the the mark is proportional to the consumption per capita – notice how small China’s is compared to Singapore’s

You can interact with the smaller version of the dashboard below, or go the full size one here.