From Excel to a unified data foundation for efficient project management
Project goal
ZVVZ needed to unify and streamline the process of evaluating projects. Pricing proposals were previously created in different Excel formats, which made comparison and connection with data from Helios difficult. The goal of the project was therefore to create an application for entering pricing proposals in a standardized format and a data warehouse that links proposals with actual costs. This enables ZVVZ to track who modified a proposal and when, compare planned and actual costs across different production phases, and evaluate project profitability, including detailed breakdowns of labor, material, and subcontracting costs.
1
application for all customer proposals
6
phases of project evaluation
We started the project with a detailed analysis of the materials available to the client. The assignment also included existing documentation prepared by a previous vendor, which we used as initial context. However, for the solution design, we decided to rely primarily on real data and actual processes.
We analyzed templates from Helios, reviewed available data, and in collaboration with ZVVZ defined in detail how individual proposals and projects should be interconnected. Based on these insights, we designed the architecture of a new data warehouse built on a clear “star” schema, forming a solid foundation for further reporting development.
In the next step, we implemented the intecs framework in MS Fabric and gradually built core tables for key areas – inventory movements, projects, production orders, and purchase orders. On top of this data foundation, we created a Power Apps application for standardized entry and management of pricing proposals. The application is bidirectionally connected to the data warehouse: it uses data from Helios and configuration Excel files stored in SharePoint, while also writing new data back to the warehouse.
The most challenging part of the project was handling so-called “calculations” – cost overviews across different project phases, from pricing proposals, through initial cost estimates at the start of production, to actual accounting costs. During implementation, we uncovered dozens of exceptions and conditions with the client that had to be incorporated into the logic. The resulting system can automatically evaluate costs while considering the project status and individual production orders.
Another challenge was flexibility. ZVVZ changes the structure of evaluated costs (the calculation formula) every year – adding or removing items and adjusting the breakdown into labor, material, or subcontracting costs. The data model therefore had to be designed to allow these changes without modifying the core system logic.
The result of the project is a fully functional data warehouse in Fabric and an application for managing pricing proposals. The client can now compare planned and actual costs across different production phases, track project profitability, and manage the entire process from proposal to execution more effectively thanks to a unified data system. The delivery also includes documentation describing the origin of each data field, enabling the client to further develop the data model independently in the future.
Technologies used
Industry solutions
Solutions
Satisfied client
“The project brought us significant time savings – calculations are now performed automatically for all projects, which was not possible before. A major benefit is also the ability to create reports directly on top of data from the HeN system, which we gained through the data warehouse. The collaboration was excellent – despite the complexity of the HeN system, the team responded quickly, flexibly, and always found a suitable solution. We appreciate not only the comprehensiveness of the final solution, but also its design, which allows us to make basic adjustments and extensions ourselves, giving us great independence for the future.”
Data Analyst, ZVVZ Group, a.s.