Order Management reporting with Microsoft BI
MSF Supply is a procurement centre owned by Médecins Sans Frontières (MSF) / Artsen zonder Grenzen Belgium. It offers integrated logistic services to MSF missions in the field, as well as to other non-profit humanitarian organisations. These services include technical support, management of customer orders, calls for tender and purchase of equipment, quality control, warehousing and shipment of goods to the different country missions.
MSF Supplys aim is to ensure the responsiveness of MSFs work by supplying the equipment and consumables required by its missions. This supply meets operational needs by fulfilling the quality criteria, pricing criteria and delivery terms (lead times, modes of transport and national delivery address) set out by the missions.
MSF Supply wanted to replace and extend their existing BI environment because of multiple reasons:
- The existing environment was running on an obsolete version of SAP BusinessObjects (BO) that was no longer supported by SAP.
- A lot of information was extracted from different operational databases and manually transformed and combined to be able to report on it with Microsoft Excel because all existing BO reports were mono-data source. There was no data warehouse with integration of this data from the different operational applications. To measure the performance of the order management KPIs data from multiple applications was required:
o Nodhos: This is the central ERP system of MSF supply. It handles management of orders, shipping, and kit production. Nodhos runs on an Oracle database.
o Portal: This application is developed internally by MSF Suisse and acts as the order creation and validation system for the field. Portal runs on a PostgreSQL database.
- When employees of MSF Supply are travelling abroad for a mission they often work in a low-bandwidth or disconnected environment. To support their work in the field they require the ability to take the data wherever they go. The existing BI tool only delivered static high-level PDF reports for this, leaving out important detailed information.
In general, the new reporting environment had to cover the following requirements:
- Allow decommissioning of the unsupported SAP BusinessObjects environment.
- Shorten the time to produce the order management KPIs by automating the process to compare combined actuals from Nodhos and Portal to targets delivered by the end users through Excel files.
- Improve the quality of information and shorten the time required to provide up-to-date information.
- Support for offline access to interactive reports with filter and drill down capabilities.
- The main reporting language at MSF Supply is French but English is required as well for the international missions.
Following the elementary phased approach, the project was split up in multiple phases. To be able to replace and decommission the old BusinessObjects environment as soon as possible, the order management process was selected for the first phase of the BI program.
The order management process involves client order information of two applications: orders are created in the Portal by the employees on the field. As soon as the orders are validated, they are imported into the ERP system Nodhos. To integrate the data from these different source systems, a 2-layered approach (staging and Kimball star schema) was used for the data warehouse.
To help set the foundation for all future BI initiatives, special attention was made as well to define the base for a true enterprise data warehouse. element61 defined the overall architecture, the project methodology, and trained the staff of MSF Supply in element61s way of working, elementary, which combines the best elements from proven industry practices, real-world lessons learnt and leading project methodologies.
The order management KPIs are all about "lead times", measuring the time required for each phase in the order life cycle, from order to packing to transport and finally invoice.
To support fast analytical reports on these KPIs and provide self-service capabilities to analyse cause and effect, a cube layer was used to expose the data.
The challenge here was the special reporting requirement for the users in the field. Reports are not just supposed to be reports. They should be a starting point for further analysis, even while being offline. Users must be able to filter and drill-down to the lowest level of detail, add extra fields or filters and change the layout wherever necessary for an ad-hoc reporting requirement.
By using Power Pivot for Excel, users are able to store the required data on their hard drive, supporting interactive reports and further analysis while being offline.
While designing the reports, special attention was given to the graphical presentation of the data. By making good use of conditional formatting, charts and other graphical capabilities of Microsoft Excel, the true meaningful exceptions are highlighted.
Another important aspect of the project was the coaching and knowledge sharing modus, where an important part of the projects was executed by MSF Supplys internal staff, who initially had no experience with the Microsoft BI tools nor with concepts of data warehousing. During the entire project, extra attention was given to teach general best practices within data warehousing, such as data modelling and ETL design and working with the Microsoft BI tools.
MSF Supply has made a clear choice for the Microsoft Business Intelligence Solution set.
Microsoft SQL Server is used as back-end :
- Microsoft SQL Server 2014 for the database
- Microsoft SQL Server Integration Services 2014 for ETL
- Microsoft SQL Server Analysis Server 2014 (multidimensional) as cube technology
Microsoft is used as front-end :
- Microsoft Excel 2010 is used for all default reports, self-service and offline reporting
Role of element61
element61 has been responsible for the following roles & activities:
- Definition of the BI roadmap
- Project management
- Definition of the BI architecture
- Development of the Microsoft BI solution
- Installation and configuration of all components
- Design & build
- Training & coaching of the internal staff, both on the concepts as well as the development tools
While this was just the first phase of the project, it was already a huge step forwards to increase MSF Supplys maturity level for BI.
- The unsupported BusinessObjects environment has been replaced with the newest technology, using Microsoft SQL Server 2014.
- Reports on combined data from Nodhos and Portal can be created using the data warehouse as a single data source. There is no need any more for manual cut and paste and other manipulations of the data to calculate the KPIs.
- Even when users are offline, the reports stay interactive and the employees can drill down to the required details to support their daily activities in the field.
- Because of the Multilanguage support of the BI solution, all reports are available in both French and English