Challenge
The client currently follows a hybrid investment management model whereby they outsource a portion of their book to external managers and manage the remainder in-house. All assets are managed on behalf of underlying insurance entities they own.
The client sought to offer a separately managed account strategy (SMA) to external investors and insurers; however, they were aware that additional process controls and reporting capabilities would be needed to satisfy the demand for transparency from investors. They attempted to build a custom data warehouse to achieve:
- Comprehensive information daily versus monthly
- Automated workflows to reduce manual intervention, human error, and processing times
- Increased transparency related to data quality, lineage, and report calculation logic
- Tools to monitor and resolve data quality issues
Ultimately, the client determined the success criteria were not being met and elected to terminate the project midway through development, seeking a different path.
Solution
The client decided to forego the cost and effort of a custom data warehouse and chose to deploy Rivvit for its software as a service (SaaS) data management and reporting solution with a pre-built investment data warehouse. Rivvit’s managed data services team worked with the client to configure the system to meet their specific needs, including:
- Interviewed stakeholders and analyzed current-state reports to identify specific data field requirements
- Created a data dictionary containing data definitions, data cleansing and translation rules, entity resolution identifiers, source hierarchy, quality rules, and data stewards for each data element
- Identified data ingestion and standardization rules across 90+ data templates
- Implemented portfolio grouping capability to support multiple portfolio hierarchies including both legal and at-risk entity groups
- Reverse engineered 20+ “black box” reports to identify detailed specifications
Results
- Deployed production-ready investment data warehouse and reporting capability in four months
- Completed four-month parallel testing period allowing the client to decommission their legacy data warehouse after eight months
- Converted monthly processes to daily by automating over 300 processes which provided the client with comprehensive information on a T-1 basis
- Implemented a four-way reconciliation between general ledger (G/L), accounting book of record (ABOR), investment book of record (IBOR), and Rivvit to ensure systems are in sync
- Converted monthly manual process to daily automated process by interfacing statutory accounting data from their IBOR to their OMS to enhance the portfolio allocation process
- Developed accounting roll forward explaining statutory accounting changes between the current and prior period and identifying accounting errors in position and transaction data
- Updated NAIC rating process to support 20 rating levels and corresponding risk-based capital (RBC) charges
- Replicated 20+ reports with drill-down capabilities and documentation support eliminating the black box nature of legacy reports and providing transparency into calculation logic
- Monitored more than 75 daily data quality rules, capturing missing data elements and outliers based on day-over-day and month-over-month changes versus defined thresholds for accounting and security analytics data to allow more timely investigation and resolution, shortening the length of the monthly close
- Monitored state regulatory guidelines daily