Innovation people can work with
Use casesPortfolio analytics reporting for private banking
A reliable reporting platform that turns portfolio and banking data into consistent banker-level insights.
Context
The starting point
The client needed a stronger analytics foundation for portfolio reporting. The practical focus shifted toward a small set of core portfolio analytics cases — building a reliable platform that turns portfolio and banking data into consistent banker-level insights and client-ready outputs.
Challenge
What made this hard
The challenge was not only to centralise data, but to make portfolio analytics repeatable, trustworthy and usable in day-to-day work. The client needed a setup that could combine historical and current portfolio data, calculate banker-level metrics consistently, and support reporting outputs consumed in Power BI and Excel.
- Constraints: legacy SQL-based source systems, historical data requirements, secure access, and the need to standardise analytics across clients and bankers.
- Success needed to look like stable nightly processing, clear reporting logic, and reliable outputs for each of the core portfolio reporting cases.
Approach
How we worked
Agoya designed the platform as a pragmatic Azure and Databricks-based analytics foundation with repeatable data pipelines and governed reporting outputs.
- Discovery & alignment
- Define the reporting logic, understand the existing portfolio workflows, and align the platform to real banker and analyst use.
- Design choices
- Medallion architecture (Bronze, Silver, Gold), infrastructure as code, dbt-based transformations, and secure access through Microsoft Entra ID.
- Implementation
- Extract data from SQL sources, transform and version it in Databricks, expose analytics through Power BI and Excel-compatible outputs, and productionise the workflows with scheduling and monitoring.
- Change enablement
- Training around Databricks and Excel integration so the client team can work directly with the portfolio analytics outputs.
Solution
What we delivered
A portfolio analytics reporting platform built to support the client's core reporting cases.
- Key components
- Azure Databricks platform, Terraform-based infrastructure, dbt transformation layer, scheduling, monitoring and role-based access.
- Integrations
- SQL Server source systems feeding the analytics platform.
- Data model
- Structured transformations through Bronze, Silver and Gold layers with historical tracking and governed outputs.
- Governance
- Reproducible deployments, versioned transformations, controlled access and production alerting.
- Reporting logic
- Return-on-capital metrics per banker portfolio, per-banker aggregate reporting tables, and standard portfolio-performance reporting that breaks return into income, capital movement and cost while reconciling opening and closing balances against external cash flows.
Outcome
What changed
A production-ready analytics foundation for recurring portfolio reporting. The client gained a more structured way to calculate, validate and distribute portfolio metrics, with banker-level reporting through standardised analytics outputs rather than fragmented manual work.
- Business impact
- More consistent reporting for bankers and stronger foundations for client-facing portfolio analysis.
- Operational impact
- Nightly production runs, alerting, cleaner onboarding for new clients and a more maintainable reporting process.
- Data & AI readiness
- Historical tracking, governed analytics layers and a reusable platform for future reporting use cases.
- Now possible
- Pulling portfolio analytics data directly into downstream reports and working from one shared analytics foundation.
“A useful analytics platform does more than calculate metrics. It gives teams one reliable foundation to report, explain and improve portfolio performance.”
Agoya perspective
Getting your data to work
Innovation people can work with
Bring the messy version. We'll help you shape it into clear, safe next steps.