Data Model for Excel Executive Dashboard
Dashboards are completely different from your conventional spreadsheet report. They require a different way of thinking about Excel and the communication of data. This includes the set-up of an excel dashboard. The design of the data model should build in flexibility and design out as many manual excel tasks as possible. This is your main goal when developing your Excel dashboard reports.
Your starting point is to write down all the processes involved from getting your source data to the finished report. You would want to automate a number of these tasks by using Visual Basic for Applications (VBA) or by effectively designing them out.
key questions
There are a few things you will need to consider in developing excel dashboard reports. How will your dashboard update with new data? Will you require outside technical help to get your dashboard up and running? Will it be mainly static or dynamic?
Breaking up the data model into parts
The data model needs to be broken up in order to build in flexibility into the way you present your dashboard reports and how the data is updated or added to it. Source data should be structured appropriately and presented as a flat data file.
Conventional spreadsheet reports usually have all three phases mixed in. Pivottables can be used for the analysis and presentation. The first phase is the data itself, then the analysis of the data, which would usually involve some aggregation. Finally, you would present the analysis as a summary with appropriate formatting, colour and labels. Using this structure allows complete flexibility over your analysis and how data is presented.
Documenting your model is critical for business continuity and as a note to the analyst as well. There can be a lot of things to remember when working with a lot of data, worksheets and dashboard components, including the processes involved.