|
NOTE- This template is part of the paid Data
Quality Package- the most comprehensive online data quality management tool-Kit. This page provides a sample view of the usage of this
template.
For Buying this template and the entire Data Quality Package, please Click Here
OVERALL USAGE GUIDE
What is the purpose of Data Mapping and Assessment WBS?
DMA WBS provides you with the list of activities that you will do in a DMA exercise. You can use this list of activities to create a project plan for DMA.
When is it used?
This work-tool is used, through-out the DMA exercise.
What it is not?
The WBS does not contain the typical project management tasks, like:
- Arranging skills, resources.
- Establishing testing infrastructure.
- Release management
- Version Management
We assume that every organization has a defined methodology for project management and they will be picking the standard tasks out of that methodology.
Who uses it?
This work-tool is used by the DMA project manager.
Linked work-tools
-----------------
HELP-GUIDE
Data Mapping and Assessment Initiation
- Data Mapping and Assessment Objectives and Trigger: This step generates high level of objective linked with DMA. The trigger could involve data warehouse initiatives, a major data quality program, Data Migration initiative, Business Process Re-engineering initiative or a need to audit comment or a regulatory compliance issue
- DMA Scoping: Identify the target systems: This is fairly straight forward. Depending upon the trigger of the DMA, you can identify the systems and databases which you will include in DMA. For example, if the DMA is being done for designing and scoping the ETL of a Data Mart, you will include all the ETL source systems as part of your DMA. However, with in these source systems, you may like to focus on the customer and related databases, as the Data Mart is for customer value and profitability analysis
- Identify the target tables: You need to identify the tables are the components of the Databases, which you will target. Depending upon the trigger and objective of the DMA, the level of targeting would change. For example- if the purpose of DMA is to 'assess the health of data in Field Servicing system', it will mean the entire database of the system. if the purpose is to assess the customer data in field systems, you will exclude the products data, servicing agent data, sales agent data etc.
- Identify Target Data-Groups: Typically the DMA, if triggered by a data quality issue, is generally targeted to resolve data quality issues with certain data groups. For example, Customer Data Quality Issue, or Reconciliation issues with Travel business accounting transactions... As you list down the Data-Groups, do mention the specific Data-Groups which are targeted to be fixed via this DMA. As a side not, DMA will not fix the data issues. It will primarily identify the current state of the data and contribute to the Root-Cause Analysis.
- Identify the DMA task (single column, multiple-column, data model...): All the previous points are on the entities involved in DMA. The other part of the scope is around the extent to which you will do the DMA. For example, three main are areas of DMA are- Single Column analysis, Multiple Column analysis and Data Model Analysis. You may like to do just column & table analysis, in case you are just looking for distribution of accurate vs. inaccurate data. One will finally come out with a matrix on what DMA activity you want to do on which system, tables or data-group.
- Formation of DMA approach: This is essentially the work-plan for DMA. Depending upon the availability of resources, skills, systems. The approach is essentially on the following factors:
- Sequence
- Offline vs. online DMA (doing DMA on production environment vs. doing it on offline replica)
- Grouping of DMA activities
- DMA hierarchy: Sometimes you stop the DMA activity at higher level, if the value distribution is as per expectations. This part of the approach includes the drill down actions you will do, if the summary level DMA shows certain exceptions.
- Formation of DMA core team with roles and responsibilities: Core team will be essentially consisting of the business and IT specialists linked to the identified Data-Group, System or process. (Refer Data Management Stake holding and Responsibility Matrix)
- Acquisition of DMA tools: If you have identified any DMA tools (Like Data Profiling Tool, Data Monitoring tools etc...). This means that you are not managing your DMA by running 'strong-arm' queries).
- Publish DMA plan and Roll-out and Communicate DMA plans: This will be the conventional closure of a DMA initiation phase.
Data Mapping and Assessment Analysis and Design
Single Column Analysis
- Identify the system tables: At this stage you finalize the list of systems tables, which you will be using for single column analysis.
- Identify the individual fields to be assessed: This task will be listing the fields you will be assessing.
- Identify the categories to be assessed: This includes the categories of DMA, which you will be applying. For example data format, Data Value, Domain Constraints etc. As a note, you need not do all DMA checks on a field. For example, in case of PIN-Code field, you may just check the data format and do not check Null or Not Null. This is because; the field is an optional field.
- Identify the volume to be assessed: DMA need not be run the entire volume set, and some time one goes with the principle of sampling. For example, you may take a sample of 200000 customer records, out of 5 million records that you have.
- Identify and document the quality standards and quality expectations: This is little difficult task as it needs business commitment. There are two components in this task:
- Quality Standards: This means an ideal data quality which you expect in the single column analysis.
- Expectations: This means the expectation of data quality in the current state of data.
For example- You quality standard for PIN-Code format of ZIP+4+2 (in US) is to have 100% of the PIN codes to follow the format. You expectations however might be only 90%. The expectations are governed by your awareness of past data quality issues or lack of controls. The quality standard is the reference against, which the Gap is quantified. One has to note that quality standards are driven by business need and does not represent an ideal world. Therefore, quality standard for Customer address could be 95%. This could be because business is aware that there will always be a faulty address given by a customer (like wrong house number or street name).
- Design the analysis scripts or configuration (if you are using a DMA tool): This task is to essentially create DMA scripts or configure DMA tools (if any) to execute the DMA for single column analysis.
Multiple Column Analysis
Multiple column analysis has the complexity as it deals with multiple fields, multiple tables and multiple systems.
- Identify the target systems: The purpose of this identification is to start the dialogue and get the right kind of resources on the table for analysis and design. This is already done to a great extent in the initiation phase. However, at this stage one is able to identify on what all you are going to be doing on that system, in much more detail. In the initiation phase, I would simply have the list of the systems and high level list of tables and activities which one will do on them. In the Analysis and design phase, the list of systems may stay the same but one will list the activities in much more detail.
- Identify target tables: This gets the list of the target tables for DMA. The tables for the time being will be listed against each system.
- Identify the target fields: This is the next level, where you identify the fields within tables which will be under review.
- Specifications for completeness assessment: This task lists all the completion criteria which you want to assess, and the scripts.
- Specifications for business rule validation criteria and create DMA scripts and DMA configuration: These are the business rules, which you want to validate. These include mainly the consistency rules for the data.
- Specifications for master to master synch criteria and create DMA scripts and DMA configuration: These validations will check on matching of the same data-group across multiple systems. For example- Matching the Vendor master in account payable table and purchase management table.
- Specifications for Master to Transaction synch criteria and create DMA scripts and DMA configuration: This can be called more of a data model check. This validation is for checking if the transaction tables have referential integrity with Master Table.
- Develop DMA scripts or configuration: Develop the scripts or configurations (for DMA tools).
- Identify and document the quality standards and quality expectations: Define the quality standards (what business needs) and the expectations (our expectation of the current state of data, given our awareness of the data quality issues and state of DQ controls.
Data Model Analysis
Specification for Cardinality/Referential integrity: We assume that readers are aware of the data modeling, as this is part of Data modeling terminology.
- Specifications for Hierarchy path validation: This task will define the specifications of how can one navigate from the lowest level in a hierarchy path to the highest level, for all distinct instances. For example, if in a customer master table, you have a field called customer_city, you should be able to reach to the region (a group of countries) to which that city belongs. In this process you will be able to travel from Customer_Master to City_Master to State_master to Country_master to Region_master. The validation will be successful if for all the cities found in the customer master, one is able to reach to their region.
- Specifications for Insert Rule Validation: There are two components to it. One is that you simply assess insert rules from the programs and data base triggers (data base triggers are the actions which automatically happen as you insert, update or delete the records in a table). Second part is to check by reviewing the data, on if it has followed the insert rules. Ideally you will start by looking at the programs followed by looking at the data.
- One may ask a question- Why data will behave differently from what the programs are doing. If a program or DB trigger is following certain insert rule, there should not be any scenarios that Data will bypass that rule. The answer to this question is- it is possible to bypass. These kinds of situations happen when:
- You correct data from the back-end.
- Data migration.
- Sometime an ad-hoc program or application sits on the data base, without using the core programs, which are expected to ensure the adherence to these insert rules.
- Specifications for Update Rule Definition: Same as above
- Specifications for Delete Rule Validation: Same as above
- Identify and document the quality standards and quality expectations: Define the quality standards (what business needs) and the expectations (our expectation of the current state of data, given our awareness of the data quality issues and state of DQ controls) for every validation that you are running.
DMA Scripts and Program Testing
Some of the scripts for DMA can be fairly complex, especially the multi-column analysis and Data Model Analysis. Test planning for DMA script is a creative challenge. A DMA exercise could involve hundreds of validations and sub-validations (for example, if the PIN-CODE is not following the standard format in 45% of the cases, drill down to find out on the city-wise distribution of this fault).
Data Mapping and Assessment Execution
This is self-explanatory. As shown in the WBS, one will be progressing from single-column to data-model to multiple-column DMA. You can use Data Mapping and Assessment Management work-tool to record the results.
Gaps will always be with reference to the quality standards needs of the business, and not on the basis of what you expected to the current state of data. The 'expectations' column is mainly to throw out 'surprises'.
Once the execution stage for each kind of DMA category (single, multiple and data-model) is over, one will need to look at the entire picture and give it a final shape before reporting it to the stakeholders.
Data Mapping and Assessment Reporting & Follow-up
- Agree on the next steps with the stakeholders: After DMA report is published, the next steps are agreed, documented and DMA is formally closed.
FAQ-
Can one mix and match the sequence of DMA, given the availability environments?
yes, you can. This however may place an extra load on going back and re-doing some of the work, in case you get unexpected results. Our recommendation has always been to do the DMA on offline database as much as possible. You need the access to the environments (programs and Databases), for doing DMA as well as some level root cause analysis.
We have submitted the DMA report. It has been over one month, since we are waiting to hear the response from our audience. What should we do?
You can do one of the following:
- If your DMA is telling that there is no major issue, which requires attention and the health of the data is in acceptable range, you may give more time to your audience. It is possible that your audience have read the report.
- If DMA has thrown up some key issues, which need immediate attention- one can escalate. It is better to use the gravitas of data steward, CFO and head of internal control and head of internal audit.
- We always recommend for you to keep your key stakeholders in loop on the progress and share with them any major data gaps you are observing. This builds a sense of anticipation.
As we go through the DMA exercise, we identified a major issue, which should be dealt immediately. Should we wait for the DMA to be over (so that we can get a more holistic view of the issue), or work on this issue for resolution?
This is a purely situational matter. If you have discovered a critical issue, look at its impact. Is it a dormant impact, or is the impact escalating by the day. Our recommendation will be take it to the right stakeholders immediately and get their inputs. On the contrary, avoid having too many of such situations, as it may detract from the main DMA exercise.
|
