Method and system for creating and loading data warehouse from semi-structured document

• US 20050262129A1
• Filed: 05/20/2004
• Published: 11/24/2005
• Est. Priority Date: 05/20/2004
• Status: Active Grant

• Current Assignee: Snowflake, Inc.
• Original Assignee: International Business Machines Corporation
• Inventors: Hacigumus, Vahit Hakan, Rhodes, James J., Kreulen, Jeffrey Thomas
• Granted Patent: US 8,271,428 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: G06F 40/143 Markup, e.g. Standard Generalized Markup Language [SGML] or Document Type Definition [DTD]

Abstract

A data warehouse is created using an input file that can contain sub-documents of different formats. A root document model including path names to all nodes among the sub-documents is generated, and a table is generated with columns being derived from the path names of the root model. The sub-documents are shredded to populate the table. Then, the dimensions of the data warehouse are defined by selecting respective columns. A routine such as a DDL may then be generated to populate the data warehouse with data.

Claims

1. A computer-implemented method for creating a data warehouse from at least one semi-structured file, comprising:

analyzing said at least one file to generate at least one generic schema;

creating at least one database table using the schema;

loading data in said at least one file into corresponding columns in the table; and

defining dimensions for a data warehouse using columns of the table.

2. The method of claim 1, further comprising generating a routine to load data into the warehouse.

3. The method of claim 2, further comprising executing the routine to load data into the warehouse.

4. The method of claim 2, further comprising saving the routine.

5. The method of claim 2, comprising using an interface for at least one of:

saving, and executing, the routine.

6. The method of claim 1, comprising using at least two files that differ from another in at least one of:

structure, format, and semantics.

7. The method of claim 1, wherein the table is created automatically.

8. A service for creating a data warehouse for a customer, comprising:

generating a root model of at least one sub-document in at least one input file;

using the root model to generate at least one table;

shredding sub-documents to populate the table;

using columns in the table to define dimensions of the data warehouse; and

providing at least one of;

the data warehouse, and a tool to populate the data warehouse with data from the input file, to a customer.

9. The service of claim 8, wherein the root model at least in part includes path names to nodes in at least one document.

10. The service of claim 9, wherein the root model at least in part includes maximum node value sizes.

11. The service of claim 8, wherein each column of the table represents a path name to all nodes of the root model containing data, and each row of the table represents a sub-document in the input file.

12. The service of claim 8, wherein the root model is generated at least in part by merging a model of a first sub-document with a model of a second sub-document.

13. The service of claim 8, wherein each sub-document is shredded to populate the table with values.

14. The service of claim 13, comprising:

creating a model populated with a sub-document'"'"'s values;

reading the sub-document'"'"'s model; and

inserting values into a table from the sub-document'"'"'s model such that the table is populated with the data in the sub-documents.

15. The service of claim 8, comprising displaying columns of the table, column types, and columns sizes using a graphical user interface (GUI).

16. The service of claim 15, wherein the GUI displays at least a predetermined integer number “

x”

records from the input file.

17. The service of claim 8, wherein in response to a dimension being defined by selecting at least one column from the table, the selected columns, dimension name, column names, column types and column sizes are stored in a hash table having the dimension name as a key and at least one vector containing the column name, type and size as the value.

18. A computer executing logic comprising:

generating a root model of plural documents, the root model including at least path names to all nodes among the documents;

generating a table having one of columns, or rows, derived from the path names of the root model; and

defining plural dimensions of a data warehouse by selecting one of;

respective columns, and respective rows, of the table.

19. The computer of claim 18, wherein the logic further comprises:

shredding at least some documents to populate the table; and

generating a routine to populate the data warehouse with data.

20. The computer of claim 18, wherein the root model at least in part includes maximum node value sizes.

21. The computer of claim 18, wherein each column of the table represents a path name to all nodes of the root model containing data, and each row of the table represents a sub-document in the input file.

22. The computer of claim 18, wherein the root model is generated at least in part by merging a model of a first document with a model of a second document.

23. The computer of claim 18, wherein the logic includes:

creating a model populated with a document'"'"'s values;

reading the document'"'"'s model; and

inserting values into the table from the sub-document'"'"'s model such that the table is populated with the data in the sub-documents.

24. The computer of claim 18, wherein the logic comprises displaying columns of the table, column types, and columns sizes using a graphical user interface (GUI).

25. The computer of claim 24, wherein the GUI displays at least a predetermined integer number “

x”

records from an input file.

26. The computer of claim 18, wherein in response to a dimension being defined by selecting at least one column from the table, the selected columns, dimension name, column names, column types and column sizes are stored in a hash table having the dimension name as a key and at least one vector containing the column name, type and size as the value.

27. A computer program product having a program executable by a digital processing apparatus to create a data warehouse for storing data in an input file, comprising:

means for generating a root document model containing path names to all nodes in the input file; and

means for using the path names to establish dimensions in the data warehouse.

28. The computer program product of claim 27, wherein the means for using includes:

means for generating a table having one of columns, or rows, derived from the path names of the root model; and

means for defining plural dimensions of a data warehouse by selecting one of;

respective columns, and respective rows, of the table.

29. The computer program product of claim 28, further comprising:

means for shredding at least some documents to populate the table; and

means for generating a routine to populate the data warehouse with data.

30. The computer program product of claim 28, wherein the root model at least in part includes maximum node value sizes.

31. The computer program product of claim 28, wherein each column of the table represents a path name to all nodes of the root model containing data, and each row of the table represents a sub-document in the input file.

32. The computer program product of claim 28, wherein the root model is generated at least in part by merging a model of a first document with a model of a second document.

33. The computer program product of claim 28, comprising:

means for creating a model populated with a document'"'"'s values;

means for reading the document'"'"'s model; and

means for inserting values into the table from the sub-document'"'"'s model such that the table is populated with the data in the sub-documents.

34. The computer program product of claim 28, comprising GUI means for displaying columns of the table, column types, and columns sizes.

35. The computer program product of claim 34, wherein the GUI means displays at least a predetermined integer number “

x”

records from an input file.

36. The computer program product of claim 28, wherein in response to a dimension being defined by selecting at least one column from the table, the selected columns, dimension name, column names, column types and column sizes are stored in a hash table having the dimension name as a key and at least one vector containing the column name, type and size as the value.