Overview of Production and KPI Dashboards
The hypothetical ACME Production Company approached me to develop a series of dashboards to track the production and distribution of their personalised brochures, which were translated into 18 languages, and distributed every month. Three brochures were created every month, the first for clients who were interested in media and entertainment news, the second for a readership with a focus on business, and the third for those interested in luxury products and fashion. Each brochure was in interactive PDF format, personalised and published to a web portal that could only be accessed by the subscriber. The PDF contained embedded meta data which included the publication date, the distribution date, language and product name. This metadata was provided to me, in a daily xml feed. My challenge was to streamline the flow of data from the web portal back to the production dashboards to show the status of production, workload for each production team and highlight any risks of meeting publication deadlines. Every month this data was to be collated into a KPI status report to show if any common production issues arose over time and help identify any potential issues that may arise in the future. In order to streamline this flow of data, I loaded the XML feed into a MS SQL database, parsed the metadata using SQL queries and shared the data with Tableau using a Tableau connector. Once I had the data in a clean, aggregated format I was ready to build the dashboards according to client requirements. The dashboard design brief was as follows:
Dashboard 1. Create a dashboard with a timeline that showed the current production status with dates referring to the current month, production, and due dates, the volumes of each production run, the workload for each operator and a colour-coded warning system to identify any risks of missing a deadline.
Dashboard 2. The second dashboard was intended to provide ‘current status’ metrics, showing the number of documents in scope, the number completed and the percentage completed. If a process was running slowly or there seemed to be any problems the charts were coloured accordingly. This dashboard showed status based either on the process model or the workload of the relevant production team.
Dashboard 3. The third dashboard was intended to show real-time visual of the amount of work completed and work left to do for each product/language. This was intended to be used by the operation teams and discussed in their morning meetings to confirm production status reports.
Dashboard 4. A fourth dashboard was intended to show the production over time, related to workload for each production team. This was used to highlight any potential gaps in available resources or if there was a risk of any team having too much work on.
Dashboard 1. I chose to use a Gantt chart to show the timeline of production. Note that the production month begins 1st September 2019 as I selected old dummy data to create the dynamic dashboard. Therefore all data is related to a hypothetical production run in the past. I have however produced date calculation formats that could be used for a similar production run sometime in the future. For example, if you select 1st January 2022, the Gantt chart will adjust to show the production dates for that month. All documents will be red though as none will have been completed for that month. In contrast, if you select a date in the past all documents will have changed to green as they have all been marked as published. Note that I have programmed the current production date to be the 19th of whichever month you have selected. If this was to go live I would code this to show today’s date.
Through analysis of this dashboard, we can see that the languages in red would suggest there may be a problem with them. As a manager reviews this dashboard she/he may highlight this with the relevant production team leader to ensure that no deadlines would be missed.
Dashboard 2. This dashboard provides a quick overview of the production run based on completed brochures and workload for each production team. A manager or team member can quickly identify that there are potential issues with Brochure B and that Production Team 3 has a lower completion rate than the other two teams.
Dashboard 3. This dashboard shows the completion process for each language version of each brochure. It provides a quick overview so a manager or operator can address any issues. The gauge charts are ordered based on the percentage of outstanding documents therefore it can be ascertained quite quickly that there are issues regarding the brochures being prepared for Romanian and Swedish, as they are at the top and to the left.
Dashboard 4. This dashboard shows the production flow timeline with colours coded for each production team. This helps to identify how workload is distributed and whether there are any risks of any teams being overstretched. It is also valuable to schedule upskilling and training sessions when workload dips towards the end of each month.