Star and snowflake schemas in extract, transform, load processes

Patent metadata

Field	Value
Publication number	US-9298787-B2
Application number	US-201113292234-A
Country	US
Kind code	B2
Filing date	Nov 9, 2011
Priority date	Nov 9, 2011
Publication date	Mar 29, 2016
Grant date	Mar 29, 2016

Abstract

A computer-implemented method, computer program product and a system for supporting star and snowflake data schemas for use with an Extract, Transform, Load (ETL) process, comprising selecting a data source comprising dimensional data, where the dimensional data comprises at least one source table comprising at least one source column, importing a data model for the dimensional data into a data integration system, analyzing the imported data model to select a star or snowflake target data schema comprising target dimensions and target facts, generating a meta-model representation by mapping at least one source table or source column to each target fact and target dimension, automatically converting the meta-model representation into one or more ETL jobs, and executing the ETL jobs to extract the dimensional data from the data source and loading the dimensional data into the selected target data schema in a target data system.

First claim

What is claimed is:

1. A computer program product for supporting star and snowflake data schemas for use with an Extract, Transform, Load (ETL) process, comprising:
a computer readable storage device having computer readable program code embodied therewith, the computer readable program code comprising computer readable program code configured to:
select a data source comprising dimensional data, wherein the dimensional data comprises a plurality of dimensions and a plurality of source tables each comprising at least one source column;
import a data model for the dimensional data into a data integration system, wherein the data model includes information pertaining to dimension tables and fact tables;
analyze the imported data model to determine a target data schema for individual dimensions of the dimensional data, wherein the target data schema is a star data schema or a snowflake data schema, and wherein the target data schema comprises target dimensions and target facts, and wherein analyzing the imported data model includes:
analyzing the imported data model and determining a quantity of dimension tables for the individual dimensions of the dimensional data;
determining the star data schema as the target data schema and mapping a dimension of the dimensional data to the star data schema in response to the analyzing indicating the dimension contains a single dimension table; and
determining the snowflake data schema as the target data schema and mapping a dimension of the dimensional data to the snowflake data schema in response to the analyzing indicating the dimension contains a plurality of dimension tables;
wherein at least one dimension of the dimensional data is mapped to the star data schema and at least one other dimension of the dimensional data is mapped to the snowflake data schema;

generate a meta-model representation by, for each target fact, mapping at least one source table or source column to the target fact, and for each target dimension, mapping at least one source table or source column to the target dimension;
automatically convert the meta-model representation into one or more ETL jobs; and
execute the one or more ETL jobs to extract the dimensional data from the data source and load the dimensional data into the determined target data schema for corresponding dimensions in a target data system.

2. The computer program product of claim 1 , wherein the computer readable program code is further configured to:
identify a major dimension for the dimensional data, and a hierarchy for the major dimension.

3. The computer program product of claim 2 , wherein the identifying the major dimension comprises the computer readable program code being further configured to:
compare the dimensional data to each template in a set of predefined dimension templates using a similarity identification algorithm that generates a similarity score; and
select the template that has the highest similarity score as the major dimension.

4. The computer program product of claim 3 , wherein the similarity score is determined using a Dynamic Time Warping (DTW) method.

5. The computer program product of claim 1 , wherein the computer readable program code is further configured to:
map the target dimensions and target facts to predefined dimension templates using a similarity score.

6. The computer program product of claim 1 , wherein the generating the meta-model representation comprises the computer readable program code being further configured to, for each target fact and each target dimension:
determine a similarity score measuring the similarity between the target fact or target dimension and each source table;
determine a similarity score measuring the similarity between the target fact or target dimension and each source column; and
select the source table or source column with the highest similarity score as the source table or source column that is mapped to the target fact or target dimension.

7. The computer program product of claim 1 , wherein the generating the meta-model representation comprises the computer readable program code being further configured to, for each target fact and each target dimension:
determine a similarity score measuring the similarity between the target fact or target dimension and each source table;
determine a similarity score measuring the similarity between the target fact or target dimension and each source column;
present all of the similarity scores to a user; and
receive a user indication of a selected source table or source column as the source table or source column that is mapped to the target fact or target dimension.

8. A system for supporting star and snowflake data schemas for use with an Extract, Transform, Load (ETL) process, comprising:
a memory; and
a processor configured with logic to:
select a data source comprising dimensional data, wherein the dimensional data comprises a plurality of dimensions and a plurality of source tables each comprising at least one source column;
import a data model for the dimensional data into the memory, wherein the data model includes information pertaining to dimension tables and fact tables;
analyze the imported data model to determine a target data schema for individual dimensions of the dimensional data, wherein the target data schema is a star data schema or a snowflake data schema, and wherein the target data schema comprises target dimensions and target facts, and wherein analyzing the imported data model includes:
analyzing the imported data model and determining a quantity of dimension tables for the individual dimensions of the dimensional data;
determining the star data schema as the target data schema and mapping a dimension of the dimensional data to the star data schema in response to the analyzing indicating the dimension contains a single dimension table; and
determining the snowflake data schema as the target data schema and mapping a dimension of the dimensional data to the snowflake data schema in response to the analyzing indicating the dimension contains a plurality of dimension tables;
wherein at least one dimension of the dimensional data is mapped to the star data schema and at least one other dimension of the dimensional data is mapped to the snowflake data schema;

generate a meta-model representation by, for each target fact, mapping at least one source table or source column to the target fact, and for each target dimension, mapping at least one source table or source column to the target dimension;
automatically convert the meta-model representation into one or more ETL jobs; and
execute the one or more ETL jobs to extract the dimensional data from the data source and load the dimensional data into the determined target data schema for corresponding dimensions in a target data system.

9. The system of claim 8 , wherein the processor is further configured with logic to:
identify a major dimension for the dimensional data, and a hierarchy for the major dimension.

10. The system of claim 9 , wherein the identifying the major dimension comprises the processor being further configured with logic to:
compare the dimensional data to each template in a set of predefined dimension templates using a similarity identification algorithm that generates a similarity score; and
select the template that has the highest similarity score as the major dimension.

11. The system of claim 10 , wherein the similarity score is determined using a Dynamic Time Warping (DTW) method.

12. The system of claim 8 , wherein the processor is further configured with logic to:
map the target dimensions and target facts to predefined dimension templat

Assignees

• Bhide Manish Anand
• Mittapalli Srinivas Kiran
• Padmanabhan Sriram
• IBM

Inventors

• Bhide Manish Anand
• Mittapalli Srinivas Kiran
• Padmanabhan Sriram

Classifications

• G06F16/254 Primary

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

• G06F16/282

  Hierarchical databases, e.g. IMS, LDAP data stores or Lotus Notes · CPC title

• G06F16/283

  Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP · CPC title

• G06F17/30589

  Physics · mapped topic

• G06F17/30563 Primary

  Physics · mapped topic