Challenge
The client sought a data warehouse solution to manage data for their four investment strategies across 28 funds, 46 share classes, and 100+ SMAs with nearly 1,000 investors and total AUM exceeding $10 billion.
On a daily basis, the client receives ~20 data files from their transfer agents, Bank of New York (Mellon) and Bank of New York (Dublin), including information for funds, daily holdings, transactions, prices, investors, accounts, sub-accounts, sales reps, consultants, agents, and other affiliated parties.
The client’s legacy data process lacked the ability to properly handle backdated accounting transactions, differing holiday schedules between US and Ireland, and daily FX conversion. Nor did it support a single source of truth for their enterprise data. As a result of these limitations, the client’s reporting processes were manual and labor intensive, and lacked the ability to perform time series analysis or provide real-time metrics related to their strategies and relationships.
Solution
With Rivvit, the client achieved a production-ready data warehouse and a white-labeled web portal in just three months. They now have updated information based on the prior business day’s close. The Rivvit team:
- Developed a process to automate the daily ingestion of ~20 data files from their transfer agents
- Designed a client master supporting enterprise data for investors, clients, sales representatives, and intermediaries
- Standardized inbound data sets to create enterprise data sets across transfer agents based on the client’s data policies
- Ingested 1,300+ daily files and reconciled a complete history for YTD holdings and transactions and other time series data
- Configured a process to automatically generate master records for new strategies, funds, share classes, investors, accounts, sub-accounts, prices, holdings, transactions, and affiliated parties based on daily incoming data files
- Implemented foreign currency conversion solution to support reporting in USD for funds denominated in non-USD currencies
- Facilitated offboarding and migration to new transfer agent without disrupting daily operations
- Created customized interfaces to integrate Rivvit with the client’s performance book of record
Results
- In three months, we delivered a production-ready data warehouse and a white-labeled web portal, providing up-to-date information based on the prior business day close
- Created a singular view of enterprise data across transfer agents
- Automated the process to roll forward balances for non-business dates to align discrepant data sets reflecting US and Ireland holiday calendars
- Developed interactive dashboards and reports, which eliminated the need to manually produce management KPIs