All dashboards require raw data to become functional. This makes the third section of the D3S structure, Datasheet/Database, the most important one (and ironically, the most ignored as well) as it forms the backbone of the dashboard. A well-structured datasheet provides a means to manage large data-sets efficiently, while maintaining control over data access and update.
In this tutorial we will cover some of the best practices that must be followed while creating/designing the datasheet of an MS Excel based dashboard.
Data should follow a certain pattern
- Well organized data in different rows/columns
- No null/blank elements in the datasheet range
- Data columns should be fixed before starting work on the dashboard; becomes more important when working with VBA based dashboards
Illustration: 5- year value and volume sales for some drugs available in select geographies have been tabulated below
Concept of Unique Key
The database concept states that there must be a unique value in each row of the database that should be able to differentiate it from all other records in the database. This unique value, commonly referred to as the Unique Key, may be composed of one or more parameters (columns ) of the data-set . Since this is a unique value, it serves as a unique identifier that can be used for referring to any specific row/record. It is this unique key which links the dashboard to the datasheet and facilitates fetching of accurate data.
In the above data-set, there are only two parameters ─ Country and Drug Name ─ both of which contain duplicate values. Hence, we need a unique key to be able to access each record separately. In this case, we can simply concatenate (combine one after another, like in a series) these two parameters to form the unique key. This concatenation would result in a different value for every row in the dataset (thus the unique key). The figure below illustrates this concept:
- Unique key cannot be a NULL value
- Unique key can be defined in more than one ways, depending upon the number of parameters available in the datasheet and the dashboard design
- Data structuring and unique key selection could be done in multiple ways, but one should always choose an optimal way of doing it to make the dashboard easy to use/maintain/update while ensuring data accuracy.
For instance, if we want to report either Value sales or Volume sales from the above data-set for select country, instead of reporting the entire data for any selected record, we would need to restructure the data-set and unique key as shown below:
A corresponding dashboard (Download File) to fetch data from this datasheet is shown below:
What you need to remember is that organizing your data in the datasheet is the key for creating efficient dashboards. Feel free to reach out to us at firstname.lastname@example.org in case you face any difficulties in this section and we would be happy to address your queries.