Method and Apparatus for Declarative Data Warehouse Definition for Object-Relational Mapped Objects

• US 20090271345A1
• Filed: 04/25/2008
• Published: 10/29/2009
• Est. Priority Date: 04/25/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Jaouani, Rafik, Rich, Lawrence Scott
• Granted Patent: US 9,043,273 B2
• Time in Patent Office: Days
• US Class Current: 706/46
• CPC Class Codes: G06F 16/13 File access structures, e.g. distributed indices arrangements of input from, or output to, record carriers G06F3/06
• 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/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques
• CPC Class Codes: G06N 5/02 Knowledge representation; Symbolic representation

Abstract

A data warehouse is constructed using the relational mapping of a transactional database without reconstructing the data relationships of the transactional database. First, an application programmer analyzes an object model in order to describe facts and dimensions using the objects, attributes, and paths of the object model. Each of the dimensions has an identifier that correlates an item in the transactional database to a dimension record in the data warehouse. The fact and dimension descriptions are saved to a description file. Second, a Data Warehouse Engine (DWE) then access the description file and uses the object model, fact and dimension descriptions, and object-relational mapping to map transactional data to the data warehouse.

Claims

1. A method to create a data warehouse comprising:

analyzing a plurality of questions to be answered from a plurality of transactional data of a business;

analyzing an object model of the transactional database;

identifying a fact in the, object model that is common to each of the plurality of questions;

creating a first description of the fact;

identifying a plurality of dimensions in the object model to which the fact can be correlated;

identifying a dimension path in the object model for each of the plurality of dimensions;

translating each of the dimension paths into a second description using a dotted notation;

combining the first description and the second description into a third description;

entering the third description into a description file;

using a computer, automatically accessing the description file to read the third description and using the third description to automatically generate a plurality of fact and dimension table definitions for the data warehouse;

when the plurality of fact and dimension table definitions are generated, automatically populating the data warehouse.

2. The method of claim 1 wherein a plurality of fact records are produced by a query of a plurality of fact values for each of a plurality of dimension combinations.

3. The method of claim 1 wherein a plurality of dimension records are produced by a query of a plurality of dimension values according to a plurality of path descriptions.

4. The method of claim 1 wherein, when a change is made to an object-relational mapping in the mapping file, a plurality of fact and dimension queries are adjusted in accordance with the change.

5. The method of claim 1 wherein, when a change is made to an object-relational mapping in the mapping file, a plurality of fact and dimension table definitions are adjusted in accordance with the change.

6. The method of claim 1 wherein when a change is made to the object-relational mapping in the mapping file, a new object-relational mapping is used to process a logical description of the data warehouse and produce a plurality of data warehouse definitions and a plurality of data extraction queries.

7. An apparatus comprising:

a computer connected to a memory and a display;

a program in the memory, the program containing instructions to cause the computer to perform the following steps;

responsive to a user entering a plurality of fact and dimension descriptions into a description file, reading the plurality of fact and dimension descriptions;

accessing a mapping file containing a plurality of object-related mapping for a transactional database;

using the plurality of fact and dimension descriptions, generating a plurality of fact and dimension table definitions for a data warehouse; and

using the fact and dimension table definitions and the plurality of object-related mapping, populating the data warehouse;

wherein each of the plurality of fact and dimension descriptions includes an identifier correlating an item in the transactional database to a record in the data warehouse.

8. The apparatus of claim 7 wherein a plurality of fact records are produced by a query of a plurality of fact values for each of a plurality of dimension combinations.

9. The apparatus of claim 7 wherein a plurality of dimension records are produced by a query of a plurality of dimension values according to a plurality of path descriptions.

10. The apparatus of claim 7 wherein, when a change is made to an object-relational mapping in the mapping file, a plurality of fact and dimension queries are adjusted.

11. The apparatus of claim 7 wherein when a change is made to the object-relational mapping in the mapping file, a new object-relational mapping is used to process a logical description of the data warehouse and produce a plurality of data warehouse definitions and a plurality of data extraction queries.

12. A computer readable memory for causing a computer to map data from an object model to a data warehouse, comprising:

a computer readable storage medium;

a computer program stored in the computer readable storage medium;

the computer readable storage medium so configured by the computer program, causes the computer to perform the following steps;

responsive to a user entering a plurality of fact and dimension descriptions into a description file, reading the fact and dimension descriptions,

accessing a mapping file containing a plurality of object-related mapping for a transactional database;

using the fact and dimension descriptions, generating a plurality of fact and dimension table definitions for the data warehouse; and

using the plurality of fact and table definitions and the plurality of object-relational mapping, populating the data warehouse;

wherein each of the plurality of fact and dimensions includes an identifier correlating an item in a transactional database to a record in the data warehouse.

13. The computer readable memory of claim 12 wherein a plurality of fact records are produced by a query of a plurality of fact values for each of a plurality of dimension combinations.

14. The computer readable memory of claim 12 wherein a plurality of dimension records are produced by a query of a plurality of dimension values according to a plurality of path descriptions.

15. The computer readable memory of claim 12 wherein, when a change is made to an object-relational mapping in the mapping file, a plurality of fact and dimension queries are adjusted in accordance with the change.

16. The computer readable memory of claim 12 where, when a change is made to an object-relational mapping in the mapping file, the plurality of fact and dimension table definitions are adjusted in accordance with the change.

17. The computer readable memory of claim 12 wherein, when a change is made to the object-relational mapping in the mapping file, a new object-relational mapping is used to process a logical description of the data warehouse and to produce a plurality of data warehouse definitions and a plurality of data extraction queries.