Siloed systems and cashflow
This case study demonstrates how IT infrastructure can become fragmented over time, and how it can be brought back under control by centralizing data from various systems into a single location, making it accessible and ready for analysis.
The introduction of the company
A multi-sector holding company operating in Austria and the Czech Republic engaged our team to develop and deploy a comprehensive Business Intelligence (BI) and controlling solution. The company oversees a diverse portfolio that includes more than 15 restaurants, 5 hotels, and 3 real estate management entities.
Despite its scale and regional reach, the organization faced significant operational challenges due to its fragmented IT infrastructure. Specifically, it relied on:
Three different types of restaurant management systems, each with varying capabilities and data formats.
A centralized but uniquely structured IT system was in place for the hotel and real estate divisions, which also handled expense administration for all companies and included a custom-built data sync function to collect outgoing invoice data from the various restaurant systems.
These siloed systems made it difficult to consolidate financial data, monitor real-time performance, and accurately forecast cash flow across business units. Each department operated with its own data sources, reporting standards, and workflows, leading to time-consuming manual reconciliation processes and limited strategic visibility at the group level.
The architecture
This case study outlines how we addressed these issues by building an end-to-end data analytics and controlling system that enabled seamless integration, centralized reporting, and dynamic cash flow monitoring across all entities in the group.
CEO’s Perspective: Core Challenges
From the perspective of the CEO, the company's fragmented IT setup created a number of critical operational and strategic challenges that prevented growth and decision-making:
1. Lack of Data Consistency
Although a data synchronization function was developed to pull outgoing invoice data from the restaurant systems into the centralized platform, it frequently produced inconsistencies. Sync errors and data mismatches created doubt in the accuracy of reports and required time-consuming manual checks.
2. Inflexible System Architecture
The centralized system was closed and rigid. Even small adjustments, such as adapting a report or changing a calculation logic, required intervention from the vendor. Each modification had to be hardcoded, resulting in slow turnaround times and no guarantee that the final outcome would meet business needs. This bottleneck delayed decision-making and reduced agility.
3. Static, Non-Visual Reporting
The existing reporting approach consisted solely of static tables, with no visual analytics or dashboards. This made it difficult for decision-makers to gain quick insights, identify trends, or monitor performance in a user-friendly format.
4. No Group-Level Financial Overview
There was no unified view of the holding company’s overall performance. Each business unit—restaurants, hotels, and real estate—was reported separately, with no consolidated financial reports or cash flow projections across the group.
5. Heavy Dependence on Manual Excel Work
Due to the limitations of the existing systems, a significant portion of reporting was handled manually in Excel. This consumed valuable human resources, increased the risk of human error, and slowed down the reporting process.
Project timeline
At the outset of the project, our goal was simple: to create a cashflow report for each company and a consolidated, group-level cash flow overview. The centralized system was designated as the primary data source for these reports. At that stage, we were unaware of the complexities and challenges that lay ahead.
An overview of the full project timeline
The First Change: New Restaurant systems
The first major turning point came with the decision to replace the existing restaurant systems. The company opted to implement a single, modern restaurant management system across all locations, phasing out the three legacy systems one by one. While this was a strategic move toward standardization, it disrupted the existing data synchronization process with the centralized administration system.
As a result, we had to redesign our data pipeline to integrate the new system directly into the final reporting structure. This introduced two significant technical challenges:
The restaurant databases were distributed across multiple servers.
We could no longer connect to them natively from within the centralized system.
These limitations required us to develop a flexible, external data integration layer capable of handling distributed sources and ensuring reliable data extraction for our reporting framework.
The Second Change: New centralized system only for the expenses
The second major change was the introduction of a new centralized system for managing expenses. With the legacy data sync no longer supported, the company chose to replace the custom-built back-office solution with a standardized ERP system. This shift aimed to bring stability and better long-term support.
Since restaurant-related expense data was already partially captured through the restaurant systems for reporting purposes, the new ERP system focused primarily on logging:
All incoming invoices and general expenses across the holding company, and
The restaurant expenses that were not handled within the restaurant systems.
One of the key technical obstacles we faced involved the new ERP system. Due to vendor restrictions, we were not granted native database access. This limitation prevented us from connecting directly to the ERP's backend to extract data in real time.
To overcome this, the ERP vendor provided a workaround: they generated a periodic database backup containing only the relevant subset of data we needed. This backup was made available on an FTP server, from which we could securely download it. We then implemented a custom script that restored and processed the backup locally.
Although not ideal, this solution allowed us to automate data extraction from the ERP in a controlled way, ensuring we could still populate the reporting system with accurate and timely financial data.
In The End: Data Warehouse
This transition required another layer of data integration to ensure that the expense data from the new ERP could be aligned with existing reporting structures, without losing continuity or data integrity.
In the end, the project resulted in a highly complex data architecture and an equally sophisticated ETL (Extract, Transform, Load) process. To manage this complexity, we built a centralized data warehouse where data from all systems, past and present, could converge. This warehouse served as the single source of truth, integrating and preparing data from multiple, distributed sources for final reporting.
A major challenge was ensuring continuity and consistency across historical and current data, especially as different systems evolved or were replaced during the project. The ETL pipeline had to be robust enough to handle structural differences, timing mismatches, and fragmented data models while delivering accurate, consolidated insights for the entire holding company.
The Final Architecture
The final solution brought together data from five distinct sources, each playing a critical role in delivering a complete and accurate financial picture for the group:
Legacy Centralized System
Used primarily for accessing historical data from the period before system transitions began. It served as a valuable reference point for trend analysis and continuity.New Centralized ERP System
The main source for current expense data, including restaurant costs not captured in the operational systems, as well as all incoming invoices and financial records from the hotel and real estate companies.New Restaurant Management System
Data was collected via custom API-based scripts from multiple servers, as each restaurant instance ran on a separate environment. This provided transactional and operational data from the new restaurant platform.Wages Excel Sheet
Maintained manually by the accounting department, this monthly spreadsheet tracked payroll and personnel-related expenses across all companies.Extension Excel Table
Due to the phased retirement of the old centralized system and the lack of automated support during the transition, certain incoming expense data still had to be manually administered in a supplementary Excel file for specific companies.
This hybrid architecture demanded a carefully designed data warehouse structure capable of aligning manual inputs, legacy data, API-driven sources, and live ERP feeds into a unified model. It ensured that all relevant data—regardless of source or format—was prepared, validated, and integrated for final reporting and analysis.
The final data architecture
Conclusion
What began as a straightforward request for a consolidated cash flow report evolved into a complex data integration and reporting project spanning multiple systems, technologies, and organizational changes. Throughout the process, we encountered shifting requirements, system migrations, vendor limitations, and technical roadblocks, all of which required flexible thinking and resilient architecture.
Despite these challenges, we successfully built a robust data warehouse and end-to-end reporting solution capable of unifying historical and current data from five diverse sources, including old systems, a new ERP system, distributed restaurant databases, and manually maintained Excel files.
Our solution replaced manually created inconsistent reporting with a dynamic ETL process and data warehouse, which is the base of the consolidated dashboards that enable fast financial oversight at both the company and group level. The holding company now has a clear, reliable view of its cash flow and financial performance, supported by a scalable data infrastructure ready to adapt to future changes.
