DQ Product Usage Methodology Highlights_v6_ltd

DQ Project: DQ Tool Usage Highlights
DV
E) dvsingh@aninfosys.com M)+31629014650

These features form the basis for the success of overall Project :
– DQ Methodology
– Desired Special Product Features
– GxP Compliant Operational Model
– Business Friendly Reporting Model

Vision for DQ Project:
- Initial Phase: Manual update of Source Systems by DMM (Data
Stewards)
- Future: Auto Update of Source Systems on case basis

Analyst
Business
Users, Analyst
Developer &
Architect
1. Profile &
Discover
2. Define & Design
Metrics and
Rules
3. Design &
Implement Rules
and Routines
4. Manage
Exceptions
5. Monitor
DQ Metrics
DMM
BPM /
SuperUser
Role-Based Data Quality Process
Managers
Project
Dash
board

DMM
- Bad records /
exceptions mgmt
- Duplicates mgmt
Business
Analysts
- Profiling
- Business Rules
- Mapping specification
- Reference Tables
- DQ scorecards
- Business Glossary
Work Aspect Matrix

• (4) Exception/ Bad Records Handling/Creation
• (22) Business/Human Tasks Creation for Bad Records
• (22) Business/Human Tasks Workflow Management
• (22) Working on Bad Records/ Task items using Web UI
• (11) Security & Authorization around Tasks / Records viewing

 DMM can get easy view of all open task items related to
data errors and can then follow them one by one and close
them after updating the correction in Source System.
 There is option to introduce multi level approvals / third eye
check mechanism too before a correction can be marked
as correctly completed
7
Manage Data Exceptions/Errors

Business Process Flow Supported:
 The user closes the tasks in Analyst tool after he fixes the rule error in the data
source system.
 The tasks are created and closed at data item level+rule level.
Technical process flow Supported:
 For checking data and creation of DQ tasks
 Tasks are created on the basis of Business Rules within a data domain, that needs
to be handled by users allocated to a particular business area. The data items are
already associated to a particular business area.
Source the
Product
Data
Run Batch
Validations
Identify
Exceptions
Notify
Responsibl
e
Correct
Data in
Source
Systems
Re-Validate

(4) Exception/ Bad Records Creation: it can be achieved using a
special Exception Control task.
The Exception transformation identifies the following types of records based on each record score:
Good records
 Records with scores greater than or equal to the upper threshold. Good records are valid and do not require review. For example, if
configure the upper threshold as 90, any record with a 90 or higher does not need review.
Bad records
 Records with scores less than the upper threshold and scores greater than or equal to the lower threshold. Bad records are the
exceptions that you need to review in the Analyst tool. For example, when the lower threshold is 40, any record with a score from 40 to 90
needs manual review.
Rejected records
 Records with scores less than the lower threshold. Rejected records are not valid. By default, the Exception transformation drops
rejected records from the data flow. For this example, any record with a score 40 or less is a rejected record.

In right side, we show:
a) Set of mappings to
perform multi level of
checks with increasing
complexity.
b) It shows a Excep task
to fill all the BAD table
c) Human Task : Manage
data governance for
example : Record
distribution to users is
based on Country code :
(can be distributed
by values from a
reference table too)
(a)
(b)
(c)
 Once we fill the BAD records table, we need to identify who & how to allocate the
correction of each of those line items.
 It can be done using a special component called «Human Task» component in IDQ

DQ System
DMM
Approve/Reje
ctchanges
= Automated= Manual
ExecuteDQ
mappings
Correctrecords in
Source System
Review/
openassigned
tasks
NextSteps
BPM /
Superuser Review/
openassigned
tasks
Good records
Badrecords
Rejected records
Approved records
Scenario (a) : SourceSystem is Manually updated
(22)Business/HumanTasks WorkflowManagement

DQ System
DMM
Approve/Reje
ctchanges
= Automated= Manual
ExecuteDQ
mappings
Correctrecords in
DQ UI
Review/
openassigned
tasks
Collect &
Writetotarget
BPM /
Superuser Review/
openassigned
tasks
Good records
Badrecords
Rejected records
Approved records
Scenario (b) : Source System is Autoupdated from DQPlatform

(22) Working on Bad Records/ Task items using Web UI
 A user sees all data correction Tasks assigned to him
 He can access the bad records with those errors
 He can visualize all errors at record level and update them
 See all audits related to a data record

 It shows all Tasks Overview associated to you
 Yousee all your DQTasks onthe main login page of Analyst Tool
 Here is summarizedview and youneed to clickon it to see full list of records and errors
 It includes workingTasks (DMM) and Review Tasks ( Superuser)
My Task Page

 Here youable to see the full set of records associated to oneTask, havingone or more errors
 Errorfields are markedinRED color
 InitialPhase: Perform the correction inSource System for these fields
 Allows for Correction,Adding Notes, view field errors,
DetailedTask View: Recordlisting

 This screen also allows you toedit the Errors directly here
 (useful for automated Source system updation scenarion only)
 Updates done herecan be reviewed by BPM /Superuser and
 henceuseful to update it heretoo apart from manual update of Source system directly
Detailed Task View : RecordEditing

 Here youcan view the ChangeHistory for a data record.
 Useful for sensitive data
 Thechangefields show up with green tick sign
Tasks: DataAuditing Tab

Manage Exceptions
Business
Users, Analyst
Developer
BPM /
SuperUser /
Manager
DMM &
Analyst &
Data Steward
Manage Review (Third
Eye principle)
Integrate People with Automated DQ
Lifecycle Processes

 Around Tasks / Records viewing
 Around Tasks Distribution
 What does it mean & How we can do
it in DQ Tool
19
Security & Authorization

A client implementationcaninclude:
100+ rolesforMaterialMaster
400+ rolesforVendor
800+ rolesforCustomers
•Need to Support Complex data hierarchywithin each subject area
•Vast no of DMM exists
•Need to restrict data visibility to the DMM to there own data only
•Support for Largeno of Data roles
•Superusers need to havemore flexible data access
•Support for multiple Subject area/Data Domain Access
•Support capability to export data to excel with restricted data
•GUIcapability to navigate through errordata and read associated errors
seamlessly
•Data is highlysensitive and desires restrictions at various levels
•Employees has complex organization setup and manage mutiple data sets
Data & Security Challenge

Level 1 restriction on data
can be done by doing data
distribution based on data
values. It can be done in
Workflow tab of DQ
Developer using a special
component called «Human
Task».
In right side, we show:
a) Human Task : Manage
data distribution governance
for example : Record
distribution to users is based
on Material Type code : (can
be distributed by values
from a reference table too)
b) The Task Performer could
be a Group in LDAP even
and he will be restricted to
specific data only
Task Creation : Data Restriction

Level 2 : Configuration
of Human Task to
restrict data to limited
DMM
We need to further
configure the Human
Task step to define the
full process around
Tasks and Review
process:
 With an Exception
Step (DMM corrects
data) restrict people
to act as DMM
 and with a Review
Step (BPM/
Superuser manager
approves changes)
restrict list of
reviewers
Task Creation : DMM Restriction

 There is support for groups and roles to organize security around data visibility
and actions that can be executed on data
 It is seamlessly supported across Analyst and Developer
DQ-Security Model Schematic

a) Users : Create normal users in
Analyst tool or in LDAP
b) Groups: Create Groups based on
Tools Access and functionality. This is
required from Tools Access aspects.
Also create Groups based on Subject
Area ROLES as defined within
corporate functions. This is needed
for Data Security aspect.
Roles can also be created in LDAP
c) Human Task creation:
Do it based on data value of Subject
Area Roles and assign it to Tools Data
Roles derived from Tool config , which
is 1 to 1.
Manage all allocations in a table for
Dynamic usage
Approach to Configuring Roles,
Groups and Task Access

 Management desires full visibility on periodic progress
 Ability to drill down at various aspects in Real time is
satisfying for Upper management
 Access to reports over Web UI is critical
 DQ Project and Tools need to provide out of box or
support a Operational Model for Aspects like GxP
compliance
 DQ Project and Tools need to provide out of box or
support a Reporting Model
25
Reporting and GxP Compliance

DQ HUBAnalyst / DQ Tool DQ/ ETL Tool
Landing
tables
Staging
tables Errors &
Data
tables
Extractor
(no
transform)
Define &
Execute
Validation
Rules
(Mappings &
Workflows)
Define
Validation
Rules
(Maplets)
UI Access
DQ Errors
Microsoft Excel
Source
Replica
tables
Aggregato
r &
Joiners
(Incl.
Filtering)
DQ
Source
tables
Landing Staging Base Object
Analytics Tool
Sourc
eSourc
e
Data Stewards –
Role/Group
Reporting Tool
Data Stewards –
Fixes Manually
Reportin
g Model
•Out of box Reporting model to support full scale corporate reporting doesnt come
with product and hence we need to sketch the process and data model around it
•Out of box data model for GxP, Process Audit etc, doesn’t come with product and
hence we need to sketch the process and data model as part of DQ Rule checking
framework

Internal and Confidential
Application Schema Analyst / IDQ for DQ Configuration

Table Description
DQC_BUS_ERROR_DTL Error message enhancement based on business input against rules failure.
DQC_ERROR_MST System generated error message against each rule.
DQC_DATA_STEW_MST Table contain list of data steward along with their managers.
DQC_PRM_THRESHOLD Threshold value set for routing records based on score between Good records, bad records and rejected records.
DQC_ROLE_DATA_STEW_ASSGN Association between Role, Data Steward for Site and Source System.
DQC_ROLE_MST All role defined for DQ solution across site and source system.
DQC_ROLE_RULE_ASSIGN Relationship Rules assigned to various Roles across site and source system along with Active Flag.
DQC_RULE_BOOK Definition of all roles configured along Activation Flag and Date.
DQC_RULE_OBJECT_ASSIGN Role assigned to data object or slice of data object.
DQC_RULE_OBJVALUE_ON_OFF Rule switch on / off at data object / data object value level.
DQC_SAP_FLD_NAMES Techical field names along with field description for reporting.
DQC_SITE_MST Site configured for DQ system.
DQC_SRC_SYSTEM Source System configured for DQ system.
DQC_SUB_SUBJ_AREA Category within Subject area like within MM we have Plant, S-Org, BOM, Storage Loc etc.
DQC_SUBJ_AREA_MST Subject area master like MM, Vendor, Customer.
DQC_ROLE_OBJVAL_ASSIGN_MM_PLNT (*) Assignment of data slice to Role (slice would be plant, site, material type etc).
DQO_MM_GENL (*) Opertaion table contain MM General Data.
DQO_MM_GENL_PLNT (*) Opertaion table contain MM Plant data along with score and check type details added.
DQO_MM_GENL_PLNT_BAD (*) System generated - opertaion table contain MM Plant data where records are categorised as bad records.
DQO_MM_GENL_PLNT_BAD_ISSUE (*)
System generated - opertaion table contain MM Plant records with issues details / error details where records are categorised as bad
records.
DQO_MM_GENL_PLNT_SRC (*) Operation table contain MM Plant data in raw format.
DQO_MM_GENL_SRC (*) Operation table contain MM general data in raw format.
DQO_MM_GENL_WHLST (*) Records whitelist for general data. For these records we don't perform any DQ checks.
DQO_RUN_DTL Table containing DQ result for each run at field level for all rules attached to the field.
DQR_DIM_DATE Reporting Time Dimension (Draft).
DQR_DIM_ERROR_MST Reporting Error Dimension (Draft).
DQR_DIM_ORG Reporting Organisation Dimension - Role / Data Steward / Site / Source System (Draft).
DQR_DIM_RULE Reporting Rule Dimension (Draft).
DQR_FCT_DATA_QUALITY Reporting Data Quality Fact table (Draft).
(*) These tables are object specific and will be duplicated

Internal and ConfidentialRules Roles Exception Data owners Data Segmentation

Source
System 1
Source
System 2
Reference
Tables
Data object
Definition
Configuration Tables
Check
Results
Distribute
Results /
Tasks
Material
Type
Material
Category
List of Plants
Material
Group

Data Quality Operational / DQO
• These are operational tables; tables on
which data quality check is performed and
tables that store the outcomes.
• Material Source data example would be
plant data table or PIR data or general
data.
• Here each source data object table is
complimented with system generated
tables like bad table, issue table along with
application table like Whitelist table and
Source table. The right side ER shows MM
Plant data object tables using MM_GENL_
in table naming
• (*) These tables are object specific
and will be duplicated
• Here DQO_Run_DTL stores all DQ check
results at data attribute level.
• There is no relationship build on database
level on purpose for these tables.

DQ – Reporting Dimensional Data Model
(Extension)
D_L_RULE_BOOK
RULE_NUMBER
RULE_NAME
RULE_DESCRIPTIO
N
RULE_PURPOSE
....
F_H_DQ_ANALYSIS_AGGR F_L_DQ_RUN
D_L_ROLE_ASSIG
NMENT
F_L_RULE_RUN
D_L_SUBJECT_AR
EA
D_DQ_STEWARD
D_L_RULE_CATEG
ORY
F_L_DQ_ERROR
D_L_SOURCE_SYS
D_L_SEVERITY
D_L_DATE

Entities/ Tables Description
D_L_ROLE_ASSIGNMENT
D_L_RULE_BOOK
D_L_RULE_CATEGORY
D_L_SEVERITY
D_L_SOURCE_SYS
D_L_SUBJECT_AREA
Dimension Tables that contains:
- All DQ rules
- Rule Severity
- Data Hierarchy
- Data Steward role
- Assigning Data Steward access to various data
hierarchy
- Various subject area
F_H_DQ_ANALYSIS_AGGR
F_L_DQ_ERROR
F_L_DQ_RUN
F_L_RULE_RUN
Fact tables to capture all stats for reporting purpose at
lower record level and at aggregated level too
- generic error data
- generic analysis data
- All run details
- Details of all rules applied during each run
Reporting Data Model Description

Feature Business Benefit
Proven Framework Quick ROI & Low learning curve to implement a DQ
project
Platform and technology independent DQ
framework
Eliminates need for any new HW & SW purchases.
Any kind of DQ/ ETL tool, UI interface, Analysis
tool can be used
Zero investment in new products.
Reuse of inhouse tecnology and applications
Can be used for any kind of data, be it Material
Master , Vendor, Customer or any other non
standard data like Vehicle, Crane
100% flexibility to extend to any new data area.
Quick and easy to use and build Short implementation time.
Normal low cost developers can be used to build it.
New Reporting Dimensions can be added
easily
Flexibility to extend the framework to accomodate
project based dimensions without dependency on
product supplier or expert consultants
Flexible data security model that supports
valrious complex data hierarchy and vast
combinations of data stewards
It helps eliminate data security and data sensitivity
discussions and prevents DQ project planning from
unneccessary delay.
Benefits of Framework

DQ Product Usage Methodology Highlights_v6_ltd

Recommended

Recommended

More Related Content

What's hot

What's hot (15)

Viewers also liked

Viewers also liked (19)

Similar to DQ Product Usage Methodology Highlights_v6_ltd

Similar to DQ Product Usage Methodology Highlights_v6_ltd (20)

DQ Product Usage Methodology Highlights_v6_ltd