Automatic data warehouse generation using automatically generated schema

• US 9,483,537 B1
• Filed: 03/09/2009
• Issued: 11/01/2016
• Est. Priority Date: 03/07/2008
• Status: Active Grant

• Current Assignee: Infor Incorporated
• Original Assignee: Birst Incorporated (Infor Incorporated)
• Inventors: Peters, Bradley S., Staelin, Paul H., Schmitz, Stefan M.
• Primary Examiner(s): Syed, Farhan
• Application Number:
  US12/381,315
• Time in Patent Office: 2,794 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/212 with details for data modelling support
• CPC Class Codes: G06F 16/2456 Join operations
• 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/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

Techniques for automatic data warehouse generation are disclosed herein. A first data set is received. The first data set includes a plurality of tables and includes a plurality of keys. A dimensional model is mapped onto the first data set. A schema is automatically generated based on the dimensional model.

Claims

1. A system, comprising:

one or more processors configured to;

receive a source data set, the source data set including a plurality of tables and a plurality of keys;

derive metadata from the received source data set, wherein deriving the metadata from the source data set includes;

determining, for a table in the source data set, a grain of the table;

determining, for a column in the source data set, a target dimension and level;

determining whether the column is a measure; and

determining one or more dimensional hierarchies, wherein one or more levels of the one or more dimensional hierarchies are designated to be transformed into dimension tables;

subsequent to deriving the metadata from the source data set, using the metadata derived from the source data set to automatically generate a corresponding database schema, wherein generating the corresponding database schema includes;

generating a dimension table, wherein the dimension table is generated based at least in part on one or more columns in the source data set determined to be targeted to a level associated with the dimension table; and

generating a measure table, wherein the measure table is generated for the determined grain;

load a data warehouse associated with the automatically generated database schema, wherein loading the data warehouse includes;

generating one or more inserts into the generated dimension table;

moving data from one or more tables in the source data set into the generated dimension table at least in part by using the generated one or more inserts; and

identifying one or more sources for one or more measure grains at least in part by scanning tables in the source data set; and

a memory coupled to the one or more processors and configured to provide the one or more processors with instructions.

2. The system of claim 1 wherein the one or more processors is further configured to identify one or more keys in the source data set.

3. The system of claim 1 wherein the one or more processors is further configured to identify one or more keys in the source data set and to identify one or more hierarchies for the keys.

4. The system of claim 1 wherein the one or more processors is further configured to determine one or more joins for the source data set.

5. The system of claim 1 wherein the one or more processors is further configured to identify measures and attributes in the source data set.

6. The system of claim 1 wherein the one or more processors is further configured to generate a synthetic key for a second table included in the source data set.

7. The system of claim 1 wherein the generated database schema is a star schema.

8. The system of claim 1 wherein the processor is further configured to provide a user with an interface for creating and modifying the metadata.

9. The system of claim 1 wherein the one or more processors is further configured to analyze a second source data set and to load the second source data set into a second data warehouse based on a second generated schema.

10. The system of claim 1 wherein the one or more processors is further configured to determine one or more indications that data in the source data set can be aggregated.

11. The system of claim 1 wherein the one or more processors is further configured to determine one or more dimensions of the source data set.

12. A method, comprising:

receiving a source data set, the source data set including a plurality of tables and a plurality of keys;

deriving metadata from the received source data set, wherein deriving the metadata from the source data set includes;

determining, for a table in the source data set, a grain of the table;

determining, for a column in the source data set, a target dimension and level;

determining whether the column is a measure; and

determining one or more dimensional hierarchies, wherein one or more levels of the one or more dimensional hierarchies are designated to be transformed into dimension tables;

subsequent to deriving the metadata from the source data set, using the metadata derived from the source data set to automatically generate, using one or more processors, a corresponding database schema, wherein generating the corresponding database schema includes;

generating a dimension table, wherein the dimension table is generated based at least in part on one or more columns in the source data set determined to be targeted to a level associated with the dimension table; and

generating a measure table, wherein the measure table is generated for the determined grain;

loading a data warehouse associated with the automatically generated database schema, wherein loading the data warehouse includes;

generating one or more inserts into the generated dimension table;

moving data from one or more tables in the source data set into the generated dimension table at least in part by using the generated one or more inserts; and

identifying one or more sources for one or more measure grains at least in part by scanning tables in the source data set.

13. A computer program product embodied in a non-transitory computer readable storage medium and comprising computer instructions for:

receiving a source data set, the source data set including a plurality of tables and a plurality of keys;

deriving metadata from the received source data set, wherein deriving the metadata from the source data set includes;

determining, for a table in the source data set, a grain of the table;

determining, for a column in the source data set, a target dimension and level;

determining whether the column is a measure; and

determining one or more dimensional hierarchies, wherein one or more levels of the one or more dimensional hierarchies are designated to be transformed into dimension tables;

subsequent to deriving the metadata from the source data set, using the metadata derived from the source data set to automatically generate, using one or more processors, a corresponding database schema, wherein generating the corresponding database schema includes;

generating a dimension table, wherein the dimension table is generated based at least in part on one or more columns in the source data set determined to be targeted to a level associated with the dimension table; and

generating a measure table, wherein the measure table is generated for the determined grain;

loading a data warehouse associated with the automatically generated database schema, wherein loading the data warehouse includes;

generating one or more inserts into the generated dimension table;

moving data from one or more tables in the source data set into the generated dimension table at least in part by using the generated one or more inserts; and

identifying one or more sources for one or more measure grains at least in part by scanning tables in the source data set.