Download - Provansys Mortgage DW Roadmap
-
8/8/2019 Provansys Mortgage DW Roadmap
1/23
ML RESI DATA WAREHOUSE
ROADMAP
RESI DATA WAREHOUSE
ROADMAP
-
8/8/2019 Provansys Mortgage DW Roadmap
2/23
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
5/23
www.Provansys.com
Option1
FederatedDataWarehouse
5
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
7/23
www.Provansys.com
Option2
CentralDataWarehouse
7
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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.
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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
-
8/8/2019 Provansys Mortgage DW Roadmap
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