End to End process for developing Dashboards

Photo by Firmbee.com on Unsplash

In this post, we will try to cover the end-to-end process that is involved in creating a report/dashboard. The steps here would resonate with a BI (Business Intelligence) engineer, data analyst, or anyone familiar with the reporting ecosystem. We will barely find any department where reports are not used. And in the increasing data literacy debates around all domains, likely, new departments and teams would quickly start adopting reporting systems to be data-informed. As diverse as the business and departments are, the same is the diversity observed in the reporting solution.

A reporting architecture can look like this

Source | Microsoft

Or it can be just plain simple excel used for storing records to creating reports all in the spreadsheet itself. But as data volume would increase the business would most likely switch to more robust solutions (one that involve not using excel as your database). Scale and use-case would define an ideal reporting/dashboarding solution and architecture.

We will cover the basic steps involved in the dashboarding process. It can be broadly classified as below

Source|Author

Step 1: Understanding Business requirements

The first and arguability most important process are to understand the business requirements.

Who is the end-user? A general manager who is in charge of multiple stores

What is the requirement? Want to track the performance of stores. This is where we convert business requirements into measurable KPIs/metrics. Performance can be sales, new customer acquisition, etc.

How is the metric measured? In our case, it’s the total billed amount. As the complexity of the report changes, we might have to spend more time working together with the business stakeholders to align on how the metric is defined.
Data Glossary is a good umbrella for these steps.

Step 2: Data Preparation

There will be lots of debate on normalization vs star schema, Inmon vs Kimball approach for data modeling, and there is no one size fits all solution here. We have to decide if the source we found above is optimal for reporting, or we need another abstraction on top of it, called reporting layer. Views and Tables in reporting layer are created to optimize analytical queries.

If we have a data governance framework (say Azure Purview for example), we document the reporting layer items there.

Step 3: Creating Visualization

Now that we have a data source ready, let’s move on to the reporting tool. Based on the tool (say Tableau, Power BI, or Qlik), there are various connectors available that we can use based on our data source. Also, there is support for different connection type, that includes a live connection to the source, i.e changes in the source are reflected in the report or extract, where an extract is created based on the specified frequency.

Extracts are faster than live connections and are preferred in most cases, but if required we have the option to go the live connection route.

As we did the toil of preparing the reporting layer, we have a data source ready to directly plug into our BI tool. But in a few cases, we might not have that extra DWH resource to support a reporting layer. Tools have evolved over time to give users some options for data preparation directly in the tool itself. We can leverage those to complete any changes to the source if required.

Very complex data preparation steps are generally avoided in the final app, as they increase the operational load on the tool and we might see slower performance

From here on it’s all about using the right visuals and making the reports approachable for end users. It’s also important that the report follows a storytelling approach that lets the user iterate over the visuals /charts along with the flow of the story you created and can reach conclusions on the metric the report is tracking.

A few things that we can follow for a streamlined process include

1. Standard Template: It helps to have a predefined template, that has a logo, color themes, the position of filters, and other attributes repeated in other reports at the same place. This makes it easy for users to quickly start using new reports as they are already familiar with the template
2. Audit Details: having the last reload and last validated timestamps can help give additional info to the business.
3. Storytelling: compatible visuals and flow of information

Step 4: Testing, UAT, and Deployment

Once we are done with the development of reports the next step is getting the data points validated. Once we are confident with the numbers in the report, its send for review to the business user. Based on the feedback received there, iterative changes are made to the report, and once it passes the review it’s ready to be shared with other users. This is a step that can also be considered part of the previous step.

There, we have completed the development and deployment of the dashboard.

What’s next?

1. Creating data-driven alerts on top of the dashboard.
2. Maintenance of the data source and dashboard.
3. Creating subscriptions (for eg weekly, monthly) on top of the dashboard
4. Performance evaluations, keeping track if the dashboard performance degrades

Dashboarding Steps | A-Z

And this concludes the lifecycle of dashboard development A-Z.