Adaptive data model and warehouse palette

• US 8,886,591 B2
• Filed: 09/09/2011
• Issued: 11/11/2014
• Est. Priority Date: 09/09/2011
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Azcue, Jeff, McDonald, Jason
• Primary Examiner(s): Uddin, Md. I
• Application Number:
  US13/228,800
• Publication Number:
  US 20130066826A1
• Time in Patent Office: 1,159 Days
• Field of Search: 707/600, 707/601, 707/602, 707/791, 707/792, 707/802, 707/803, 705/2, 705/4, 705/7.28
• US Class Current: 707/602
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

Systems, methods, and other embodiments associated with and\ adaptive data model and warehouse palette are provided. In one embodiment, a method includes providing a user interface for collecting item definition attributes from a user. A database structure is generated to store item data according to collected the item definition attributes. An extract, transform, and load (ETL) layer is generated to extract item data from user data, transform the extracted data for storing in the database structure, and load the transformed extracted data into the database structure.

Claims

1. A computing system, comprising:

a definition engine configured to;

provide a core fact table for storing data for risk item attributes common to all lines of business (LOBs);

provide an interface for collecting, from a user, user defined risk item attributes for a specific LOB;

create corresponding user defined risk item attributes for the specific LOB;

generate an adaptive database structure that includes an XML file containing metadata describing the user defined risk item attributes;

with an ETL (Extract, transform, and load) tool, receive the XML file and identify the user defined risk item attributes in the XML file, and generate an ETL layer comprising interface rules for populating line of business extension fact tables with data for the user defined risk item attributes;

create a data mart schema comprising the core fact table and the extension fact tables, where the core fact table does not store data corresponding to the user defined risk item attributes collected from the user;

populate the line of business extension fact tables with data using the ETL layer; and

integrate the adaptive database structure including the line of business extension fact tables and the core fact table to construct a data warehouse according to the data mart schema;

where the core fact table and line of business extension fact tables include join keys that can be used to join the core fact table with the extension fact tables; and

where the core fact table and the extension fact tables store records corresponding to transactions and further where the data mart schema comprises dimension tables that are keyed to columns of the core fact table and the extension fact tables and store records corresponding to attributes of the transaction; and

a memory configured to store a populated adaptive database structure.

2. The computing system of claim 1, where the definition engine and ETL layer comprise an ETL tool and further where the definition engine is configured to generate the ETL layer by generating instructions for the ETL tool that cause the ETL tool to identify the user defined attributes in the XML file and create interfaces and data structures for storing the user defined attributes.

3. The computing system of claim 1, where the definition engine is configured to generate the ETL layer by extracting metadata from the user defined attributes and storing the metadata in an ETL tool metadata repository.

4. The computing system of claim 1, where the definition engine is configured to integrate the adaptive database structure and the ETL layer with a core database structure and a core ETL layer.

5. The computing system of claim 4, where the core fact table and the extension fact tables include join keys that can be used to join the core fact table with selected extension fact tables to create a view that corresponds to an extended fact table for data for a line of business associated with the selected extension fact table.

6. A computer-implemented method comprising:

providing a core fact table for storing data for risk item attributes common to all lines of business (LOBs);

providing an interface for collecting, from a user, user defined risk item attributes for a specific LOB;

creating corresponding user defined risk item attributes for the specific LOB;

generating an adaptive database structure that includes an XML file containing metadata describing the user defined risk item attributes;

with an ETL (Extract, transform, and load) tool, receiving the XML file and identifying the user defined risk item attributes in the XML file, and generating an ETL layer comprising interface rules for populating line of business extension fact tables with data for the user defined risk item attributes;

creating a data mart schema comprising the core fact table and the extension fact tables, where the core fact table does not store data corresponding to the user defined risk item attributes collected from the user;

populating the line of business extension fact tables with data using the ETL layer; and

integrating the adaptive database structure including the line of business extension fact tables and the core fact table to construct a data warehouse according to the data mart schema;

where the core fact table and line of business extension fact tables include join keys that can be used to join the core fact table with the extension fact tables; and

where the core fact table and the extension fact tables store records corresponding to transactions and further where the data mart schema comprises dimension tables that are keyed to columns of the core fact table and the extension fact tables and store records corresponding to attributes of the transaction.

7. The computer-implemented method of claim 6 where instructions for the ETL tool to identify the user defined risk items attributes in the XML file and generate an ETL layer are generated automatically in response to receiving the XML file.

8. The computer-implemented method of claim 6 where the core fact tables and extension fact tables include join keys that can be used to join the core fact table with the extension fact tables, the method comprising creating a materialized view for a selected line of business by joining the core fact table with one or more extension fact tables that store data for the selected line of business.

9. The computer-implemented method of claim 6 where providing an interface comprises providing a web based interface to access an ETL tool in a remote server.

10. A non-transitory computer-readable medium storing computer-executable instructions that when executed by a computer cause the computer to perform a method, the method comprising:

providing a core fact table for storing data for risk item attributes common to all lines of business (LOBs);

providing an interface for collecting, from a user, user defined risk item attributes for a specific LOB;

creating corresponding user defined risk item attributes for the specific LOB;

generating an adaptive database structure that includes an XML file containing metadata describing the user defined risk item attributes;

with an ETL (Extract, transform, and load) tool, receiving the XML file and identifying the user defined risk item attributes in the XML file, and generating an ETL layer comprising interface rules for populating line of business extension fact tables with data for the user defined risk item attributes;

creating a data mart schema comprising the core fact table and the extension fact tables, where the core fact table does not store data corresponding to the user defined risk item attributes collected from the user;

populating the line of business extension fact tables with data using the ETL layer; and

integrating the adaptive database structure including the line of business extension fact tables and the core fact table to construct a data warehouse according to the data mart schema;

where the core fact table and line of business extension fact tables include join keys that can be used to join the core fact table with the extension fact tables; and

where the core fact table and the extension fact tables store records corresponding to transactions and further where the data mart schema comprises dimension tables that are keyed to columns of the core fact table and the extension fact tables and store records corresponding to attributes of the transaction.

11. The non-transitory computer-readable medium of claim 10 where instructions for the ETL tool to identify the user defined risk items attributes in the XML file and generate an ETL layer are generated automatically in response to receiving the XML file.

12. The non-transitory computer-readable medium of claim 10 where the core fact table and extension fact tables include join keys that can be used to join the core fact table with the extension fact tables, where the method further comprises creating a materialized view for a selected line of business by joining the core fact table with one or more extension fact tables that store data for the selected line of business.

13. The non-transitory computer-readable medium of claim 10 where providing an interface comprises providing a web based interface to access an ETL tool in a remote server.