GEMA – Central process control with Camunda
GEMA orchestrates more than 580 cross-team processes with millions of records centrally using Camunda. cimt developed the process control and supported the migration to Google Cloud.
Veolia Umweltservice GmbH Building a cloud DWH
Veolia Umweltservice GmbH
Company: Veolia Umweltservice GmbH
Industry: Waste management and energy supply
Headquarters: Hamburg
Your contact
Rouven HomannMember of the Executive Board, cimt Group
Technologies and methods
- Google Cloud Platform (Google BigQuery, Google Storage, Google Drive)
- Talend Data Integration (Cloud)
- Oracle DB, MySQL, MS SQL Server
- CSV, XML
- SAP
- Scrum/Kanban
Consulting topics
- DWH development
- DWH architecture
- Talend component development
Measurable benefits
- Significant increase in user numbers
- Long-term reduction of BigQuery and implementation costs
- Comprehensive monitoring for supervision and process optimisation
Customer
Veolia Umweltservice GmbH has 160 years of experience in water, waste management and energy. Veolia puts the capacity for innovation rooted in this experience at the service of human progress and well-being and of the performance of companies and regions.
Project requirements
A central element of the BI strategy at Veolia Umweltservice GmbH is building a DWH that provides data from the operational systems for BI analyses. It should be possible to visualise DWH processing and analyses. A new DWH should be scalable and easy to build and extend with little administrative effort in operation. To achieve this, existing, proven technologies and a modern, future-proof platform were to be chosen wherever possible. The choice fell on the existing ETL tool Talend Data Integration and Google BigQuery (Google Cloud Platform, GCP). As a cloud database, Google BigQuery provides a very powerful basis for storing the data, complemented by existing cloud applications. With Data Studio, the visualisation tool integrated in GCP, reports and dashboards can be created in the cloud based on this data. ETL jobs developed with Talend Data Integration and run automatically connect the data from a wide variety of source systems.
Challenges in optimising the cloud DWH
Existing ETL processes ran into challenges with data sources that combine large data volumes and a high frequency of change. When providing the data in Google BigQuery as business objects in the form of denormalised tables with up to several hundred columns and up to 100 million records, the ETL jobs sometimes reached their performance limits. The many transformations and numerous lookups on reference tables within the ETL jobs also made maintenance, extensions and changes more difficult. As a result, the timeliness of the data provided could not always be guaranteed, and implementing extensions or adjustments took relatively long.
Solution concept
The solution concept involved switching from an ETL to an ELT approach and extending the layered architecture of the DWH accordingly. A prototype was used to test feasibility. After that, the connection of the most important data source – the order processing system of Veolia Umweltservice GmbH – was converted within just three months. Since then, various other source systems have been successfully connected to the DWH following the new pattern. Data sources include databases such as Oracle, MySQL and MS SQL Server, CSV and XML files provided via SFTP, Google Storage, Google Drive or AWS S3, and SAP.
“At Veolia Germany, user acceptance and trust in new technologies are the most important success factors for our digital transformation. With the support of Google Cloud Platform and our implementation partner cimt, we were able to significantly increase user acceptance and strengthen trust in our data and analytics strategy.”
Nici Heuke
Veolia Umweltservice GmbH
Measurable results
Within a year, acceptance of the DWH grew in both the BI department and the business departments, as the rising number of users shows. During this period, the number of DWH users per month increased by an average of 88 percent and the number of queries executed by 55 percent. At the same time, BigQuery costs were cut by 90 percent. The increased trust in the timeliness, integrity and reliability of the DWH is also reflected in its growing use as a central data supplier for operational systems instead of direct data transfers between these systems. For connecting data from database systems, implementation times have recently been shortened even further through the development of generic, metadata-driven staging jobs. Comprehensive monitoring was set up to ensure the smooth operation of the DWH loading processes. The monitoring jobs themselves are monitored by AWS CloudWatch alarms. In addition, monitoring views in the DWH provide information on load times and data timeliness and are monitored with reports and dashboards.
Why Talend
With its extensive connectors, Talend is ideally suited to serve as integration middleware. The visual representation of ETL jobs makes the platform easy to use for many developers and power users. Getting started is intuitive and delivers value quickly.
Why cimt
For the development of generic, metadata-driven staging jobs, cimt developed a custom Talend component specifically for Veolia Umweltservice GmbH that enables schemaless import of CSV files into BigQuery (dynamic schema). A single Talend job can thus connect an unlimited number of source tables to the stage layer of the DWH in a very short implementation time. The DWH loading jobs are monitored with the cimt JobInstance framework (know-how provided as part of cimt’s consulting engagement). Information on job runs is logged in structured form in database tables. Monitoring jobs evaluate this data and trigger the corresponding email notifications.
References
More business cases
Panasonic Europe – Scalable approval processes with Camunda and DMN
Panasonic Europe digitalises hundreds of approval processes with one generic BPMN model and DMN decision tables in Camunda; cimt is responsible for the methodological design and supports the implementation.
GEMA – New data strategy with Databricks
GEMA used SQL, Excel and manual reconciliation for its reporting. cimt supported the build-up of a lakehouse platform with Databricks, through which business units now receive their data automatically – in under two hours instead of two to three days.