Implementation of a BI-Saas solution for production reporting & analysis
This case study discusses the work achieved by element61 & Lessius Mechelen together with Reynders Etiketten. For this customer a BI-Saas solution was created using Microsoft technology. The main focus of the solution was production reporting & analysis.
Reynders Label Printing is a group which provides self-adhesive label printing services. The company offers flexo, offset, screen print, letterpress and hot foil printing services, as well as digital presses. Their products mainly target the pharmaceutical, security & specialty sectors.
Reynders was established in 1956 by Emile Reynders, who started a local print shop for traditional stationery products. In the past decades, Reynders has steadily grown to one of Europe's leading companies in the field of self-adhesive labels.
Today, Reynders includes the following entities: Reynders Etiketten, Reynders Pharmaceutical Labels, Reynders Etiquettes Cosmétiques, Reynders Polska, Reynders Etiquettes France and Reynders Label Printing India.
Reynders Etiketten, which is still family owned and situated in Boechout, has a yearly turnover of approximately 32 million euro. They employ 140 people.
Reynders Label Printing was coping with certain difficulties related to their information insight:
- An iSeries - AS/400 system is used to supply ERP functionalities. Data is split up into an old system and a new system, each containing disperse functionalities, but also showing certain overlap.
- Some reporting is done based on a limited set of AS400 tables in which some information is gathered based on complex and time-consuming queries joining all necessary base tables. Certain reporting queries can take up 15 or even 20 minutes to finish.
- It often takes much more than one single day to make information available.
- The reporting front-end is Microsoft Excel through the use of pivot-tables. These pivot-tables are perceived as too difficult by most information consumers. Information needs to be offered in a more user-friendly way.
- Production information is required by a wide audience; business controller, management, production leaders, pricing calculators etc.
- Production insight from an information perspective should lead to a more easy control of printing presses and their corresponding operators.
- Performance of both machine and man should be traced, leading to higher profitability of both.
- Limited insight concerning eruption / waste percentages is currently available.
- Lack of one version of the truth, which leads to continuous discussions on parameters and timings concerning print job efficiency.
- Gut feeling management should be replaced by fact based management.
- The business controller takes care of nearly all reporting questions, on top of his daily job. His role in terms of information management is too demanding. Ideally end-users are enabled to be more self-sufficient.
- On the production floor a multitude of similar presses are situated, which makes it difficult to use plain tabular reports. Here a dashboard displaying the presses on a floor map would make identification of the different presses much easier.
- Given that no experience, nor knowledge on how to go about with a BI project was present at Reynders Etiketten, the need existed to learn and absorb the different steps within a BI project, next to the different functionalities within a BI front-end.
- Limited budget for reporting and analysis.
The European Regional Development Fund (ERDF) subsidies projects that diminish inequalities between regions, increase competition, create employment and reinforce cohesion between regions. The objective of the ERDF project "SaaS BI for Small & Medium sized Enterprises (SMEs)" is assisting SMEs in implementing a Business Intelligence (BI) SaaS platform (software as a service) to enable better business performance management and more efficient Management Information for reporting and analysis purpose. SMEs are frightened in thinking that a BI setup is too complex and too expensive for a SME. This project can helps in fighting these preconceptions.
Promotors of this initiative are Lessius Mechelen together with element61.
Based on our methodology elementary, which combines the best elements from proven industry practices, real-world lessons learnt and leading project methodologies, element61 designed and built a Business Intelligence solution, where the following aspects are worthwhile being highlighted:
- Application of a BI-method, which is able to focus on the core BI-project components in a very short time frame. Here the necessary attention was given to : a correct project scoping, a clear-cut requirement analysis, appropriate data modeling, the necessary source profiling & analysis, a source-to-target definition and finally the design & build of the required reports.
- Only a very short time frame was foreseen for establishing a complete solution, therefore it was quite essential that Reynders Etiketten, who had no relevant BI nor data warehousing background at all prior to the project start, were guided in this BI journey in a true coaching and knowledge sharing mode. An important part of the efforts were executed by internal Reynders Etiketten staff; creation of a detailed information matrix, source data analysis, source-to-target mapping and interface delivery.
- A classical & proven Data Warehouse 2-layer approach: A staging area and a Kimball star scheme oriented data warehouse layer.
- A multidimensional Cube allowing to answer all "ad-hoc reporting needs using different axis of analysis (Type of press, Press Operator, Time, etc)
- A set of parameterized reports accessible through a browser user interface with interactive drill down capabilities allowing navigating from high level overview to detailed information.
- Automated error tracking based on the Kimball screening technique.
- A solution hosted 100% in the cloud: no client and/or server installation at Reynders.
The architecture of the project consisted in combining classic & proven Microsoft technology & BI-cloud specific Microsoft technology.
- Microsoft is used as back-end environment:
- Microsoft SQL Azure DB is used as a database in the Cloud
- Microsoft SQL Server Integration Services 2008 is used as ETL tool
- Microsoft SQL Server Analysis Services 2008 is used as OLAP tool
- Microsoft is used as front-end environment:
- Microsoft SQL Server Reporting Services 2008 and SQL Azure Reporting CTP2
- Microsoft Excel 2007 is used to browse the Multidimensional Cube
element61 has been responsible for the following roles & activities:
- Definition of a Business Intelligence roadmap
- Project management
- Requirement analysis
- Definition of the architecture
- Microsoft technology expertise
- Pro-active follow up of new BI-cloud developments at Microsoft.
- Installation of all components
- Design & build
- Coaching of the customer
Although Reynders Etiketten has just taken its first step in terms of Business Intelligence, various benefits are already being established.
To quote the customer - Kristof Vandenbroucke (business controller Reynders Etiketten) :
- In a very short time frame and with limited costs Reynders Etiketten became acquainted with the different aspects of Business Intelligence; project methodology, different deliverables, conceptual data modeling, information matrix, source-to-target mapping. Front-end functionalities versus complexity and many more
- "This is only a first taste of Business Intelligence; Plans are already being made to also open up the financial accounting data as a next iteration.
- "Easier and faster access to integrated information means less time required for preparing the data and leaving more time for actually analyzing the business information.
- "Important business insight is gained in terms of elapsed time between the different steps within the quotation-to-order-to-print process, pinpointing specific problem areas.
- "The "cloud really means a limited impact on the in-house IT department leaving room for other IT supporting tasks.