Building a Central Data Platform for Reporting and Monitoring Alternative Investments

Every day, countless investments are executed, adjusted, and monitored. Keeping track of all this activity is a major challenge.

One central task is limit monitoring – that is, determining which investments fall below their target limits and need further allocation, and which positions exceed permitted thresholds and should therefore be reduced or stopped. At the same time, key market indicators such as Market Value and Return on Investment need to be calculated precisely and consistently.

The complexity is further increased by the fact that the data comes from many different sources. Stakeholders first had to manually gather information before they could even begin their analysis.

We extended the harmonized data model and introduced a new "project" level, in which all related cash flows are bundled together, instead of treating each investment with multiple cash flows separately.

Challenge

Before the transformation, the data for this project was scattered across multiple systems, Excel files, and SharePoint folders. There was no central place where all information was stored, reconciled, and managed consistently.

As a result, investment data had to be collected and consolidated manually.

Monitoring investment limits – that is, identifying positions below target allocations or above permitted thresholds – was complex and incomplete. Important views such as exposures by country, sector, currency, or asset class were only possible with considerable manual effort.

Reporting was time-consuming and lacked transparency and accuracy. Metrics such as Market Value, Internal Rate of Return (IRR), and Return on Investment often had to be calculated manually. Because data definitions were not harmonized and critical fields were sometimes missing, confidence in the numbers was limited.

In short: there was no single source of truth – only fragmented data, duplicated effort, and inefficient reporting processes. Above all, there was no central overview that would have allowed stakeholders to gain insights quickly and make well-founded decisions.

Implementation

To get from fragmented data to a true single source of truth, we followed a clearly structured approach:

1. Standardizing data delivery

Instead of receiving multiple separate files from systems such as SAP RE, eFront, and third-party databases, we moved data delivery to a structured and coordinated process.

2. Introducing project-level granularity

We extended the harmonized data model and introduced a new "project" level, in which all related cash flows are bundled together, instead of treating each investment with multiple cash flows separately.

This enabled more detailed analyses and greater transparency across investments. At the same time, it was clearly defined where mapping logic needs to be maintained.

3. Deploying an ETL framework

All dashboards were connected directly to the data lakehouse, which serves as the central and reliable foundation for reporting and analysis.

By centralizing the data in the data lakehouse, we were able to ensure consistency, transparency, and trust across all metrics. Data from various sources was collected via ETL pipelines in Databricks, consolidated, and stored in a unified environment. This required a medallion architecture.

3.1 Bronze Layer

The first step was creating the Bronze layer. This layer stores exact, unaltered copies of all ingested data (e.g., CSV files).

The files are kept fully in their original format – without transformation, harmonization, or changes of any kind. This ensures complete traceability and data integrity.

Since the data in the Bronze layer remains unchanged, no prior transformation is needed before querying. Users can access the data directly via SQL, which significantly improves performance and usability.

3.2 Silver Layer

While the Bronze layer stores raw, unaltered data, the Silver layer transforms this data into a structured, business-ready format.

The main purpose of the Silver layer is to harmonize data from all source systems, normalize it according to business terminology, and decouple it from specific source systems.

In the Silver layer, data is stored in a highly normalized Delta Lake structure. Each core business entity – such as position, portfolio, or instrument – is organized into its own dedicated tables. This clear separation significantly improves data quality, consistency, and maintainability.

3.3 Gold Layer

The Gold layer delivers the actual business value. This layer is entirely geared toward data consumption.

Since different systems and stakeholders have different requirements, the Gold layer provides optimized data models for each specific use case. This eliminates the need for additional reformatting, manual adjustments, or reinterpretation of the data.

4. Consolidating the dashboards

We merged previously separate Power BI dashboards into a single, integrated reporting solution. With Power BI, we were able to develop interactive dashboards that present the data in a clear, structured, and visually appealing way.

Through close collaboration with stakeholders, the focus was on delivering exactly the insights relevant to their needs.

Result

What originally consisted of fragmented data spread across multiple systems evolved into a fully integrated, transparent, and scalable reporting solution.

We established a data lakehouse and implemented a medallion architecture that made it possible to transform raw, inconsistent data into reliable, business-ready insights.

Today, the added value is clearly visible: data is stored centrally in a consolidated data warehouse, definitions are harmonized across systems, manual data collection and calculations are no longer needed, reporting is automated and significantly faster, transparency and trust in the numbers have been restored, investment limits and exposures can be monitored in real time, and stakeholders can make well-founded decisions faster.

Potential improvements for future projects

The biggest challenge often lies not in the technical complexity of the tasks, but rather in understanding the actual business purpose – which is essential for implementing the right logic.

For future projects, I would recommend investing more time with relevant stakeholders early on to clearly define what is needed and why.

Step-by-step documentation of all access requests can be very helpful internally, so that teams can understand the project structure more quickly and integrate more efficiently into the client environment.

Better communication through regular alignment meetings and clearly defined escalation paths significantly reduces inefficiencies and helps guide projects successfully to go-live.