Analysis platform CLAIR achieves 2nd place at the "Best Data Project Award"

The project team of the analysis platform "CLAIR" of our long-standing customer Zentrale Stelle Verpackungsregister (ZSVR) has been awarded 2nd place in the category "SME" for the "Best Data Project Award" at the VOICE ENTSCHEIDERFORUM 2022 in Berlin.

With more than 400 members, VOICE - Bundesverband der IT-Anwender e. V. is considered the largest community of digital decision-makers in the German-speaking world, serves as a network for CIOs of leading companies from a wide range of industries and promotes professional exchange and the strengthening of IT user interests.

The Stiftung Zentrale Stelle Verpackungsregister ensures fairness of payment in packaging recycling

Since the introduction of the "Yellow Sack" and the "Green Dot" in 1993, the principle is well known: Companies that fill and distribute packaging for private final consumption are also responsible for its recycling and pay for it (the so-called system participation). Unfortunately, there have increasingly been free riders who have ignored the legal requirements. From 2019, the legislator has tightened the reporting obligation for the companies concerned in the Packaging Act (VerpackG) and set up a public reporting register. Manufacturers, i.e. the "initial distributors" of filled packaging, must register in the LUCID packaging register of the Central Packaging Register Body (ZSVR) foundation and regularly state which packaging has been put into circulation by them and to what extent. For the sophisticated data collection, data management and data analysis, the ZSVR relies on a multi-level BI architecture with the CLAIR analysis platform. noventum BI consultants played a key role in the development of this platform.

The Central Packaging Register Office is the central monitoring body in the packaging disposal market. It is a foundation entrusted with sovereign tasks and thus a federal authority. The foundation faces the great challenge of evaluating the reports received from manufacturers and taking action in the event of underreporting or non-reporting. Based on the data available to it, the ZSVR analyses whether the obligated companies have registered correctly and verifies the information on the packaging quantities reported in the register. However, the ZSVR is not responsible for enforcement. In the case of violations of the Packaging Act by companies, the ZSVR prepares and hands over digital evidence files to the competent state enforcement authorities.

CLAIR analysis platform

The detection of violations in the sense of non-participation or underparticipation and the preparation and forwarding of digital evidence files require complex data evaluations on a considerable amount of data. The information collected must meet the highest qualitative standards in order to be legally valid.

In order to meet these quantitative and qualitative demands and to be able to perform the tasks effectively and efficiently with a high degree of automation, the ZSVR has a powerful solution at its disposal in the form of the CLAIR analysis platform, which combines the latest technologies and methods from the areas of business intelligence and big data.

The overall architecture of the analysis environment is designed for flexibility and future diversity of use. It consists of two core components, a data warehouse and a data lake. In the overall concept, both have their own role and justification and yet are ultimately closely interlinked.

The basic prerequisite for the success of the ZSVR project was the establishment of data governance tailored to the specific situation. The high degree of freedom of the architecture as well as the diversity of the objects of analysis, which is not yet foreseeable today, required special care here. In the end, the data governance must guarantee the interaction of all architecture components - classic data warehouse and data lake - as well as the coordination of all roles involved in the company.

BI architecture with data warehouse and data lake

The foundation commissioned the IT management consultancy noventum consulting to set up the data warehouse architecture. noventum has stood for sophisticated business intelligence and analytics projects based on the Microsoft Data Platform for many years and was thus awarded the contract for this demanding project.

The multi-layer data warehouse solution based on the components of the Microsoft SQL server offers an integrated and consolidated view of the structured data from different sources. Primarily, this is the information and operational data of the packaging register, which is collected via the LUCID platform. The highly automated and standardised processing of the data via the SQL Server Integration Services (SSIS) and the transfer of the data into powerful and versatile multidimensional analysis models based on the Microsoft SQL Server Analysis Services Tabular (SSAS) and DAX (Data Analysis Expressions) based key figure definitions form the basis for extensive standard and ad-hoc analyses in the form of paginated reports and Microsoft Power BI reports.

The realisation of the data warehouse project took place in close cooperation between noventum and the foundation's project team. Due to a lack of experience with the newly enacted law VerpackG and the analysis requirements still to be derived from it, the target scenario could not be fully and conclusively defined at the beginning of the project, which favoured the use of agile project management methods and led to the progress of the project over two-week sprints. The use of proven noventum templates and standards ensured rapid project progress and a high level of quality right from the start and promoted the constant transfer of knowledge to enable ZSVR employees to operate, maintain and further develop the solution. Particular attention was paid to topics such as quality assurance, revision security, data lineage and historisation in order to obtain secure, fully traceable information, sometimes from different temporal evaluation perspectives (as-is vs. as-was vs. as-of).

Interaction of data lake and data warehouse

Further evaluation possibilities result from the second core component of the analysis platform, the data lake. This was developed in parallel to the data warehouse in a second project. The data lake supplements the data warehouse with additional data sources, tools and methods from the big data environment. In particular, the processing of unstructured data and the application of AI (artificial intelligence) and ML (machine learning) models opens up completely new possibilities for the Foundation and its data scientists. The combination of data warehouse and data lake within the CLAIR analysis platform combines the advantages of both worlds and also leads to synergies. On the one hand, the data lake builds on the structured data prepared in the data warehouse (including all transformations and logics already defined there) and takes these as a basis for deriving new insights in combination with the unstructured data and processes available there. On the other hand, the operationalisable information from the data lake flows back into the data warehouse via an interface, and ultimately forms an extended, holistic and integrated data space that reveals numerous analysis options.

An example of the successful interaction of the components are the PDF documents uploaded to LUCID, which are checked for completeness and correctness with the help of text mining algorithms within the data lake. The resulting findings are then available to the data warehouse and can be comprehensively reported together with the structured data.

The CLAIR analysis platform, along with the LUCID packaging register, is a central step towards a digital and lean authority. Data that is to be legally checked is processed automatically in the best possible way with consistently high quality in order to keep the manual workload for recurring processing and checking steps as low as possible and to create free capacities for targeted investigations of complex individual cases. The duties assigned to the ZSVR under the Packaging Act are comprehensively supported by automated data processing and analysis processes. Mailings based on standardised analyses contribute to the continuous improvement of the master data quality of the producer register, numerous analyses for plausibility checks of packaging quantities reported by companies contribute to the detection of under-involvement and the processed analyses are automatically made available to the state enforcement authorities via interfaces. Further standard analyses are used to determine and publish the market shares of the collectors active in the dual system.

More Information

Thank you for your interest in the noventum BI-Consulting!

If you would like to find out even more about noventum BI-Architecture or Data Governance you can find information and  Contact here.