Extract Transform Load

by

Data Governance – Did you drop something?

Welcome to part 5 of Solvency II Standards for Data Quality – common sense standards for all businesses.

Solvency II Data Quality - Is your data complete?

Solvency II Data Quality – Is your data complete?

I suspect C-level management worldwide believe their organisation has controls in place to ensure the data on which they base their critical decisions is “complete”. It’s “applied common sense”.

Therefore, C-level management would be quite happy with the Solvency II data quality requirement that states: “No relevant data available is excluded from consideration without justification (completeness)” (Ref: CP 56 paragraph 5.181).

So… what could go wrong?

In this post, I discuss one process at high risk of inadvertently excluding relevant data – the “Data Extraction” process.

“Data Extraction” is part of the most common business process in the world, the “Extract, Transform, Load process”, or ETL for short. Data required by one business area (e.g. Regulatory reporting) is present in different (source) systems. The source systems are often operational systems. Data is commonly “extracted” from “operational systems” and fed into “informational systems” (which I refer to as “End of Food Chain Systems”).

If the data extraction can be demonstrated to be a complete copy – there is no risk of inadvertently omitting relevant data. In my experience, few data extractions are complete copies.

In most instances, data extractions are “selective”.  In the insurance industry for example, the selection may be done based on product type, or perhaps policy status.  This is perfectly acceptable – so long as any “excluded data” is justified.

Over time, new products may be added to the operational system(s). There is a risk that the data extraction process is not updated, the new products are inadvertently excluded, and never make it to the “end of food chain” informational system (CRM, BI, Solvency II, Anti-Money Laundering, etc.)

So… what can be done to manage this risk.

I propose a “Universal Data Governance Principle” – namely: “Within the data extraction process, the decision to EXCLUDE data is equally important to the decision to INCLUDE data.”

To implement the principle, all data extractions (regardless of industry) should include the following control.

  1. Total population (of source data)
  2. Profile of source data based on the selection field (e.g. product type)
  3. Inclusion selection list (e.g. product types to be included)
  4. Exclusion selection list (e.g. product types to be excluded) – with documented justification
  5. Generate an alert when a value is found in the “selection field” that is NOT in either list (e.g. new product type).
  6. Monitor the control regularly to verify it is working
So – ask yourself – Can you demonstrate that your “data extractions” don’t overlook anything – can you demonstrate that “No relevant data available is excluded from consideration without justification (completeness)”?
Feedback welcome – as always.
by

Solvency II mandates Data Governance

Welcome to part 3 of Solvency II Standards for Data Quality – common sense standards for all businesses.

Regardless of the industry you work in, you make critical business decisions based on the information available to you.  You would like to believe the information is accurate.  I suggest the CEIOPS’ standards for “Accuracy”apply to your business, and your industry, just as much as they apply to the insurance industry.  I would welcome your feedback…

The CEIOPS (now renamed EIOPA) advice makes it clear that Solvency II requires you to have Data Governance in place (which CEIOPS / EIOPA refers to as “internal systems and procedures”).   The following sections of the document make this clear:

3.32 In order to ensure on a continuous basis a sufficient quality of the data used in the valuation of technical provisions, the undertaking should have in place internal systems and procedures covering the following areas:

• Data quality management;

• Internal processes on the identification, collection, and processing of data; and

• The role of internal/external auditors and the actuarial function.

3.1.4.1 Data quality management – Internal processes

3.33 Data quality management is a continuous process that should comprise the following steps:

a) Definition of the data;

b) Assessment of the quality of data;

c) Resolution of the material problems identified;

d) Monitoring data quality.

I will explore the above further in my next post.  Meanwhile, what Data Quality Management processes do you have in place?  Do you suffer from common Enterprise-Wide Data Governance Issues?

by

How to deliver a Single Customer View

How to deliver a Single Customer View

How to cost effectively deliver a Single Customer View

Many have tried, and many have failed to deliver a “Single Customer View”.  Well now it’s a regulatory requirement – at least for UK Deposit Takers (Banks, Building Societies, etc.).

The requirement to deliver a Single Customer View of eligible deposit holders indirectly affects every man, woman and child in the UK.  Their deposits, large or small, are covered by the UK Deposit Guarantee Scheme.  This scheme played a key role in maintaining confidence in the banking system during the dark days of the world financial crisis.

UK Deposit Takers must not only deliver the required Single Customer View data, they must provide clear evidence of the data quality processes and controls they use to deliver and verify the SCV data.

The deadline for compliance is challenging.  Plans must be submitted to the regulator by July 2010, and the SCV must be built and verified by Jan 2011.

To help UK Deposit Takers, I have written an E-book explaining how to cost effectively deliver a Single Customer View.  You may download this free from the Dataqualitypro website:

While the document specifically addresses the UK Financial Services Requirement for a Single Customer View, the process steps will help anyone planning a major data migration / data population project.

If you are in any doubt about the need for good data quality management processes to deliver any new system (e.g. Single Customer View, Solvency II, etc.), read the excellent Phil Simon interview on Dataqualitypro about why new systems fail.

by

Common Enterprise wide Data Governance Issues – #12. No Enterprise wide Data Dictionary.

This post is one of a series dealing with common Enterprise Wide Data Governance Issues.    Assess the status of this issue in your Enterprise by clicking here:  Data Governance Issue Assessment Process

No Idea What This Means

Anyone know what this acronym means?

An excellent series of blog posts from Phil Wright (Balanced approach to scoring data quality) prompted me to restart this series.  Phil tells us that in his organisation, “a large amount of time and effort has been applied to ensure that the business community has a definitive business glossary, containing all the terminology and business rules that they use within their reporting and business processes. This has been published, and highly praised, throughout the organisation.” I wish other organisations were like Phil’s.

Not only do some organisations lack “a definitive business glossary” as Phil describes above, complete with business rules….
Some organisations have no Enterprise wide Data Dictionary.  What is worse – there is no appreciation within senior management of the need for an Enterprise wide Data Dictionary (and therefore no budget to develop one).

Impact(s):

  • No business definition, or contradictory business definitions of the intended content of critical fields.
  • There is an over dependence on a small number of staff with detailed knowledge of some databases.
  • Incorrect or non-ideal sources of required data are identified – because the source of required data is determined by personnel with expertise in specific systems only.
  • New projects, dependent on existing data, are left ‘flying blind’.  The impact is similar to landing in a foreign city, with no map and not speaking the language.
  • Repeated re-invention of the wheel, duplication of work, with associated costs.

Solution:

CIO to define and implement the following Policy:  (in addition to the policies listed for Data Governance Issue #10):

  • An Enterprise wide Data Dictionary will be developed covering critical Enterprise wide data, in accordance with industry best practice.

Does your organisation have an “Enterprise wide Data Dictionary” – if so, how did you achieve it?  If not, how do new projects that depend on existing data begin the process of locating that data?  Please share your experience.

by

Common Enterprise wide Data Governance Issues #11: No ownership of Cross Business Unit business rules

This post is one of a series dealing with common Enterprise Wide Data Governance Issues.  Assess the status of this issue in your Enterprise by clicking here:  Data Governance Issue Assessment Process

Business Units often disagree

I'm right, he's wrong!

Different Business Units sometimes use different business rules to perform the same task.

Withing retail banking for example, Business Unit A might use “Account Type” to distinguish personal accounts from business accounts, while Business Unit B might use “Account Fee Rate”.


Impact(s) can include:

  1. Undercharging of Business Accounts mistakenly identified as Personal Accounts, resulting in loss of revenue.
  2. Overcharging of Personal Accounts mistakenly identified as Business Accounts, which could lead to a fine or other sanctions from the Financial Regulator.
  3. Anti Money Laundering (AML) system generates false alerts on Business Accounts mistakenly identified as Personal Accounts.
  4. AML system fails to generate alert on suspicious activity (e.g. large cash lodgements) on a personal account misidentified as a Business Account, which could lead to a regulatory fine.
  5. Projects dependent on existing data (e.g. AML, CRM, BI) discover that the business rules they require are inconsistent.

Solution:
Agree and implement the following Policy:  (in addition to the policies listed for Data Governance Issue #10)

  • Responsibility for resolving cross business unit business rule discrepancies lies with the Enterprise Data Architect.

For further details on Business rules – see Business Rules Case Study.

Your experience:
Have you faced a situation in which different business units use different business rules?   Please share your experience by posting a comment – Thank you – Ken.

by

My interview with Dylan Jones

Dylan Jones of DataQualityPro interviews me about the process I use to assess common Enterprise wide data issues. Use this process to assess the status of data governance within your organisation or that of a client.

Data Quality Pro interview with Ken O'Connor Data Consultant

by

Russian Gas Pipe and Data Governance

As you know, Russia supplies Gas to many European countries.

What's flowing through your critical data pipelines?

Do you know what’s in your critical data pipelines?

Could you imagine Italy purchasing gas from Russia without checking what exactly was flowing through the pipe?  I’m no expert on gas pipelines, but I know that before completing the agreement to purchase the gas, Italy and Russia would have agreed metrics such as:

  • Volume of Gas
  • Calorific value (Energy content)
  • etc.

So what? What else would one expect?  Applied common sense… yes?

Why is it that such common sense is often lacking in Data Migration and Data Population projects?  Why do some Enterprises continue to perform data population of, and ongoing data entry to, critical data repositories without fully understanding the data they are pumping into the repository?

A simple example involves Date of Birth.  The business ask the IT function to populate Date of Birth in the new AML / BASEL II / CRM / other repository. Some time later, when data population is complete, the business begin to express concerns:

  • “We never realised we had so many customers aged over 100 ???”
  • “I thought we had more Student customers”
  • “How come so many of our customers share the same birthday ?”
  • “These are not the results we expected”
  • etc.

Performing data population on the basis of what the source data “should contain”, without analysing what exactly it does contain is known as ‘Load and Explode’ approach to Data Population.  I cover this Enterprise Wide Data Issue in more detail here.

We in the “Data Governance”, “Data Quality” industry need to educate the business community on the “common sense” parts of data governance, and the need to engage “Data Governance Professionals”  to ensure that “Data Quality Common Sense” is actually applied.

Feedback welcome – Ken

by

Business Rules Case Study Part I

I would like to start a discussion about Business Rules.  I hope you will join in.  Over a series of posts I plan to explore questions like:

  1. Why are Business Rules necessary?
  2. What exactly is a Business Rule?
  3. What should happen if the data fails a Business Rule?
  4. What controls should you have in place to manage Business Rules?
  5. Where should you look for Business Rules (if your Enterprise has no Master Business Rules Repository)

I will use a case study from an Anti Money Laundering (AML) programme.

In this AML programme, the client selected a “Best in breed AML vendor solution”.   The vendor specified the data required, and the client was responsible for locating the data to populate the new AML repository, and for the quality of the data entered in the repository.

Why are Business Rules necessary?

A standard AML business requirement is the requirement to monitor “Minor Accounts” (accounts held by customers under 18 years of age) for ‘unusual transaction activity’.  This high level requirement would result in a number of more specific business requirements, such as:

“Generate an AML alert when the total value of Cash lodged in a month, to an account held by a minor, exceeds a predefined amount, say EUR5000”

Having  agreed the above business requirement, the vendor asked the client to provide the Business Rule for identifying a ‘Minor Account’.

So:

1. Why are Business Rules necessary?
Business rules are required to distinguish between product types, customer types, car parts etc. etc.  AML systems require business rules in order to apply different alert rules to different account holder types.

AML business staff are AML experts, not business rules experts.  It was unclear who owned the data and it took a long time for the IT department to research the business rule(s) for the vendor.  Q:  How do business users in your enterprise get details of Business Rules?  Do your business users find it difficult to access the data they require?

Let us suppose the Business Rule supplied to the vendor was:
A minor account may be identified as follows:
1. Account Type: Personal
2. Account SubType:  Minor
3. Customer Age:  Less than 18

The age check was required to manage the risk that an account opened when a customer was a Minor was not converted to a Standard Personal account when the customer reached his/her 18th birthday.

So:

2. What exactly is a Business Rule?

A Business rule provides critical details about data, including the ‘business’ name of the field, the business purpose of the field, the values it may hold, the business meaning of each value, and interdependencies with other data.  Let’s explore this a little further:

  1. Business name of the data field(s):
    In the above example, three data fields are used in the Business Rule:
    ‘Account Type’, ‘Account Subtype,’ and ‘Age’ (probably determined from Date of Birth).’
  2. Business purpose of the data field:
    e.g. ‘Account SubType’ is used to identify different account types, such as ‘Minor’, ‘Mature years’ etc.
  3. Permitted values (also known as enumerations):
    e.g. Permitted values for Account Subtype are 101 to 199.
  4. Business meaning of each permitted value:
    e.g. ‘Account SubType’ value 101 means Minor Account
  5. Interdependencies with other data:
    e.g. ‘Account SubType’ depends on ‘Account Type’
    ‘Account SubType’ value 101 means Minor Account, when Account Type is ‘Personal’
  6. Field precedence:
    This defines the order in which the fields should be interrogated
    e.g.  First check Account Type, then Account Sub Type

The AML vendor configured the AML tool to apply the “MINOR” rule when Account Type was personal, Account SubType =101 (Minor), and Customer Age less than 18.

During testing, few alerts were generated on Minor accounts.  From an AML business perspective, the less alerts generated the better, since the workload for the AML staff is dictated by the number of alerts generated.

The AML business area was pleased with the low number of alerts, and the vendor was pleased that the alert worked ‘as specified’.

However, it was common knowledge that Date of Birth was not populated 100% of the time, so what was happening when there was no Date of Birth present?  There was no culture of  data quality measurement in the Enterprise, and no facilities for data profiling. Custom built SQL queries against the new AML repository identified multiple instances in which the actual data failed to conform to the Business Rules.

So:

3. What should happen if the data fails a Business Rule?
In our AML example, what do you think should happen when:
a) Account Subtype is ‘101’ indicating a MINOR account, but the customer is aged over 18?
b) Account Subtype is ‘101’ indicating a MINOR account, but date of birth is not populated for this customer?

Business Rules define what data fields “should” contain.  On this AML programme, as in all real world data programmes, the actual data content did not always match what was expected.

This only became apparent as a result of custom built data profiling.  Based on the actual content of the data, the AML business area had to ask the vendor to implement Exception Rules to handle the non-conforming data.  In an ideal world, the data would have been corrected.  In the real world of “achieve compliance by a given date, or face a regulatory fine”, workarounds are quite normal.

So – what are Exception Rules?
Exception rules define what must happen when an account contains data that fails to comply with a business rule.

This post is already far longer than I had planned – I hope it hasn’t bored you to tears.
In my next post, I will explore:

Please share your experience by posting a comment – Thank you.

by

Common Enterprise wide Data Governance Issues #10: No ‘Master’ repository of business rules

This post is one of a series dealing with common Enterprise Wide Data Governance Issues.  Assess the status of this issue in your Enterprise by clicking here: Data Governance Issue Assessment Process

Business rules provide critical details about data fields, including the ‘business’ name of the field, the business purpose of the field, the values it may hold, the business meaning of each value, and interdependencies with other data.

An example of a business rule could be ‘Account Type must be consistent with Account Fee Rate, both must be BUSINESS, or both must be ‘PERSONAL’.  Such a business rule would be critical on an Anti Money Laundering Programme, where you must apply different alert rules to personal and business accounts.

In some organisations, there is no ‘Master’ repository of business rules.  Business rules are not easily researched, not formally documented, and not subject to change control.

Impact: Projects dependent on existing data must research business rules governing that data from first principles, and face the risk of not finding them, or finding inconsistent business rules.  This leads to duplication of effort, project delays, and the risk of making incorrect business decisions based on incorrect business rules (e.g. generating False Anti Money Laundering Alerts on accounts you treat as PERSONAL, when in fact they are BUSINESS.)

Solution:
Agree and implement the following Policies:

  1. Overall ownership for business rules governing data within the Enterprise lies with the CIO.
  2. Ownership for business rules within each Business Unit lies with the CIO and the head of the Business Unit.
  3. Business rules must be formally documented and subject to change control (Enterprise-wide, and Business Unit specific).
  4. The CIO must appoint a person (TitleX) with responsibility for Enterprise wide business rules.
  5. TitleX is responsible for the definition and maintenance of Enterprise-wide business rules, in consultation with business units.
  6. TitleX must provide a single point of contact to handle requests for business rule details.

Your experience:
Have you faced the above issue in your organisation, or while working with clients?  What did you do to resolve it?  Please share your experience by posting a comment – Thank you – Ken.

by

Common Enterprise wide Data Governance Issues #9: Data Migration and ETL projects are Metadata driven

This post is one of a series dealing with common Enterprise Wide Data Governance Issues.  Assess the status of this issue in your Enterprise by clicking here: Data Governance Issue Assessment Process

Too often, Data Migration and ETL projects are built on the basis of Metadata, without measuring what is actually contained in the source data fields.  This happens when the IT function build data ‘pipes’ on the basis of what the metadata says the source fields should contain, and don’t perform data content analysis, or data profiling, to find out what the source fields actually contain.

Impact:
The IT function turn the  ‘tap’ on, the data flows through the ‘pipes’ and the business express surprise, followed by denial, when expectations cannot be met due to data quality issues.  This is known as the ‘Load and Explode’ approach to data.

Solution:
To prevent ‘Load and Explode’ impacting the success of your data dependent projects, agree and apply the following policy:

Before building, or purchasing a system that is dependent on existing data, projects must complete the following process:

  1. Define what data is required.
  2. Define the quality requirements of the required data.
  3. Identify the source of the required data.
  4. Specify the data quality metrics to be captured.
  5. Measure the quality of the available source data.
  6. Understand the implications of the quality of available source data for the proposed system.
  7. If necessary, and if feasible, implement data quality improvement measures to raise the quality to the required level.
  8. Worst case – if the facts tell you data quality is too low and cannot be improved – Cancel the project and save yourself a ton of money!

Your experience:
Have you faced the above issue in your organisation, or while working with clients?  What did you do to resolve it?  Please share your experience by posting a comment – Thank you – Ken.