The Feasel Challenge

This is part eight of a series on dashboard visualization.

Well, at this point we’ve nearly crossed the finish line.  The last part of this series covers The Feasel Challenge, which has given rise to the single most important dashboard I have:  the Victory Burrito dashboard.  My challenge is to eat a Victory Burrito in 50 different cities around the world.  A Victory Burrito is defined as a burrito that I eat after a speaking engagement.  I’d like to track my Victory Burrito consumption and see how close I am to beating this challenge.  Specifically, I have four measures that I’d like to track:

  1. How close I am to 50 distinct cities
  2. Where I have had a victory burrito (city, state, country)
  3. Breakdown of victory burritos by chain/restaurant
  4. How many victory burritos I’ve had per year (not unique cities)

Because the data doesn’t update that frequently—maybe once a week—I can create a strategic dashboard which would refresh periodically, like whenever I feel like updating the underlying data source.  Because it’s a strategic dashboard, that helps me define how I want the thing to look and what sorts of visuals fit the motif.

Cousin Joey caught wind of what I was doing and offered to create a dashboard for me.  Now, Cousin Joey doesn’t know much about dashboards but was just learning about Power BI and wanted to do something cool, so I decided to let Cousin Joey run wild.  Here is what he came back with:

Bad Dashboard

Cousin Joey is now banned from creating dashboards.

This is close to the worst dashboard possible, and it took me Cousin Joey more time than you’d think coming up with something this awful.  Let me enumerate the problems:

  1. There is a totally unnecessary background image which is a bit distracting.
  2. The burrito tile on the sides is awful and needs to go.
  3. We don’t need to know that Cousin Joey (why does he refer to himself as Cousin Joey?  Wouldn’t he just think of himself as Joey?) made this dashboard.
  4. We really don’t even need a title and if I did, it wouldn’t sound like a third-rate wrestling competition.
  5. My eye gets twitchy when I see that pie chart, and the donut chart isn’t doing much better.  Those are awful choices to display this information.
  6. The waterfall chart is also a bad use of a good visual—burritos eaten by year is a count which is always positive, and years are independent of one another.  Waterfall charts are good for things like profit and loss metrics over a time frame, but not good in this case.
  7. The word cloud is never a good choice for a dashboard.
  8. This dashboard fails to answer the most important of my four questions:  how many unique cities have I hit so far?

Here is a better version of that dashboard:

Better Dashboard

Made by Cousin Joey?  Nope.

So let’s talk about the changes.  In the top-left corner, I have a gauge.  As I’ve mentioned, gauges are very risky.  But in this case, they tell me my progress toward the goal.  My official goal is 50, but I wanted to say that I can go above and beyond, aiming for 60.  I’m currently at 34 cities, but that number should increase by 3 over the course of the next two months.

Then, moving to the right, I have a line chart and a bar chart.  The line chart shows a count of burritos per year.  I chose a line chart for this because I’m thinking about time series data and want to watch the ups and downs.  This shows that I slacked off a little bit in 2017 in the burrito category (though to be fair, you try to find a Mexican restaurant in Vienna!), so it gives me a little motivation for 2018.

To the right of it, I have a bar chart of burritos eaten by country.  The US dominates this, but I expect (or at least  hope) that I’ll get a chance to add a few more countries to the list this year.

In the bottom right corner, I have a treemap which shows burritos by state/province and city, so I can break it down further.  As the number of countries increases, I can squish this treemap in, or even replace it with a different kind of visual.

Finally, in the bottom left, I have a breakdown of burritos by restaurant.  It’s not that I like Chipotle more than anything else; it’s that you can find a Chipotle nearby in most American cities.  I do plan to make some effort in increasing my Victory Burrito restaurant choices this upcoming year, and that chart gives me some motivation to do so.  One thing that I might think about doing is bundling up the single-serving entries into one category and labeling it as “Others” or something.  If I start getting a space crunch, that will buy me some room.

I’m not going to claim that this dashboard is perfect.  It does, however, answer my four important questions and helps me tell a story of a man traveling around in search of a good burrito.  If you have thoughts on how to make the dashboard even better, let me know in the comments.  And if you want Cousin Joey’s contact information for dashboard consulting, I’m sure I can dig it up…

 

Advertisements

Types Of Visuals, Part 2

This is part seven of a series on dashboard visualization.

Yesterday, I covered useful and interesting (and sometimes both!) visuals.  Today, I cover visuals which are fun to hate.

Pie Charts

Of course I’m going to start with pie charts.  Pie charts are the whipping boy of data visualization and for good reason.

Pie charts show that you hate your viewers.

The best use of a pie chart is to show a simple share of a static total.  Here, we can see that Daredevil has almost half of the critics’ reviews, and that The Punisher and Jessica Jones are split.

This simple pie chart also shows some of the problems of pie charts.  The biggest issue is that people have trouble with angle, making it hard to distinguish relative slices.  For example, is Jessica Jones’s slice larger or is The Punisher’s?  It’s really hard to tell in this case, and if that difference is significant, you’re making life harder for your viewers.

Second, as slice percentages get smaller, it becomes harder to differentiate slices.  In this case, we can see all three pretty clearly, but if we start getting 1% or 2% slices, they end up as slivers on the pie, making it hard to distinguish one slice from another.

Third, pie charts usually require one color per slice.  This can lead to an explosion of color usage.  Aside from potential risks of using colors which in concert are not CVD-friendly, adding all of these colors has yet another unintended consequence.  If you use the same color in two different pie charts to mean different things, you can confuse people, as they will associate color with some category, and so if they see the same color twice, they will implicitly assign both things the same category.  That leads to confusion.  Yes, careful reading of your legend dissuades people of that notion, but by the time they see the legend, they’ve already implicitly mapped out what this color represents.

Fourth, pie charts often require legends, which increases eye scanning.  I took the legend off of the pie chart above, more common is a pie chart like the following:

A pie chart from someone who hates his viewers even more than normal people.

I particularly love this chart because I have a legend, I didn’t sort things by relative size, and I have two pairs of almost-equal data but you can feel that there’s a difference.  The problem is, it’s hard for people to tell which slice is bigger, A or C, B or D.

Alternatives

There are several alternatives to pie charts that you should consider.  If you have a large number of categories you wish to compare, a treemap might be your better option.  If you just have a few categories, I’d use a column chart or a bar chart, depending upon how long the labels are.

The same pie chart as a column chart.  This makes it much easier to see that A is larger than C and B is larger than D, even though I left the ordering the same.

And if you have a two-class visual, just use a pair of percentages or numbers.  For example, suppose you are visualizing a Congressional race with two candidates, where one candidate received 58% of the vote and the other received 42%.  A visual which reads “58% – 42%” is at least as clear to a person as a pie chart, and often clearer.

Gauges

Gauges are another highly risky visual, as they tend to get misused.  Their job is to show either progress toward a goal or the percent fill at a point in time.  In the following example, I have a goal of 50 entries and currently have 34, so I’m inching my way toward the goal.

Looks like I’m halfway there.  Or 68% of the way there, depending upon your perspective.

The above is what I would consider a good use, but even then, I don’t like it very much.  By default, Power BI takes your current value as the 50% marker, so I have this arbitrary selection of 68 as the bottom of the visual.  But if I do want to indicate that I can go above 50, I have to pick some end point somewhere.

We’re familiar with gauges in real life, of course:

20171226_090326

Automotive goals:  hit 150 MPH and 8000 RPM.  One of these is easier to do than the other.

Gauges themselves are fine, but they need to show you either how far along to your goal you are or an easily-understandable current status.  Quite often, people will misuse gauges by showing things without real targets, like the following:

A gauge without a target or intuitive landmark.

In this case, we’re at 34 of 68.  We’re halfway there…but what does “there” mean?  We don’t really know whether this is good, bad, or indifferent.  Even if I switch the vague and generic “entries” with something like revenue or profit, it’s still hard to make heads or tails from the visual alone.

Stacked Area Charts

Another pretty visual that I don’t like is stacked area charts.

A stacked area chart of critic reviews by show and episode.

Stacked area charts take the numbers for each category and lay them on top of one another, so it’s like having multiple line charts which, instead of starting from the origin, start from the prior line.  This can lead to a very appealing visual effect.  Stacked area charts are best used when you want to show the relative and absolute differences of data which changes over time but has relatively few periods, like my 13-episode season.

But here’s the problem with the stacked area chart:  looking at this data, what can I tell you with certainty?  I can definitely tell you the total number of critic reviews by episode:  that’s the top of The Punisher’s line.  Aside from that, I can tell you how many critic reviews there were for each Daredevil episode.  But to figure out how many reviews there were of Jessica Jones or The Punisher requires mentally calculating a difference.

I also, while putting this post together, just noticed that the origin started at 20 instead of 0.  That’s something you can change in Power BI, but goes back to the line chart / dot plot versus bar / column chart issue.  If you’re using area as an indicator of value—which we certainly are doing with a stacked area chart—then you want to start at the origin.  Daredevil looks like it has a suspiciously small number of reviews, but that’s because there’s a solid block of 20 sitting below our viewpoint.

This visual format is also harder to do exactly what it was intended to do:  allocate proportional responsibility for changes over time.  We can see that there was definitely a drop between episode 7 and episode 9. Which shows were responsible for that drop by losing reviews?  Which shows held steady?  Were there any which gained reviews?  It’s hard to tell.

Alternatives

The best alternative to a stacked area chart is a line chart.  Here’s the same data in line form:

Our stacked area chart data as a line chart.

We can see here that The Punisher kept a steady number of reviews, but the other two dropped between episode 7 and episode 9.  That would have been hard to tease out of a stacked area chart with three categories; if you have five or six categories which fluctuate in both directions, it becomes close to impossible.

The downside to using a line chart is that I don’t have a totals line.  But if I add that, I get the same information as that stacked area chart and more.

I could also use a ribbon chart:

A ribbon chart in Power BI

Ribbon charts give more information, especially for desktop users who can mouse over the ribbon sections and get a lot of info from the tooltip. They also show relative changes in rank from period to period.  This is a fairly boring ribbon chart because there was only one change, but this will let you see who’s currently in which position.

Otherwise, ribbon charts suffer from the same flaws as the stacked area chart and also tend to dominate a screen.  If your ribbon chart is the single focal point of your dashboard and you expect people will mouse over the sections to gain more insight, then it can work as an exciting visual.  Otherwise, it tends to be simply too much.

Conclusion

A common theme today is “pretty but useless.”  Dashboards are fundamentally utilitarian in nature:  they exist to provide people timely and accurate information so that those people can do their jobs.  The more noise you throw on—even if it’s pretty noise—the harder it is for people to do their jobs.

This was not an exhaustive look at risky or often-bad visuals, and there are more that I am willing to throw on the pyre (like donut charts) and others which get misused more than they get correctly used (like waterfall charts), but I hope that this smattering of examples serves the purpose of explaining that not all visuals deserve to show up on your dashboard.

Types Of Visuals, Part 1

This is part six of a series on dashboard visualization.

Today, we will look at a few of the many types of visuals available to us.  For each of these, I’ll cover some general information about the visual, as well as good uses on a dashboard.  All of my examples here will be in Power BI and will use a data set of IMDB ratings and votes for each Marvel Netflix series.

Tables And Matrices

The first type of visual is the table or matrix, which I’ll look at as a unit instead of breaking them out separately.  These are great when users need to compare large amounts of data directly.  Tables and matrices do not belong on strategic dashboards (because tl;dr), but they can belong on tactical or operational dashboards.

11_Matrix[1]

Don’t forget about the venerable table of numbers.

A table or matrix is a compact method for disseminating a good amount of information to an informed audience, and that’s why it shows up as the basis of so many reports.  Just try to avoid them on high-priority strategic dashboards.

Bar And Column Charts

Column Charts

Next, we have column charts.  Column charts are great when you have a relatively small number of categories but a fairly large number of data points.  In the following example, I compare across three shows how many critic reviews each episode got.

11_ColumnChart[1]

A column chart covering episodic critic reviews.

We have three shows but a fairly large number of episodes, and I want to compare the trend for each show.  If I were to do this for all six Netflix shows, that would be too much, in my opinion.  Also, note that we need a legend to explain each show.  That’s a downside to using a column chart to show periodic data like this, but it’s not a deal-killer.

Bar Charts

Bar charts run left-to-right whereas column charts run bottom-to-top.  The fact that bar charts run left-to-right makes them much better when you have a large number of categories, as well as when the labels themselves are lengthy.  Notice how clear the following bar chart of critic reviews by show is:

Critic reviews by show as a bar chart.

This is much better than how it’d look as a column chart because we can put the labels in their natural format:  left-to-right and flowing to the visual element.

Choosing A Bar Or A Column

This leads me to a little bit of advice for choosing bars versus columns.  You will want to choose a bar chart if the following are true:

  1. Category names are long, where by “long” I mean more than 2-3 characters.
  2. You have a lot of categories.
  3. You have relatively few periods—ideally, you’ll only have one period with a bar chart.

By contrast, you would choose a column chart if:

  1. Viewing across periods is important.  For example, I want to see the number of critic reviews fluctuate across the season for each of the TV shows.
  2. You have many periods with relatively few categories.  The more periods and the fewer categories, the more likely you are to want a column chart.
  3. Category names are short, by which I mean approximately 1-3 characters.

Some people will rotate text 90 degrees to try to turn a bar chart into a column chart.  I don’t like that because then people need to rotate the page or crane their necks.  In that case, just use the bar chart.

Cleveland Dot Plot

The Cleveland dot plot is a minimalist take what a bar or column chart would normally visualize. It’s not built into Power BI, but there are a couple custom visuals which implement the dot plot, and I think you’ll want to check them out.

For example, here is a bar chart with average rating by show:

Bar Chart Rating

Bars dominate the picture.

By contrast, here is a dot plot of the same information using the MAQ Software Dot Plot:

Dot Plot Rating

Dots don’t dominate like bars do.

The dot plot gives us the same results as the bar chart, but we can fit it in a more compact space if we want, scrunching down the visual to take up a lot less space without losing any of the information about series variance.  For example, in this picture, I can safely cut the visual down to about a third of its original size and still clearly see the differences in shows.  The reason for this is that dot plots don’t need to start at the origin, whereas bar and column charts really do.

Let’s get into that for a moment.  When we see bar charts, we immediately compare the relative areas of those bars and get information from that comparison.  If I show you a bar which looks to be about twice the size of another bar, we automatically assume that the first element has about twice the value of the second.  When we start at the origin, this is true.  But if we start from a different spot, we exaggerate the differences and can lead viewers to the wrong conclusion.  Yes, if you read the numbers you’ll see the difference, but you immediately put the image into someone’s mind before that person even gets a chance to see the numbers.

By contrast, if you see dots, you aren’t assuming relative sizes and can orient yourself to the axes more easily.  In the above picture, we can see that our shows hover between just under 8 and about 9 on the ratings scale.  We can also see that there is a pretty big difference between The Punisher & Daredevil versus the other shows.

Cleveland dot plots area nice way of contrasting values for a relatively large number of categories and can let you get finer-grained than a bar chart.  I’d use this type of visual if most of the categories are relatively close together, like our ratings examples are.

One downside to the dot plot visuals in Power BI is that they aren’t as complete as the built-in visuals.  For example, the dot plot that I showed above doesn’t appear to let you sort the data by average rating, which is a bit surprising to me.  But that doesn’t change the fact that in principle, dot plots are useful.

Radar Charts

I like radar charts, but there are very few good uses for them.

A good use for radar charts.

The best use for a radar chart is giving a normalized comparison of measures across several categories.  For example, the above chart shows normalized critic reviews and normalized user reviews by show.  In other words, both charts are normalized to 1.0, and it turns out that Daredevil season 1 had the greatest number of critic reviews and the greatest number of user reviews.  Then, the other shows follow.  Luke Cage had almost as many critic reviews, but the smallest proportion of user reviews; Jessica Jones was the opposite, having the smallest number of critic reviews but the second-largest number of user reviews.

I consider this a fun type of  chart but one that probably doesn’t end up on many dashboards.

Line Charts

Line charts are great  for time series data which stretches over a large number of periods.  The trick is that you don’t want to have many categories.  For example, the following line chart shows average rating by episode for Daredevil versus The Punisher:

This line chart does a great  job comparing two shows across a full season.  It also shows the three-episode stretch where the crew behind Daredevil ran out of material and padded one episode out into three before giving us a fantastic finale.

Line charts start to get risky after you get past 4-5 categories.  At that point, lines start overlapping and it becomes tricky to read.  But for a two- or three-category comparison  over time, line charts are hard to beat.  I could also use column charts here, but if you want to emphasize the time series nature of the data, lines are great because they indicate flowing from left to right.

Line And Column Charts

Combining a line chart with a column chart can be risky, but under the right circumstances, it can pay off handsomely:

Many people voted on episode 12 of The Punisher, and they loved it.

This works because we have two interrelated but distinct variables that we are measuring over time.  We have the number of votes as a column chart taking up the background, and we have a line chart representing rating per episode in the foreground.

Another place that you will see line and column charts is dealing with stocks, where you typically see a line with the daily closing price and a column chart showing volume traded per day.

Scatter Plots

Scatter plots are great for showing relationships between two variables over a relatively small number of categories.  In the following example, I show two variables per episode:  the number of user votes on the x axis and the number of critic reviews on the y axis:

A scatterplot showing that one episode of Jessica Jones was not like the others.

In this case, a scatter plot is great because it shows just how distinct one show was from the other two.  We see episodes of The Punisher and Jessica Jones overlap, but Daredevil blows them both away in terms of number of votes and number of reviews.  This makes sense given that Daredevil season 1 was also two years before The Punisher and a year before Jessica Jones, so there was more time to rack up votes.  We can also see a fairly linear relationship for each series, showing that more user votes correlates positively with more critic reviews.

Bubble Charts

Bubble charts are the three-variable versions of scatter plots.  We have x and y axis variables as well as a third variable which controls the bubble size.

A bubble plot which also includes number of user reviews.

This bubble chart includes the number of user reviews as the bubble size.  This particular example also shows why I’m not typically a fan of bubble charts:  they get too busy too fast.  If there are only a few points on the chart, they’re not bad, but even with just 39 points, it’s hard to make out much information here.  It’s also a lot harder to get approximate values for the different points, given how big some of the bubbles can get.

Treemaps

Treemaps are another example of a risky chart that I really like in the right circumstance.  Here is an example of a treemap:

A treemap breaking down entries by state and then by city.

Treemaps work if the following circumstances hold:

  1. You have categorical data.  For example, my treemap is data about states and provinces.
  2. That data is typically hierarchical.  This is optional, but hierarchies tend to make treemaps a bit more interesting.  Here my hierarchy is state to city, and you can see how each city makes up a certain percentage of the state totals.
  3. You have a medium to large number of categories.  This works best with 20-40 categories.  If you have a lot more than that, then you probably don’t want to use a treemap.
  4. A relatively small percentage of categories dominate.  In this example, we can see that the four biggest states (Florida, Ohio, Virginia, and New York) take up about 40% of the total.  If everything were approximately equal, that would make this treemap a little harder to follow.

Treemaps have their downsides, of course (starting with all of that color), but they do a pretty good job at letting people estimate relative value by area.   People aren’t fantastic at estimating relative areas in general, but they are pretty decent at getting relative sizes of similarly shaped squares and rectangles right, and that’s what a treemap gives us.

Conclusion

Today, we looked at a series of visuals that I like on dashboards.  I wouldn’t use all of these all of the time, and there are other good visuals that I didn’t include here, but you can see that you have some options available to you.  Each one of these has its own good and bad use cases, but they all work.  Tomorrow, we’re going to look at some other types of visuals which range from “very risky” to “If you use this, you’re out of the family.”

Visual Principles, Part 2

This is part five of a series on dashboard visualization.

Yesterday, I covered three visualization principles:  cognitive load, the idea that less is more, and a quick look at where the eye looks.  Today, I’m going to look at three more principles:

  1. The Rule of Thirds
  2. Glanceability
  3. Color Vision Deficiency

The Rule of Thirds

We typically think about the rule of thirds when looking at photographs or paintings.  The idea is that if you take an image and slice it into three zones horizontally and three zones vertically, people naturally gravitate toward the intersections of lines.  For example, check out the photo below.

20170814_110729.jpg

A sample photograph showing off the rule of thirds.

In this case, my flower picture takes advantage of the rule of thirds.  The stem follows the bottom horizontal line, and the petals dominate the top-left intersection, with berries flanking the lines.

The rule of thirds can also work for dashboards.  For example, let’s take another look at the strategic dashboard from earlier:

3_StrategicDashboard[1]

An example of a strategic dashboard. (Source)

If we put on gridlines, here’s what we see:

3_strategicdashboard1

Adding gridlines to the strategic dashboard.

Just like with my photograph, you can see that the most interesting elements of this dashboard generally fit in the gridlines.  It would have been better if the dashboard developer could shift the graphs over to the left just a little bit, but you can see that the spots the eye gravitates toward generally will have the most interesting-looking visuals in them, and that’s a good idea.

This doesn’t mean that you absolutely need to follow the rule of thirds, but it is smart to keep this idea in mind.

Glanceability

Now we move on to one of my hobby horses:  glanceability.  This is a straightforward word with a straightforward meaning and a lot of implication.  The idea of glanceability is that a human can, at a glance, gain important information from your dashboard.  In other words, suppose you’re building a dashboard for the CEO as she shuffles from one meeting to the next.  She has about 5 seconds to look at your dashboard and make a decision.  If you can provide her accurate information that meets her needs within that five-second window, you succeed.  That is glanceability in a nutshell.

In a bit more detail, you can make a dashboard glanceable by following these guidelines:

  1. Ensure that there is clear purpose in your metric design and display.  In other words, think about which metrics you want to show, how you want to show them, and where you put metrics in relation to one another.
  2. Group metrics by function into sections.  Look at the dashboard above.  It has four clusters of metrics:  those around revenue, new customers, revenue per customer, and customer acquisition cost.  All of the revenue metrics are clustered in the top-left quadrant of the dashboard.  Furthermore, all revenue-related metrics (that is, revenue metrics and revenue per customer metrics) are on the left-hand side of the dashboard, so the CEO can focus on that half and learn about revenue and revenue per customer.  She doesn’t need to look in the top-left corner for one revenue measure and in the bottom right for another; she can focus down to a portion of the dashboard and get an answer.
  3. It should be easy to see and differentiate those clusters of metrics.  Our natural instinct might be to put borders around the clusters, but whitespace is your friend—remember, less is more.  If you add a bit more whitespace between clusters of measures, you’ll make it easy for people to see that there’s a difference without distracting them with unnecessary lines.
  4. There should be no scrolling on the dashboard.  If a user has to scroll, the dashboard is not glanceable:  you could end up on the wrong section of the dashboard and need to scroll around to figure out where you can even begin to look.  If it doesn’t fit on one dashboard, then you probably need multiple dashboards with separation of concerns, or maybe some of those measures and graphs should be in a report instead of a dashboard.
  5. Make sure that the individual measures are easy to read.  That means using a large enough font that people can see it without squinting.  It means using a dark enough color that the title doesn’t blend into the dashboard.  Those, by the way, are things that Power BI is not very good at by default…  But something that Power BI is good at is rounding big numbers.  For example, if I tell you that revenue is 27482649.97, that is useful information but it is also hard to read.  You’re having to count from the left, mentally draw out the commas, and try to figure out if that’s $2.7 million or $27 million.  Formatting it as $27,482,649.97 is nicer.  But you know what’s even better on a dashboard?  $27.5m.  That is a much easier number to see and understand.  It also takes up less space on the screen, so it’s win-win.  If a person really needs to see the exact number, Power BI does offer the ability to mouse over a visual element and get the exact value as a tooltip.  But for people glancing at a dashboard, the big round number is enough.

Following these basic guidelines will make your dashboard a lot easier for people to read.

Color Vision Deficiency

 

Color Vision Deficiency (aka color-blindness) affects approximately 8% of men and 0.5% of women.  There are actually several forms of CVD, each of which will affect people in different ways, but fundamentally it comes down to certain cones in the eye not perceiving light normally.  For example, 5% of men have deuteranomaly, in which the cones acting on green light do not fire as expected.  For the more extreme case, about 1% of men have deuteranopia, where they lack entirely green cones.

Where this can affect you is if you use colors which a person with CVD cannot differentiate.  For example, check out this bar graph:

CVD Test

Six perfectly distinguishable colors.

If you have normal color vision, you can easily see the six colors:  pink, light blue, orange, green, purple, and brown.  But I chose each of these colors because they lead to trouble when used in concert.  For example, here’s what it looks like if you have deuteranopia (green cone deficiency):

CVD Test - Deuteranopia

The view for someone with deuteranopia.

Notice that the first two colors are practically indistinguishable.  I opened this image in Paint and the RGB codes were very close; not perfect matches, but so close that I can’t tell the difference.  Also note that bars 3 and 4 are really close, too.

Here’s what it looks like for someone with protanopia (red cone deficiency):

CVD Test - Protanopia

The view for someone with protanopia.

Now bars 3 and 4 are really tough to separate, and bars 1 and 2 aren’t that different either.  You can see that protanopia and deuteranopia have somewhat similar effects, which is why people talk about “red-green” color-blindness.

Finally, let’s look at tritanopia.  This is the least common type, with tritanomaly and tritanopia combining to affect approximately 0.5% of men and 0.5% of women.  So here’s what they see:

CVD Test - Tritanopia

The tritanopic viewpoint.

The bottom two bars are very close now.

As a side note, tritanopia is pretty wild, almost like you’re living on an alien planet.  Everything just looks so red and pink.  Here’s the flower photo from earlier seen from the perspective of someone with tritanopia:

Tritanopic Flower.png

How someone with tritanopia sees our flower.  But hey, at least they can still see the gridlines and follow the rule of thirds!

Notice how we basically lose the green in this photo, replacing it with pinks and blues.

One last bit.  You may not think it makes much of a difference, but it’s the reason that SSIS no longer looks like this:

10a_SSIS

How SSIS used to look, back in the day.

That’s because if you’re protanopic, this image looks like:

10a_SSISProtanopia

SSIS with protanopia.  The greens have become yellow and reds have become a yellowish brown.

And if you’re deuteranopic, it looks like:

10a_SSISDeuteranopia

SSIS with deuteranopia.  The greens have become a sandy tan and the reds have become a light brown.

Either way, making green and red indicate success and failure?  That’s a failure.

If you want to learn more, Martin Krzywinski has an awesome page on colorblindness.  I was able to piece together overlapping colors thanks to that resource.  I also used Coblis to show the effects of different forms of CVD.

Conclusion

This was an image-heavy post today, but that’s what I get for putting together a data visualization talk…  Anyhow, we covered three important visualization principles:  the rule of thirds, glanceability, and Color Vision Deficiency.  Combined with the first three principles, you have a lot of information at your disposal to improve your visuals.

Tomorrow, we’re going to continue the series by looking at a bunch of data visualization formats.  Stay tuned!

Visual Principles, Part 1

This is part four of a series on dashboard visualization.

Today, I’m going to look at three important principles when it comes to visualization.  In the next post, we will look at three additional principles.  There are certainly more principles, but also there’s only so much time in a presentation, so go with me here…

Those principles that I want to cover are:

  1. Cognitive Load
  2. Less Is More
  3. Where The Eye Looks

Cognitive Load

Did you read Meagan Longoria’s post on cognitive load?  Because you should do that.  And the rest of her design concepts series.  She covers it so well that I’m not even going to bother here; just read what she has.

Less Is More

Remove unnecessary items.  This includes things like extraneous text, logos, indicators, and other things which do not advance your story.

Decluttering

Here’s an example of a dashboard:

8a_ClutteredDashboard[1]

This dashboard has too much going on.

My just-so story for this dashboard is as follows:  we started out with a logo and title, and then put on a couple of graphs.  Well, I wanted to explain what the donut chart and treemap are doing there, so I put in some explanatory text.  But there’s so much stuff on here and I want people to look at the treemap first, so I put a bunch of arrows in to tell them to look there.

This is an absurd example, but too many dashboards end up looking sort of like this.  Here’s stuff that we don’t need:

  • The logo and company name.  If this is an internal dashboard, your users already know where they work and don’t need the reminder.  If it’s an external dashboard, there can be an argument for putting the logo or title in a tasteful place, but you don’t want it in the top-left.  That’s okay for reports but this is a dashboard and the rules are different.  For dashboards, we only get the single screen and can’t go on for page after page.
  • I don’t need to put explanatory text on the dashboard itself.  If users need help or more information, I’d rather that be in some separate help file somewhere.  The reason is that this text is only helpful once, after which point it becomes a nuisance because it’s taking up space and not telling people anything interesting.
  • Once I get rid of items, I can also get rid of the silly arrows.

After doing those simple changes, I have a much more concise dashboard:

8b_UnclutteredDashboard[1]

It turns out you don’t need arrows to get people to look at a treemap.  Surprise!

From there, I can tighten it up a little bit more by removing the legend on the donut chart.  Legends are pernicious things because they require people to move their eyes up and down and up and down.  I go look and see that black represents 2016, and then go look and see that red represents 2017, and then I can go look and see that green is 2015 and…wait, what was black again?  Yeah, on this example it’s a little silly, but if you have a chart whose legend includes a dozen or more items, people really do have to keep looking back and forth.  Instead, include the data labels inline:

8c_NoLegendDashboard[1]

Moving data labels inline reduces the number of back-and-forth eye movements and keeps your users from getting dizzy…or at least upset with you.

If you’re concerned that you have too many labels to fit on your visual, then you might have the wrong visual.  This is particularly true if you’re using something like a pie chart or donut chart, about which I’ll rage some other day.

Decoloring

Reduce your color usage, and keep color usage limited and consistent.  Here is an example of a bar chart with a lot of color.  From this chart, you can see that Kentucky and Ohio are related, just like Indiana and New York.

8d_TooManyColors[1]

Kentucky and Ohio are related, though!

I didn’t intend for there to be assumed relationships between these states, but when people see colors grouped together like this, they naturally assume that there is some relationship.  It goes back to the cognitive load example above.

So if I don’t really mean to link states together with color, the best thing I can do is use a single color to represent each state:

8e_FewerColors[1]

Now all of the states relate to Ohio.  Just as it should be.

At this point, I’m now telling the user that there are no co-state relationships of note.  This also lets me do something nice.  Let’s say that I want to single out the state of California:

8f_FocusColor[1]

Picking out a bad apple from the lineup.

Looking at this picture, you immediately see the different color without me even explaining what I was going to do.  If I left it as-is, I could not have used color to draw your attention to California.

De-3

The third dimension is sometimes good for movies, but it’s almost never good for data visualizations.  There are rare exceptions where a 3D image is better than its 2D equivalent, and those are usually in areas like geology and physics.  For the world of business, you want to stick to the first and second dimensions.  For example, this Excel maestro does absolutely nothing helpful for us:

8h_3D[1].png

Sales in Q4 were in your face, at least if you move your face to the right side of your monitor.

It’s unnecessary 3D and actively harmful because it’s hard to read those values.

Where We Look

In European languages, we read from left to right and from top to bottom.  In Middle Eastern languages like Hebrew and Arabic, we read from right to left and top to bottom.  In ancient Asian languages (particularly Chinese), we read from top to bottom and right to left, but in modern Chinese, we read left to right and top to bottom.  As far as Japanese goes, we read every which way because YOLO.  The way we read biases the way we look at things.

There has been quite a bit of research done on looking at where we look on a screen or on a page. I’m going to describe a few layouts, but focusing on research done on Europeans.  If you poll a group of Israeli or Saudi Arabian readers, flip the results.

The Gutenberg layout is a classic and indicates that we tend to scan our eyes from the top left down to the bottom right, focusing at those two points and glazing over the rest unless we see something particularly interesting.

The Z layout contrasts with the Gutenberg layout and indicates that people will start out interested and read across the top, and then scan back to the left and across the bottom, ending up in the terminal area.

About a decade ago, Jakob Nielsen came up with the F layout after additional design studies.  The F layout is a testament to the interest level of  readers:  they start out interested and move their eyes left to right, but as they lose interest, they start sliding down the page, skimming through and waiting for something to catch their eye.

The trick with all three of these layouts is that they work for evenly distributed, text-heavy, homogeneous data like books or newspaper articles.  Your dashboards shouldn’t look like books or newspaper articles, so I ascribe to a different type of layout style:  focal points.  The idea with focal points is that people will gravitate toward the most interesting-looking parts of your page and meander from one focal point to the next, sometimes catching the stuff in between.

When I say interesting-looking parts, there are a few attributes which work well for focal points.  First of all is size:  if a particular visual is a bit larger than its neighbors, people will naturally look at it first.  Second, if you’ve followed the advice of limiting usage of color, a judicious splash of color on a visual can be a great attention-grabber.  Third, gradient can matter too:  maybe a bolded or darker-shaded title could grab attention, or possibly even a shading for the background.  This starts to become riskier as you do it more often, and the question eventually becomes, how many focal points do you really have on this dashboard?

Nevertheless, judicious focal point layout can help us a lot in telling a coherent story with a dashboard.  You have the beginning of the story be the first focal point, and critical sections of your visualization tale make up the remaining points.  Exculpatory and explanatory visuals can cluster around the focal points.  On a dashboard, I’d probably want no more than three or four focal points; more than that and you probably start to overwhelm the user.

Conclusion

In today’s post, we looked at three visual principles (including the one where I said Meagan can explain it better than me so just go read her stuff).  Next time around, we’re going to look at a few more principles.

Implementing A Leaky Bucket Algorithm In T-SQL

A long time ago, I talked about alerts and monitors.  A bit later, I added thoughts on auditing as well.  Today, I’m going to cover a technique that I’ve used to alert me to real problems without too much noise.  That technique is called the leaky bucket algorithm.

Step One:  Get A Bucket

Let’s say that we have a process we want to monitor, like an API.  Further suppose we have a table in SQL Server which collects every 500 class response that our API sends out.  As a quick refresher, 200 class responses (e.g., HTTP 200) indicate success.  300 class responses (e.g., 301, 302) indicate redirection.  400 class responses (e.g., 403, 404) typically indicate that the user has failed us somehow.  500 class responses (e.g., 500) typically indicate that the server has failed us somehow.  We might find it interesting to track 404 errors because that might help us find out that, for example, we don’t have a favicon.ico file, but there’s a lot of noise in those types of errors (especially if your API is publicly accessible), so we’ll ignore those and focus on the 500 errors.

So let’s create that table:

CREATE TABLE [dbo].[ApiCall]
(
	ApiCallID BIGINT IDENTITY(1,1) NOT NULL,
	EndpointName NVARCHAR(128) NULL,
	RequestVerb NVARCHAR(10) NOT NULL,
	ResponseCode SMALLINT NOT NULL,
	ResponseTimeMS INT NOT NULL,
	LogDateGMT DATETIME2(3) NOT NULL CONSTRAINT [DF_ApiCall_LogDateGMT] DEFAULT (SYSUTCDATETIME()),
	CONSTRAINT [PK_ApiCall] PRIMARY KEY CLUSTERED(ApiCallID)
);
GO

This is a very simple table, and a real alerting table will have additional information, but let’s not complicate things unnecessarily here.

Step Two:  Put A Hole In It

Now that we have a table, we want to do something with it.  The most naive solution would be to fire off an alert every time a row gets added to this table.  The problem with this solution is that it can flood our inbox with useless information.  Suppose the developers push out an API change that breaks everything.  The first 500 response will be important information.  The second one might be important because it’s confirmatory.  The six hundredth alert will not help.  And heaven help you if you’ve got this alert tied to your PagerDuty account…

So let’s only alert if we get to a certain threshold—we’ll call it 5 messages.  Fewer than 5 and we can assume it’s a blip or just somebody doing something weird.  The next part of the problem is, 5 messages per what?  We don’t want to alert for every fifth error message.  Let’s say we get one error message a year, so there was one in 2014, one in 2015, one in 2016, one in 2017, and now one in 2018.  If we simply set our threshold to report on every fifth error, we’ll get an alert in 2018, but most likely, the prior years’ errors are long gone by now, so that’s not helpful.

Instead, we want to set a timeframe for those alerts.  In my case, I’ll say that I want to look for 5 errors anytime during the past 30 minutes.  The particular number and timeframe are variable and I pulled those numbers out of a hat, but they’ll be useful for us.  But what makes it nice is that errors “drain” from the bucket automatically.  Errors lose their importance over time in our alerting system, and 30 minutes of calm completely drains the bucket.

Step Three:  Thinking About Overflow

But now we have a new frequency of alert issue.  Let’s suppose that I alert anytime I see 5 errors in the past 30 minutes.  Let’s further suppose that my process checks every 5 minutes and sends out an e-mail.  The problem is that I might get upwards of 6 alert e-mails for the same five errors—that’s definitely not what I want!

As an example of this, suppose that we had 5 errors happen in a burst at 5:30 PM, right before my alert check ran.  I will subsequently get alerts at:

  1. 5:30 PM
  2. 5:35 PM
  3. 5:40 PM
  4. 5:45 PM
  5. 5:50 PM
  6. 5:55 PM

They will finally wash out at my 6 PM alert cycle, but I really didn’t need 5 of those alert e-mails.  One viable solution is to mark those particular errors as alerted and therefore no longer interesting to us.  That solution can work for us with this table design (though I’d want to add a new bitflag indicating whether we’ve sent an alert for this error), but in real life, I’m storing a form of this data in a table with a clustered columnstore index and I don’t want to update rows in that table.  So I’m going to use a different approach:  only alerting when we overflow.  And now we’re getting to the metaphor.

Think about a bucket that can hold five gallons of water.  If  I try to pour in six gallons, I can get the first five, but the remaining gallon overflows and causes a mess everywhere.  This also gives us a cap on how much water we care about:  if the bucket is empty and I immediately dump 10 gallons into it, 5 of those gallons will overflow immediately, leaving me with a full five-gallon bucket.  We tell people about the spill (those five gallons not in the bucket) but don’t care any more about that spilled water.  Now suppose that I take that five-gallon bucket and drill a hole in it.  Let’s say that it has a drain rate of one gallon every six minutes.  If I average no more than one gallon every six minutes, I can pour into perpetuity without overflow…assuming that the bucket wasn’t full when I began the process.

Step Four:  Handling History

That’s where we need to think about the next problem.  In order to implement a leaky bucket, I need to know the entire history of the bucket, or at least a long enough period of time that I can feel confident that I’m not going to miss important alerts.  If I’m only looking at the last 30 minutes, I’m missing the fact that the bucket might not have been empty when my 30-minute window started, so I have to look back at the prior 30-minute window.  But to do that, I need to look back at the prior window, and so on, until I get back to the first usage of that bucket.

You might be wondering why that’s important, and here goes:  let’s say that we have one error every 8 minutes.  Based on the error rate alone and alerting at 5 errors in a 30-minute window, we will never fire an alert even though we have a consistent error.  That means we’re potentially ignoring useful information and this potential false negative might come back to bite us later.

By contrast, with our leaky bucket, we will eventually overflow, though it will take a while.  We have 7.5 errors per hour and 6 drained errors per hour, so we have a little bit of an algebra problem:  5 = 7.5n – 6n –> 5 = 1.5n –> n = 3.33 hours.  After 3.33 hours, our bucket will fill and we’ll get an alert.

But looking at the entire history of a table is not really practical for us when that table gets large, so instead I’ll compromise:  I’ll look at 5 cycles.  The idea here is that no matter what happened, the entire bucket will have been flushed five times during this process and older errors are very unlikely to have an effect.  We still get the benefit of looking at longer timeframes and capturing slowly-building errors, but don’t need to accept the full cost of reading everything.

Step Five:  The Final Product

After all of this talk, I finally get to the code.  We’ll take the table defined above, add a few rows, and test a couple of scenarios.

So let’s add some rows:

INSERT INTO dbo.ApiCall
(
	EndpointName,
	RequestVerb,
	ResponseCode,
	ResponseTimeMS,
	LogDateGMT
)
VALUES
--Initial burst to trigger an alert; note that there are more than enough errors here.
('Endpoint', 'POST', 500, 15, '2018-01-08 09:03:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:03:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:03:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:03:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:03:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:03:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:03:10.274'),
--Occasional record, but should stay under the threshold
('Endpoint', 'POST', 500, 15, '2018-01-08 09:14:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:24:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:34:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:44:10.274'),
--Another burst to trigger an alert
('Endpoint', 'POST', 500, 15, '2018-01-08 09:48:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:48:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:48:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:48:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 09:48:10.274'),
--Occasional record to stay under the threshold, but at a fast enough pace that we eventually trigger again
('Endpoint', 'POST', 500, 15, '2018-01-08 09:59:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 10:09:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 10:19:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 10:24:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 10:29:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 10:34:10.274'),
('Endpoint', 'POST', 500, 15, '2018-01-08 10:39:10.274');

I have some tests I’d like to cover, but first let’s walk through the leaky bucket procedure itself.

CREATE OR ALTER PROCEDURE dbo.LeakyBucketAlert
(
@HistoricalFrameCycles INT = 5,
@FrameSizeSeconds INT = 60,
@DrainEventsPerFrame DECIMAL(9,2) = 0.1,
@MaxNumErrors INT = 5,
@ObservationFrameSeconds INT = 1800,
@StartDateGMT DATETIME2(3) = NULL
)
AS
SET NOCOUNT ON;

CREATE TABLE #Events
(
	FrameBeginTime DATETIME2(0),
	FrameEndTime DATETIME2(0),
	AlertIfFull TINYINT,
	NetBucketChange DECIMAL(9,2),
	FillLevelAtEndOfFrame DECIMAL(9,2)
);

DECLARE
	@FrameBeginTime DATETIME2(0),
	@StartingFillLevel DECIMAL(9,2),
	@NetBucketChange DECIMAL(9,2),
	@PriorFrameBeginTime DATETIME2(0);

SET @StartDateGMT = ISNULL(@StartDateGMT, GETUTCDATE());

DECLARE
	@CurrentWindowStartTime DATETIME2(0) = DATEADD(SECOND, -1 * @ObservationFrameSeconds, @StartDateGMT),
	--Start at ObservationFrameSeconds and then go back the amount of time needed for the bucket to cycle through completely.
	@PriorCycleBeginTime DATETIME2(0) = DATEADD(SECOND, -@HistoricalFrameCycles * (1.0 * @MaxNumErrors / @DrainEventsPerFrame * @FrameSizeSeconds) - @ObservationFrameSeconds, @StartDateGMT);

--The Drain events happen every period
INSERT INTO #Events
(
	FrameBeginTime,
	FrameEndTime,
	AlertIfFull,
	NetBucketChange
)
SELECT
	frame.FrameBeginTime,
	frame.FrameEndTime,
	CASE
		WHEN frame.FrameBeginTime >= @CurrentWindowStartTime THEN 1
		ELSE 0
	END AS AlertIfFull,
	-1 * @DrainEventsPerFrame AS NetBucketChange
FROM dbo.Tally t
	CROSS APPLY
	(
		SELECT
			DATEADD(SECOND, t.N * @FrameSizeSeconds, @PriorCycleBeginTime) AS FrameBeginTime,
			DATEADD(SECOND, (t.N + 1) * @FrameSizeSeconds, @PriorCycleBeginTime) AS FrameEndTime
	) frame
WHERE
	t.N < 1.0 * DATEDIFF(SECOND, @PriorCycleBeginTime, @StartDateGMT) / @FrameSizeSeconds; --The Fill events might happen during a period; if they do, collect them.

WITH FillEvents AS (
	SELECT
		e.FrameBeginTime,
		COUNT(1) AS FillEvents
	FROM dbo.ApiCall ac
		INNER JOIN #Events e
			ON ac.LogDateGMT >= e.FrameBeginTime
			AND ac.LogDateGMT < e.FrameEndTime
	WHERE
		ac.ResponseCode = 500
		AND ac.LogDateGMT >= @PriorCycleBeginTime
	GROUP BY
		e.FrameBeginTime
)
UPDATE e
SET
	NetBucketChange = e.NetBucketChange + fe.FillEvents
FROM #Events e
	INNER JOIN FillEvents fe
		ON e.FrameBeginTime = fe.FrameBeginTime;

--Marking the bucket fill line
--Basic rules:
--	1)  Cannot go below 0
--	2)  Cannot go above @MaxNumErrors
--	3)  Addition and subtraction happen "simultaneously" so we apply the net change as a unit
--Because of rules 1 and 2, we cannot simply use a rolling window summation to determine fill level.
DECLARE
	BucketCursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT
		e.FrameBeginTime,
		e.NetBucketChange,
		LAG(e.FrameBeginTime) OVER (ORDER BY e.FrameBeginTime) AS PriorFrameBeginTime
	FROM #Events e
	ORDER BY
		e.FrameBeginTime ASC;

OPEN BucketCursor;
FETCH NEXT FROM BucketCursor INTO @FrameBeginTime, @NetBucketChange, @PriorFrameBeginTime;

WHILE (@@FETCH_STATUS = 0)
BEGIN
	SELECT
		@StartingFillLevel = FillLevelAtEndOfFrame
	FROM #Events e
	WHERE
		e.FrameBeginTime = @PriorFrameBeginTime;

	SET @StartingFillLevel = ISNULL(@StartingFillLevel, 0);

	UPDATE #Events
	SET
		FillLevelAtEndOfFrame = CASE
			WHEN @StartingFillLevel + @NetBucketChange < 0 THEN 0 WHEN @StartingFillLevel + @NetBucketChange > @MaxNumErrors THEN @MaxNumErrors
			ELSE @StartingFillLevel + @NetBucketChange
		END
	WHERE
		FrameBeginTime = @FrameBeginTime;

	FETCH NEXT FROM BucketCursor INTO @FrameBeginTime, @NetBucketChange, @PriorFrameBeginTime;
END
CLOSE BucketCursor;
DEALLOCATE BucketCursor;

WITH records AS
(
	SELECT
		e.FrameBeginTime,
		e.FrameEndTime,
		e.AlertIfFull,
		LAG(e.FillLevelAtEndOfFrame) OVER (ORDER BY e.FrameBeginTime) AS FillLevelAtStartOfFrame,
		e.NetBucketChange,
		e.FillLevelAtEndOfFrame
	FROM #Events e
	WHERE
		EXISTS
		(
			SELECT 1
			FROM #Events ee
			WHERE
				ee.AlertIfFull = 1
				AND ee.FillLevelAtEndOfFrame = @MaxNumErrors
		)
)
SELECT
	r.FrameBeginTime,
	r.FrameEndTime,
	r.FillLevelAtStartOfFrame,
	-1 * @DrainEventsPerFrame AS DrainEventsPerFrame,
	r.NetBucketChange + @DrainEventsPerFrame AS AddEventsPerFrame,
	r.NetBucketChange,
	r.FillLevelAtEndOfFrame
FROM records r
WHERE
	r.AlertIfFull = 1;
GO

Fun fact: I spent an entire day trying to remove that cursor. Don’t tell my boss.

Let’s explain what’s happening here.  At the top, we declare two variables:  @CurrentWindowStartTime and @PriorCycleBeginTime.  @CurrentWindowStartTime is @ObservationFrameSeconds ago from our starting point, and @PriorCycleBeginTime is @HistoricalFrameCycles time periods back behind that.  To make this concrete, our current window’s start time is 30 minutes prior to the start date, so the prior cycle begin time is 180 minutes prior to the start date—that is, there are 5 30-minute windows which sit between the prior cycle’s begin time and the current window’s start time.

From there, I use a tally table that I’ve built separately to build a constant drain rate.  If you don’t have a tally table, here’s one for you:

SELECT TOP(10001)
	IDENTITY(INT, 0, 1) AS N
INTO dbo.Tally
FROM sys.objects s1
	CROSS JOIN sys.objects s2;
ALTER TABLE dbo.Tally ADD CONSTRAINT [PK_Tally] PRIMARY KEY CLUSTERED (N);

The Events temp table lets me see how many events per minute I have, as well as the drain rate.  I’m using a constant drain rate here, though you could modify this to have a proportionate drain rate (where it drains faster with more errors) if you wanted.  I also have a flag, AlertIfFull.  That flag makes sure that I don’t accidentally alert on historical overflows—my assumption is that I already caught those, so I don’t need to keep sending messages out.

Next up, there’s the cursor.  I feel obligated to explain why I need a cursor here, especially when I clearly know enough about  window functions to use them.  The problem is that we have not only a window, but also a floor and ceiling (no extended metaphor or pun intended).  I cannot go below 0 errors and I cannot go above@MaxNumErrors errors.  In other words, I cannot build up error immunity by having an extended period of time with zero errors and thereby go negative.  I also do not want to see the error count pushed up so high that it never drops back down—that would be like trying to pour 10 gallons into a 5-gallon bucket and counting those 10 gallons as there.  They’re not really there—we had an overflow and now we’re moving on with a full 5-gallon bucket.  Anyhow, the nature of that rule means that I couldn’t simply use a window function to solve this problem.

Instead, for each minute, I figure out what the starting point is, add or drain based on whether NetBucketChange is positive or negative (or do nothing if it’s 0), and deal with floors or ceilings.  The end result of that is FillLevelAtEndOfFrame, which is the thing I really care about.

Finally, after the cursor ends, we look at the Events temp table and return results if we experienced an overflow event.

Step Six:  Proving That It Works

Here are my tests given the leaky bucket.  I only alert when I get rows back, so if the result set is empty, we don’t send an alert.

--Expected:  no results.
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 09:02:00';

--Expected:  results.
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 09:04:00';

--Expected:  results with some drain.
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 09:07:00';

--Expected:  results with continued drain and occasional bumps.
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 09:25:00';

--Expected:  no results.
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 09:45:00';

--Expected:  new results
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 09:50:00';

--Expected:  no results
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 10:20:00';

--Expected:  results
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 10:34:00';

--Expected:  results.  We aren't letting the bucket drain and keep getting errors, so we keep alerting.
EXEC dbo.LeakyBucketAlert
	@StartDateGMT = '2018-01-08 10:39:00';

Step Seven: Another Approach

One area of potential improvement could be tracking whether a set of errors have already been part of an alert and ignoring them from then on.  That would be equivalent to, when the bucket overflows, pouring out the entire bucket and starting over.  There are arguments for and against this approach.  I ended up going with my approach because I want to be prodded if my API keeps sending me waves of errors, but I could see the argument in favor of emptying the bucket completely.  In that case, you’d just need to update #Events to set WHEN @StartingFillLevel + @NetBucketChange > @MaxNumErrors THEN 0. Then you’d have a leaky bucket with full flush after overflow.

Know Your Data

This is part three of a series on dashboard visualization.

Knowing your data can matter as much as knowing your audience when it comes to creating good dashboards.  There are several critical questions to ask when it comes to the data:

  1. What are the most important measures people need to see?
  2. How do my data elements relate to those measures?
  3. Am I missing any data, or are there incomplete elements?
  4. Is my data source any good?  What is the likelihood that my data is wrong?

Let’s dig into each a little bit.

Finding The Right Measures

Your dashboard’s users are going to want answers to particular questions.  Knowing those questions and what they’re expecting is critical to building a good dashboard.  And that doesn’t mean coming to them with a data dictionary and expecting them to put all the pieces together.

So where do we get these measures?  The first avenue is discussion.  Figure out what they’re always looking at.  What are the data sources they bring in?  What kinds of decisions do they make?  What causes them to make those decisions?

From there, look at the kinds of reports people use.  Reports and dashboards serve different purposes and have separate sets of rules around how you can best visualize data, but those long-form reports can commonly include interesting metrics.

Another place to look at are alerting systems.  If there’s an alert, you have a measure with some threshold which is important enough that people are willing to get messages about it.  That measure might be interesting to visualize, whether it be a historical trend or a point-in-time calculation.

Going From Data To Measures

You might not have all of the information in your data to answer their questions.  This is where it starts to get interesting, and where a tool like Power BI can be great:  if your users are getting data from a source like an API, webpage, spreadsheet, CSV file, or anything with a fairly consistent structure, you can use Power BI to import that data and merge it in with what you do have.  This isn’t a great strategy if there’s a big change in granularity or complex rules around how things tie together, but the functionality is there for those times when it is useful.

A lot of this comes down to finding and combining data from disparate sources.  Maybe you’ll get lucky and someone built a great data warehouse which holds all of these measures.  But then you wake up from that dream and get back to working on gnarly ETL processes.

Missing Data

A common issue with data is that we’re missing pieces.  This could mean missing values in certain fields, corroborating data which doesn’t exist in a second data source, or missing rows.  In some of these cases, we can ignore missing values or slap a default on it.  For example, if I have a small percentage of API calls return with no response time, I could set those response times to the median response time of all calls in the sample.  That way, I don’t bias the data set and don’t have to throw away missing rows.  But if half of the sample is missing response times, then this technique no longer works and I’m better off ditching the incomplete data.

How Good Is My Data Source?

The last question I want to cover is how good the data source itself is.  Within a company, there are data sources of better and worse quality.  Some of the lower-quality sources have problems with missing data, but another avenue of concern is, how was the data collected?  Data collected from a survey does not have the same level of quality as data collected from sampling a process, and we should treat them separately.

If you are using external data, how good is that quality?  If your users start picking out errors in the data, you run the risk of users no longer trusting your system.  That means you’ll want to perform some validation of the quality of external data.