Dashboards: data model and design choices (part 2/4)

In the first article of this four-part series, we discussed when a dashboard is the solution for your information needs. If all goes well, you now know what a dashboard must meet to ensure that you get the right insights from the data so you can make better decisions.

The next step is to translate your information needs into concrete KPIs and determine what data is needed to measure progress on these KPIs. In the previous article, we referred to an example of a manager who wants to achieve 20% revenue growth over the previous year. The KPI here is 20% revenue growth, the dimension against which you want to measure this is time. In the dashboard, you want to be able to monitor the drivers for this KPI, for example, in the form of the number of new customers and their average order value. And this is just one example.

Make sure you have input from all key stakeholders before you start working on the data model. Our tip: start with an outline of your final product. In our opinion, this is a step that is underestimated and has a lot of impact on the end result. What information are you going to show, where exactly, with what visualizations? An outline helps you determine what requirements you have for the design of your data model. What information exactly do I need to realize this sketch and how can I tell if the KPI is positive or negative?

Why is a data model important for dashboard development?

Building a data model starts with matching information needs to available columns in your source data. This often requires considerable work, as most source data is not immediately suitable to be used in a report. Designing a data model forces you to think about logical relationships between entities and unique keys. With this design, you will later be able to do quality control. The conditions that a solid data model should meet as far as we are concerned are as follows:

1. The data model must contain all the data needed to calculate the KPIs and compare them to the desired dimensions, especially not more (heavier in terms of performance) and obviously not less (insufficient insight into the data to make decisions). In our experience, the integration of targets is also not without difficulty. This data is often not present in a data warehouse or not available at all levels. So include these in your data model and discuss with your client who is responsible for providing data
2. The level of detail should match the initial demand:
   1. Do stakeholders want to be able to monitor monthly, weekly or even daily? And to how far back in time?
   2. What depth of insight is needed to adjust the organization? Is it at the store level, (sub)product group/brand and which customer groups need to be monitored? Also consider the volume of data that can increase rapidly with a high level of detail, long history or expansion of the number of source systems.
3. The robustness of the model determines its performance, cost and ease of use and thus adoption within the organization. People often choose a STER model, a multi-dimensional modeling technique in which there are one or more fact tables surrounded by dimension tables that efficiently store information about the facts. This form is popular because a lot of data can be queried quickly in the dashboards based on it.

If you take these criteria and weigh them against the demand/information needs, the data will naturally take the required form for maximum flexibility for creating insights vs. optimization for performance. However, figuring out the data model generally gets too little attention. Three common reasons for this are:

1. Time: you need the insights now (read: yesterday) and often there is little time or insufficient expertise to work on BI/Reporting in a structured way.
2. Too Lean Minimum Viable Products (MVP): the dashboard starts as an idea based on a few separate files and as the initial insights are well received, one builds on this without first properly setting up the data for large-scale use.
3. Many dashboarding software can extract data directly from front-end applications without the intervention of a DWH. You can often import a full data model from the source, so you are not forced to think about a data model that fits your issue. Visualizing a single source is still doable, integrating multiple sources within your visualization tool is not recommended. This way there is no standardization between the sources, you often get far too much data and you are dependent on changes in the source's data model.

Paying insufficient attention to the data model and data refresh time later in the process can have a major impact not only on the performance or adoption of your dashboard (no one uses a slow or dated dashboard), but also on the overall time investment. An inefficient dashboard requires a lot of time in maintenance and regularly leads to modification of the data model and dashboard. In short, starting too late with a data model irrevocably leads to extra work.

Who writes who stays

The construction of the KPIs and dimensions and (meta-)data involved must be well documented and maintained. Both end users and administrators appreciate it when the origin of the data and calculations are documented. Changes are often done in another corner of the organization (e.g., product development) and it is important to have process agreements in place for announcing, interpreting and implementing these changes through the data chain (from source system, to BI team, to end users). Documentation can of course be done in PowerPoint, Word or Excel but tools like Confluence and Microsoft Data Catalog are also great for this.

With the points of this article in mind, chances are you will arrive at a solid data model as the basis for the visualizations in your dashboards. This is because the data model contains all the data needed to meet the demand, is aggregated at the desired level of detail and transparently constructed which keeps it clear for all stakeholders. In addition, care has been taken to minimize the impact on the systems and the associated costs.

Stay tuned!

In the next article in this series, we will discuss data visualization and the ease of use of a dashboard. If you would like to learn more about specific topics in this article or the more technical side of the data modeling process, please leave a message or contact us.