A Fantastic Solution to Make Dashboard Data Downloadable

One of the polarizing topics in Business Intelligence is if users should be able to download the source data visualized in dashboards. Like most things, there are pros and cons to this functionality. Another phrase that comes to mind is that too much of a good thing can be bad. I’m in the camp that supports data downloads, but only when it’s done the right way, which I’ll cover in this post.

Design dashboard data downloads to 1) provide curated, trusted data, 2) make metadata available, 3) provide user traceability and 4) create a pathway to incorporate recurring needs into the visualization.

The Pros and Cons of Downloading Data

The debate on downloading data revolves around the risk vs. reward ratio. Also, the right decision for one dashboard or team may not be the same as for another. Regardless, if the business need exists, it falls to the BI Analyst to identify a solution that mitigate the risks.

Pros

1. Self-service analytics. Sometimes the business needs to conduct research that cannot be performed in the visualization. It’s simply easier for the business to filter and pivot data in an Excel spreadsheet than it is in a dashboard.
2. Metric validations. Let’s face it, sometimes the numbers on the dashboard look wrong to the business. A quick way to verify is for them to download the source data and confirm the accuracy.
3. Efficiency. BI teams are rarely staffed to meet all the visualization needs of every user. It’s also impossible to anticipate how business needs will evolve over time, and giving the users the ability to download the data allows them to solve for short term needs while giving you time to incorporate new visualizations.
4. Greater data literacy. It’s critical that business decision makers understand the data behind the numbers, can speak to the definitions of metrics and achieve greater comfort in the validity of the metrics. Being able to download and spend time with the source data increases this literacy over time.

Cons

1. Creating Shadow Reports. Downloading functionality gives the user the ability to create new reports that a centralized BI team is not aware of. This type of shadow reporting can create risk in the form of report dependencies that are not visible to the BI team. For example, if they make a change to the dashboard, it could break a downstream process unknowingly.
2. Incorrect use of the data. Even when metadata is readily available, the user might not invest time in understanding the data. As a result, they could use it to draw the wrong conclusions, which might in turn result in incorrect business decisions.

Making Data Downloads Available the Right Way

I believe the benefits of making the data available to business users far outweighs the risks, but even so, we should make an effort to reduce any risk as much as possible. Here are the ways to design your downloads to create a win-win for the BI team and their users.

1) Provide Curated/Trusted Data

To reduce the chance that data is used incorrectly, ensure that it is curated in a way that makes it easier for less data literate users to leverage. Much of this work you likely already did when prepping the data for visualization. However, you might also be providing additional fields in your download, so be sure to look at all of it. Here are some examples:

• Normalize the values inside the fields to eliminate data discrepancies
  • Ensure date fields include only date values with the same format. Date fields may have a mix of blanks and dummy values like 1/1/1900. There might also be different formats for the data if the field is stored as text. Using a consistent format for values and blanks prevents issues when working within Excel.
  • Clean up variations in spellings. Some text fields are created through manual entries in an application, which can lead to miss-spellings or word choice variations.
  • Convert IDs to their text string counterparts. You might use an ID in your dashboard to improve performance, but the user won’t know what those IDs mean. Convert them to text for their use.
• Provide the fields they need for self-service analytics
  • Eliminate visualization specific fields. If you created data fields to aid in your visualization, leave those out of the download. For example, having fields for longitude and latitude might help you in your map visualization, but they will be useless to the user.
  • Add fields for analysis. There might be additional fields in the data, not used for visualization, but helpful to the users. For example, in a workflow pipelines visualization, you might not need the name of the person an item is assigned. However, that field can be very helpful for your user to perform ad hoc analysis on the data.

2) Be Vigilant for Automation Opportunities

Design your download so that there is distinct usage data, and then monitor activity to look for patterns in download activity. If you see the same person downloading the data routinely, then schedule some time to have them show you what they do with the data. This is valuable insight into actual LOB use which you can then turn into an automated solution which meets those same needs.

For this to work, it is critical to separate the download from your main visualizations so that usage data can be captured. If it is integrated into your visuals, then you may not know if a user is going to your dashboard to download the data or simply to view it.

Here are a couple suggestions which you might be able to leverage in your BI space:

• Dedicated Dashboards. Create your data download as a standalone dashboard with that sole purpose in mind. You can link to it from your primary visualizations and then you get distinct usage data. An added benefit is having real estate to put plenty of filters for ad hoc research on screen.
• Excel Reports. Publish a pre-formatted data set in Excel. In my post on the Top 3 Powerful Controls Tools To Empower BI Teams I recommended using a tool for report file publishing. With this tool, you can create a custom data set in Excel and publish. The tool will then capture usage data when the user downloads the file. You can also link to this tool from your visualization.

3) Promote Data Literacy

For users who are working directly with the data fields, it is even more important that they understand them. This understanding can take two primary forms:

1. Field Definitions. This is fairly straightforward, simply give them easy access to a list of the fields included in the download along with their definitions. For calculated fields, I encourage explaining how it was calculated here as well.
2. Data Lineage. If using raw data, you can simply share the source application it is coming from. However, it is equally important on transformed data to demonstrate how it was transformed. I recommend a simple high-level explanation of how the data went from the source and may have merged with other sources.

You will also need to consider how to present the definitions and lineage in a way that makes sense to the user, but is also efficient for maintenance. For example, create a centralized data repository of all field definitions that you can source from in any of your visualizations or excel extracts.

As I mentioned previously, in my experience this is a hot topic that sparks a lot of debate. Now I’d like to hear from you on your own thoughts. Do you agree that users should be able to download the data behind the dashboards? Leave a comment below if you have thoughts or suggestions!