DYNAMICALLY BUILDING AND POPULATING DATA MARTS WITH DATA STORED IN REPOSITORIES

• US 20100106747A1
• Filed: 10/23/2008
• Published: 04/29/2010
• Est. Priority Date: 10/23/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: HONZAL, BENJAMIN, Kache, Holger, Roth, Mary A., Sauter, Guenter A.
• Granted Patent: US 7,970,728 B2
• Time in Patent Office: Days
• US Class Current: 707/803
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Methods, systems, and articles of manufacture for constructing and populating data marts with dimensional data models from a set of data repositories that contain factual and association information about a set of related assets are disclosed. An intermediate data warehouse is generated to process the facts and associations for each asset. Using the intermediate warehouse, one or more data marts are generated with fact tables, dimensions, and hierarchies to fully model the information available for each asset.

Claims

1. A computer-implemented method for dynamically building and populating a data mart with data from a data repository, comprising:

receiving a user configuration that specifies a data repository and an asset type in the data repository;

generating a schema for an intermediate data warehouse for storing data relevant to the asset type, wherein the data relevant to the asset type comprises all measures for all assets of the asset type;

populating the intermediate data warehouse by querying the data repository for the data relevant to the asset type;

generating a schema for a data mart corresponding to the asset type; and

populating the data mart by querying the intermediate data warehouse for the data relevant to the asset type.

2. The method of claim 1, wherein the schema for the data mart comprises facts, dimensions, and hierarchies.

3. The method of claim 1, wherein the schema for the data mart comprises a master fact table, a multiple fact table, a static dimension representing measures, and a dynamic dimension representing an asset group;

and wherein the multiple fact table links the dynamic dimension to the master fact table.

4. The method of claim 1, wherein the user configuration further specifies measure types and associations between asset types, wherein the data relevant to the asset type further comprises associations between asset types, and wherein the dimensions are dynamically derived from associations between asset types.

5. The method of claim 1, wherein the schema for the intermediate data warehouse is a vertical schema, and wherein the schema for the data mart is selected from at least a snowflake schema and a star schema.

6. The method of claim 4, wherein the measures of the asset type comprise simple measures and complex measures;

wherein the user configuration further specifies an input measure and an algorithm for each complex measure type;

wherein each complex measure is computed using the input measure and the algorithm specified by the user configuration; and

wherein the data relevant to the asset type is stored with a timestamp to allow versioning of the data relevant to the asset type.

7. A computer readable storage medium containing a program which, when executed, performs an operation for dynamically building and populating a data mart with data from a data repository, comprising:

receiving a user configuration that specifies a data repository and an asset type in the data repository;

generating a schema for an intermediate data warehouse for storing data relevant to the asset type, wherein the data relevant to the asset type comprises all measures for all assets of the asset type;

populating the intermediate data warehouse by querying the data repository for the data relevant to the asset type;

generating a schema for a data mart corresponding to the asset type; and

populating the data mart by querying the intermediate data warehouse for the data relevant to the asset type.

8. The computer-readable storage medium of claim 7, wherein the schema for the data mart comprises facts, dimensions, and hierarchies.

9. The computer-readable storage medium of claim 7, wherein the schema for the data mart comprises a master fact table, a multiple fact table, a static dimension representing measures, and a dynamic dimension representing an asset group;

and wherein the multiple fact table links the dynamic dimension to the master fact table.

10. The computer-readable storage medium of claim 7, wherein the user configuration further specifies measure types and associations between asset types, wherein the data relevant to the asset type further comprises associations between asset types, and wherein the dimensions are dynamically derived from associations between asset types.

11. The computer-readable storage medium of claim 7, wherein the schema for the intermediate data warehouse is a vertical schema, and wherein the schema for the data mart is selected from at least a snowflake schema and a star schema.

12. The computer-readable storage medium of claim 10, wherein the measures of the asset type comprise simple measures and complex measures;

wherein the user configuration further specifies an input measure and an algorithm for each complex measure type;

wherein each complex measure is computed using the input measure and the algorithm specified by the user configuration; and

wherein the data relevant to the asset type is stored with a timestamp to allow versioning of the data relevant to the asset type.

13. A system, comprising:

a processor; and

a memory containing a program, which when executed by the processor is configured to perform an operation for dynamically building and populating a data mart with data from a data repository, comprising;

receiving a user configuration that specifies a data repository and an asset type in the data repository;

generating a schema for an intermediate data warehouse for storing data relevant to the asset type, wherein the data relevant to the asset type comprises all measures for all assets of the asset type;

populating the intermediate data warehouse by querying the data repository for the data relevant to the asset type;

generating a schema for a data mart corresponding to the asset type; and

populating the data mart by querying the intermediate data warehouse for the data relevant to the asset type.

14. The system of claim 13, wherein the schema for the data mart comprises facts, dimensions, and hierarchies.

15. The system of claim 13, wherein the schema for the data mart comprises a master fact table, a multiple fact table, a static dimension representing measures, and a dynamic dimension representing an asset group;

and wherein the multiple fact table links the dynamic dimension to the master fact table.

16. The system of claim 13, wherein the user configuration further specifies measure types and associations between asset types, wherein the data relevant to the asset type further comprises associations between asset types, and wherein the dimensions are dynamically derived from associations between asset types.

17. The system of claim 13, wherein the schema for the intermediate data warehouse is a vertical schema, and wherein the schema for the data mart is selected from at least a snowflake schema and a star schema.

18. The system of claim 16, wherein the measures of the asset type comprise simple measures and complex measures;

wherein the user configuration further specifies an input measure and an algorithm for each complex measure type;

wherein each complex measure is computed using the input measure and the algorithm specified by the user configuration; and

wherein the data relevant to the asset type is stored with a timestamp to allow versioning of the data relevant to the asset type.

19. A computer-implemented method for dynamically building and populating a plurality of data marts with data from a plurality of data repositories, comprising:

receiving user configuration input that specifies the plurality of data repositories and a plurality of asset types in the plurality of data repositories, wherein the specified plurality of data repositories contain;

(i) a plurality of assets of the specified plurality of asset types;

(ii) at least one association between assets of two asset types;

(iii) at least one asset group that groups assets based on the at least one association between two asset types; and

(iv) a plurality of measures of at least one measure type, wherein the plurality of measures contain values that describe the plurality of assets;

generating a schema for an intermediate data warehouse for storing data relevant to the specified plurality of asset types, wherein the data relevant to the specified plurality of asset types comprises;

(i) measures for measure types for assets of the specified plurality of asset types;

(ii) at least one association between an asset of one asset type of the specified plurality of asset types and an asset of another asset type of the specified plurality of asset types; and

(iii) at least one asset group that groups assets of the specified plurality of asset types; and

wherein the schema for the intermediate data warehouse comprises a timestamp to allow versioning of records in the intermediate data warehouse;

populating the intermediate data warehouse by querying the specified plurality of data repositories for the data relevant to the specified plurality of asset types;

generating, for each asset type of the specified plurality of asset types, a schema for a data mart corresponding to the respective asset type;

wherein the schema for the data mart comprises (i) a static dimension representing measure types and (ii) a static dimension representing time to allow versioning of records in the data mart;

wherein a static dimension is a dimension that exists in each of the plurality of data marts; and

populating, for each asset type of the specified plurality of asset types, the data mart corresponding to the respective asset type by querying the intermediate data warehouse for the data relevant to the respective asset type.

20. The method of claim 19, wherein the schema for the data mart further comprises a master fact table, a multiple fact table, and a dynamic dimension representing an asset group;

wherein a dynamic dimension is specific to the respective data mart and represents an asset group; and

wherein the multiple fact table links the dynamic dimension to the master fact table.

21. The method of claim 19, wherein the user configuration input further specifies measure types and associations between assets of different asset types;

wherein the schema for the intermediate data warehouse is a vertical schema;

wherein the schema for each data mart is selected from at least a snowflake schema and a star schema;

wherein the measures of the asset type comprise simple measures and complex measures;

wherein simple measures contain values found in the plurality of data repositories;

wherein complex measures contain values computing from values found in the plurality of data repositories;

wherein the user configuration input further specifies, for each complex measure type, an input measure and an algorithm; and

wherein each complex measure is computed using the input measure and the algorithm specified by the user configuration input.

22. The method of claim 19, wherein each data repository is selected from at least a relational repository, an object repository, and an object-relational repository.

23. A computer readable storage medium containing a program which, when executed, performs an operation for dynamically building and populating a data mart with data from a data repository, comprising:

receiving user configuration input that specifies the plurality of data repositories and a plurality of asset types in the plurality of data repositories, wherein the specified plurality of data repositories contain;

(i) a plurality of assets of the specified plurality of asset types;

(ii) at least one association between assets of two asset types;

(iii) at least one asset group that groups assets based on the at least one association between two asset types; and

(iv) a plurality of measures of at least one measure type, wherein the plurality of measures contain values that describe the plurality of assets;

generating a schema for an intermediate data warehouse for storing data relevant to the specified plurality of asset types, wherein the data relevant to the specified plurality of asset types comprises (i) measures for measure types for assets of the specified plurality of asset types;

(ii) at least one association between an asset of one asset type of the specified plurality of asset types and an asset of another asset type of the specified plurality of asset types; and

(iii) at least one asset group that groups assets of the specified plurality of asset types; and

wherein the schema for the intermediate data warehouse comprises a timestamp to allow versioning of records in the intermediate data warehouse;

populating the intermediate data warehouse by querying the specified plurality of data repositories for the data relevant to the specified plurality of asset types;

generating, for each asset type of the specified plurality of asset types, a schema for a data mart corresponding to the respective asset type;

wherein the schema for the data mart comprises (i) a static dimension representing measure types and (ii) a static dimension representing time to allow versioning of records in the data mart;

wherein a static dimension is a dimension that exists in each of the plurality of data marts; and

populating, for each asset type of the specified plurality of asset types, the data mart corresponding to the respective asset type by querying the intermediate data warehouse for the data relevant to the respective asset type.

24. The computer-readable storage medium of claim 23, wherein the schema for the data mart further comprises a master fact table, a multiple fact table, and a dynamic dimension representing an asset group;

wherein a dynamic dimension is specific to the respective data mart and represents an asset group; and

wherein the multiple fact table links the dynamic dimension to the master fact table.

25. The computer-readable storage medium of claim 23, wherein the user configuration input further specifies measure types and associations between asset types;

wherein the schema for the intermediate data warehouse is a vertical schema;

wherein the schema for each data mart is selected from at least a snowflake schema and a star schema;

wherein the measures of the asset type comprise simple measures and complex measures;

wherein simple measures contain values found in the plurality of data repositories;

wherein complex measures contain values computing from values found in the plurality of data repositories;

wherein the user configuration input further specifies, for each complex measure type, an input measure and an algorithm; and

wherein each complex measure is computed using the input measure and the algorithm specified by the user configuration input.