Data Quality and its components

                Data Quality is a fitness level of a data. A set of data which we receive in beginning of any project needs to be analyzed by several means of statistical theories. Data quality is a result of value it generate aligning the subject area goal. There are several “Ilities” (around 200) to judge the quality of data, but data analyst put some of them to weighing the purity and impurity in a set. Goal is to get To-the-point, Correct and Effective data.

*** Data quality has a vast scope of discussion. Several organizations is researching and inventing new ways to analyze a piece of data. We are not going to discuss everything in a blog post (hence it is impossible). Here I want to explain some parameters of data quality which I use in my BI projects. I will use Automobile sales information as example here.  

Five w + H ( A short introduction)

What Data Quality means?

It determines that the data we are using for business decision is dependable, well processed and error free. A quality data is achieved by combined analysis of business and technology.

When to apply this?

Data Quality analysis is not a project it is not also similar to quality control. It is a horizontal in and organization which is used in different segment of SDLC phases of different project running in the same organization. Data is generated regularly and problems are identified frequently. Data changes with the change of organizational planning and business rule changes. A quality data is always viewed in term of business impact. Whether you are starting a project or the project is in support (maintenance) this can be implemented anytime.

Which are the benefits for implementing it?

Data quality maintaining or implementing is a costly affair. But it benefits every user who uses a set of data. A fully computerized organization also has a huge manual task to remove redundant data from their reports using separate tool. Data quality removes that redundancy. In my experience I saw people used to extract data from two system and match to get confidence on data quality. Implementing data quality analysis removes the problem.

Who need to implement?

Business owns the data, SMEs understand the data, and Technology structures the data and end user use the data. To get a quality data there should be a collaborative effort of all stakeholders of the data.

Where it need to be implemented

When a data quality gets implemented it captured all the aspects of the data. Suggestion from Data Quality analysis results modification of all the systems involved in an organization. It starts with the legacy data in an organization have and continue to the everyday generated operational data.

How to implement?

There are several tools present in the industry to get it done with some organization level customization. Those tools are built on the basic data analysis theories. You also can analyze the data by checking several parameters.

A real life data quality measurement

Sales information is always the high priority in an organization as it is the lifeline of an industry. Sales volume is connected to most of the decision making KPIs in an organization. Automobile industry or manufacturing industry endorses automation prior to any other industry, but they are the last in game to understand importance of data flow.

                Intense labor oriented organization works with people and there people update the information manually. In an oil industry retail volume directly comes from automated gas stations. But in automotive industry there is an involvement of third party settlement and negotiation. Dealership involvement makes the whole things complicated.

                In any automotive organization this is common problem to get the correct sales data by different hierarchy. Different department looks sales volume differently. After sales department don’t see the sale of Car. So they have parts sales volume. Sales department don’t see parts sales volume. Multinational Car Company has different departments sitting in headquarter for individual country car sales. Trend management analytics checks the volume of the old car sales to new car sales.  Finance department see sales volume per individual dealership, and individual sales person. Automotive trainers look to sales volume with the level of knowledge of the dealership professional. They see the sales volume of their courses taking the car and parts sales volume as a parameter. A dealership looks to a company sales by their own sales, comparative sales with other dealership and their salespersons sales volume. A sales person sees the sales volume of his own to compare with trend to get the incentive.

                So they all have different view on sales volume. As result sales volume require several different aspects to be checked. For representing a sales volume a big amount of information need to be checked. In a project of finding incentive given to sales professional, project owner wants to check the data quality of the modeled data on which the reports are built and ready to go to production.

                If no data quality measurement tool is provided then I personally prefer the X parameters in 39 parameters set by Blundell, Hines and Stach for information quality. It is obvious that you may not require all. Database is in Oracle, ETL is in Informatica, reporting tool is Business Objects. Let’s start the analysis of the data with some parameter used by them:

               

Blundell, Hines, and Stach
1	Accuracy	Read the rest and at the end of the point accuracy will be discussed as it is the single show stopper in data quality analysis.
2	Adaptability Change ability Modifiability Flexibility	1.       Check if the reports are dynamic. If the reports data are not dynamic and not data driven then a same report needs modification in time which affects changeability. 2.       Check the variable definition. If the variables are smart enough to handle change in rule that will adapt business rule changes. Ex: The Company started business in Cuba and need to add the sales volume in the sales data. If the country is a variable then it will automatically updated with the new data comes in. 3.       Prohibit Hardcoding. Try to remove hardcoding in universe, in data tables, in reports.
3	Auditability	1.       Checks if audit trail is enabled if not enable it for important tables. Like Sales volume and dealer attributes 2.       Create a purging plan to avoid maintaining huge logs 3.       Assign timestamp to all set of data. History information helps to audit a database.
4	Availability	1.       Check report output and turnaround time. 2.       If output time is more than 30 second then do a root-cause-analysis. 3.       If the turnaround time (from opening the application to getting the output) is more than 3 min, availibity is challenged. 4.       Test with a full set of filter rather narrowing down the criteria. 5.       Reports and data should be available in a relevant location. It should run in a place where all the analytical data is present. Location determines the quality of information. Ex: Sales data should appear right after the employee information.
5	Completeness Correctness	1.       Compare with the legacy system data and check the correctness. 2.       Create SQL queries separate than application generated SQL with the business rules mentioned in requirement. Return random small set of data and compare with a large volume and do a theoretical match.  3.       Check the user expectation from BRD and the data returned in reports. Ex: if user wants to see only California Sales. It is irrelevant to give West coast Sales data with drill down capabilities. 4.       Check language requirement. Sometime English language developers think that Spanish and French are using same character set and create a mess when writing in report. May be they have similar set but ñ or Œ are never used in English language. So confirm with the native language experts to completeness of data. 5.       Though Dollar is vastly used in international calculations. But multinationals face lots of problem calculating the native currency to dollar. Ex: the exchange rate varies every time. And the sales data from different country generates in different currency. The analytical data should comprise the both and fix a rule for exchange rate conversion. Completeness, Correctness and accuracy have several sub parameters to deal with. These are the end result parameter of data quality measurement.
6	Consistency	1.       Consistency in data and documentation need to be checked. If in user given mockup “Employee ID” is given then this should not be changed to “Emp ID” for optimized space utilization. 2.       Consistency with bigger picture. Sales data is used in different application across the organization. If different application is showing different data then the consistency and reliability is getting hampered. Make sure the downstream feed is coming at the same time as other application using the same source. 3.       Consistency of design. A design must follow a guidelines and standard. If those are not followed then a same set of data looks different when compared. Ex: in an application dealer name and dealer code are referred as N_Dealer and C_Dealer. If somewhere else it is notified as Dealer_code, Dealer_Name, it will be hard to maintain. 4.       Check Point-in-Time Consistency. This means check the if the data are indexed properly and all the data are uniform at a point of time. If the system crash then the data should be properly retrieved in a point of time. Simply set a backup policy and data retrieval point. 5.       Check application consistency. If the sales data showing in your report is differs from the sales data showing in another application then you need to find justification. If your application is showing a day old data then you need to compare with other application which is showing a day old data. If find inconsistent, must be fixed. 6.       Check transaction consistency. If you have a transactional system where sales data updates in a real time environment. Then you need to check that the sales volume shows same in managerial application and employee report in same point of time. If you have historical analytical system you need to check the delay of update of your system.
7	Data commonality	Covered in data consistency in design. Also data commonality refers to data architecture and variable definition guidelines. If a rule is set in an organization that the dimension key need to be 5 digit. Then it should be 5 digits across all tables in the system.
8	Dependability	Dependability comes from the accuracy of the data on time. Check all the business rule and the generated data return time. With multiple checks in multiple times in different scenario build the confidence of a system that it can show sales volume of the guy name X from Y dealership of Z country or guy P from Q district of R state in S country.
9	Efficiency	Efficiency leads to dependability of a data. To check the data is efficient you need to: 1)       Check the number of joins pulled in a report. Joins are the single most expensive item regarding data. Less number of joins makes a system more efficient. 2)       Avoid loops in modeling. It causes cancer in system. 3)       Unnecessary variable declaration. 4)       Remove cascading if possible. 5)       Use flags more in condition in place of major where clause. 6)       Minimize user input and prompts. 7)       Schedule reports. 8)       Refresh reports with data before users open computer in morning. 9)       Remove trap, loops, fan trap, circular joins. 10)   Avoid outer joins.
10	Error tolerance Robustness	1)       There should be a fault tolerance system works for the database. 2)       Check if the tables contain proper error handling mechanism. 3)       Checks the database have proper recovery system.
11	Hardware independence	This is common problem now days because of the mobile application boom. Data should be visible and proper in different hardware. You need to check the data in desktop and mobile application.
12	Integrity Security	Sales data is one of the most secured data in an organization. Information’s are confidential. For a data quality inspector there is no need to check the physical or access level security for a data. Every organization has a security or governance plan and policy which safeguard the data integrity. But you need to make sure the documentation provided by the different team of data and network security which actually save your project data. But you need to : 1)       Check if any row level or object level security is needed or not. If needed then check the sanctity of the security. 2)       Authorized data should be visible to authorized person. Not everyone. Setup a security matrix sitting with SME and implement. 3)       Test the security matrix using different dummy id setup for different level. This should be thoroughly tested. 4)       Data administration should be controlled by skilled employees and at least three layer of data security should be implemented.
13	Maintainability	Maintainability is the one of the most important parameter to check as data quality depend on availibity of data. As a regular maintenance is needed for a data set you need to check if the data is maintainable or not. For that  you need to check: 1)       Data loading mechanism and delay in data availibity. That means if you have a OLTP system the sales data should appear in your dashboard almost in real time. If OLAP then analytical data should appear in next business day. 2)       Quantity or volume of the data. If the data set is huge and cannot be broken into pieces by dimensions or similar type of dimension is present with a large volume then need to check modeling methodology to retrieve and maintain that. 3)       Complexity of the report. Sometime it happens that the retrieval query is too much complex and if any error occur, code fix will take time. It happens due to lack of documentation and non-reusability of code snippets. 4)       Joins are the single most expensive item in data retrieval time. Less number of joins is less complexity in code and less data retrieval time. De-normalization solves most of the problem and makes data maintainable. 5)       Some of the other maintenance check needed for below times(Mentioned in original document) a.       problem recognition time b.       Administrative delay time c.        maintenance tools collection time d.       Problem analysis time e.        Change specification time f.         Active correction time g.       Local testing time h.       Global testing time i.         Maintenance review time j.         Total recovery time
14	Modularity	Modularity is important while coding for a dataset. Modularity enhances maintainability. If the data is not modularize and every piece of data is loaded as a heap or dump then it get reflected in all the development done on that piece of dataset. Check if the data set is having modular categorization of data by business values.
15	Reusability	Reusability is having vast meaning in data quality analysis. It means reusability of all components in a dataset. Sometime creating more usability of data creates lots of joins which affects the performance. So smart and to the point reusability is important. Check if your data has reusable content or not. As example sales data is one of the most reusable content in any industry. But the presence of that data should be in standardizing format and easily referenced. Documentation have major role in reusability. Improper documentation can lead data modeling to confusion. A good practice I saw is to maintain a table where definition of all the column of the tables in a schema is detailed. On that table column should be like, Table name, Column Name, Description, Updated by date, Updated by person name.
16	Self‑documentation	This can be achieved by several means: 1)       Data modeling with organization standard naming convention. 2)       Proper Meta data table. 3)       Organized modeling separated by business rules.
17	Understandability simplicity	Ease of understanding the function of a data set. VIN numbers have meaning for each and every number in it. But to decrypt it you need to put logic behind it. So it is not simple to use to get sales volume. Individual level sales volume can show the VIN number which they sold but sales volume should be directly picked up from the sales to a dealership. That makes the whole set easily understandable and simple.
18	Accuracy	Above mentioned checks leads to Accuracy of data. But several level of testing makes a data accurate. There is lots of testing methodology available in market but trust me best testing can be done by the people who use the data. So before delivery: ·         Do unit testing. ·         Do testing with legacy system ·         Do testing referencing BRD ·         Do pick up and check testing ·         Introduce error and do fault testing ·         Ask SME’s for doing the testing ·         And also test with all testing methodology If a data is accurate then user can live with the other problem in data quality but if data is not accurate then it is the end of the story.

If you read this whole story I believe you may not agree to all of my point. And that’s the beauty of data modeling and data quality analysis. It differs from people to people, organization to organization, structure to structure, business to business. But yes , you can start from this and start your own definition. Thanks for reading.