Skip to main content

Managing and analyzing heterogeneous databases and large amounts of data

Excel is currently the most widely used tool in corporate “any” reporting. Although simple to use, the manual workflows that are typical of Excel carry several proven risks.

It is far from guaranteed that the data are valid, accurate and of high quality. There are several reasons for this:

  • We do not see a discrepancy / error, so the final result will be incorrect
  • We copy a value or table to the wrong place (even if it is just a cell out of place)
  • VLOOKUP function is used where multiple values should be returned (VLOOKUP returns only one value)
  • In the logic, a filter condition has changed, but we forget to change the filter
  • We make a mistake between versions when referencing
  • Synchronization errors occur due to a lack of environment for parallelism
  • Crashes and slowdowns are experienced with significant amounts of data

These are mostly due to the high manual labor requirements of Excel. This also reduces efficiency, as report generation and data processing are time-consuming and cumbersome. Those who usually work with more mature (proven) tools and solutions experience a disproportionate loss of efficiency.

At some companies, Excel is already integrated with internal systems (various ETL processes automatically retrieve data from / put data into Excel). However, this ideal situation is usually disrupted when a new system is added to the company (acquisition, S4 migration, procurement, etc.) and users start working manually in Excel again, which brings the risks listed above.

These are all symptoms of having “outgrown” Excel, which is a spreadsheet tool and nothing else (not a database manager, not an ETL tool, not an OLAP database, not a reporting tool, etc.). In such cases, it is advisable to use some kind of data preparation/transformation application. If you want to try such software, one of our suggestions is KNIME.

But what is KNIME?

In a nutshell, KNIME is a highly scalable, open source data analysis and management tool (both in capability and design) that makes reading, transforming, processing, integrating, loading, and visualizing data easy and fast. But we can’t be nearly so parsimonious when it comes to the capabilities of this tool.

What are the main advantages of KNIME compared to Excel?

Thanks to the graphical user interface, it is really easy and intuitive to use, and the first steps can be done without any serious IT knowledge. The design of the interface makes it easy to follow the path of your data, monitor it at any point in the data flow or even intervene. This means that if an error occurs later, we can find the source of the error in no time.

Thanks to the modular design, we have practically all the transformation or analysis tools we need at our fingertips, we just need to parameterize them.

Due to community support (parallel use, development), we can find solutions to almost any specific challenge, which we can drag and drop into our own data streams.

Because of its flexibility, the tool can handle almost any source system or file format. Not only can it retrieve data from them, but it can also write back or export the transformed result at the end of the data stream if necessary.

The system is designed to handle and process large amounts of data (the number of rows that can be handled is virtually infinite).

By running the solutions in a scheduled manner, it is also possible to automate previous tasks in a continuous manner, thus replacing the manual work that was previously required.

Below are some operations (not exclusively in the form of Excel functions) that are almost fully compatible between KNIME and Excel:

There are hundreds of other functions that could be listed, but this short list is enough to show: KNIME can replace Excel. In addition, three other advantages are highlighted:

  • KNIME has an easy-to-learn and use graphical user interface
  • The tool is free to download and use (as a full-fledged version)
  • KNIME has an extensive and well-developed forum network (the Write To Excel Template node used in the example is also a so-called “Community Extension”, a node developed by the community)

One of the strongest arguments in favor of Excel today is that many organizations have been using it almost exclusively for decades to perform their cyclical activities. Therefore, deep competence (and the associated sense of security) has been built in Excel. KNIME can be a solution that helps to permanently transform, and where appropriate remove, these entrenched systems.

A couple of key use cases of KNIME

Systematic management report automation

Data collection, transformation, and data preparation for visualization are all tasks that can be done easily, quickly, automatically, and without human error in KNIME. But what if the boss wants to see the results in Excel, PDF, or some BI tool, in an email every Friday? KNIME offers a solution for all of this.

Provision of data to the supervisory authority/parent company

Reports often have to be prepared for which the receiving party (parent company, audit authority, etc.) has strict formal requirements. Formatting to the required format is often difficult (data looks different in the own system, has to be collected from many places, etc.). In such cases, KNIME is a great help, as its direct and fast data links, on-demand re-transmission, and formatting possibilities replace the Excel formulae – copy-format work.

Collecting data from multiple sources

Most companies tend to keep their data in several separate systems. However, there is also a need for statements that require data from many different sources. KNIME is able to import data directly from a multitude of systems (SAP BW, SQL Server, Google Drive, Sharepoint, Azure Blob, etc.) and make it uniformly manageable for users in its own spreadsheet format.

Automated data collection from the web

If you want to collect data from the internet on a regular basis (e.g. to track competitors’ market prices), you can also use KNIME’s web scraping capabilities. You can schedule data to be collected automatically on a daily basis, and within the system, you can also generate a summary report/discrepancy alert and send it, for example, by email.

Alerts

By combining the data processing functions with the notification options (email, pop-up window), we can create automatic mechanisms to notify the user when something interesting appears in the process. With good parameterization, all the checks that an expert can formulate can be performed. For frequently repeated, timed tasks, it is particularly helpful to involve human supervision only when really necessary.

Data Science

KNIME has a lot of advanced analytics functionality (Advanced Analytics), which makes state-of-the-art Data Science models available to the user. With KNIME’s user-friendly drag & drop method, anyone can quickly feel like a data scientist.

Another reason to use KNIME: It drives and stabilizes enterprise digitization

KNIME can provide an excellent opportunity to increase the level of digitalization within your company or to start this journey. As a kind of “teaser”, it is an easy way to open the door to a world that at first sight seems distant and complex.

Even with practically zero experience, it can be an ideal starting tool because of its ease of use and community support. As it is open source and its licensing model allows it to be used without financial investment (the desktop version is free to download and use).

The scope of this article does not allow it, but our aim is to provide more concrete examples of how KNIME can help in different areas of digitization.

This article is based on the following Hungarian article on Controlling Portal

Authors:

Bertold Balázs, Corporate Reporting Expert at IFUA Horvath

Levente Havas, Partner, Head of the Enterprise Analytics Competence Centre at IFUA Horváth

György Nemes, Consultant at IFUA Horváth

Gergely Pálfi, Senior Consultant at IFUA Horváth

Zoltán Szőke, Consultant at IFUA Horváth

Gábor Zombory, Data Engineer, Datraction

Leave a Reply