Establishment of a Central Data Platform for Reporting and Monitoring for Alternative Investments

"Almost all bigger projects lack sufficient documentation and clear product specifications. The main challenge was to understand the underlying business purpose — which is always crucial for implementing the correct logic."

Hisham Merdan

Analytics Engineer

One key task is limit-monitoring — understanding which investments are below their target limits (and require further allocation) and which are above the allowed thresholds (where investments must be reduced or stopped). At the same time, essential market indicators such as market value and return on investment need to be calculated accurately and consistently.

The complexity increases because the data comes from many different sources. As a result, stakeholders had to manually collect information before they could even begin their analysis.

This led to a significant amount of manual work, limited transparency, and slow decision-making — making effective investment management far more difficult than it needed to be.

Challenge

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

As a result, investment data had to be collected and consolidated manually. Monitoring investment limits — identifying positions below target allocations or above allowed thresholds — was complex and incomplete. Important views, such as exposures by country, industry, currency, or asset class, were difficult to generate and required significant manual effort.

Reporting was time-consuming and lacked transparency and accuracy. Key indicators such as market value, Internal Rate of Return (IRR) and return on investment often required manual calculations. Because data definitions were not harmonizedand critical fields were sometimes missing, stakeholders had limited confidence in the numbers.

In short, there was no single source of truth — only fragmented data, duplicated effort, and inefficient reporting processes. Most importantly, there was no comprehensive overview that enabled stakeholders to quickly gain insights and make fast, informed decisions.

Implementation

To move from fragmented data to a true single source of truth, we followed a clear and structured approach:

Modernizing Data Architecture to Azure

1. Standardize Data Delivery

Instead of receiving multiple separate files from systems such as SAP RE, eFront, and 3rd-party databases, we worked toward consolidating the delivery of data into a structured and coordinated process.

2. Introduce Project-Level Granularity

We improved the harmonized data model by adding a new "project" level, where all related cashflows are grouped together, instead of treating each investment with multiple cashflows separately. This allowed for more detailed analysis and better transparency across investments, while clearly defining where mapping logic should be maintained.

3. Using ETL framework 

All dashboards were directly connected to the Data Lakehouse, making it the single and reliable source for reporting and analysis. By centralizing the data in the Data Lakehouse, we ensured consistency, transparency, and trust in all reported figures. One of the main challenges was that the data originated from multiple different systems and sources. The Data Lakehouse solved this problem by acting as a single source of truth. Data from various sources was collected, consolidated, and stored in one unified environment using ETL pipelines running on Databricks. To achieve this, a Medallion Architecture was required. 

3.1 Bronze Layer

The first step was the creation of the Bronze Layer. This layer stores exact, unmodified copies of all ingested data (e.g. CSV files). The files are preserved in their original format, without any transformation, harmonization, or alteration. This guarantees full traceability and data integrity.

Because the data remains unchanged in the Bronze Layer, no prior transformation is required before querying. Users can directly access the data using SQL, which significantly improves performance and usability. In addition, this structure enables flexible data combinations, cross-system analysis, and scalable future extensions.

3.2 Silver Layer

While the Bronze Layer stores raw, unchanged data, the Silver Layer transforms that data into a structured and business-ready format.

The main purpose of the Silver Layer is to harmonize data from all source systems, normalize it according to the business vocabulary, and decouple it from any specific source system. This ensures that the data follows consistent definitions and standards, regardless of where it originally came from.

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 improves data quality, consistency, and maintainability.

As a result, the Silver Layer enables flexible data combinations, cross-system analysis, and scalable extensions. It creates a stable and structured foundation that supports advanced reporting and analytics in the next stage: the Gold Layer.

3.3 Gold Layer

The Gold Layer delivers real business value. This layer is designed entirely around data consumption. It prepares and structures the data exactly in the format required by each external system, report, or dashboard.

Since different systems and stakeholders have different requirements, the Gold Layer provides optimized data models tailored to each specific use case. Every solution is purpose-built to ensure clarity, efficiency, and performance.

As a result, no additional reformatting, manual adjustments, or reinterpretation of the data is needed. The data is reliable, consistent, and ready to use. This enables fast, confident decision-making based on clear and accurate insights.

4. Consolidate the Dashboards

We combined previously separate Power BI dashboards into one integrated reporting solution.

Using Power BI, we could build interactive dashboards that present the data in a clean, structured, and visually appealing way. Through clear visualizations such as bar charts, tables, and matrices, complex financial information becomes easy to understand. Behind the scenes, advanced financial calculations and business logic are implemented to ensure accurate and meaningful results.

By working closely with stakeholders, we focus on delivering the insights that are most relevant to their needs. This enables them to quickly understand performance, identify trends, and make informed investment decisions without requiring any technical or data analysis expertise.

Medallion Architecture

Result

What started as fragmented data across multiple systems evolved into a fully integrated, transparent, and scalable reporting solution.

We established Data Lakehouse, implemented the Medallion Architecture and which allowed us to transform raw and inconsistent data into reliable, business-ready insights. The journey from Bronze to Silver to Gold ensured full traceability, harmonized definitions, and purpose-built data models tailored to stakeholder needs.

Today, the impact is clear:

• Data is centralized in one consolidated data warehouse

• Definitions are harmonized across all systems

• Manual data collection and calculations are eliminated

• Reporting is automated and significantly faster

• Transparency and trust in the numbers are restored (High Data Quality)

• Investment limits and exposures can be monitored instantly

• Stakeholders can make faster, more informed decisions

Potential Enhancements for Future Projects

The main challenge often is not the technical complexity of the tasks but rather understanding the underlying business purpose — which is always crucial for implementing the correct logic. Going forward, I would recommend investing more time with the relevant stakeholders upfront to clearly define what is needed and why.

The architecture on the client side is considerably complex, and obtaining the correct access permissions is a time-consuming process that requires knowing exactly what to request and whom to approach. Documenting each access request step-by-step can be highly beneficial for internal purposes, helping the team understand the project structure faster and integrate into the client environment more efficiently.

Limited availability for developers and business analysts can lead to unnecessary iterations and avoidable delays. Improving communication by establishing regular touchpoints and clear escalation paths significantly reduces these inefficiencies and keeps the project on track leading to a successful launch in the end.