Success Story Dillinger

BI,Sucess Story

MADE OF STEEL DATA VAULT 2.0 AT DILLINGER DR. NICOLAS FRITZ AND DR. ALEXANDER BRUNNER

ABOUT DILLINGER The Dillinger group is composed of a series of companies which provide services in the field of heavy plate production. On their main site, the group features an integrated iron-and-steel plant as well as facilities for the production of coke and smelting of iron. The latter is shared with their sister company Saarstahl AG (referred to as SAG). It is worth noting, both are united under one holding company, the Stahl Holding Saar (referred to as SHS)

Subsidiaries of Dillinger Group provide further downstream services in trade, flame-cutting and semi-fabrication of heavy plates and other steel products as well as sales services to round out the company’s efforts in the industry. Dillinger steel is used to produce monopiles for the offshore wind energy market as well as pipes for oil and natural gas pipelines. Their projects include impressive bridges like the Viaduc de Millau in France, the pylons for off-shore wind parks in the North Sea, stadiums like the one in Warsaw Poland, various ships,, tunnel drilling machines as well as a number of skyscrapers to name a few examples in which Dillinger’s heavy plates come into use. HOW STEEL IS MADE The process of generating high-quality heavy plates is an extensive process. Figure 1 shows an abstract view of the production site in Dillingen and the material flow through the processing plants: First, coal is transformed to coke which is supplied predominantly from the Zentralkokerei Saar (ZKS) coking plant, also owned jointly by Dillinger and Saarstahl. After the sinter plants pig iron, known to steelmakers as “hot metal”, is produced in the blast furnaces from iron ore, coke, coal and additives. This is the task of ROGESA, another joint subsidiary of Dillinger and Saarstahl. Once the iron has been melted, it is transported with torpedo-wagons to the steel plant where the first goal is to get rid of the unwanted

The group praises its excellent customer service as one of the USPs of their steel. To support customers to better understand the quality of the products, Dillinger provides multiple front-ends for customer self-service. For example, quality measurements are performed in production on a regular basis and reported to the customer and served via one of those front-ends. To add more background, Dillinger has two production facilities, one in Dillingen, Germany and one in Dunkirk, France. In 2020, 1800 kilo tons of crude steel were produced and the production of heavy plates amounted to 1400 kilo tons, 70% of which was milled at Dillingen. ingredients: sulfur and carbon. After desulphurisation carbon is strongly reduced via the blowing process in the converter. Thus transforming iron to steel. In secondary metallurgy, vital chemical processes are triggered by adding alloy agents under very low pressure in vacuum degassing. The steel is then filled into the continuous casting plant and after exiting the plant in a solid state is cut into slabs. The rolling mill transforms these slabs and ingots to heavy plates. The two rolling lines each feature a four-high reversing stands allowing the production of heavy plates with exceptional dimensions. Lengths of up to 40 m, widths of up to 5 m and thicknesses of up to 600 mm are no rarity here and are quite common.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

2/13

Prior to shipping, every plate is submitted to inspection in accordance with the customer’s specifications. Non-destructive testing, such as surface inspection, geometrical and dimensional checks and ultrasonic inspection, is performed on the plate itself during the flow of production..

Unlike many manufacturing processes there is no “bill of materials” that describes how the final product is assembled from smaller components. Actually, it is the other way around. Steel plates are produced from a huge bucket of liquid iron, called “heat”, which is formed, cut, rolled and cut again until the plates are ready.

Figure 1: Overview of the steel production process at Dillinger

INITIAL SITUATION During the described process, Dillinger collects a vast amount of data from sensors and operational data sources. For example, data about orders and their parameters, basic data on products and intermediate products, chemical analysis results and measurement data is collected on a regular basis. This data is aggregated and stored in Dillinger’s technical data warehouse. However, that “warehouse” was never designed as a data warehouse as such but it is “historically grown” meaning researchers and technologists from different departments needed various evaluations and over twenty man-years, solutions were built, successfully fulfilling their requirements. The result is a conglomerate of over a hundred ETL applications based on a variety of technologies. The majority of the applications are written in Java but there are also ETL jobs in third-party ETL tools as well as database views based on replications of production data. The reporting methods included Java programmes and Visual Basic applications mainly.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

3/13

CHALLENGES

One issue is that there are cases in which the reported information is inconsistent across the frontends: while each system by itself provides correct information, the measurements are not comparable, for example because they have been collected at different times or under different circumstances. On the one hand, none of the warehouse developers were trained programmers nor was there an underlying scheme for the warehouse. On the other hand, what was lacking in programming skills was made up for in technical knowledge of steel production. This concept worked utilizing an average of two people for quite some time until the individuals retired. The next generation of warehouse developers faced largely undocumented code, some of which was difficult to read. In some places, it was unclear which version of the code was productive, making debugging, maintenance and enhancement very difficult. In addition, technical knowledge about the production process that people had previously acquired

over years was missing. Adjustments to the ETL applications were like playing Mikado, if you made changes in the wrong place it could break other ETL routes as well. In addition to the aforementioned challenges with the existing solution, it is foreseeable that it is not possible to implement new functionality that may be required in the future. For example, Dillinger is currently in the process of establishing data quality and data governance initiatives. Also, it should be possible to centrally define and manage key performance indicators (KPIs) and business logic. In some cases, there is also no meta-data available for the source datasets and the processing of business logic in the data warehouse. In short, the current data warehouse solution is unable to meet the requirements from these initiatives. Also, advanced topics like Data Science Support or Predictive Maintenance, are not well supported. It was time to rethink the old concepts and build a modern data warehouse with a promising future.

SOLUTION APPROACHES

For Dillinger, it was important that any new system should also be maintainable utilizing only a few people. Dillinger’s main business is steel production and their IT department is limited in size. Another requirement was that the new solution should be built in an incremental fashion. As the old warehouse has over thirty years of development, a big bang solution would be very risky and require a lot of manpower. They also wanted to have a scalable data warehouse as it should be possible to start with a small setup in terms of hardware and number of source systems and increase as needed. This should also include the selection of database technologies: ● They wanted to be able to integrate different technologies and the new solution should be flexible enough to allow it. ● In order to have a future-proof concept in place, Big Data technologies should also be supported since these will be required at some point. ● Additionally, Self-Serviced BI was a further requirement. ● Departments should be able to quickly create their own evaluations based on warehouse data.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

4/13

The promise of data warehouse automation was also encouraging: ETL jobs should be generated from metadata. The generated model, likewise generated from metadata, should allow historization of data to capture changes to productive data and allow traceability and full auditability. The new data warehouse should provide reliable and reusable KPI values that increase the consistency of the final reports. To achieve this, each metric should be calculated in this location and made available to reporting and end users.

SELECTION PROCESS

To evaluate their possibilities in terms of data warehouse technology and tooling, Dillinger started three proof-of-concepts together with Scalefree for the new TDW2.0 initiative in 2017. The goal of these PoCs was to evaluate two data warehouse automation tools and one manual implementation to compare the different approaches. Ultimately, Wherescape was selected. To prove the feasibility of DV2.0, Scalefree analyzed the main source system and focused on the main business objects: orders, positions, bars, slabs and sheets. For these, the first DV model was built within the scope of the PoC then in which a first data mart was loaded. Dillinger immediately understood the value of DV and chose it as their EDW Framework for the so-called technical data warehouse 2.0. The PoC loading was done with an open source ETL tool but once WhereScape was installed, Dillinger asked WhereScape’s on-site support team to accurately migrate the ETL process to WhereScape. This ensured that the system was properly configured for the first milestone. Once the feasibility of the Data Vault 2.0 approach was established, together with Wherescape ETL automation, the Dillinger warehouse team had to raise the project budget. It took a while to gather all necessary signatures and to release the budget though. However, Wherescape RED and 3D were licensed in the beginning of 2019 and the TDW2.0 project could start once cleared.

The idea was to lay the foundation for the warehouse in collaboration with Scalefree experts and train the internal team in parallel resulting in a continuing support and commitment from Scalefree.

Dillinger calculated internal and external costs in such a way that the project was able to run for six sprints together with Scalefree over the course of half a year.

PROJECT PROGRESS

The data warehouse development and implementation was steered by Scalefree and was planned to run with three internals and four externals on the long run. Six sprints, each of a one month duration, were planned. So, as indicated by the term “sprint”, the agile delivery, scrum-based approach was used for running the project. This is perfectly in line with the Data Vault 2.0 methodology and focuses on delivering a dashboard or at least part of a dashboard at the end of each sprint.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

5/13

Thereby, the development team quickly gets in touch with the product owners which allows for an iteration process where requirements and possibilities converge to the final dashboard. Due to short interaction times between the development team and the product owners a “fail hard fast” approach is enforced which allows Dillinger to solve issues quickly. Scalefree coins this also as the “tracer- bullet approach” since the first results are presented to the product owners within sprint one. In order to meet the tough timeline to produce a dashboard within sprint one, there is not much room for failure. Additionally, for the internal team, Data Vault 2.0 as well as WhereScape was completely new. Of course, this is a risk as Scalefree is committed to meeting the deadlines of the sprints. To mitigate this, at least partly, Scalefree commends so-called “project setup” sprints which are denoted with numbers -2, -1, 0. These sprints are standardized and contain a huge list of checkpoints as well as a technical smoke test which should be addressed prior to the first “delivery” sprint. By going through these project setup sprints Scalefree and Dillinger put themselves in an ideal position to hold the tight schedule. FIRST SUBPROJECT: ROLLING MILL - SLAB THROUGHPUT The first project was to provide key performance indicators for the first subsection of the rolling mill and its slab throughput. One of the key learnings for Dillinger was the value of the “right” business keys to be selected for the Data Vault model. In this project, the main business objects are orders, positions, bars, slabs and sheets. All of them are equipped with reference numbers. The design of the source database is mostly hierarchical. This dates back to the time when most programs were written in Fortran. Since then, all development is done in Java but the database layout still reflects the old Fortran design. The database for the mill’s main frame is constantly under heavy load. With its very large tables at high traffic volumes, even minor blockages can have unpleasant impacts on the production environment. But there were more challenges: unfortunately, but typical for legacy databases, the documentation of the key figures was non-existent in a user accessible format. Old visualizations of key figures written in Visual Basic, were the only input available for the requirements analysis but these also needed to be reverse-engineered in a time-consuming manner to extract the relevant information. In this project, Dillinger’s DWH team is responsible for the full delivery of not only the information marts (and upstream layers) but also the dashboards. With this in mind, simply providing a database view would not be sufficient to get the job done. There was no designated team to take care of the visualization of metrics at the end-users side, adding to the challenge. The availability of team resources was an issue, initially. In addition to the limited end-user availability, the data warehouse team was also very limited at the very beginning, with only one internal person working on the project. On top of that, this person had additional responsibilities in maintaining the legacy data warehouse and project management tasks.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

6/13

DERIVING BUSINESS KEYS All tables and objects feature unique, database generated IDs as primary keys and reference numbers that are known within the department. For the latter, the number ranges are so limited that they are often reused by the business over time. This presents a challenge as a business key in Data Vault should uniquely identify only one business object over time. Therefore, deriving unique keys from the reference numbers or combinations was not possible. Dillinger spent a considerable amount of time analyzing possible business keys based on the reference numbers. The DWH team worked with the departments as well as with colleagues from IT and tried to find suitable business keys for the business objects in the rolling mill. Together with Scalefree’s experience, the result was rather simple: Dillinger had to fall back on the surrogate keys to be used in Data Vault hubs. This sounds a bit unspectacular at first but in practice it was the best decision to be made in this specific case. Most of the objects in the rolling mill exist only in this system or their surrogate keys were also used in adjacent systems making integration very easy. Alexander Brunner, a Scalefree Partner commented: “At the beginning of a project considerable effort should always be taken to identify business keys, because it is worth it. But it is not unusual to fall back on the surrogate key, especially, when the business key candidate is not unique over time which happens sometimes for older source systems, and in addition, it cannot be extended in the same manner over all source systems used, for example by a creation date. In the case of Dillinger, the surrogate key is even used by adjacent source systems, so it is a very good key for the hub’s purpose of integration.”

IMPLEMENTATION CHALLENGES

With Wherescape 3D and RED, Dillinger hoped for a one-button-solution. They were ready to hit that button only to realize that unfortunately it is not that simple. The combination of the two becomes very powerful and enables fully automated, traceable, fast development of ETL routes. The tools are highly customizable which makes them very flexible but some training is needed to effectively use these tools. Also, due to high demand on the market, Dillinger could only get less experts than we would have liked to at the beginning of the project. On top of that, PostgreSQL, as a target, was still very new and some of the features Dillinger needed for Data Vault building were not yet fully available. But this was solved over the course of the first sprints by the excellent project support from WhereScape on- site consulting together with Scalefree.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

7/13

Also, as a strategic decision, we did not plan to fully use the potential of 3D right from the start, i.e. some of the more advanced Data Vault structures beyond the standard hubs, link and satellites were only integrated at a later point into the model conversion rule set. This was done because the initial effort for implementation was estimated to be too high due to the inhomogeneous modeling of the source systems. But thanks to the automation capabilities of the tool, a later redesign and redeployment of these entities repaid the technical debts without any major issues. Another challenge was that the provided initial model conversion rules aimed for databases in 3NF did not match the design of the rolling mill database which was rather hierarchical. So, with their first sub-project, Dillinger had to dive deeper into the 3D model conversion rules to generate a simple Raw Vault. But it should be remembered that they were able to implement these refactorings in a relatively short time once the ruleset was up and running to deal with their individual scenario. To be further noted, changes could be implemented rapidly which shows the strengths of the Wherescape tool suite. The combination of the less manpower and limited tool experience at Dillinger, including an initial lack of Data Vault knowledge, unfortunately led to a steep learning curve. As a positive result, Dillinger acquired a lot of experience during the course of the project. The support from Wherescape was very helpful and swift, though the difficulty was mostly found in communicating to get on the same page. CHANGE DATA CAPTURE A major technical problem was the susceptibility to faults of the productive mill database. In their initial attempts to perform a full load, it quickly became clear that full loads were only feasible in seldom, exceptional cases. They cannot be used for regular loading of the warehouse and consequently, Dillinger had to find a solution to establish regular loading. One of their DBAs with prior data warehouse experience developed a custom trigger-based solution: Change data capture, or CDC for short. Every change on tables that have this trigger is written to a copy of the table that has additional information like the CSCN number, the commit timestamp and the type of operation. The CSCN number is a unique, database-given number ordering the sequence of the changes. This brings some advantages: ● No interference with the original tables ● Low, constant, additional load on the database, no peaks ● Complete historization ● Loading cycle for the warehouse is variable, with CDC giving a 48 hours buffer for data

On the other hand, it also has a few disadvantages:

● Very high volume of data on tables with a lot of traffic ● More historization than necessary in some cases

In RED, Dillinger added the CSCN number in milliseconds to the load date, thus giving each row a unique load date and preserving the sequence of the changes.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

8/13

EXTRACTING BUSINESS LOGIC Fortunately for Dillinger, the author of the VB Code implementing the business logic was still working at the organization. Together with the DHW team they analyzed several thousand lines of code and were able to reproduce the numbers in their Business Vault. However, it was not possible to implement an up-to-date definition of the metrics. The knowledge of the business logic and the responsibilities were unfavorably distributed and it was not possible to quickly get an agreed definition together. Nevertheless, the discussion about the definition of key figures has led to the fact that those responsible now meet regularly in a working group and define agreed key figures. DELIVERING KPIS When it comes to displaying KPIs, Dillinger has relied on custom-written Java and VB applications. These offer a high degree of flexibility but require good programming skills. Similar to their sister company SAG, new visualizations are to be realized with a selected dashboard application: the department is to create prototypes and then make them available to interested parties with the help of internal experts for dashboard development. Since their internal customers in both sub-projects had no training in developing dashboards, it was up to the Dillinger DWH team to provide those. To address this, Scalefree provided a specialist who assisted with the implementation. Together, they were able to deliver a presentable result in a very short time. SECOND SUBPROJECT: STEEL MILL - SECONDARY METALLURGY For Dillinger’s second sub-project - key figures for the secondary metallurgy of the steel mill - the conditions were much better from the onset. The entire effort of finding “right” business keys could be saved because of prior knowledge from the first project. Another major advantage was the redesign of the source database in 3NF within the course of another migration project. Thus, the model conversion rules customized in WhereScape worked much smoother given they were initially designed to digest 3NF models and CDC was established as well. In this sub-project, the business logic was provided to the Dillinger DWH team by the business department in a well-structured requirements document, so there was no need to organize working groups or analyze old code to get KPI definitions. Since the to-be-generated reports are for quality assurance purposes, it was extremely important that the data in the warehouse was prepared in a timely manner. Thus, a loading cycle of two hours was considered an acceptable timeframe by the business department. In the meantime, the Dillinger team was able to double their team size which allowed for better knowledge transfer and more active participation in the implementation. The only real challenge was to handle the large amount of measurement data in combination with the dashboard application.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

9/13

VISUALIZATION OF MEASUREMENT DATA For years, the quality assurance department in the steel mill worked with a Java application that displayed the process data of the secondary metallurgy and also showed the associated measured values. The task was to deliver a new data basis and to implement the presentation. While the process data could be displayed well, the measured values presented some challenges. There were twelve measurement points per vacuum system and a total of over one-hundred- thousand treatments. The roughly 50GBs of measurement data was compressed, so that had to be interpolated for displaying. That said, holding the complete set of measurements in memory was not possible with the dashboard application. In addition, fetching data according to a selection is very limited in scope. Although it was possible to interpolate within the visualization, it was not possible to have the different graphs synchronized to the same starting and end point. After evaluating multiple approaches, the team at Dillinger decided to move the measurement data to a PostgreSQL database with Timescale. Instead of showing the graphs in the dashboard application, they called an Angular application that only sends one request to Timescale per treatment. OUTCOMES Scalefree supported the project at Dillinger with three consultants in the first phase of the project. This phase was characterized by the empowerment of the internal staff and project organization by the Scalefree consultants. In the second phase, Scalefree supported the team in a more strategic and advisory role. Dillinger integrated the two largest source systems for production data, the steel mill and rolling mill database, modeled the central business objects and generated ETL routes from models. They were able to provide the desired KPIs of the two subprojects on a fully historicized data basis with a loading frequency of under two hours on a single node system. Nicolas Fritz points out: “One of the most important goals was to enable the internal team to independently expand the warehouse. And I am proud to say that together with Scalefree we achieved this goal.”

Scalefree’s support ended at the beginning of 2020, and since then, Dillinger has

● Implemented several technical projects for research and development ● Established supply for myEService, a way for Dillinger’s customers to receive timely information about the production status of their orders. ● Connected further source systems, among others also with ActiveMQ ● Implemented numerous optimizations in their business logic ● Replicated a source system to relieve the productive system with user requests ● Installed two monitoring solutions: Nagios for current problems and Grafana with Prometheus for longterm

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

10/13

From Scalefree’s perspective, the company is happy to see that the project plan finally succeeded very well, both in time and budget as well as regarding the project scope. To see that the dashboards based on the DV2.0 BI framework are used productively on a daily basis by several departments is very satisfying.

Alexander Brunner, a Scalefree Partner added: “As a consultancy, our mission is to empower our customers to independently pursue their visions and continue to grow the data warehouse like Dillinger did. I also very much appreciated that when Dillinger had the choice between solving a problem “quick and dirty” or very clean, almost text-book- like, they always chose the later one. Every company is unique and so is every data warehousing project. Therefore, we advise our customers independently, dependably and extensively according to their needs to find the best solution for them.”

Nicolas Fritz sums it up: “I think for Dillinger DV2.0 with Wherescape ETL automation was the right approach. With Scaleree we had an experienced partner in designing Data Vaults and we laid a solid foundation to build on. And the Wherescape product suite turned out to be a great tool for ETL automation especially with a small team size.”

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

11/13

OUTLOOK

For a year, Dillinger and Scalefree worked together to build their new data warehouse. The following year, Dillinger were on their own and continued to drive the expansion. But there is still a lot of work to do: The migration of the old warehouse has just begun: ● Thirty man-years of ETL development has to be analyzed, possibly corrected, rebuilt and documented. ● Hundreds of gigabytes of aggregated data in the old warehouse must be integrated into the new structures. ● Their end-user access layer needs to be redesigned and unified. Be it a star schema or maybe even a unified star schema. ● There are archives of production databases that can now be usefully integrated. ● In the not too distant future, Dillinger will reach the performance limit of their virtualization and they will need to move to a cluster. But the good thing, however, is that with Data Vault as Dillinger’s foundation, the DWH team around Nicolas Fritz feels confident that they can meet these challenges. Scalefree has helped them get on the right track and has brought the internal team to the point where they can stand on their own two feet. With Wherescape RED and 3D Dillinger is able to implement new requirements of the departments as well as to push the migration despite the small team size. ABOUT SCALEFREE Scalefree presents the Data Vault 2.0 System of Business Intelligence; the concept that grows with your data needs without running into limitations of scale. Data Vault 2.0 contains the first data warehouse system to increase scale without limits. Set up now, for all your future Big Data needs. Our clients understand what it means to have a lot of data. They are global automotive brands, large banks and governments. Initially developed for the U.S. government, Dan Linstedt and Michael Olschimke are now bringing this technology to the industry for everyone to use.

Made of Steel - Data Vault 2.0 at Dillinger | Scalefree International GmbH

12/13

Scalefree International GmbH Schützenallee 3, 30519 Hannover, Germany

P: +49 (511) 879 893 41 M:+49 (175) 811 0336 F: +49 (511) 879 893 49

info@scalefree.com www.scalefree.com

CEO: Michael Olschimke & Christof Wenzeritt

Page 1 Page 2 Page 3 Page 4 Page 5 Page 6 Page 7 Page 8 Page 9 Page 10 Page 11 Page 12 Page 13

Powered by