Defining reports

The report is probably the most common application of business intelligence. A report can be described as a document that contains data used for reading or viewing. It can be as simple as a data table or as complex as a subtotaled view with interactive drill-downs, similar to Excel’s Subtotal or Pivot Table functionality.

The key attribute of a report is that it doesn’t lead a reader to a predefined conclusion. Although reports can include analysis, aggregations, and even charts, reports often allow for the end users to apply their own judgment and analysis to the data.

To clarify this concept, Figure 1-1 shows an example of a report. This report shows the National Park overnight visitor statistics by period. Although this data can be useful, it’s clear this report isn’t steering the reader toward any predefined judgment or analysis; it’s simply presenting the aggregated data.

Defining dashboards

A dashboard is a visual interface that provides at-a-glance views into key measures relevant to a particular objective or business process. Dashboards have three main attributes:

• Dashboards are typically graphical in nature, providing visualizations that help focus attention on key trends, comparisons, and exceptions.
• Dashboards often display only data that are relevant to the goal of the dashboard.
• Because dashboards are designed with a specific purpose or goal, they inherently contain predefined conclusions that relieve the end user from performing his own analysis.

Figure 1-2 illustrates a dashboard that uses the same data shown in Figure 1-1. This dashboard displays key information about the national park overnight-visitor stats. As you can see, this presentation has all the main attributes that define a dashboard. First, it’s a visual display that allows you to quickly recognize the overall trending of the overnight-visitor stats. Second, you can see that not all the detailed data is shown here — you see only the key pieces of information relevant to support the goal of this dashboard, which in this case would be to get some insights on which parks would need some additional resources to increase visitor rates. Finally, by virtue of its objective, this dashboard effectively presents you with analysis and conclusions about the trending of overnight visitors.

Establish the audience for, and purpose of, the dashboard

Chances are your manager has been asked to create the reporting mechanism and he has passed the task to you. Don’t be afraid to ask about the source of the initial request. Talk to the requesters about what they’re asking for. Discuss the purpose of the dashboard and the triggers that caused them to ask for a dashboard in the first place. You may find, after discussing the matter, that a simple Excel report meets their needs, foregoing the need for a full-on dashboard.

If a dashboard is indeed warranted, talk about who the end users are. Take some time to meet with a few of the end users to talk about how they’d use the dashboard. Will the dashboard be used as a performance tool for regional managers? Will the dashboard be used to share data with external customers? Talking through these fundamentals with the right people helps align your thoughts and avoids the creation of a dashboard that doesn’t fulfill the necessary requirements.

Delineate the measures for the dashboard

Most dashboards are designed around a set of measures, or key performance indicators (KPIs). A KPI is an indicator of the performance of a task deemed to be essential to daily operations or processes. The idea is that a KPI reveals performance that is outside the normal range for a particular measure, so it therefore often signals the need for attention and intervention. Although the measures you place into your dashboards may not officially be called KPIs, they undoubtedly serve the same purpose — to draw attention to problem areas.

The topic of creating effective KPIs for your organization is a subject worthy of its own book and is out of the scope of this endeavor. For a detailed guide on KPI development strategies, pick up David Parmenter’s Key Performance Indicators: Developing, Implementing, and Using Winning KPIs (Wiley Publishing, Inc.). That book provides an excellent step-by-step approach to developing and implementing KPIs.

The measures used on a dashboard should absolutely support the initial purpose of that dashboard. For example, if you’re creating a dashboard focused on supply chain processes, it may not make sense to have human resources head-count data incorporated. It’s generally good practice to avoid nice-to-know data in your dashboards simply to fill white space or because the data is available. If the data doesn’t support the core purpose of the dashboard, leave it out.

Here’s another tip: When gathering the measures required for the dashboard, I find that it often helps to write a sentence to describe the measure needed. For example, rather than simply add the word Revenue into my user requirements, I write what I call a component question, such as “What is the overall revenue trend for the past two years?” I call it a component question because I intend to create a single component, such as a chart or a table, to answer the question. For instance, if the component question is “What is the overall revenue trend for the past two years?” you can imagine a chart component answering this question by showing the two-year revenue trend.

I sometimes take this a step further and actually incorporate the component questions into a mock layout of the dashboard to get a high-level sense of the data the dashboard will require. Figure 1-3 illustrates an example.

Each box in this dashboard layout mockup represents a component on the dashboard and its approximate position. The questions within each box provide a sense of the types of data required to create the measures for the dashboard.

Catalog the required data sources

When you have the list of measures that need to be included on the dashboard, it’s important to take a tally of the available systems to determine whether the data required to produce those measures is available. Ask yourself the following questions:

• Do you have access to the data sources necessary?
• How often are those data sources refreshed?
• Who owns and maintains those data sources?
• What are the processes to get the data from those resources?
• Does the data even exist?

These are all questions you need answered when negotiating dashboard development time, data refresh intervals, and change management.

Conventional wisdom says that the measures on your dashboard shouldn’t be governed by the availability of data. Instead, you should let dashboard KPIs and measures govern the data sources in your organization. Although I agree with the spirit of that statement, I’ve been involved in too many dashboard projects that have fallen apart because of lack of data. Real-world experience has taught me the difference between the ideal and the ordeal.

If your organizational strategy requires that you collect and measure data that is nonexistent or not available, press Pause on the dashboard project and turn your attention to creating a data collection mechanism that will get the data you need.

Define the dimensions and filters for the dashboard

In the context of reporting, a dimension is a data category used to organize business data. Examples of dimensions are Region, Market, Branch, Manager, or Employee. When you define a dimension in the user requirements stage of development, you’re determining how the measures should be grouped or distributed. For example, if your dashboard should report data by employee, you need to ensure that your data collection and aggregation processes include employee detail. As you can imagine, adding a new dimension after the dashboard is built can get complicated, especially when your processes require many aggregations across multiple data sources. The bottom line is that locking down the dimensions for a dashboard early in the process definitely saves you headaches.

Along those same lines, you want to get a clear sense of the types of filters that are required. In the context of dashboards, filters are mechanisms that allow you to narrow the scope of the data to a single dimension. For example, you can filter on Year, Employee, or Region. Again, if you don’t account for a particular filter while building your dashboarding process, you’ll likely be forced into an unpleasant redesign of both your data collection processes and your dashboard.

If you’re confused by the difference between dimensions and filters, think about a simple Excel table. A dimension is like a column of data (such as a column containing employee names) in an Excel table. A filter, then, is the mechanism that allows you to narrow your table to show only the data for a particular employee. For example, if you apply Excel’s AutoFilter to the Employee column, you are building a filter mechanism into your table.

Determine the need for drill-down features

Many dashboards provide drill-down features that allow users to “drill” into the details of a specific measure. You want to get a clear understanding of the types of drill-downs your users have in mind.

To most users, drill-down feature means the ability to get a raw data table supporting the measures shown on the dashboard. Although getting raw data isn’t always practical or possible, discussing these requests will, at minimum, allow you to talk to your users about additional reporting, links to other data sources, and other solutions that may help them get the data they need.

Establish the refresh schedule

A refresh schedule refers to the schedule by which a dashboard is updated to show the latest information available. Because you’re the one responsible for building and maintaining the dashboard, you should have a say in the refresh schedules — your manager may not know what it takes to refresh the dashboard in question.

While you’re determining the refresh schedule, keep in mind the refresh rates of the different data sources whose measures you need to get. You can’t refresh your dashboard any faster than your data sources. Also, negotiate enough development time to build macros that aid in automation of redundant and time-consuming refresh tasks.

Rule number 1: Keep it simple

Dashboard design expert Stephen Few has the mantra, “Simplify, simplify, simplify.” The basic idea is that dashboards cluttered with too many measures or too much eye candy can dilute the significant information you’re trying to present. How many times has someone told you that your reports look “busy”? In essence, this complaint means that too much is going on in the page or screen, making it hard to see the actual data.

Here are a few actions you can take to ensure simpler and more effective dashboard designs.

Use layout and placement to draw focus

As I discuss earlier in this chapter, only measures that support the dashboard’s utility and purpose should be included on the dashboard. However, it should be said that just because all measures on your dashboard are significant, they may not always have the same level of importance. In other words, you’ll frequently want one component of your dashboard to stand out from the others.

Instead of using bright colors or exaggerated sizing differences, you can leverage location and placement to draw focus to the most important components on your dashboard.

Various studies have shown that readers have a natural tendency to focus on particular regions of a document. For example, researchers at the Poynter Institute’s Eyetrack III project have found that readers view various regions on a screen in a certain order, paying particular attention to specific regions onscreen. They use the diagram in Figure 1-4 to illustrate what they call priority zones. Regions with the number 1 in the diagram seem to have high prominence, attracting the most attention for longer periods. Meanwhile, number 3 regions seem to have low prominence.

You can leverage these priority zones to promote or demote certain components based on significance. If one of the charts on your dashboard warrants special focus, you can simply place that chart in a region of prominence.

Note that surrounding colors, borders, fonts, and other formatting can affect the viewing patterns of your readers, deemphasizing a previously high-prominence region.

Format numbers effectively

There will undoubtedly be lots of numbers on your dashboards. Some of them will be in charts, and others will be in tables. Remember that every piece of information on your dashboard should have a reason for being there. It’s important that you format your numbers effectively to allow your users to understand the information they represent without confusion or hindrance. Here are some guidelines to keep in mind when formatting the numbers on your dashboards and reports:

• Always use commas to make numbers easier to read. For example, instead of 2345, show 2,345.
• Use decimal places only if that level of precision is required. For instance, there’s rarely a benefit to showing the decimal places in a dollar amount, such as $123.45. In most cases, the $123 will suffice. Likewise in percentages, use only the minimum number of decimals required to represent the data effectively. For example, instead of 43.21%, you may be able to get away with 43%.
• Use the dollar symbol only when you need to clarify that you’re referring to monetary values. If you have a chart or table that contains all revenue values, and there’s a label clearly stating this, you can save room and pixels by leaving out the dollar symbol.
• Format very large numbers to the thousands or millions place. For instance, rather than display 16,906,714, you can format the number to read 17M.

In Chapter 3 of this book, you explore how to leverage number-formatting tricks to enhance the readability of your dashboards and reports.

Use titles and labels effectively

It’s common sense, but many people often fail to label items on dashboards effectively. If your manager looks at your dashboard and asks you, “What is this telling me?” you likely have labeling issues. Here are a few guidelines for effective labeling on your dashboards and reports:

• Always include a timestamp on your reporting mechanisms. This minimizes confusion when distributing the same dashboard or report in monthly or weekly installments.
• Always include some text indicating when the data for the measures was retrieved. In many cases, the timing of the data is a critical piece of information when analyzing a measure.
• Use descriptive titles for each component on your dashboard. This allows users to clearly identify what they’re looking at. Be sure to avoid cryptic titles with lots of acronyms and symbols.
• Although it may seem counterintuitive, it’s generally good practice to de-emphasize labels by formatting them to hues lighter than the ones used for your data. Lightly colored labels give your users the information they need without distracting them from the information displayed. Ideal colors for labels are colors commonly found in nature: soft grays, browns, blues, and greens.

Does my dashboard present the right information?

Look at the information you are presenting and determine whether it meets the purpose of the dashboard identified during requirements gathering. Don’t be timid about clarifying the purpose of the dashboard again with your core users. You want to avoid building the dashboard in a vacuum. Allow a few test users to see iterations as you develop it. This way, communication remains open, and you won’t go too far in the wrong direction.

Does everything on my dashboard have a purpose?

Take an honest look at how much information on your dashboard doesn’t support its main purpose. To keep your dashboard as valuable as possible, you don’t want to dilute it with nice-to-know data that’s interesting but not actionable.

If the data does not support the core purpose of the dashboard, leave it out. Nothing says you have to fill every bit of white space on the page.

Does my dashboard prominently display the key message?

Every dashboard has one or more key messages. You want to ensure that these messages are prominently displayed. To test whether the key messages in a dashboard are prominent, stand back and squint while you look at the dashboard. Look away and then look at the dashboard several times. What jumps out at you first? If it’s not the key components you want to display, you’ll have to change something. Here are a few actions you can take to ensure that your key components have prominence.

• Place the key components of your dashboard in the upper left or middle left of the page. Studies show that these areas attract the most attention for longer periods.
• De-emphasize borders, backgrounds, and other elements that define dashboard areas. Try to use the natural white space between your components to partition your dashboard. If borders are necessary, format them to lighter hues than your data.
• Format labels and other text to lighter hues than your data. Lightly colored labels give your users the information they need without distracting them from the information displayed.

Can I maintain this dashboard?

There is a big difference between updating a dashboard and rebuilding a dashboard. Before you excitedly send out the sweet-looking dashboard you just built, take a moment to think about the maintenance of such a dashboard. You want to think about the frequency of updates and what processes you need to go through each time you update the data. If it’s a one-time reporting event, set that expectation with your users. If you know it will become a recurring report, you’ll want to really negotiate development time, refresh intervals, and phasing before agreeing to any timetable.

Does my dashboard clearly display its scope and shelf life?

A dashboard should clearly specify its scope and shelf life. That is to say, anyone should be able to look at your dashboard and know the period it’s relevant to and the scope of the information on the dashboard. This comes down to a few simple things you can do to effectively label your dashboards and reports.

• Always include a timestamp on your dashboard. This minimizes confusion when distributing the same dashboard or report in monthly or weekly installments.
• Always include some text indicating when the data for the measures was retrieved. In many cases, timing of the data is a critical piece of information when analyzing a measure.
• Use descriptive titles for each component on your dashboard. Be sure to avoid cryptic titles with lots of acronyms and symbols.

Is my dashboard well documented?

It’s important to document your dashboard and the data model behind it. Anyone who has ever inherited an Excel worksheet knows how difficult it can be to translate the various analytical gyrations that go into a report. If you’re lucky, the data model will be small enough to piece together in a week or so. If you’re not so lucky, you’ll have to ditch the entire model and start from scratch. By the way, that troublesome Excel data model doesn’t even have to be someone else’s. I’ve actually gone back to a model that I built, and after six or so months I had forgotten what I had done. Without documentation, it took me a few days to remember and decipher my own work.

The documentation doesn’t even have to be hifalutin fancy stuff. A few simple things can help in documenting your dashboard.

• Add a Model Map tab to your data model. The Model Map tab is a separate sheet you can use to summarize the key ranges in the data model and spell out how each range interacts with the reporting components in the final presentation layer.
• Use comments and labels liberally. It’s amazing how a few explanatory comments and labels can help clarify your model even after you’ve been away from your data model for a long time.
• Use colors to identify the ranges in your data model. Using colors in your data model enables you to quickly look at a range of cells and get a basic indication of what that range does. Each color can represent a range type. For example, yellow could represent staging tables, gray could represent formulas, and purple could represent reference tables.

Is my dashboard user-friendly?

Before you distribute your dashboard, you want to ensure that it’s user-friendly. It’s not difficult to guess what user-friendly means:

• Intuitive: Your dashboard should be intuitive to someone who has never seen it. Test it out on someone and ask that person whether it makes sense. If you have to start explaining what the dashboard says, something is wrong. Does the dashboard need more labels, less complicated charts, a better layout, more data, less data? It’s a good idea to get feedback from several users.
• Easy to navigate: If your dashboard is dynamic, allowing for interactivity with macros or pivot tables, make sure that the navigation works well. Does the user have to click in several places to get to her data? Is the number of drill-downs appropriate? Does it take too long to switch from one view to another? Again, test your dashboard on several users. And be sure to test any interactive dashboard features on several computers other than yours.
• Prints properly: Nothing is more annoying than printing a dashboard only to find that the person who created the dashboard didn’t take the time to ensure that it prints correctly. Be sure to set the print options on your Excel files so that your dashboards print properly.

Is my dashboard accurate?

Nothing kills a dashboard or report faster than the perception that the data in it is inaccurate. It’s not within my capabilities to tell you how to determine whether your data is accurate. I can, however, highlight three factors establishing the perception that a dashboard is accurate:

• Consistency with authoritative sources: It’s obvious that if your data does not match other reporting sources, you’ll have a data credibility issue — especially if those other sources are deemed to be the authoritative sources. Be aware of the data sources that are considered to be gospel in your organization. If your dashboard contains data associated with an authoritative source, compare your data with that source to ensure consistency.
• Internal consistency: It’s never fun to explain why one part of your dashboard doesn’t jibe with other parts of the same dashboard. You want to ensure some level of internal consistency within your dashboard. Be sure that comparable components in different areas of your dashboard are consistent with each other. If there is a reason for inconsistency, clearly notate those reasons. It’s amazing how well a simple notation clears up questions about the data.
• Personal experience: Have you ever seen someone look at a report and say “That doesn’t look right?” They are using what some people call “gut feel” to evaluate the soundness of the data. None of us looks at numbers in a vacuum. When we look at any analysis, we bring with us years of personal knowledge, interaction, and experience. We subconsciously use these experiences in our evaluation of information. When determining the accuracy of your dashboard, take into consideration organizational “anecdotal knowledge.” If possible, show your dashboard to a few content experts in your company.