Accessible Business Intelligence Solution for OFR and FSOC
By quickening the delivery of private fund analysis and reporting, OFR is positioned to help reduce systemic financial risk and provide advanced notification to regulatory bodies and the Financial Stability Oversight Council.
Client: United States Treasury’s Office of Financial Research (OFR) and Financial Stability Oversight Council (FSOC)
Challenge: The Dodd-Frank Act requires investment advisers with at least $150 million in private assets under management to file portfolio position, trade, and risk information via a newly-created form (Form PF) for the assessment of systemic risk by the FSOC and OFR. Form PF data resides in nearly 100 highly-normalized database tables yet OFR and FSOC have a need to quickly perform analysis on the data.
For defined questions about the data, OFR researchers developed SAS-based queries to extract, transform, and load data into more analytic-friendly formats. These SAS-based queries took time to develop and could not be easily applied to the Form PF data as a whole. This limited the analytic efforts of OFR and FSOC because analysts were unable to explore the data to identify interesting trends or outliers. This challenge was complicated by a complex data model and the need for the solution to be easy to navigate and based in familiar, Microsoft Office-based software.
FI Solution: FI developed and demonstrated a Microsoft Excel-based reporting and analytic solution for Form PF Data. Key to the success of the Form PF solution was simplifying the data model, reducing 94 highly normalized tables into substantially fewer fact and dimension tables organized in an intuitive “star-schema” architecture. That is, each logical grouping of business artifacts could be sliced, grouped, or summarized by various dimensions, each requiring just one join. The star-schema data warehouse facilitated rapid development and testing of Online Analytic Processing (OLAP) cubes stored on a SQL Server Analysis Services repository.
Leveraging OLAP cube architecture enables users to quickly build pivot tables and pivot charts in Excel. Analysis workbooks can be created on the fly for ad-hoc queries with minimal requirements gathering, saved and refreshed for later use, and securely shared with other analysts. Users select a topic they’d like to explore and within minutes can answer simple questions like “which hedge funds have the largest exposures to credit strategies?” or “which funds are the largest and most leveraged?” Answering these questions quickly and leveraging Microsoft Excel’s built in chart formatting increases the timeliness and usefulness of previously opaque data.
FI Impact: FI developed an accessible business intelligence solution for OFR and the FSOC that facilitates Form PF data analysis through a familiar, Microsoft Excel-based interface.