This case study describes the work achieved by element61 together with and at Veiling Haspengouw to set up a complete management reporting environment. The assignment at Veiling Haspengouw started with a Business Intelligence roadmap, in which the different steps needed to get started with business intelligence were defined together with the required budget, timeline, resources and potential results from the initiative.
The customer
Fruit auction Veiling Haspengouw started back in 1938 as a local initiative of some fruit producers to market their fruit together in a cooperative enterprise. Currently fruit auction Veiling Haspengouw has become much more than just a fruit auction; with their technology and knowledge they help fruit producers make the right decisions in variety, fruit protection, fruit storage and much more.
Over the years, the focus at Veiling Haspengouw has moved from pure fruit auctioning towards more and more intermediation between buyers and producers, making them an internationally valued partner on the market. Through the intermediation, some new aspects of commercializing fruit are now part of Veiling Haspengouw's offerings: sorting and packaging, storing fruit under the best conditions, creating added value products with fruit (like fruit salads), and many others. The main activities at Veiling Haspengouw are still the intermediation of fruit: the buying and selling of apples, pears and soft fruits such as strawberries and cherries. The main activity represents a turnover of more than 70 million euros and more than 100.000 tons of fruit on an annual basis.
The challenges
Veiling Haspengouw was facing some challenges for getting the right information to the end-users:
- Reports were based on the operational system; because of the source and the amount of detailed data available, all data was captured overnight into Excel sheets. It was decided to run the reports and data loading at night not to endanger the operational systems. The output reports were based on multiple files within Excel. The different basic Excels had to be joined together via complex pivot tables. Only a very limited group of people had the knowledge to create reports upon the complex Excel files.
- Most of the reporting at Veiling Haspengouw was based on different systems. There was no clear overview on which data was in which system and which system was master for certain (master) data. No comparison could be made over the different applications, resulting in silo-reporting.
- All knowledge of the source system and the reports was managed by a very limited group of people. Therefore all reports had to be created by a few people on an ad-hoc basis. Due to the ad-hoc approach, almost no time was left to create standard reports upon the ERP system.
- Veiling Haspengouw was willing to solve all issues by investing in a Business Intelligence solution to create BI reports. The idea was that those tools would help spread the information.
Veiling Haspengouw initially missed the skillset to define a Performance Management architecture and select and implement the right tools to get on track with business intelligence within the company. element61 was the preferred partner to guide them through the different processes in setting up a complete Business Intelligence infrastructure and process within the cooperative society.
Solution
Based on the setting described above, element61 choose a pragmatic approach that is based on the elementary methodology.
The first step in getting to a corporate Business Intelligence strategy was defining a roadmap together with the customer. Within that roadmap, element61 helped Veiling Haspengouw develop a clear vision on how to start with business intelligence. The roadmap described the following aspects of Veiling Haspengouw's business intelligence strategy:
- Current situation of Veiling Haspengouw for management reporting
- Defining the different reporting and subject areas that could become part of the data warehouse at Veiling Haspengouw
- An overview of the BI-solutions available on the market with a clear advice for Veiling Haspengouw, based on the required functionality, budget and project approach
- Architectural and technological proposal based on the needs of Veiling Haspengouw
- Scoped, iterative approach of element61 for the Veiling Haspengouws data warehouse & business intelligence implementation
For Veiling Haspengouw's senior management, it was clear that the first reporting process had to create some visibility within the organization to make people aware of the need of a company-wide business intelligence program. Therefore Veiling Haspengouw selected as a first iteration in the data warehouse initiative the process that creates most added value within the company: the process of buying and selling fruit.
Within the organization everybody had a feeling on how the performance of this process was, but no clear and unique figures could be given about the process. The project executed at Veiling Haspengouw would help to deliver accurate reports on who had delivered or bought what at Veiling Haspengouw. For the reporting on Veiling Haspengouw's sales figures, data from both ERP systems had to be extracted and loaded in the new corporate data warehouse. This resulted in 2 star models; one based on the old ERP system where the invoicing data are maintained and one based on the new GOLD ERP system which had richer product data available.
Where possible, element61 enriched the data coming from the old ERP system with extra data available in the new GOLD system.
Data quality
Data quality is often a problem within operational systems. It is also typically a relatively unknown problem, often only discovered fully during the course of a BI project.
Veiling Haspengouw was aware of potential data quality issues and had already anticipated some of the issues by identifying one system as the clear master and putting a system of replication between the two ERP systems in place.
At Veiling Haspengouw, where data is coming from 2 ERP systems, additional data quality issues were discovered during the data loading processes. element61 put in place a data quality framework, as part of the ETL layer. Data that does not comply with defined business rules is marked and can be corrected afterwards. The administrator of both the Business Intelligence system and the ERP system is notified via email with a detailed report of the issues. It is a best practice not to fix data quality issues at the data warehouse level. Organizations should take care that the source is changed accordingly and updated in a way that those types of quality issues cannot appear again in the system.
Technology used
Veiling Haspengouw has made a clear choice for the Microsoft Business Intelligence Solution set.
- Microsoft is used as back-end environment:
- Microsoft SQL Server 2008 is used as database
- Microsoft SQL Server Integration Services 2008 is used as ETL containing a staging area and a data warehouse target area
- Microsoft SQL Server Analysis Server 2008 is used as Cube technology
- Microsoft is used as Front-end reporting environment:
- Microsoft SQL Server 2008 Reporting services for the default parameterized reports
- Microsoft Excel 2010 as the OLAP front end interface for analyzing the cubes
element61 responsibilities
element61 has been responsible for the following roles & activities:
- Definition of a Performance Management roadmap
- Project Management
- Requirement analysis
- Definition of the architecture
- Microsoft Integration services: ETL development
- Microsoft Reporting services: Report development
- Installation of all components
- Design & build
- Rollout, training & coaching of the customer.
Conclusion
Veiling Haspengouw has made a first step on their Performance Management journey. Based on the roadmap some clear next steps can be identified and will be started shortly. Based on the first project various benefits can be identified:
- Improvement of data quality. The fruit producers can now be clearly identified and delivered with correct information. The data quality has made it possible to create a trusted environment where both ERP systems can be compared
- IT can focus on its core activities: supporting and maintaining the systems. Where before all reports had to be created and maintained by IT staff, sales and administration departments can now self-service themselves with both standard reports and ad-hoc analysis reports coming from the data warehouse environment
- Based on more detailed and precise information available about sales and production, more accurate decisions about pricing, conditioning and bringing products to the market can be made