provansys mortgage dw roadmap

8/8/2019 Provansys Mortgage DW Roadmap

1/23

ML RESI DATA WAREHOUSE

ROADMAP

RESI DATA WAREHOUSE

ROADMAP


2/23


3/23

www.Provansys.com

The Current Data Structure is in the following stateThe Current Data Structure is in the following state

1. Multiple data sources storing data in various formats and to multiple unrelated standards2. Multiple non-standardized processes to provide data to Data Customers (Manual, Automated, Undefined)3. Multiple un-related source of truth (Sometime Conflicting) data definitions, source locations and

organizational purposes /requirements4. Multiple duplicated/unrelated reporting needs across various business units5. System wide duplication of data6. Multiple points of manual intervention/Multiple manual intervention processes

a) Reporting

b) EDI

c) Data Validation/Correction

ResultsResults System wide Data InconsistencySystem wide Data Inconsistency

Delayed/Late Data ReportsDelayed/Late Data Reports

Delays across Enterprise/Multiple InitiativesDelays across Enterprise/Multiple Initiatives

Current Data Structure

Operational Issues causing interruption or delay in business operations Time Time dedicated by Trade desk, Surveillance, Research, and other related business away from primary tasks. Resource Resources must be pulled from other projects and/or tasks, resulting in delays elsewhere in the

enterprise Opportunity Missed Opportunities (acquisition and/or execution) or non-maximized profit.

Risk Incorrect Risk Grading or Pricing Collateral Valuation of Loan/Note place on Warehouse line Goodwill Degree of confidence by 3rd parties (Regulatory Agencies, Investor, Rating Agencies

Types of CostsTypes of Costs

3


4/23

www.Provansys.com

Current DataStructure and DataFlows

Manual Intervention

FirstAM Loan Perf.

data not alwaysconsistent

Formats are not

always consistent withABS Fields

Data variances

Data changes as itcomes from , FF and

HLS (Current UPB)

Data Standards

Formats

Rounding

Time Delay

Manual Interv. due to

resource availability &complexity of manual

intervention

Different points in timewhere data was

collected

Multiple Data Flows

FF Data goes directly

to Trade Desk

FF Data goes to Trade

Desk Via HLS

FF Data goes to TradeDesk via QC

4


5/23

www.Provansys.com

Option1

FederatedDataWarehouse

5


6/23

www.Provansys.com

MasterDataManagement (MetaandMasterData)

Master data (a.k.a. reference data) represents a companys business vocabularythe

business entities, terminology, definitions and classifications used to describe businessinformation. Master data provides the context for recording transaction datafactsabout events in the business

The management ofmaster (reference) data is crucial. Master Data is data aboutproducts, customers, collateral, the organization, geography etc and any common rulesand definitions for calculated data. This focuses on managing the taxonomy of commonbusiness definitions and descriptors.

Master data should ideally be stored separately from transaction data to allow fordiffering coding structures in disparate source systems and more easily accommodatechange in the master data (ie: recognize that master data is not static).

When it is properly and consistently managed, master data provides a consistentcontext within which business performance can be measured and monitored. It enablesmanagement of the link between disparate definitions, aggregation hierarchies and

mappings.

In the absence of well-managed master data, data warehouses deliver unreliableresults at the reporting stage, which reduces their credibility and value to the business.Also, as we noted, master data management is key to the successful design andoperation of a collection of linked data stores/warehouses.

6


7/23

www.Provansys.com

Option2

CentralDataWarehouse

7


8/23

www.Provansys.com

ComparativeAnalysis

Pros Cons

Federated Data

Warehouse

Centralized Master Data

Lower TCO

Increased LOB Flexibility

Local performance optimization

Leverage existing architecture andinfrastructure

Increased Efforts to manage Master Data

Need for Strong enterprise Data

Governance entity

Central Data

Warehouse

(Clean SlateApproach)

Centralized Master Data

Simpler Design

Low Entry Cost

Least duplication of Data

Higher TCO

Longer Implementation duration

Inflexible to change

Local vs Global tensions. business units,

each with their own local demands. Aligningthem to corporate needs and vice versa

continues to be a key challenge

8


9/23

www.Provansys.com

Federated DW - Option 1 Central DW Option 2

SCALABILITY High Medium

FLEXIBILITY High Low

PERFORMANCE High Medium*

COMPLEXITY Medium Low

RE-USE High Low

TCO Medium High

ALIGNMENT High Low

SCALABILITY: This metric pertains to the Scalability of the ML Resi DW proposed options in terms of how the DW will scale as data grows

over time

FLEXBILITY: This metric refers to the Design Flexibility in order to be able to accommodate multiple LOBs as well as overall Change

ManagementPERFORMANCE: This metric refers to the overall Performance of the DW with BI, Reporting and Analytics

COMPLEXITY: This metric refers to the Complexity of the Architectural Design, Overall Complexity of Data Flows & Processes

RE-USE: This metric refers to the degree to which existing DW/Databases can be used.For e.g. re-use ofFF DW data model to retro fit into

the overall ML Resi Data store

TCO: This metric refers to the Total Cost of Ownership for the ML Resi DW based on overall Software, Hardware & Service Costs

ALIGNMENT: This metric refers to the issue of Alignment between Corporate Needs (Cap Markets/Desk) vs. LOB (Origination/Servicing)

Operational needs.

ComparativeAnalysis

Green means Positive Attribute


10/23

www.Provansys.com

Reference Data Servicing Origination Risk/Performance

Borrower Information

Property InformationLoan Products Offered

Basic Loan Data- Loan Numbers (Svc,

Origandassociation ifapplicable)

- Key Loan Attributes(Loan Type, LoanProduct, Original LoanAmt, Term, FundingDate,etc)

History (Changestotheseprimaryattributes)

PaymentHistories

CurrentBalances(Escrow, UPB,Advances)

REO/DefaultManagement Status

ARM Change

CurrentCreditProfile

Original CreditProfile

(CreditScores, DebtRatios,etc)

Original InsuranceInformation (MI,Hazard, Flood, ARMChange

(Documentation Type,LTV, Term, Lien Pos.)

1008/1003 Data

Risk Ratings (S&P,

Fitch, Moodys,etc)

Loan Performance Data

Pool Information (CUSIP#,etc)

Investor Data

Pricing

Collateral Risk Exposure Visibility Pipelineand Inventory

Pricing Optimization (Competitive Analysis, Operational Capacity Measurements, CostModel) Pricing Elasticity Pricing Analysis

Data Segmentation

10


11/23

www.Provansys.com

Servicing Data Segmentation

Subject Area Data Points

Bankruptcy 387

Borrower_Info 42Claims 187

Collections 66

Delinquency 7

Escrow 181

Finance 129

Foreclosure 281

HR 5Investor 216

Legal 141

Loan_Core_Data 355

Loan_tracking 17

Loan_Workout 342

Loss 84

Payment_Info 31

6Payoff 86

Property_Info 171

REO 646

Repurchase_LoanSale 344

Risk_Management 18

Valuations 54

Current WCC Data Points

Risk/Performance Data

will contain

MISMO Servicing Data Sets* Investor Reporting ~100

Servicing Loan Set ~600

* MISMO Defines 3 Servicing Data Sets Investor Reporting

Servicing Loan Set

Servicing Transfer

11


12/23

www.Provansys.com

CoordinatedTeam

Cap Markets Team- BAs- ETL Developers- ML Resources

WCC Team- DA- BAs/PM- ETL Developers- ML Resources

HLS Team- DA- BAs/PM- ETL Developers- ML Resources

Provansys SME & Data Architect (NY) Tech Lead/PM (NY)

12

FF Team- BA- DA


13/23

www.Provansys.com

LeverageWCC DW Efforts

WCC data model for the Servicing DW

Reuse for HLS Integration

Reuse for Trustee integration

Reuse for Third party integration

Reuse DW Architecture and Infrastructure framework

Use the WCC model as a base for RESI DW model

Analysis of Data Requirements

Use as baseline for HLS

Use as baseline for Capital Markets servicing data requirements

Implementation of ARM Administration Data Mart Reuse Data Model for HLS

Meta Data Dictionary

Single Meta Data dictionary across RESI DW

13


14/23

www.Provansys.com

WorkStreamsandDeliverables

14

Implementation Stage Time Line

RESI DW Initial Analysis 3 Weeks

HLS Integration 3 Months

WCC Implementation 3 Months

Capital Markets Implementation 3 Months

FF Analysis 2 Months

SEP OCT NOV DEC JAN

Analysis Phase

Master Data Set Definition (Reference Data Points)

Workflow Rules, Validation and Change Propagation Specification

Gap Analysis of FF, HLS, WCC and External Data sources

Data Flow streams Clearly defining the data flow both from external sources into the

RESI DW as well as within the DW. Road map for Data Governance in the RESI DW space.


15/23

www.Provansys.com

Q4WorkstreamsandDeliverablescontd.

Capital Markets

DW Logical Schema for Enterprise Performance Data Set

Core Loan Data Model + Risk/Perf Data Model

MISMO Compliant Enterprise Performance Data Set

Data Integration of Critical Performance Data Set (FF/HLS/WCC)

External Data Sources Integration (Market Data/Trustee Data/LP First AM/outside Servicers)

15

HLS / WCC

Extend WCC Data model to integrate HLS Data

Analysis of HLS Data Sources and WCC Integration Plan

HLS Data Integration of Core Loan Performance Data (Fidelity/DAISY/MS Access )

Servicing Data Mart for primary loan servicing data set

WCC Physical Schema Implementation as designed in Phase1

WCC Prioritized ETL implementation as specified in Phase 1 (Not all ETL

implementation will be complete in Q4 07) WCC Prioritize and Implement Data Marts as specified in Phase 1 (Not all Data Marts

will be implemented in Q4 07) FF

Current state Gap analysis (Integration of FF into Resi DW)

Requirements Analysis


16/23

www.Provansys.com

CapitalMarkets

Phase 1- MISMO Compliance, Rules Framework, Schema Design and Addition of Critical Performance data fields

Gap Analysis of the Current State

Master Data Set MISMO Compliance of the entire enterprise performance data set

DW Logical Schema for the entire enterprise performance data set

Analysis of the basic loan level validation for the Critical performance data fields

Data Quality Rules Framework Design & Implementation

Data integration of the Critical Performance Dataset for FF/HLS, WCC, TRUSTEE

Migration of the existing data into the new schema

Enhancement for UI & Reports/Model (to be performed by ML resources)

QA/QC of the Rules Framework, loan level rules, data integration, enhancements, migration, enhancements (ML Resources)

Phase 2 Addition of the remaining Enterprise Performance Data set

Analysis of the basic loan level validation for the remaining components of the entire enterprise performance data set Implement data quality rules for the remaining components of the entire enterprise performance data set

Rules & Exception Framework Enhancements

Data integration of remaining Enterprise Performance data set for FF/HLS, WCC, TRUSTEE, LMS, CAS, etc

Migration (if any)

DW Model Optimization

Enhancement -UI & Reports/Model (if any) (ML Resources)

QA/QC of the Rules Framework enhancement, loan level rules, data integration, migration due to the addition of the remaining fields (MLResources)

Objectives

Analyze the entire enterprise performance data set,

Consolidate data from HLS, WCC and Trustees Identify Trends and Predict Performance of Loan Portfolio including pre-payment models..

Enhanced consolidated view of loan performance across all servicers and trustees.

Early warning indicators

Timely and accurate availability of data

16


17/23

www.Provansys.com

Implementation Stage Time Line

User requirements Analysis 8 Weeks

DB Modeling 8 Weeks

Physical Design. 6 Weeks

Technical Infrastructure 12 Weeks

Data Staging & Data TransferDevelopment

10 Weeks

BI development 10 Weeks

CapitalMarkets Implementation, Resources

& Budget

Implementation Stages & High Level Timelines - Phase 1

Budget Item Resources Budget

Phase 1*

Calendar Duration 16 Weeks

1 Lead Data Architect

1 Technical Lead/PM

1 Business Analyst

3 ETL Developers (2.5 Months)

1 BI Developer (Leverage Cognos or MicroStrategy)

Phase 2*Calendar Duration 20 Weeks

1 Data Architect1 Technical Lead/PM

1 Business Analyst

2 ETL Developers

Budget

Note: As there are some unknowns for the phase 2, in the mid or end of phase 1 the timelines and efforts for the phase 2 will bereviewed, which could revise the above budget.

W0 W4 W8 W12

17


18/23

www.Provansys.com

HLS/WCC Integration

Objectives Integrated view of both servicing businesses

Combine & Enhance Management/Investor reporting Single data warehouse and reporting platform

Increase technology ROI by utilizing WCC architecture and infrastructure

Phase 1 Analysis of HLS integration into WCC DW and initial core loan data implementation

Modifications to WCC data model to fit HLS Data

Analyze HLS data sources and provide integration solution plan

Create HLS Operational Data Store logical model

Analyze all data sources and create ETL strategy

Integrate Core Loan Performance Data from Fidelity/DAISY & Multiple MS Access Databases

MISMO compliant data transfer model (Will be reused to provide data to Capital Markets)

Data mart for primary loan servicing data set (~100-150 data points), e.g. UPB, next due date, delinquency data,

collateral attributes, borrower attributes, investor attributes etc.

Phase 2+ Implement Integration of all data sources

Complete HLS Data integration into DW.

Implement ETL for all data sources and subject areas

Implement ETL for all Data Marts identified

18


19/23

www.Provansys.com

Implementation Stages & High Level Timelines - Phase 1 (Duration 12 weeks)

Budget Item Resources Budget

Phase 1Calendar Duration 16 Weeks

1 Data Architect1 Technical Lead

1 Business Analyst/PM2 ETL Developers (2.5 Months)

Phase 2Calendar Duration 24 Weeks

1 Data Architect1 Program Manager

1 Business Analyst2 ETL Developers

Budget

Note: As there are some unknowns for the phase 2, in the mid or end of phase 1 the timelines and efforts for the phase 2 will bereviewed,which could revise the above budget.

Implementation Stage Time Line W0 W4 W8 W12

User requirements Analysis 8 Weeks

DB Modeling 6 Weeks

Technical Infrastructure 6 Weeks

Physical Design. 6 Weeks

Data Staging & MISMO DataTransfer Development

8 Weeks

Data Mart Development 8 Weeks

HLS/WCC Integration

19


20/23

www.Provansys.com

WCC DW Phase2

Objectives

- Complete implementation of DW specifications created in Phase 1

Deliverables- Physical Schema implementation as designed in Phase 1

- ETL implementation as specified in Phase1

- Full DW implementation

- DW model enhancements as necessary

Resources Budget

1 Data Architect

1 Business Analyst/PM

3 ETL Developers (Assumes 1 ML Resource)

Implementation Stage Time Line W0 W8 W16 W24

Data Mart Requirement Analysis and

Design

16 Weeks

ETL Implementation 16 Weeks

Physical Design and Implementation 18 Weeks

20


21/23

www.Provansys.com

Origination(FF & Nationpoint)Objectives& Phases

Objectives

Integrate Origination Data points into Resi DW

Revenue and sales growth Performance of new products

Cost and profitability analysis

Pricing and price sensitivity

Productivity, performance and turn times of employees

Relationship analysis with correspondents, channels, brokers and investors

Investor and Broker commitment variance

Pipelines, fallouts and expected production Pipeline history and trending

Risk and hedging analysis

Portfolio quality analysis

Loan conditions and resolution effort

Initial Tasks

Current state Gap analysis (Integration between FF & Nationpoint)

Requirements Analysis

Feeds and Reporting Requirements

Logical Data Modeling

21


22/23

www.Provansys.com

Origination(FF & Nationpoint)

Deliverables- Gap Analysis of current FF DW

- Integration plan with Nation-point

- Source System and Data requirements/availability Analysis

- OTHERS (TBD After GAP Analysis)

Resources Budget

1 Data Architect (Same as Lead Data Architect)

1 Business/Data Analyst

Implementation Stage Time Line W0 W4 W8

Current State Analysis 4 Weeks

Gap Analysis ofFF DW 4 Weeks

Source System Analysis 6 Weeks

Data Integration Strategy and

Plan6 Weeks

Logical Data Models TBD

22


23/23

www.Provansys.com

TheProvansysAdvantage

Single Team Approach

Single Lead Data Architect for all work streams

Leverage resources across LOBs Reduces cost

Knowledge sharing and common technical approach

Analysis with larger picture in mind

Reuse components and Data Structures across WCC, HLS and Capital Markets

MISMO Data Transfer Model (HLS, WCC, Trustee Data)

Data Quality Framework

Data Marts Across LOBs

Leverage knowledge gained at WCC to expedite analysis

Assign resources with WCC knowledge

Leverage Analysis performed within Capital Markets earlier in the year

Reuse methodology and documentation standards

Significant Relevant Industry Experience

Provansys derives significant amount of Revenues from Capital Markets, Mortgage & Retail Banks. Experience working with majority of Wall Street Banks as well as Traditional Banks (Lehman Brothers,

Wachovia Securities, Credit Suisse, Deutsche Bank, Washington Mutual, Impac etc)

Relevant experience in building DW and Business Intelligence Solutions for Mortgage Banking & Capital

Markets Clients including Lehman Brothers, Key Bank & Wachovia Securities

23

provansys mortgage dw roadmap

Documents