METHOD AND SYSTEM FOR VALIDATING DATA

• US 20120221510A1
• Filed: 05/07/2012
• Published: 08/30/2012
• Est. Priority Date: 03/31/2010
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Li, Xue C., Fu, Xiao J., Gao, Xue F., Xin, Xin
• Granted Patent: US 9,710,536 B2
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 7/026 Magnitude comparison, i.e. determining the relative order of operands based on their numerical value, e.g. window comparator

Abstract

A method and system for validating data. Warehouse data is generated by transforming source data via an ETL transformation model. A data cube is generated by transforming the warehouse data via an OLAP transformation model. A report dataset (MDS1) is generated from the data cube. A reference dataset (S) is generated from the source data. A data validation is performed, the data validation being based on a matching relationship between MDS1 and S.

Claims

1. A method for validating data, said method comprising:

a processor of a data processing system generating warehouse data in a data warehouse by transforming source data in a data source into the warehouse data via an Extract-Transform-Load (ETL) transformation model;

said processor generating a data cube by transforming the warehouse data in the data warehouse into the data cube via an On-Line Analysis Processing (OLAP) transformation model;

said processor generating a report multi-dimensional data set (MDS1) from the data cube via a data cube query of the data cube;

said processor generating a reference multi-dimensional data set (S) from the source data via a source data query of the source data, said source data query corresponding to the data cube query; and

said processor performing a data validation based on a matching relationship between MDS1 and S.

2. The method of claim 1,

wherein said performing the data validation comprises determining whether MDS1 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S, then the method further comprises said processor performing an OLAP inverse transformation on MDS1 to generate an OLAP multi-dimensional data set (MDS2) and said processor determining whether MDS2 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S and said determining whether MDS2 matches S comprises determining that MDS2 does not match S, then said processor performing an ETF inverse transformation on MDS2 to generate an ETL multi-dimensional data set (MDS3) and said processor determining whether MDS2 matches MDS1 and said processor determining whether MDS3 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S and said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS3 matches S comprises determining that MDS3 does not match S, then said processor determining whether MDS3 matches MDS2.

3. The method of claim 2, wherein said determining whether MDS2 matches S comprises determining that MDS2 matches S.

4. The method of claim 2, wherein said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS2 matches MDS1 comprises determining that MDS2 matches MDS1 and said determining whether MDS3 matches S comprises determining that MDS3 matches S.

5. The method of claim 2, wherein said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS2 matches MDS1 comprises determining that MDS2 matches MDS1 and said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS2 comprises determining that MDS3 matches MDS2.

6. The method of claim 2, wherein said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS2 matches MDS1 comprises determining that MDS2 matches MDS1 and said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS2 comprises determining that MDS3 does not match MDS2.

7. The method of claim 2, wherein said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS2 matches MDS1 comprises determining that MDS2 does not match MDS1 and said determining whether MDS3 matches S comprises determining that MDS3 matches S.

8. The method of claim 2, wherein said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS2 matches MDS1 comprises determining that MDS2 does not match MDS1 and said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS2 comprises determining that MDS3 matches MDS2.

9. The method of claim 2, wherein said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS2 matches MDS1 comprises determining that MDS2 does not match MDS1 and said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS2 comprises determining that MDS3 does not match MDS2.

10. The method of claim 1, wherein said transforming the source data into the warehouse data comprises performing an irreversible transformation of the source data into the warehouse data via the ETL transformation model.

11. The method of claim 1, wherein said transforming the warehouse data into the data cube consists of performing a reversible transformation of the warehouse into the data cube via the OLAP transformation model.

12. A data processing system comprising a processor and a computer readable memory unit coupled to the processor, said memory unit containing program code configured to be executed by the processor to implement a method for validating data, said method comprising:

said processor generating warehouse data in a data warehouse by transforming source data in a data source into the warehouse data via an Extract-Transform-Load (ETL) transformation model;

said processor generating a data cube by transforming the warehouse data in the data warehouse into the data cube via an On-Line Analysis Processing (OLAP) transformation model;

said processor generating a report multi-dimensional data set (MDS1) from the data cube via a data cube query of the data cube;

said processor generating a reference multi-dimensional data set (S) from the source data via a source data query of the source data, said source data query corresponding to the data cube query; and

said processor performing a data validation based on a matching relationship between MDS1 and S.

13. The data processing system of claim 12,

wherein said performing the data validation comprises determining whether MDS1 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S, then the method further comprises said processor performing an OLAP inverse transformation on MDS1 to generate an OLAP multi-dimensional data set (MDS2) and said processor determining whether MDS2 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S and said determining whether MDS2 matches S comprises determining that MDS2 does not match S, then said processor performing an ETF inverse transformation on MDS2 to generate an ETL multi-dimensional data set (MDS3) and said processor determining whether MDS2 matches MDS1 and said processor determining whether MDS3 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S and said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS3 matches S comprises determining that MDS3 does not match S, then said processor determining whether MDS3 matches MDS2.

14. A computer program product, comprising a computer readable hardware storage device having a computer readable program code stored therein, said computer readable program code configured to be executed by a processor of a data processing system to implement a method for validating data, said method comprising:

said processor generating warehouse data in a data warehouse by transforming source data in a data source into the warehouse data via an Extract-Transform-Load (ETL) transformation model;

said processor generating a data cube by transforming the warehouse data in the data warehouse into the data cube via an On-Line Analysis Processing (OLAP) transformation model;

said processor generating a report multi-dimensional data set (MDS1) from the data cube via a data cube query of the data cube; and

said processor generating a reference multi-dimensional data set (S) from the source data via a source data query of the source data, said source data query corresponding to the data cube query; and

said processor performing a data validation based on a matching relationship between MDS1 and S.

15. The computer program product of claim 14,

wherein said performing the data validation comprises determining whether MDS1 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S, then the method further comprises said processor performing an OLAP inverse transformation on MDS1 to generate an OLAP multi-dimensional data set (MDS2) and said processor determining whether MDS2 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S and said determining whether MDS2 matches S comprises determining that MDS2 does not match S, then said processor performing an ETF inverse transformation on MDS2 to generate an ETL multi-dimensional data set (MDS3) and said processor determining whether MDS2 matches MDS1 and said processor determining whether MDS3 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S and said determining whether MDS2 matches S comprises determining that MDS2 does not match S and said determining whether MDS3 matches S comprises determining that MDS3 does not match S, then said processor determining whether MDS3 matches MDS2.

16. A method for validating data, said method comprising:

a processor of a data processing system generating warehouse data in a data warehouse by transforming source data in a data source into the warehouse data via an Extract-Transform-Load (ETL) transformation model;

said processor generating a report multi-dimensional data set (MDS1) from the data warehouse via a data warehouse query of the data warehouse;

said processor generating a reference multi-dimensional data set (S) from the source data via a source data query of the source data, said source data query corresponding to the data cube query; and

said processor determining whether MDS1 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S, then the method further comprises said processor performing an ETF inverse transformation on MDS1 to generate an ETL multi-dimensional data set (MDS3) and said processor determining whether MDS3 matches S;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S and said determining whether MDS3 matches S comprises determining that MDS3 does not match S, then said processor determining whether MDS3 matches MDS1.

17. The method of claim 16,

wherein if said determining whether MDS1 matches S comprises determining that MDS1 does not match S, then the method further comprises ascertaining that a data error exists such that said ascertaining ascertains that the data error is an ETL model error in the ETL transformation model and/or a source data error in the source data;

wherein if said determining whether MDS1 matches S comprises determining that MDS1 matches S, then the method further comprises determining that a normal data condition exists such that the ETL transformation model and/or a source data error in the source data does not exist.

18. The method of claim 17,

wherein said determining whether MDS1 matches S comprises determining that MDS1 does not match S;

wherein if said determining whether MDS3 matches S comprises determining that MDS3 matches S, then said ascertaining ascertains that the data error is the ETL model error;

wherein if said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS1 comprises determining that MDS3 matches MDS1, then said ascertaining ascertains that the data error is the source data error;

wherein if said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS1 comprises determining that MDS3 does not matches MDS1, then said ascertaining ascertains that the data error is the ETL model error and/or source data error.

19. The method of claim 16, wherein said determining whether MDS3 matches S comprises determining that MDS3 matches S.

20. The method of claim 16, wherein said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS1 comprises determining that MDS3 matches MDS1.

21. The method of claim 16, wherein said determining whether MDS3 matches S comprises determining that MDS3 does not match S and said determining whether MDS3 matches MDS1 comprises determining that MDS3 does not matches MDS1.

22. The method of claim 16, wherein said transforming the source data into the warehouse data comprises performing an irreversible transformation of the source data into the warehouse data via the ETL transformation model.