At Romaco, self-created Excel sheets for reporting are a thing of the past. No longer is each department doing its own thing; instead, everyone has the same data in front of them and the same opportunity to analyze it.
Excel and Business Intelligence, these terms are often mentioned in the same breath. Certainly, Microsoft has made its spreadsheets more and more BI-ready over the years (by coupling them with the products PowerPivot, Power Query, etc., up to today's Power BI). This is especially interesting for companies that perform analyses on more than 100,000 data records. However, with this amount of data, among other things, the processing speed is greatly reduced.
The fact that the Romaco Group said goodbye to Excel as the leading BI system some time ago has less to do with too large files. The reason is rather the distributed structure of the internationally active group of companies, which has acquired many additional companies in recent years. In addition to one Italian and three German production sites – Romaco S.r.l. in Bologna, Romaco Pharmatechnik in Karlsruhe, Romaco Innojet in Steinen and Romaco Kilian in Cologne – Romaco has six sales and service companies in Moscow, Shanghai, Sao Paulo, Mumbai, New Jersey (USA) and Alsace (FR).
Receiver of reports needed to become active themselves
In the past, the IT landscape was heterogeneous. When Simon Hopkins, Group IT Director at Romaco, joined the company in July 2016, he found various parallel ERP systems and utilities, two CRM solutions, and 30 other tools. To create reports, information from dozens of upstream systems had to be compiled, prepared, and manually transferred into Excel lists. The users in the departments used these for their analyses. They aggregated figures from the departments and controlling and used them to create monthly reports in PDF format or PowerPoint files. The reports were stored on a network drive, where they were not subject to uniform administration. The report recipient had a quasi duty to get these him/herself.
Romaco wanted to change this situation and reduce the number of Excel sheets in order to minimize manual work. A single point of truth was to provide users with uniform access to their daily updated reports.
Reduce number of data units
For this reason, Romaco built a Data Warehouse that connects to the various systems/data sources in the subsidiaries – which are being reduced at the same time. Simon Hopkins, Romaco Group IT Director: "If we reduce the number of data pots, this automatically simplifies reporting." Romaco's long-term goal is to provide its applications centrally from the Cloud as far as possible in the future, whether from the private Cloud, Microsoft Azure or AWS. CAD systems, whose data volumes are too large for transfers, are an exception.
Romaco did not have a Data Warehouse in the past. BI specialist TIMETOACT installed such a database based on the MS SQL server, into which all relevant data from the upstream systems runs automatically. The data is transferred via Talend Integration Studio, an Eclipse-based tool for Data Integration and business processes. TIMETOACT sets up the necessary processes and takes over the monitoring. Only content that is relevant for reporting is transferred: Financial and order data from the ERP, quotation information, details on service technician assignments from time recording systems, CRM info records, etc. This does not include, for example (at least in the first step), data from design, i.e. from the CAD and PDM systems.
At the Karlsruhe site of Romaco Pharmatechnik, the time recording software and the Abas ERP are now connected to the Data Warehouse, and at the level of the corporate group, the quotation tracking and service planning. At the end of 2017, the SAP connection started in Bologna, followed by the internal helpdesk.
Daily updated data
In the meantime, Romaco's IT team generates the evaluations in the form of dashboards and graphical displays independently with Microsoft Power BI. IT creates one-off reports or dashboards, such as the monthly quotation reporting or an evaluation of customer satisfaction. To do this, it is necessary to define once only which data from which systems in the Data Warehouse should flow into it and who should or may view the report. The respective recipient then only receives a web link via which they can view the dashboard stored in the Cloud platform at any time.
Simon Hopkins: "The advantage of this is that no one has to search through locally stored Excel sheets and reports. Most importantly, the reports are updated daily as new information from the Data Warehouse is automatically transferred to them." In the Power BI dashboards, users can navigate drill-down and use slice-and-dice selection operators to track in detail which sales were generated by which field service providers with which machines in the month of October, for example.
In this way, important KPIs can be seen at a glance, where it was previously difficult to track such information spread across individual Excel sheets. "No longer is anyone doing their thing and creating their own personal reports; everyone has the same data in front of them and the same ability to analyze it. No longer can two people come up with two different results for the same query. That's what we think of as a single point of truth," says a delighted Simon Hopkins. Excel is now only used to a very limited extent in reporting at Romaco – where it is not practical or possible to maintain data in central systems.