Data model transformation

Patent metadata

Field	Value
Publication number	US-11334594-B2
Application number	US-202017002596-A
Country	US
Kind code	B2
Filing date	Aug 25, 2020
Priority date	Oct 19, 2019
Publication date	May 17, 2022
Grant date	May 17, 2022

Abstract

Described herein are systems and methods of transforming data models, for example, creating a data warehouse. A directives model may be loaded based upon a parsed directives file. An entity model may be loaded, and tables, attributes, and foreign keys of a data warehouse model may be created based upon the directives model and the entity model. Mappings may be created between tables, columns, and foreign keys of the data warehouse model and entities, attributes, and relationships, respectively, of the entity model. Code to define a data warehouse may be generated based upon the tables, attributes, and foreign keys of the data warehouse model. Code to transfer data from the source data source can be generated based upon the created mappings. A lineage report can be generated that provides information identifying a corresponding source for each table and column in the data warehouse.

First claim

The invention claimed is:

1. A system, comprising:
a processor; and
a storage for storing instructions which, when executed by the processor, causes the system to:
provide a graphical user interface (“GUI”) to a user that enables the user to graphically model mappings from a source data model to a target data model, the GUI presenting a graphical representation of the mappings to the user;
receive, via the GUI, an input from the user that graphically represents the mappings;
generate directives code for defining directives based on the mappings;
generate schema code for creating the target data model based on the directives; and
generate pipeline code for transferring data from the source data model to the target data model based on the directives using predefined patterns.

2. The system of claim 1 , wherein the source data model is a structured query language (“SQL”) database.

3. The system of claim 1 , wherein the target data model is a data warehouse.

4. The system of claim 1 , wherein the instructions further cause the system to:
present entities in the source data model to the user for selection via the GUI.

5. The system of claim 1 , wherein the mappings include at least one of:
a correspondence between an entity in the source data model and a table in the target data model;
a correspondence between an attribute in the source data model and a column in the target data model; or
a correspondence between a relationship in the source data model and a foreign key in the target data model.

6. The system of claim 1 , wherein the mappings include at least one of data denormalization or data aggregation.

7. A computer-readable storage medium storing instructions which, when executed by a processor, cause the processor to:
receive directives information from a user via a graphical user interface (“GUI”), the directives information being provided graphically by the user using the GUI, the directives information specifying how to map one or more source data models to a target data model using predefined pattern templates;
generate schema code for creating the target data model based on the directives information; and
generate pipeline code for copying data from the one or more source data models to the target data model based on the directives information.

8. The computer-readable storage medium of claim 7 , wherein the instructions further cause the processor to:
generate directives code based on the directives information.

9. The computer-readable storage medium of claim 7 , wherein the directives information includes directives code.

10. The computer-readable storage medium of claim 9 , wherein the directives code includes JavaScript Object Notation (“JSON”) language.

11. The computer-readable storage medium of claim 7 , wherein the schema code includes SQL language.

12. The computer-readable storage medium of claim 7 , wherein the pipeline code includes extract-transform-load (“ETL”) code.

13. The computer-readable storage medium of claim 7 , wherein the target data model includes surrogate keys for at least one of a data dimension or a time dimension.

14. A computer-implemented method, comprising:
receiving, via a graphical user interface (“GUI”), an input from a user, the input including mappings from a source data model to a target data model, the mappings being graphically defined by the user using the GUI;
generating first code for creating the target data model based on the mappings; and
generating second code for transferring data from the source data model to the target data model based on the mappings.

15. The computer-implemented method of claim 14 , further comprising:
executing the first code to create the target data model.

16. The computer-implemented method of claim 15 , further comprising:
executing the second code to transfer data from the source data model to the target data model.

17. The computer-implemented method of claim 14 , wherein receiving the input comprises:
receiving a first selection from the user of an entity or an attribute in the source data model; and
receiving a second selection of a dimension table, a fact table, or a column in the target data model.

18. The computer-implemented method of claim 14 , further comprising:
graphically displaying the mappings to the user via the GUI.

19. The computer-implemented method of claim 14 , further comprising:
generating directives code based on the mappings.

20. The computer-implemented method of claim 14 , further comprising:
generating a lineage report that traces tables and/or columns in the target data model to corresponding entities and/or attributes, respectively, in the source data model.

Assignees

• Microsoft Technology Licensing Llc

Inventors

• Gibson William Edward

Classifications

• G06F16/258 Primary

  Data format conversion from or to a database · CPC title

• G06F16/252

  between a Database Management System and a front-end application · CPC title

• G06F3/0482

  Interaction with lists of selectable items, e.g. menus · CPC title

• G06F16/254

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

• G06F16/84 Primary

  Mapping; Conversion · CPC title