Business case: modernising report creation for real estate investment funds
Challenge: A large real estate investment fund struggled with an outdated reporting system based on Excel spreadsheets and an Access database. This system offered no flexibility in report creation, made data validation difficult and did not allow granularity-based access control. The origin of the data was often unclear, which led to faulty reports. This made decision-making and reporting to the regulatory authorities more difficult.
Solution: We developed an innovative solution that revolutionised the customer's reporting processes through the implementation of a modern cloud-based data infrastructure. Our approach included:
- Data aggregation and processing: The integration of data from various API services, sensors and databases into a central cloud store (Data Warehouse) using multithreading techniques in Python and AWS Step Functions. This enabled the efficient processing of large volumes of data, in particular time series data.
- Real-time data pipelines: The implementation of real-time data pipelines using Snowpipe from Snowflake to ensure continuous updating of the data in the Data Warehouse.
- Data modelling and management: The definition of a comprehensive data model and data storage system with Data Vault 2.0 to ensure data quality and consistency.
- ETL pipelines for reporting: The development of ETL pipelines with Python and Step Functions for reporting in the field of renewable energy to meet the customer's specific requirements.
- Data quality measurement: The application of statistical methods to measure data quality and identify potential errors.
- Big data processing pipelines: The creation and support of big data processing pipelines for the efficient handling of large volumes of data.
- AWS cloud infrastructure: The development and configuration of services and DWH systems in the AWS cloud environment using Terraform to ensure a scalable, flexible and cost-effective infrastructure.
Result:
The project led to the following significant business successes:
- Minimisation of manual work: The automation of report creation led to a considerable reduction in manual working time and increased efficiency.
-
Achieved: Reduction of manual working time for report creation by 70 %.
-
- Centralised data view: The migration of all Excel reports to Tableau enabled a centralised view of the data, improved data management and supported compliance with governance policies.
-
Achieved: Reduction of the time required for report creation from 2 days to 2 hours.
-
- Scalable cloud infrastructure: The cloud-based infrastructure with Terraform management enabled easy and cost-effective administration of all new services and allowed flexible adaptation to future requirements.
-
Achieved: Scalability of the infrastructure on demand to keep pace with the company's growing volumes of data and requirements.
-
- Transparent data origin: The implementation of data lineage enabled easy tracing of the data's origin, which facilitated control and compliance with regulatory requirements.
Benefits for the customer:
- Improved decision-making: By providing up-to-date, accurate and easily accessible data, the customer was able to make better-informed decisions.
-
Achieved: Increase in the accuracy of investment decisions by 10 %.
-
- Increased efficiency: The automation of the reporting processes enabled more efficient use of resources and higher productivity.
-
Achieved: Increase in employee productivity by 20 %.
-
- Improved compliance: Compliance with governance policies and the traceability of data made it possible to meet legal and regulatory requirements.
-
Achieved: Reduction of the time required for compliance audits by 50 %.
-
- Cost savings: The cloud-based infrastructure led to a reduction in operating costs and enabled flexible scaling of resources as required.
-
Achieved: Savings of 15 % of operating costs through the use of the cloud infrastructure.
-
Conclusion: The modernisation of the reporting processes through the implementation of a modern cloud-based data infrastructure was a key factor in increasing efficiency (the manual effort required for report creation was minimised), improving decision-making and ensuring compliance for the real estate investment fund (all Excel reports were migrated to Tableau to guarantee data governance). The cloud infrastructure with Terraform management enables easy and cost-effective maintenance of all new services. And data lineage supports simple control over the data and straightforward monitoring by the authorities. We are confident that our solution provides a sustainable and forward-looking answer to the reporting challenges in the financial sector.