
Brief history

The Federal Office for Agriculture of Switzerland (FOAG) is the federal administration’s competence center for the agricultural sector. In addition to its political and administrative function, it is responsible for the collection, analysis and publication of agriculture-related data. With a data warehouse and reporting system, a professional, standardized and automated solution for this task was created through a uniform database and standardization of the evaluation processes.

“To realize our BI platform, we chose synvert from a large selection of potential candidates. The contact with synvert was easy from the very first moment and we were able to concentrate on the work from the very first minute, which took place in an extremely pleasant atmosphere. Our BI solution had to be completely rebuilt, from the infrastructure to the enterprise DWH with complex ETL routes to the dashboard. Thanks to the outstanding technical and social skills of the consultants, all project goals were successfully realized within the planned budget. In particular, the implementation of the multidimensional analysis rooms proved to be very beneficial. It now allows our employees who are responsible for the analysis to handle a large amount of information in a particularly intuitive way. To ensure operation and further development with internal employees, we have also focused on technology and know-how transfer. This delicate point also proved to be a real success thanks to the excellent coaching skills of synvert saracus.” Um den Betrieb und Weiterentwicklung mit internen Mitarbeiter zu gewährleisten, haben wir zudem den Schwerpunkt auf den Technologie- und KnowHow-Transfer gelegt. Auch dieser heikle Punkt hat sich, dank der ausgezeichneten Coaching-Fähigkeiten der synvert saracus, als echter Erfolg erwiesen."
Goals & challenges
The central requirement was to set up a data warehouse in which all agricultural topics in Switzerland could be successively recorded and integrated. Important aspects that were to be taken into account:
- Establishment of uniform business intelligence expertise at the Federal Office for Agriculture
- Simplification and automation of existing preparation and reporting processes
- Replacement of manually created SQL-based evaluations with a standard reporting solution that is easy to use (self-service)
- Expansion of the target group to include specialists and public target groups who, in addition to the annual agricultural report, should also have interactive access to the Federal Office’s statistics via the Internet
Requirements

The following requirements were placed on the data warehouse and reporting system by the Federal Office for Agriculture:
- Definition of a data model and data supply for the integration and consolidation of the individual subsystems, with particular attention paid to performance aspects in order to enable ad-hoc analyses in addition to standard reporting
- Simple integration of existing data and future source systems thanks to an expandable architecture using generic processing methods
- Creation of uniform definitions and calculation rules as well as standard reports
- Ensuring multilingualism across the entire system
Architecture
A classic data warehouse architecture based on best practices is used at the Federal Office for Agriculture. Based on various source systems that are operated and maintained directly by the FOAG, various external source systems must be integrated. The source system data can be supplied via different channels and is historicized and persisted in a staging area in the first step. In order to be able to expand the system efficiently in the future, the extraction and transfer of data from the source systems to the staging area has been automated. The staging area forms the basis for the data warehouse modeled in the Galaxy schema. The dimensionally modeled framework manager models are based on this. For selected data-intensive evaluation areas, additional data marts are generated in which performance-critical calculations are available in pre-aggregated form. Reports and dashboards are provided for analysis and monitoring purposes, while standard reports are also used to publish statistics.
Generated benefits
- Expandable DWH infrastructure based on best practices with established technologies
- Enabling cross-source system evaluations and utilization of ad-hoc analysis options
- Standardization and simplification of the connection of source systems
- Reduction of manual effort when creating evaluations
- Creation of uniform definitions, reporting standards and a uniform appearance of evaluations
Services accomplished by synvert
synvert provided the following services as a competent partner:
- Carrying out the business analysis and creating the specialist concepts and technical concepts
- Architectural design
- Structure of ETL processes with generic components and complex historization
- Data modeling of relational data layers (core DWH, data marts)
- Structure of the dimensionally modeled IBM Cognos Framework Manager models
- Creation of standard reports
- Configuration of the IBM Cognos BI system, in particular adaptation to the corporate design and consideration of the authorization concept
- Support with testing and productive implementation
- Implementation of training courses for administrators and users
Delivery / conclusion
The data warehouse and reporting system has created a standardized and automated technical basis that allows the Federal Office for Agriculture (FOAG) to perform its tasks of collecting, processing and presenting agriculture-related data professionally and with little effort. In particular, the know-how with regard to different architectures as well as the experience in design and conception have created a new, stable platform as the basis for the future BI landscape in a short time. In this respect, synvert has become a valuable consulting and integration partner for the Federal Office for Agriculture