Slide 1 - Data Migration Matters

Document Sample
Slide 1 - Data Migration Matters Powered By Docstoc
					Data Migration Matters 4

Lloyds Banking Group

        Rob Weir
  Corporate Markets Risk

                           26th May 2011
Presentation Content

     Background

     Pre-Migration – Data Cleanse Project

     Data Cleanse Lessons Learned

     Post-Migration – Customer Hierarchy

     Customer Hierarchy Lessons Learned

Background - LBG

   Lloyds Banking Group (LBG) formed January 2009 through
    acquisition of HBOS by Lloyds TSB Bank

   Largest Integration project in Europe to create one of the
    “strongest and safest financial services companies in the

   Expect £2bn of savings through integration by end of 2011

   Renewed focus on Risk Management

   Integrated systems and higher data management
    standards a key pillar in Risk Management (Integrated
    Credit Risk System)

 Background - Data Management Maturity Level

 Davenport and Harris (p.173) recommend that aspiring data driven organisations “must
 be committed to the creation and use of high-quality data that is scalable, integrated,
 well-documented, consistent, and standards based.

                 Data Management Maturity Level

 UNDISCIPLINED - Organisations use their data
 “tactically” and executive lack awareness of the value of
 data as a commercial asset

 REACTIVE - Data management exists and tools are                                             Estimated Level of Data Management Maturity:
 available to “fix” data problems but there is an absence                                                Lloyds TSB Bank &
 of cohesion across business areas with a lack of
                                                                                                      Bank of Scotland Corporate
 strategic vision

 PROACTIVE - Executives are “beginning to view data
 as a strategic asset.” Data monitoring is constant and
 “goals shift from fixing problems to preventing them.”

 GOVERNED - Data drives the organisation because
 business decisions are based upon trusted data. Good
 data management practices prevail and are funded

Davenport, Thomas H. and Harris, Jeanne G. (2007). Competing on Analytics: The New Science of Winning. USA: Harvard Business School Publishing Corporation.
Fisher, Tony. (2009). The Data Asset: How Smart Companies Govern Their Data for Business Success. USA: John Wiley & Son.
  Pre Migration – Data Cleanse Project

OBJECTIVE: To cleanse key risk data attributes from each heritage’s credit
management systems prior to migration of data to new Integrated Credit Risk System

Data Cleanse Criteria:                                         Migration Approach
  „Touch‟ all in-scope attributes at least
   once                                            BoS
                                                                    Bank of
  The data had to be checked                                         Hub

   independently by three data cleanse                                                 Target
   analysts (2-4-6 eye checking procedure)                           Lloyds
                                                                    TSB Bank
  Systems had to be scalable and                Lloyds’              Hub
  Operations had to be capable of being
   off-shored to ensure low operational                    Initial Team Structure
   costs                                                             Director,
                                                               Risk Infrastructure &
  Find key stakeholders and set up                                   Control

   Working Group
  The pilot project would be finished by                        Senior Manager,
                                                                   Data Utility
   the end of June 2010
                                                                    Risk Data

Data Cleanse Project - Scope

Cleanse key risk attributes:

 Counterparty Data
     De-scoped due to complexity and managed by a separate team

 Ratings Data (Golden Source: Risk Rating System) – credit risk ratings that
  indicate the likelihood of default
     Probability of Default (pre and post adjustments)
     Key rating dates

 Facility Data (Golden Source: Facility Docs) – data that relates to:
       Products
       Term
       Interest rate margins
       Currency of loans
       Lending limits

                (Number of attributes and records to be cleansed varies from system to system)

   Data Cleanse Project – Process
Data cleanse has two distinct components:
1. DataFlux - ETL, Business Rule Manager, Reporting
2. Workflow

                             Management & Reporting

Data Sources                   Data Cleanse Application                         Data Cleanse                             Process
                                                                                                                         1. Application pre processing
                                                                                    Check /                              to prepare data for the data
                                                                                              6                          cleanse environment
                                                                        5                                                2. Data cleanse application
                   Apply Business                                                                                        reads data and applies data
    Files              Rules                      Staging Area                       4 Eye    7                          quality business rules
                                                                                                                         3. Events recorded in audit
                                  2               Status: Check /                                                        4. Application outputs data
               1                                  Edit / Approve                     6 Eye    8                          and data quality exceptions
                                                                                                                         5. Data to be checked passed
                     Files                                              10                                               to workflow
                                         4          Status: Ready                                                        6. Manual checks and edits
                                                                                  Reporting          11                  applied then set to „complete‟
                                   3                                                                                     7. 4 eye check – if approved,
                                                    Status: Issue               Remediation        Update       Source   set to „complete‟
                       Audit                          Detected                    reports                       System   8. 6 eye check – if approved,
                                                                                                                         set to „complete‟
                                                                                                                         9. Issues requiring special
                                                                                                                         attention managed by Team
                                                                             Special Case Review                         Lead
                                                                                                                         10. Data to be corrected
                                                                                                                         appears in remediation report
                                                                                                                         11. Source system updated
                                                                                Special Review

                                  Audit & Control

Data Cleanse Project - Workflow

    Data Cleanse Project – Achievements (1)

 Data Quality Discoveries:

                System 1 - 13% of ORG ratings were incorrectly captured
                                                                                                   +ve RWA Impact of £300m (Sys 1)
Rating Data
                System 2 – 88 expired ratings (as at Dec 2010) identified and            Impact
 Checks:                                                                                           +ve RWA Impact of £269m (Sys 2)
                re-rated by Feb 2011

                System 1 (Business Area A) –
                28% of Facility Types routinely captured incorrectly
                                                                                                   Business processes changed
Facility Data   22% of Net Limit Amount values „Null‟ incorrectly interpreted as „Nil‟            and training initiated
  Checks:       System 2 (Business Area B) –                                                       Business project started to
                                                                                                  standardise Facility Types
                11% of Maturity Dates either incorrectly captured or out-of-date
                12% of Gross Amount values do not match facility documentation

 Data cleanse enabled one business area to sell £1bn of loans to the market

 Data Cleanse Service:
 Funding being requested to accommodate requests from different business areas to
 cleanse their data
 Data Cleanse Project – Achievements (2)

Successful Data Cleanse System:

                             Operational offshore
                             Quick to develop
                             Ease of use
                             Strategic          Based upon DeLone & McLean‟s Information System Success Model

 Data Cleanse - Lessons Learned

 Be realistic with the scope
 Analyse the data – it tells you everything that you need to know
 Data Cleanse system must be robust (beware of Excel champions)
     Users appreciate structure and simplicity
     Structure leads to quick processing
     Don‟t underestimate the power of reporting
 Trust – Trusted sources, Trusted associates
 Politically charged affair – become the data quality champion
 Keep business colleagues close (buy-in) – remediation is key
     Processing the data is quick but remediation is slow
 Don‟t overlook training
 Plans are in place to set up a new data quality team
 Post-Migration – Customer Hierarchy
Separate project set up to establish business and credit hierarchies due, which will
eventually be mastered in the new Integrated Credit Risk System.

                     Legal Hierarchy                    Credit Hierarchy

                               Subsidiary –             Subsidiary –
                                 Level 1                  Level 1

                                      Subsidiary –                Subsidiary –
                                        Level 2                     Level 2

                         Subsidiary –                             Subsidiary –
                           Level 1                                  Level 2

                                      Subsidiary –
                                        Level 2

                                      Subsidiary –
                                        Level 2

Customer Hierarchy - Issue

                 Lloyds TSB Bank                 Bank of Scotland
                  Counterparties                  Counterparties
                (Various Systems)               (Various Systems)

                           Integrated Credit Risk System
               Legal Hierarchy                  Credit Hierarchy

                          Subsidiary –          Subsidiary –
                            Level 1               Level 1

                                 Subsidiary –             Subsidiary –
                                   Level 2                  Level 2

                    Subsidiary –                          Subsidiary –
                      Level 1                               Level 2

                                 Subsidiary –
                                   Level 2

                                 Subsidiary –
                                   Level 2

 Customer Hierarchy – Post Migration Analysis

   ICRS                Groups - 6,002 (17%) potential duplicates among groups
Counterparty           Subs – 10,668 (32%) potential duplicates among subs                                          Impact      Currently unable to master
Data Checks:                                                                                                                     counterparties
                       Branches – 3,552 (73%) potential duplicates among branches

                                                                                   DATA INTEGRITY ISSUES

        DATA INTEGRITY                 DATA ACCURACY                                                                   DATA COMPLETENESS          DATA TIMELINESS
                                                                     DATA CONSISTENCY              DATA VALIDITY
     Counterparties paired via       Omission of „Ltd‟ indicates                                                       Matching counterparties   Is the SIC code up to
                                                                   Same post code different         CRN Missing
       automated matching             poor data standards /                                                             do not share the same            date?
                                                                   city and legal names differ
           techniques                 inaccurate data entry                                                                   SIC Code

  Counterparty_Name              CP_Id   Post_Code    City_Town                       Counterparty_Legal_Name       CRN Domicile_Country SIC_Code Legal_Type
 ABCPRODUCTS                     36694   WR11 4TU WORCESTERSHIRE                     ABCPRODUCTS LIMITED           836010 UNITED KINGDOM >UNKNOWN   Group
 ABCPRODUCTS LTD                 24028   WR11 4TU EVESHAM                            ABCPRODUCTS LTD.                     UNITED KINGDOM    1610    Group

                                     NOTE: 1st draft counterparty matching results were produce in 1 1\2 hrs

 Customer Hierarchy – Lessons Learned

 Analyse the data – it tells you everything that you need to know
 Invest in and exploit data quality application
 Establish your governance structures ASAP
 Get data people – don‟t assume that “it‟ll be ok”
 Test the migration processes
 Reconcile the data between source and target
 Spread the workload i.e. engage the commitment and buy-in of all
  key stakeholders

  DataFlux Summary

The following describes how DataFlux‟ functionality has been used to meet data quality and data
management challenges in Risk Infrastructure & Control projects.

 Data Cleanse Project:
 Data Profiling – In-scope attributes profiled to reveal data ranges, formats, null values, etc
 ETL – Used as a generic ETL tool for Ad Hoc data manipulation, application of data quality business rules and
 loading results into the workflow tool for checking

 Business Rule Manager – Create and store data quality business rules that determine what attributes require

 Data Standardisation – On occasion, standardisation techniques used to avoid unwanted data mismatches e.g.
 UK vs GB, Ltd vs Limited

 Audit Repository – All data quality exceptions, actions and events are recorded and stored in the audit repository.

 Data Quality Dashboard:
 Crisp Ratings – Ratings data consolidated and presented in HTML report format, which can be distributed to

ICRS Matching Model:
Counterparty Matching – Matching model built using fuzzy logic (including phonetics reduction) to detect
potential duplicate counterparties in ICRS.


            Any Questions?

              Thank You!

Shared By: