Star and snowflake schemas in extract, transform, load processes

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.

Description

BACKGROUND[0001]1. Technical Field[0002]The present invention relates generally to information warehouse systems, and more particularly to supporting star and snowflake data schemas in order to improve Extract, Transform, Load processing.[0003]2. Discussion of Related Art[0004]Enterprises are building increasingly large information warehouses to enable advanced information analytics and to improve the business value of information. The data in the warehouses are loaded via Extract, Transform, Load (ETL) processes, which extract data from a source, transform the data into a suitable form according to particular business needs, and then load the data into the warehouse(s). Establishing a structure for performing an ETL process is time-consuming, and complex, and there is no automated way to identify and handle loading of data into star and snowflake schemas while building ETL jobs. Conventional ETL systems require a user to manually write several dozen jobs for loading data into a typ...

AI Technical Summary

Benefits of technology

[0005]Accordingly, embodiments of the present invention include 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 target data schema for the dimensional data, where the target data schema is a star data schema or a snowflake data schema, and where the target data schema comprises target dimensions and target facts, generating 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 converting the meta-model representation into one or more ETL jobs, and executing the one or more 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.

Problems solved by technology

Establishing a structure for performing an ETL process is time-consuming, and complex, and there is no automated way to identify and handle loading of data into star and snowflake schemas while building ETL jobs.

Images