Talend enables us to run analysis that include current SAP data, something we would otherwise never be able to afford.Karsten Neumann, Technical Manager
For almost 500 years now, Stolberg, a town with 60,000 inhabitants close to Aachen, has been producing and processing copper and brass. Following this tradition, Schwermetall Halbzeugwerk GmbH & Co. KG (or in short Schwermetall) became the world's largest manufacturer of pre-rolled strips in just 40 years. Schwermetall is owned at equal shares by Arubis Stolberg GmbH & Co. KG and Wieland-Werke AG, Ulm. Every day, up to 1,000 tons of pre-rolled strips in all types of copper alloys leave the plant in Stolberg. Its main customers are active in the construction, automotive, electronics, and telecommunication industries. The product portfolio even extends to the pockets of every EU citizen, because one quarter of all materials used for the manufacturing of Euro coins comes from here.
All manufacturing areas are subject to very strict quality standards that are officially substantiated by the company's DIN EN ISO 9001: 2000 certification. These standards are continuously reviewed and improved. High-quality raw materials for transistor and lead frame strips set the quality standard in the roller plant. Automated planeness and thickness regulations as well as automated surface inspection devices at the interim and final checkpoints guarantee flawless manufacturing results. All these machines, however, also generate data that are processed with an effective IT infrastructure, in which SAP plays a leading role. Schwermetall uses the Talend Data Integration to integrate this data.
Schwermetall's IT infrastructure is mainly based on two systems: SAP, which is used for administrative and processing purposes, and a manufacturing execution system (MES) that manages the manufacturing machines. Manufacturing management constantly generates current data about the condition of machines, which, for example, describe which material has moved when and under which conditions between different points. The operators of machines receive information from the MES about which steps have to be taken next in which order, and which target values have to be complied with.
The information is produced in many different file formats: some users update Excel files, other files are generated by SAP. A consolidated view of the manufacturing installations was not really possible in the past, because a part of the data sources could not be accessed via process interfaces, and others, especially SAP, offer an easy data transfer interface, but these were not affordable for Schwermetall. "Our choice was either to implement an architecturally appealing solution we could not afford, or to improvise," recalls Karsten Neumann, technical manager of the foundry and responsible for information technology at Schwermetall.
"In order to visualize manufacturing management data, the company developed its own import program that imported excel files, the most common source of data in the manufacturing environment, into the central Oracle database. The program was only a makeshift solution, however, since it was difficult to work and did not allow for process automation."
"Another problem related to data from the manufacturing environment was written directly into the database. The central data collection was governed, but its analysis was extremely time-consuming due to the sheer volume of the datasets. The largest database includes events from manufacturing control and has more than twelve million datasets, regarding energy consumption or statistical data. Ultimately, queries or aggregations ended up putting enormous strain on the online database. In short, our issue was that we needed to consolidate data from the MES and SAP in a database and to ensure at the same time that we did not have to strain our database as much when running queries," summarized Karsten Neumann.
The Talend Solution
In order to find a more elegant solution for the extraction of data from Excel files, it was decided to use the open source version of the Talend software, Talend Open Studio for Data Integration. The software quickly turned out to be much more efficient than originally thought. "At the beginning of 2009, we experimented with the free version and found that this tool is able to solve quite a few of our other problems in addition to the pure ETL," said Karsten Neumann. After extensive testing, Schwermetall decided on the commercial version of the open source solution, the Talend Data Integration. Three developers are currently working with this tool.
The current solution for the data import to the central database currently looks like this: Talend accesses the different sources systems in predefined intervals and checks, based on the timestamp of the files, whether any changes were made. If this is the case, the transfer job for this current version is started. From the user perspective, this process completely runs in the background; users simply save their data as before, and the update takes place automatically. The Talend solution offers particularly interesting advantages for SAP information. Schwermetall does not operate its own SAP system, but is linked to the SAP of its parent company, Wieland-Werke in Ulm. For the analysis and visualization of information, however, the data from the ERP software is very important, but it is difficult to access without an expensive SAP interface.
"SAP offers many analyses that link information from different areas or that access information that otherwise cannot be linked. A mid-sized company like us cannot afford SAP via this large interface," explained Karsten Neumann. Schwermetall has taken a detour here, which Karsten Neumann refers to as "Interface one size smaller." Lists in file format are generated on a regular basis, depending on the department, either daily or monthly. With Talend's ETL process it is possible to extract the required data from the list, to convert it accordingly, and to ultimately write it to the target system, the central Oracle database. Consequently, all information is available for additional analyses or visualization purposes. For its analyses, the company uses Intranet software called Confluence. The users work with a web-based user interface, where they can access reports, graphics and analyses, or drill deeper into more complex issues. Many connections are visualized, and the respective images and charts are automatically updated once a source file is edited.
The #1 problem was thus solved. With regard to the second challenge, the enormous database load created by queries, Talend Data Integration was helpful. Instead of running queries online directly on the database, the data is processed bit by bit. Initially, the source data has to be processed once, such as the information on the equipment's energy consumptions. In a next step, the data is condensed step by step, or calculated on a daily, weekly, or monthly basis. Currently, Schwermetall is running approximately 50 such processes.
From a logical perspective, this process sounds simple, but what makes it so ingenious is Talend's automation: when new information regarding energy consumption is gathered, the software moves the datasets in the database, updates, if necessary, the condensed fields ("such as monthly consumption to date") and therefore automatically transfers the index numbers to the Intranet. The figures obtained by this condensation process, and more complex calculations or visualizations, are now possible. "Instead of putting too much load on the database, we process small parts in short intervals and then save the results. In the end, this is also ETL, but the data source is not a file in this case, but a database," explained Karsten Neumann.
The Talend technology enables Schwermetall today to run analyses and visualizations based on the central Oracle database, which had not been possible before in this form. Data from the production control system and SAP is regularly and automatically accessed and included in an overview, a sort of management dashboard, with which managers can constantly monitor the installations. Aside from the technical implementation, Talend also made sure that the professional know-how can remain in the respective departments. Data maintenance continues to be the responsibility of the employees; the IT department's assistance is not required for the ongoing process. What is particularly noteworthy is that nothing has really changed in the employees' routine. They do not notice that a system is running in the background but they simply create their files as before; the rest is completely automated.
Thanks to Talend, Schwermetall did not only solve its data logistics problems, but now has a central monitoring system for all jobs. The system is growing continuously and new processes are added constantly that used to run via manually programmed interfaces. In total, there are still approximately 50 other interfaces that move data from system A to system B. The developers are working on gradually migrating all jobs into the Talend infrastructure. Karsten Neumann is very happy with its decision for Talend. "I am sure our solution approach will not win any beauty pageants, but we have definitely made the best with what we have. Talend enables us to run analyzes that include current SAP data, something we would otherwise never be able to afford. I also doubt that SAP projects would be able to handle the many areas we cover at this speed. In the end, sometimes a detour turns out to be a shortcut."