short to story
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.
"Um unsere BI-Plattform zu realisieren haben wir, aus einer grossen Auswahl potentieller Kandidaten, die synvert saracus consulting AG ausgewählt.
Der Kontakt mit synvert saracus war vom ersten Moment an einfach und wir konnten uns ab der ersten Minute auf die Arbeit konzentrieren, welche in einer ausgesprochen angenehmen Atmosphäre stattfand.
Unsere BI-Lösung musste, von der Infrastruktur über das Enterprise DWH mit komplexen ETL-Strecken, bis hin zum Dash-Board, vollständig neu erstellt werden. Dank den herausragenden Technik- und Sozialkompetenzen der Berater, konnten sämtliche Projektziele innerhalb des vorgesehenen Budgets erfolgreich realisiert werden.
Vor allem die Implementierung der mehrdimensionalen Analyseräume hat sich als sehr vorteilhaft erwiesen. Sie erlaubt heute unseren Mitarbeiter, welche für die Analyse verantwortlich sind, eine grosse Anzahl an Informationen auf eine besonders intuitive Weise zu handhaben.
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:</p
- 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 saracus
synvert saracus 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 saracus consulting has become a valuable consulting and integration partner for the Federal Office for Agriculture
