Data warehouse model validation

Patent metadata

Field	Value
Publication number	US-10733175-B2
Application number	US-201715498619-A
Country	US
Kind code	B2
Filing date	Apr 27, 2017
Priority date	Apr 6, 2016
Publication date	Aug 4, 2020
Grant date	Aug 4, 2020

Abstract

This invention relates to a system, method and computer program product for a data warehouse model validation system, said data warehouse model validation system having an ETL model and a corresponding data warehouse model, said data ETL system comprising: an element group locator for locating an element group across the ETL model and the data warehouse model, whereby the element group comprises ETL elements and related data warehouse elements; an inconsistency determiner for determining inconsistencies between the ETL elements and data warehouse elements, whereby one or more elements are missing from the data warehouse model or one or more elements in the data warehouse model do not correspond to expected elements or features of elements; and an inconsistency recorder for recording any located missing elements or unexpected elements from the located element group.

First claim

What is claimed is:

1. A method for a data warehouse model validation system having an extract transform load (ETL) model and a corresponding data warehouse model, the method comprising:
locating at least two element groups across an ETL model of an ETL system and a data warehouse model of a data warehouse, wherein the at least two element groups comprise ETL elements and related data warehouse elements;
determining, for the located at least two element groups, inconsistencies between ETL elements of the ETL system and related data warehouse elements of the data warehouse, wherein the inconsistencies include: an ETL element in a staging area of the ETL system being missing from the data warehouse, an ETL element in a source system being missing from the staging area of the ETL system or the data warehouse, or a related data warehouse element from the located at least two element groups being missing from the staging area of the ETL system or the source system;
recording a missing ETL element or a related data warehouse element from the located at least two element groups;
determining respective recommendations for an improvement for the located at least two element groups; and
prioritizing the recommendations for an improvement for the located at least two element groups.

2. The method according to claim 1 , further comprising validating the data warehouse model for the located at least two element groups when there is nothing missing from the at least two element groups.

3. The method according to claim 1 , wherein the determining respective recommendations for an improvement for the located at least two element groups further comprises recommending an improvement to fix the ETL element in a staging area of the ETL system that is missing from the data warehouse, to fix the ETL element in a source system that is missing from the staging area of the ETL system or the data warehouse, or fix the related data warehouse element from the located at least two element groups that is missing from the staging area of the ETL system or the source system, or the ETL model when there are missing ETL elements or related data warehouse elements in the at least two element groups.

4. The method according to claim 1 , further comprising effecting improvements in the ETL elements the ETL element in a staging area of the ETL system that is missing from the data warehouse, the ETL element in a source system that is missing from the staging area of the ETL system or the data warehouse, the related data warehouse element from the located at least two element groups that is missing from the staging area of the ETL system or the source system, or the ETL model when there are missing ETL elements or related data warehouse elements in the at least two element groups.

5. The method according to claim 1 , wherein the prioritizing the at least two element groups is based on a number of differences between each of the two or more element groups.

6. The method according to claim 1 , wherein a number of the ETL elements or the related data warehouse elements in one of the at least two element groups are counted as differences.

7. The method according to claim 1 , wherein a number of differences between each two or more element groups includes differences between sub-elements of the ETL elements or the related data warehouse elements.

8. The method according to claim 1 , wherein the ETL model comprises:
a source system data models; and
a ETL staging data models.

9. The method according to claim 1 , wherein the ETL elements or the related data warehouse elements of the at least two element groups include data model elements and operational data.

10. The method according to claim 1 , wherein the ETL elements or the related data warehouse elements of the at least two element groups further include ETL staging instructions.

Assignees

• IBM

Inventors

• Denner Gary
• Kilroy Paul
• Loughran Michael J

Classifications

• G06F16/212 Primary

  with details for data modelling support · CPC title

• G06F16/254

  Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses · CPC title

• G06F16/2365 Primary

  Ensuring data consistency and integrity · CPC title