Central database and KPI dashboard web app on Google Cloud for an automotive parts manufacturer in Thailand

How the brief came about

The customer wanted to consolidate internal organisational data in a single database on the company's GCP and view KPIs across several areas from one website. Before the work began, each area's data sat in separate sets of report files from multiple systems.

The customer wanted staff to use the same website both to enter on-site data and to view dashboards. The first phase therefore began with internal loss and defect KPIs, before adding data from other departments to the same system.

Requirements and constraints

The system had to run on the customer's Google Cloud Platform (GCP) and meet the requirements of the customer's IT department. The main requirements and constraints are as follows.

• Store data on the customer company's GCP, under the organisation's account and policies.
• Keep the server maintenance burden to a minimum, so the system is built mainly on serverless cloud services (the cloud provider looks after the servers).
• Users still produce the same Excel report files and enter some on-site data through web pages.
• Systems going onto the cloud must pass the security checklist of the customer's IT department and require approval before every deployment.
• At the start of the first phase, the input and output file formats were not ready, and requirements changed during development.

How does this central database system work?

This system takes existing report files into a single database, then calculates KPIs and displays them as charts and tables in a web browser. We chose to accept files in their existing format because most of the data was already in those report files. The web entry pages are therefore open only for on-site data not yet in the reports.

The customer also wanted to reduce server maintenance work, so the system uses services managed by GCP, such as Cloud Run and Cloud SQL, to reduce server upkeep. The system works through the following steps.

1. 1

   Bring data into the system

   Users upload Excel report files and corrective plan files (PDF) through the upload page. On-site data, such as defect quantities and hourly output for each Part No. (part code), is entered through the Defect Record Sheet and Production Today pages.

2. 2

   Calculate KPIs automatically

   The Overall Summary page calculates Internal Loss and the defect rate for the selected period: daily, weekly, monthly or yearly. The Business KPI Internal Loss page shows charts by product group, the monetary value lost, and the target line set on the KPI target-setting page.

3. 3

   See which defects to fix first

   The defect dashboard ranks the most frequent defects and lets responsible staff enter the cause, corrective approach and due date for the top-ranked items. Users can click a point on a chart to open the attached PDF file.

4. 4

   Compare plan with actual output

   The Performance Analysis page calculates the production plan from the production cycle times set on the Master Data page, then compares it with the actual output entered, by shift, along with a record of problems that caused lost time.

5. 5

   Control security according to the customer's checklist

   Cloud security work covers the Cloud Armor web application firewall (WAF), SSL encryption, the ClamAV antivirus program, password rules, restricted administrator privileges, access and activity logs, database backups, and separate GCP environments for development and production.

Deliverables

We delivered the first-phase web app on the customer's GCP in 2023, along with a user manual and system administration documents. The main pages of the first phase are as follows.

• Overview page (Overall Summary): Internal Loss and defect rate by time period
• Upload page for Excel report files and corrective plan files (PDF)
• KPI target-setting page and Business KPI Internal Loss page by product group
• Defect record entry page and dashboard (Defect Record Sheet)
• Hourly output entry page (Production Today) and plan-versus-actual output comparison page by shift (Performance Analysis)
• Master Data page and user login page

How did this system expand from the first phase into several modules?

We expanded this system by adding each department's module to the same web app and database one part at a time. Each module has its own scope and requirements document. The security work added separate GCP environments for development and production, and an approval step before deployment, so that new modules can be tested before going onto the live system. The sequence of work is as follows.

1. 2023, first phase: internal loss KPIs, defects, output and master data; delivered, with acceptance signed
2. 2023, internal loss module revision: attaching PDF files in tables, uploading files from the table page, and checking uploaded data against defect reports and production volume reports, with alerts on the upload page
3. 2023, sales and profit module: Sales Dashboard (plan, actual and variance), Profit Dashboard and Forecast, with pages for uploading and exporting data by template
4. 2023, cloud security work following the checklist of the customer's IT department
5. 2023–2024, quality module: customer claims and complaints, market claims, and email settings; in a round of revision and data accuracy checking in early 2024

The same customer's expense module is a separate case. Read the details in the case on a web app tracking actual expenses against targets by department

Confirmed results from the acceptance documents

The confirmed results of this case are that the first phase has been delivered and that there are acceptance documents signed by the customer's representative. For modules added later, there is only our own project status summary. Each document confirms different things, as follows.

• Acceptance checklist (Exit Criteria Checklist) for the first phase: signed by the customer's representative, the responsible reviewer and our signatory in July 2023
• Delivery note: the customer's representative signed to accept the first-phase system in August 2023
• First-phase closing document: lists the user manual, system administration documents and the signed acceptance checklist among the deliverables
• Our project status summary at the end of 2023: an internal document of ours, not a customer acceptance document, and the only source stating the status of the follow-on modules

The modules added after the first phase do not yet have signed acceptance documents, so we do not describe them as having passed acceptance. Additional corrections after first-phase acceptance had a separate retest round. This case has no business performance figures, so we report only the results backed by documents.

What we learned

This work offers the following principles for central database projects that take data from report files.

• Agree the source file format before development. A system that reads report files reads values according to the file structure. In this project, the input and output file formats were not ready at the start and changed during development, so the next project should lock down the columns, codes and KPI formulas with the owners of the figures, along with real sample files, before starting to build the system.
• A system that accepts uploaded files must check the data against the source reports. In this project, the scope of the internal loss module revision included checking data against defect reports and production volume reports, with alerts on the upload page, because a dashboard can be used for decisions only when its figures match the reports each department already uses.
• The IT department's security requirements are part of a cloud system, not an add-on for later. A system going onto an organisation's cloud must answer the checklist, configure firewalls, keep usage logs and have an approval step before deployment, so these should be included in the scope of work from the start.

How this approach can apply to your work

If your organisation has KPI reports across several areas in multiple sets of Excel files and already has a company cloud account, you can use the same approach as this case: take existing report files into a central database, calculate KPIs and display them on one website. This approach suits situations where each department keeps its figures in its own files and seeing the overall picture has to wait for someone to compile the reports. The parts that need adapting are your organisation's file formats, KPI formulas and security requirements.

• Prepare real sample report files for every set, and confirm how long the file format can be kept unchanged.
• Agree the KPI formulas and the green/red thresholds with the owner of the figures in each department before development.
• Request the IT department's security checklist from the start and include it in the scope of work.
• Start with the module whose data is most ready, then allow for revision rounds based on user feedback in each module.