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.
Hagebau Connect Data lake and data warehouse in the AWS cloud
Hagebau Connect
Customer
Hagebau Connect
Industry: e-commerce
in construction and DIY
Headquarters: Hamburg
Your contact
Rouven HomannMember of the Board, cimt Group
Technologies used
- Data Lake
- AWS Lambda
- Python 3
- AWS S3
- ETL
- Spring Batch/Boot, Java
- Docker
- AWS Elastic Container Service
- Data Warehouse
- AWS Aurora (PostgreSQL-compatible database cluster)
- Cross-cutting
- AWS CloudWatch
- Terraform
Quantifiable benefits
- Cost savings by doing without maintenance-intensive server infrastructure and additional staff.
- By eliminating licence costs and introducing on-demand pricing, operating costs were reduced drastically.
- Faster implementation through targeted generalisation in the software development process.
- Flexible, interoperable and long-lasting data applications were created.
- Version control and maintainability of the system landscape were simplified by infrastructure as code (IaC).
Initial situation
The customer project was carried out at a subsidiary of a company in the DIY sector that operates a chain of building materials, DIY and specialist stores. The hagebau connect GmbH & Co KG is responsible for e-commerce, online marketing and customer experience and therefore has to connect a wide range of operational systems (ERP, web shop, logistics systems, CRM and others) as well as business partner and third-party systems promptly. A data lake was to be established for long-term storage, keeping the data permanently in its source format and in compliance with data protection guidelines.
Challenges
Before our project started, a licensed graphical ETL tool had been selected. Newly developed or adapted ETL jobs were put into production on a job server (a large virtual machine) by copying and configuring them manually. As the number of ETL jobs grew, the job server proved to be slow and unreliable, so that scheduled execution regularly came to a standstill. As a result, the data had to be reloaded manually and with great care. Manual testing and deployment of ETL jobs also proved to be time-consuming and error-prone.
So that intraday data changes can be analysed by business users in reporting, most of the loading of the data lake and the data warehouse (DWH) was to take place in an hourly cycle. Loading data and calculating materialised views in the DWH therefore required considerably more computing power at regular intervals than the job server could provide.
The solution concept
The DWH is a central component of modern, data-driven companies. Data is collected in a data lake, loaded into the DWH and prepared there for reporting and analysis. Growing data volumes, increasing information needs and the necessity of IT and data security are constantly raising the requirements for operating DWH applications.
Using cloud infrastructure and services made it possible to operate the data lake and DWH flexibly, scalably and with greater cost transparency. With the cloud, new projects can be launched without lead time and without upfront costs for procuring and providing IT infrastructure internally. Unlike on-premises solutions, the costs of data processing and storage are always proportional to usage. Redundant, scalable services that are updated and maintained by the cloud provider itself deliver the performance, availability and security required for modern DWH applications.
Why AWS?
Amazon Web Services (AWS) is the market leader among cloud providers and offers numerous cloud services for storing and processing data volumes of any size. For integration, AWS provides suitable interfaces, mature documentation and libraries for all common programming languages. This makes it easy to integrate cloud services into any application.
A flexible data lake
The AWS S3 cloud service was chosen for storing the data in its source format. AWS S3 is an object store with industry-leading scalability, data availability, security and performance. Objects (e.g. files) are stored in so-called buckets under a unique name. Name prefixes make it possible to establish a virtual folder structure. In this case, the data lake was structured by source system and data type (e.g. erp/invoice). In addition, time-based partitioning by storage date was created (e.g. 2023/04/30). This made it possible to locate specific business objects from a selected period for further processing.
There are various ways of connecting source systems to the data lake. The diagram shows four specific integrations that were implemented as part of the customer project:
- Event-driven, HTTP-based integration via an API gateway that the source system calls for certain events or data changes
- Scheduled integration that accesses a source system via existing interfaces at regular intervals
- Bulk data storage by the source system using the S3 API
- Manual or automated data delivery via SFTP
In principle, there is a suitable option for every existing system.
AWS data warehouse
The DWH was operated as a relational database cluster. For this, AWS offers the Amazon Aurora cloud service, which handles the operation and management of the distributed database management system. Thanks to database snapshots and regular automatic backups, it is not only protected against outages or natural disasters, but can also be adapted individually to stricter data protection measures if needed. As it is a PostgreSQL-compatible database, the database authorisation concept is not AWS-specific, and a conventional database client can be used for access.
Necessary data cleansing in line with the GDPR and the targeted storage of source data in the lake were carried out by functions running on AWS Lambda, an event-driven execution environment for code. This cloud service makes it possible to run functions scalably in predefined runtime environments for Node.js, Python 3 and others without having to manage the underlying infrastructure. Function calls are always triggered by an event, such as an HTTP request or a regular, scheduled trigger.
Out with the old, in with the new – replacing the job server with Amazon ECS
ETL jobs read the objects from the data lake, extract the data and then load it into the DWH. With Spring Batch/Boot, high-performance, streaming-based batch jobs that process a wide variety of data formats (e.g. JSON, XML, CSV, Excel) could be implemented with little code.
For operation in the cloud, AWS offers the Elastic Container Service (ECS), a scalable runtime environment for containerised applications. In AWS ECS, the ETL jobs can be defined as regular scheduled tasks, so that a specific job instance is ultimately the execution of a correspondingly parameterised Docker container. Through this well-orchestrated interplay of various AWS services, the project succeeded in replacing an old, slow server with high licence costs with modern cloud solutions – saving not only time but also money.
Why cimt?
For many years, cimt has been supporting customers in designing, implementing and operating DWH applications. Our expertise in custom development using open-source software and cloud technologies enables us to implement efficient and sustainable IT solutions for our customers.
To lower the barrier to entry, we offer the reference architecture described here, combined with an infrastructure as code (IaC) definition, as a starting point for new business intelligence projects. On the one hand, this approach speeds up the technical initiation of your projects; on the other, it minimises potential sources of error when building your cloud infrastructure.
Our solution allows you to implement and operate your DWH cost-effectively, efficiently and with little maintenance. This way, you can achieve your BI goals by making better use of existing resources on the most reliable cloud platform on the market.
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.