Pension Scheme Data Store and Business Intelligence reporting

Background:

Reformis was chosen by the executive arm of a large corporate pension scheme to design, develop and implement a data store and business intelligence reporting platform for all their management information, decision making and operational support needs.

The client’s responsibilities to the pension scheme included:

  • Executive reporting to the scheme trustee and parent company
  • Investment management of the scheme’s strategic asset allocation by means of tactical overlay portfolios
  • Scheme risk management – monitoring and assessing the risk profile of the scheme’s assets
  • Liability management – monitoring the scheme’s liability related risks including the members of the scheme

The business functions that discharged these responsibilities required consolidation of data from a number of sources to generate the desired information. Inevitably, consistency between sources and functions was lacking and the resultant reporting anomalies difficult to explain.

It was determined that a single reporting data store would be implemented to provide a centralised source of data for each of the business functions. This would improve efficiency and consistency in re-use of data across business functions, focus on improving data quality from all sources into a single repository, and enable advanced reporting and data analysis capabilities across all scheme related data.

Reformis Solution

Project Justification – The initiation phase of the project demanded that the business case for the data store was made in order to secure sufficient funding. Business Intelligence and Data Warehousing projects often require significant investment and long timescales before tangible benefits are realised. Therefore a strong business-driven benefit case is required to justify the investment in time and resources.

To make this justification, Reformis identified the business issues that were barriers to meeting strategic business goals, provided cost-benefit analysis of the tangible benefits available (such as efficiencies in report production), and highlighted the operational risk improvements that would be gained by the solution.

Following project initiation and set up of the solution technical framework, the project implemented the take-on and reporting of various data sources as follows:

Member Data and Liability Analysis – The first phase of the project focused on take-on of data relating to members of the pension scheme. This data was then interfaced to an asset and liability modelling application that generated the cash flows and liabilities associated with the members, which were loaded back into the data store. The data store then provided reporting facilities to enable verification of the actuarial valuations of the pension scheme, liability analysis based on different calculation methods, member population analysis and cash flow projections based on various actuarial assumptions. In addition, extracts from the data store were interfaced to Algorithmics, the scheme risk system, for cash flow risk analysis.

Risk Analysis – Daily feeds from the Algorithmics Risk System were consumed by the data store to enable enhanced management information reporting on risk data and to reconcile valuations made by the risk system to those generated by the various scheme fund administrators. The data store also provided back-testing of historical risk data, a facility not available within Algorithmics.

Pension Scheme Performance – Monthly portfolio performance returns over multiple periods (1 month, 6 month, YTD etc) for all scheme investments were loaded into the data store. This was primarily to automate and support executive reporting to the scheme Trustee, but additionally enabled more detailed analysis of investment returns, more comprehensive trend and historical analysis and portfolio and fund manager performance evaluation.

Member Data and Liability Analysis – The first phase of the project focused on take-on of data relating to members of the pension scheme. This data was then interfaced to an asset and liability modelling application that generated the cash flows and liabilities associated with the members, which were loaded back into the data store. The data store then provided reporting facilities to enable verification of the actuarial valuations of the pension scheme, liability analysis based on different calculation methods, member population analysis and cash flow projections based on various actuarial assumptions. In addition, extracts from the data store were interfaced to Algorithmics, the scheme risk system, for cash flow risk analysis.

Scheme Assets under Management – One of the key data domains for the data store was the take-on of daily positions and valuations for all scheme investments and the tactical overlay portfolios. This data, together with portfolio and asset master reference data, enabled detailed analysis of scheme assets according to multiple classification structures. This underpinned the decision support processes for rebalancing the scheme to the strategic asset and currency allocations and management of the tactical overlay portfolios.

Business Benefits
The data store has enabled the client to centrally pool all data that is important to its business operations. By enforcing consistency of data source and reporting facilities, the business is more confident in the accuracy of information used, and generated, by the disparate functions under its control. Consistency of data also breeds efficiency of use, eliminating cottage industries and silo processes.

Another benefit of centralised reporting is that it provides previously unavailable insights into the business data. For example, portfolio attributes historically only used for risk analysis can be applied to performance data to provide new views and understanding of the scheme.

Solution Architecture
The data store implemented by Reformis utilised the following technologies and methodologies:

  • Kimball dimensional data model design
  • Extract, Transform, Load (ETL) layer using SAP-BusinessObjects Data Integrator
  • Business Intelligence reporting using SAP-BusinessObjects XI R3, including:
    • Web Intelligence
    • SAP-BusinessObjects Live Office
    • SAP-BusinessObjects Dashboard Manager
  • Microsoft SQL Server database

Click here to get in touch with Reformis