Data quality in storage is a crucial prerequisite for obtaining valuable information. Poor quality leads to a negative chain reaction in the long term.
Initially, trust in the provided information is lost. People start using Business Intelligence applications less, leaving the potential of these applications untapped.
As a result, further investments in the analytical project are called into question.
Responsibility for data quality
The aspect related to improving data quality is mega-important in BI projects. However, it is not a privilege reserved only for technical specialists.
Data quality is also influenced by aspects such as
Corporate culture
- Are the employees themselves interested in producing good quality?
- If not, why? There may be a conflict of interest.
- Are there corporate rules that designate responsibilities for quality?
Processes
- What data is created at the end of these chains?
- Perhaps the operating systems are configured in such a way that one must 'adapt' to reflect a particular situation in reality.
- Do the operating systems perform their own checks and reconciliations of data?
Everyone in the organization is responsible for the quality of data in reporting systems.
Definition and significance
Quality is confirmed satisfaction of customer expectations.
However, data quality does not contain a definition. It always reflects the context of use. Data warehouses and BI systems serve different purposes than the operating system from which the data is sourced.
For instance, in an operating system, a customer attribute may not be a mandatory field. In the warehouse, this attribute may be used as a measure and its completion is required. This, in turn, introduces the necessity of populating it with default values.
Data warehouse requirements are constantly changing and are generally higher than those for operating systems. But it can also be the other way around, where the warehouse does not need to retain detailed information from the operating system.
To make data quality measurable, its standards must be defined. People who use information and numbers for their work should be involved in the description process. The outcome of this involvement may be a rule that allows one to determine at a glance whether there is an error in a table. This rule needs to be formalized in the form of a script/code for subsequent verification.
Improving Data Quality
It is impossible to clean and correct all hypothetical errors during the data loading process into the warehouse. Good data quality can only be achieved through close collaboration among all participants. Those who input data into operating systems must understand what actions lead to errors.
Data quality is a process. Unfortunately, many organizations lack a strategy for its continuous improvement. Many limit themselves to merely storing data and do not utilize the full potential of analytical systems. Typically, during the development of data warehouses, 70-80% of the budget is spent on data integration implementation. The process of monitoring and improving remains underdeveloped, if it exists at all.
Tools
The use of software tools can assist in the automation of improving and monitoring data quality. For instance, they can fully automate the technical check of storage structures: field formats, presence of default values, compliance with the requirements for table field names.
Checking the content may be more complicated. As storage requirements change, the interpretation of data may also change. The tool itself can turn into a massive project that requires support.
Advice
Relational databases, in which warehouses are typically designed, possess a remarkable ability to create views. They can be used for quick data verification if the specific characteristics of the content are known. Every instance of finding an error or issue in the data can be recorded as a query to the database.
Thus, a knowledge base about the content will be formed. Of course, such queries must be quick. Typically, the maintenance of views takes less human time than tools organized in tables. A view is always ready to display the result of the check.
In the case of important reports, the view may contain a column for the recipient. It makes sense to use the same BI tools to report on the data quality status in the warehouse.
Example
The query is written for the Oracle database. In this example, the tests return a numeric value that can be interpreted as needed. The values of T_MIN and T_MAX can adjust the level of alert. The REPORT field was once used as a message in a commercial ETL product that couldn't send emails properly, so rpad is a workaround.
In the case of a large table, you can add, for example, AND ROWNUM <= 10, that is, if 10 errors are reached, that is enough for an alert.
CREATE OR REPLACE VIEW V_QC_DIM_PRODUCT_01 AS
SELECT
CASE WHEN OUTPUT>=T_MIN AND OUTPUT<=T_MAX
THEN 'OK' ELSE 'ERROR' END AS RESULT,
DESCRIPTION,
TABLE_NAME,
OUTPUT,
T_MIN,
T_MAX,
rpad(DESCRIPTION,60,' ') || rpad(OUTPUT,8,' ') || rpad(T_MIN,8,' ') || rpad(T_MAX,8,' ') AS REPORT
FROM (-- Test itself
SELECT
'DIM_PRODUCT' AS TABLE_NAME,
'Count of blanks' AS DESCRIPTION,
COUNT(*) AS OUTPUT,
0 AS T_MIN,
10 AS T_MAX
FROM DIM_PRODUCT
WHERE DIM_PRODUCT_ID != -1 -- not default value
AND ATTRIBUTE IS NULL ); -- count blanks
The publication uses materials from the book
Ronald Bachmann, Dr. Guido Kemper
Out of the BI trap
How Business Intelligence can lead to success
Source: habr.com
