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% sales 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. Exactly what information do I need to realize this outline 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; most source data is not immediately suitable to be used in a report. Figuring out 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. As far as we are concerned, the conditions that a sound data model should meet 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 understanding of the data to make decisions). In our experience, the integration of targets is not without controversy either. 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 data deliver.
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 should be monitored? Also note 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. One often opts for 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 receives 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 complete 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. For example, there is no standardization between sources, you often get way too much data, and you are dependent on changes in the source’s data model.

Not paying enough 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 takes a lot of time in maintenance and regularly leads to modification of the data model and dashboard. In short, starting a data model too late inevitably leads to extra work.

Who writes who stays

The construction of the KPIs and dimensions and (meta-)data involved must be well documented and tracked. Both end users and administrators appreciate it when the origin of the data and the 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 purpose.

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 was taken to minimize the impact on systems and associated costs.

Contact

Want to know more about this topic? If so, please contact us!