Facilitating identification of star schemas in database environments

• US 9,158,818 B2
• Filed: 04/05/2007
• Issued: 10/13/2015
• Est. Priority Date: 04/05/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Kadambalil Baby, Geemon, Jayashankar, Mahesh, Fragapane, Paolo, Mali, Suresh, Durran, Mike, Mittal, Ashish, Steadman, James
• Primary Examiner(s): Kim, Taelor
• Application Number:
  US11/696,730
• Publication Number:
  US 20080249985A1
• Time in Patent Office: 3,113 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

Facilitating identification of star schemas in database environments. In an embodiment, queries directed to relational database tables (organized according to normalized schema) are examined to determine the relationships specified in the queries. A star schema is then generated by including the specified relationships. A data warehouse may be organized according to the generated star schema and the data previously stored in the database tables may be copied to the data warehouse.

Claims

1. A non-transitory computer readable storage medium storing one or more sequences of instructions for causing a system to facilitate identification of a star schema defining a structure for storing a desired data in a data warehouse, said desired data being already stored in a database organized as a normalized schema, wherein said normalized schema of said database defines a plurality of tables in said database, wherein execution of said one or more sequences of instructions by one or more processors contained in said system causes said system to perform the actions of:

receiving a first query and a second query directed to said database organized as said normalized schema;

examining said first query to determine a first join of a first table and a second table, wherein said first table and said second table are contained in said plurality of tables, and wherein said first join is according to said normalized schema,

wherein said examining also examines said second query to determine a third join of a seventh table and an eighth table, wherein said seventh table and said eighth table are contained in said plurality of tables, wherein said third join is according to said normalized schema; and

forming said star schema for said data warehouse by including a first relationship associating said first table and said second table in said star schema based on said examining of said first query in response to said receiving said first query, said forming also including a sixth relationship associating said seventh table and said eighth table in said star schema based on said examining of said second query in response to said receiving of said second query,

whereby said star schema includes both of said first relationship and said sixth relationship.

2. The non-transitory computer readable medium of claim 1, wherein said examining examines said first query to further determine a second join of said second table and a third table, wherein said second join is according to said normalized schema, further comprising more instructions for:

including a second relationship associating said second table and said third table in said star schema; and

identifying said second table as a fact table and said first table and said third table as dimension tables in said star schema based on determination of said first join and said second join in said first query directed to said database.

3. The non-transitory computer readable medium of claim 2, further comprising more instructions for:

identifying a fourth table contained in said plurality of tables, but not yet included in said star schema, by inspecting said normalized schema;

determining a fifth table in said star schema having a relationship with said fourth table in said normalized schema by inspecting said normalized schema; and

adding a fourth relationship associating said forth table and said fifth table in said star schema in response to said determining said fifth table,

wherein said identifying of said fourth table, said determining and said adding are performed after said examining.

4. The non-transitory computer readable medium of claim 3, further comprising more instructions for merging a first mini-star with a second mini-star, wherein said first mini-star contains said first relationship, said second relationship and said fourth relationship, wherein said merging comprises:

finding a common dimension table in both of said first mini-star and said second mini-star; and

linking said first mini-star with said second mini-star using said common dimension table as a single node in response to said finding said common dimension table.

5. A method of managing data, said method comprising:

storing data of interest in a database organized as a plurality of tables specified according to a normalized schema, wherein said normalized schema of said database also indicates a structure of each of said plurality of tables and a plurality of relationships existing among said plurality of tables in said database;

receiving a plurality of queries directed to said database organized as said normalized schema, wherein each of said plurality of queries specifies a corresponding one of a plurality of subset of relationships, said subset of relationships being contained in said plurality of relationships;

identifying a star schema by including said plurality of subset of relationships in response to said receiving and by examining said plurality of queries;

organizing a data warehouse according to a second structure defined by said star schema; and

after said organizing, copying data of interest previously stored in said database to said data warehouse organized as said star schema,

wherein said copying is performed after said receiving and said identifying also.

6. The method of claim 5, wherein said generating further comprises:

identifying a plurality of mini-star schemas corresponding to said plurality of queries, wherein each of said plurality of mini-star schemas includes said subset of relationships specified in one of said plurality of queries directed to said database organized as said normalized schema; and

merging said plurality of mini-star schemas to generate said star schema.

7. The method of claim 6, wherein said identifying further comprises:

determining a first table not existing in said plurality of mini-star schemas and a second table existing in said plurality of mini-star schemas, wherein said first table and said second table are contained in said plurality of tables; and

adding a new relationship to one of said plurality of mini-star schemas, said new relationship existing between said first table and said second table according to said normalized schema, wherein said new relationship is contained in said plurality of relationships.

8. The method of claim 6, wherein merging a first mini-star schema with a second mini-star schema is performed only if there is a common table existing in said first mini-star schema and said second mini-star schema, wherein said first mini-star schema and said second mini-star schema are contained in said plurality of mini-star schemas.

9. The method of claim 5, wherein each of said subset of relationships is specified by a corresponding join in the corresponding query according to said normalized schema.

10. A digital processing system for managing data, said digital processing system comprising:

a memory to store a plurality of instructions;

and one or processors to execute said plurality of instructions, which causes said digital processing system to perform the actions of;

storing data of interest in a database organized as a plurality of tables specified according to a normalized schema, wherein said normalized schema of said database also indicates a structure of each of said plurality of tables and a plurality of relationships existing among said plurality of tables in said database;

examining a plurality of queries directed to said database organized as said normalized schema, wherein each of said plurality of queries specifies a corresponding one of a plurality of subset of relationships, said subset of relationships being contained in said plurality of relationships;

identifying a star schema by including said plurality of subset of relationships based on said examining said plurality of queries;

organizing a data warehouse according to a second structure defined by said star schema; and

after said organizing, copying data of interest previously stored in said database to said data warehouse organized as said star schema,

wherein said copying is performed after said examining and said identifying also.

11. The digital processing system of claim 10, wherein said generating further comprises:

identifying a plurality of mini-star schemas corresponding to said plurality of queries, wherein each of said plurality of mini-star schemas includes said subset of relationships corresponding to one of said plurality of queries; and

merging said plurality of mini-star schemas to generate said star schema.

12. The digital processing system of claim 11, wherein said identifying further comprises:

determining a first table not existing in said plurality of mini-star schemas and a second table existing in said plurality of mini-star schemas, wherein said first table and said second table are contained in said plurality of tables; and

adding a new relationship to one of said plurality of mini-star schemas, said new relationship existing between said first table and said second table according to said normalized schema, wherein said new relationship is contained in said plurality of relationships.

13. The digital processing system of claim 11, wherein said merging a first mini-star schema with a second mini-star schema is performed only if there is a common table existing in said first mini-star schema and said second mini-star schema, wherein said first mini-star schema and said second mini-star schema are contained in said plurality of mini-star schemas.

14. The digital processing system of claim 10, wherein each of said subset of relationships is specified by a corresponding join in the corresponding query according to said normalized schema.

15. The digital processing system of claim 10, wherein said actions further comprise:

receiving a request to identify a star schema for said database,

wherein said examining, said identifying, and said organizing are performed in response to receiving said request.

16. The digital processing system of claim 15, wherein some of said plurality of queries are received prior to receiving of said request, whereby said star schema is identified based on at least some queries received prior to said request.