Terabytes Data Processed
At a large financial services company, the risk management group’s reporting application was extremely limited due to data and architecture constraints; change management limitations; poor data quality; and, lack of application analytical and executive reporting capabilities. This led to frustrated business users and management performing risk analysis and executive level reporting functions. As a result, other manual and stove-pipe reporting processes (i.e., end-user queries across multiple data sources, Excel spreadsheets, paper-based reporting) were developed.
Design and develop a large-scale data mart and business intelligence application that will enable better risk management around market and interest rate risk (duration, convexity, spread, etc.); profit and loss; position; and, activity reporting for the trading desk and company executives.
Implement a flexible data architecture and robust reporting application that enables better risk management and more efficient processes.
Unissant’s Project Manager and Solution Architect led a hybrid team of consultants and client employees in delivering a redesigned Data Mart and BI reporting capability. Unissant redesigned the data model used for reporting. The revised model utilized a dimensional approach with type-1 and type-2 dimensions and a core set of fact tables joined to the dimensions using point-in-time surrogate keys. This enables the viewing of “as-was” and “as-is” data without taking a redundant snapshot of all data attributes every day. In addition, cross-domain analysis would no longer resort to recursive, complex queries traversing a chain of tables. Next, the reporting database was separated from the source database and ETL processes were built to move the data from the source’s de-normalized structures into the target’s dimensional structures. Data quality routines for capturing larger than reasonable numeric amounts and missing / invalid foreign key relations to reference data were built as part of the ETL process.
A new MicroStrategy application was implemented. This application converted existing daily reports from the old system, but also delivered reporting Wizards and Templates for MicroStrategy Web users for user-friendly ad-hoc analysis functionality. Executive dashboards were built to supplement and eventually replace the existing Excel-based management reporting packages. Unissant consultants worked closely with end-users and subject matter experts to design a scheme of highly intuitive and navigable shortcut folders that allow users to find and access reporting objects in a much easier and rapid fashion.
Unissant adhered to the client’s stringent SDLC methodology passing through multiple corporate quality control checkpoints and delivering 78 separate artifacts onto their SharePoint portal. Additionally, a concurrent project to upgrade the Netezza database to a TwinFin multi-rack architecture required constant coordination and nimble reaction time by project team members through development, test, and UAT. Knowledge transfer and Lessons Learned were completed prior to contract end.
The solution improved the risk management group’s flexibility and usability in accessing data and reduced report processing times by 67%. Executive level dashboards are available. Users are now able to quickly drill down from valuation analytic summaries to detailed instrument position data and then across to detailed activity views. All of this supports the business goal of rapid, intuitive risk management analysis.
The project team implemented over 1,000 columns across 56 tables mapped to 900 reporting objects. Over 130 reports and dashboards were created.
The solution loads over 200 million data records on a nightly basis to a 20+ terabyte Netezza data warehouse appliance.
Unissant is an innovative software development and consulting company that manages complex initiatives, solves data challenges, and transforms business. Unissant brings technical excellence and program/project execution best practices that exceed the expectations of our clients in the Banking and Finance, Health and Life Sciences, National Security, and Federal/Civilian sectors.
Copyright © 2006-2015 Unissant, Inc. All rights reserved. No part of this publication may be reproduced, distributed, or transmitted in any form or by any means, including photocopying, recording, or other electronic or mechanical methods, without the prior written permission of Unissant, Inc., except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law.