Star and snowflake schemas in extract, transform, load processes

• US 9,298,787 B2
• Filed: 11/09/2011
• Issued: 03/29/2016
• Est. Priority Date: 11/09/2011
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bhide, Manish Anand, Mittapalli, Srinivas Kiran, Padmanabhan, Sriram
• Primary Examiner(s): Jami, Hares
• Application Number:
  US13/292,234
• Publication Number:
  US 20130117216A1
• Time in Patent Office: 1,602 Days
• Field of Search: 707/600, 707/602
• US Class Current: 1/1
• 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/282 Hierarchical databases, e.g. IMS, LDAP data stores or Lotus Notes
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

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.

Claims

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 templates using a similarity score.

13. The system of claim 8, wherein the generating the meta-model representation comprises the processor being further configured with logic 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.

14. The system of claim 8, wherein the generating the meta-model representation comprises the processor being further configured with logic 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.