This company is the second largest (by production volume) automotive manufacturer in Germany – with 280,000 employees and annual revenue of €150 billion. Led by the Chief Information Security Officer, the global cybersecurity team, which is made up of 800 individuals across 16 global teams, is responsible for the design of tailor-made, holistic solutions for IT security, as well as the tracking of cyber security processes and key figures. Progress is measured via Key Performance Indicators (KPIs) such as the percentage of websites scanned for risk or number of tickets opened for security-relevant incidents. A monthly KPI report evaluates the current status as well as target fulfilment. A multitude of data, which are imported from different sources including proprietary software, manually created Excel files as dictionaries, and SharePoint, are combined to calculate the KPIs.
An Excel Alternative Saving 150+ Hours Per Month
Previously, one employee was responsible for this report, which included collecting and integrating the data, computing all KPIs, and visualizing and distributing the results. This was done entirely in Excel and required one month to be completed. The biggest pain points were the inability for Excel to handle the large amounts of data (often crashing mid computation) as well as the manual process, which had a precise order of steps that needed to be taken and was extremely error-prone.
For one year, the team tried to automate the processing and delivery of the KPIs in a dashboard using IBM Congos. However, this was resource-intensive and didn’t meet the desired speed of development, nor the desired level of interactivity within the visualizations. The current solution, built by KNIME Partner Gemmacon, uses KNIME Analytics Platform, KNIME Server, and PowerBI (via the native KNIME PowerBI integration).
As the first step, the current, rather simple data transformation processes, which are currently done using Excel, are reproduced as KNIME workflows. Each of the six teams has their own KPIs and gets their own dashboard. Each workflow combines two to four data sources such as data extracts from proprietary software and they use data transformation and integration nodes such as the Rule Engine node for rule-based classification and group assignment of data rows such as security incidents, assignment of target values, and deletion/censoring of sensitive data. The Date&Time nodes are used to enable time filtering, and the Math Formula node for calculating KPIs.
Combining KNIME and PowerBI for Efficient KPI Dashboards
One advantage of performing the data transformation in KNIME instead of directly in PowerBI, is that transformation in KNIME is more straight forward and comprehensible than with DAX Formulas in PowerBI. Whenever a new calculation is needed, it’s implemented in the KNIME workflow and the data source is automatically updated in PowerBI. The workflow results are collected in a database, meaning the dashboard views present both the current month’s data as well as historical data. Dashboards in PowerBI are designed and queried using the data out of the database. In a second step, a KNIME Server Small, installed on an internal IT-managed server, hosts all the workflows where they are also executed automatically.
The dashboard replaces a report in Excel/Power Point and is updated daily instead of monthly – making the current status available to any user on demand.
The effort needed for updating the KPIs was reduced from 160 hours per month to ten hours per month.
Users have access to more information than previously because they have the option to drill down into the visualization if and when needed.
Due to the fixed process for how data is handled in a KNIME workflow and what it must look like, neighboring processes are now also standardized. This means colleagues can no longer handle the data in their own way, which has increased reliability and improved processes.
More time is now available to implement additional KPIs because the processing limit is no longer measured by the monthly working hours of one person, and most dictionaries and basic data structures are already available.
From a software and technology perspective, the biggest advantage of KNIME is the visual workflow builder and self-documenting workflows in KNIME Analytics Platform. This not only documents important knowledge, but also makes any KNIME workflow comprehensible - and usable - to a new user. The gentle learning curve enables even those with minimal data science experience to understand what’s happening with the data at any point in the workflow. KNIME workflows don’t require external development, which removes the need for specialized software setups and expert knowledge, and enables the team to independently make any changes they want/need. KNIME offers extensions and integrations with many other open source and commercial tools. This enables data scientists who are building or adjusting the workflow to continue working with the tools that they know and like – in this case exporting visualizations to PowerBI. KNIME Server is a valuable addition because it supports the automation of the entire process, thereby freeing up a team member who no longer has to manually execute the workflow and send the results to the team.
Gemmacon, a KNIME Partner, was brought on board after delays and difficulties with the previous solution, and because a solution was needed to communicate to all users and stakeholders at an internal, global event. The project deliverables, which consisted of six KNIME workflows and PowerBI dashboards, were completed in one month. More are to be developed by the cybersecurity department, who has been enabled and empowered to do this independently. Next steps in the project include removing the need for human effort at any step of the process by, for example, automatically extracting the data from the proprietary software as well as emails and text files. This will enable those team members to focus on more value-adding tasks and projects.