Back to results
US 8,543,535 B2Granted
Generation of star schemas from snowflake schemas containing a large number of dimensions
AssigneeOracle International Corporation
InventorsSamir Satpathy, Alextair Dominico Mascarenhas, Harvard Jun-Hua Pan, Kenneth Khiaw Hong Eng, Ladislav Kovac
Life of this patent
Estimating the term…
Abstract
An aspect of the present invention simplifies generating a star schema from a snowflake schema. In an embodiment, a user first specifies fact tables to be included in a star schema, and a synchronization tool inspects the snowflake schema to determine the dimension tables linked to the specified fact tables. The determined dimension tables are included in the star schema sought to be generated.
Claims17
A method of supporting business applications to operate based on data stored in data warehouses, said method comprising:
- storing data in a plurality of fact tables and a plurality of dimension tables of a data warehouse, said plurality of fact tables and said plurality of dimension tables being initially organized in the form of a first star schema, each of some of said plurality of fact tables and said plurality of dimension tables having a corresponding set of key fields, which are enabled to be configured to change said first star schema to a snowflake schema;
- providing an application development framework (ADF) in a digital processing system external to said data warehouse, said ADF containing metadata objects that form a layer of abstraction over said plurality of fact tables and said plurality of dimension tables present in said data warehouse,
- wherein a developer is enabled to specify links between said metadata objects in said ADF, which in turn updates the key fields of corresponding tables to cause the formation of said snowflake schema in said data warehouse,
- wherein a business application developed using said ADF is designed to operate with data organized only as star schemas in said data warehouse, thereby necessitating the generation of a second star schema corresponding to said snowflake schema;
- generating in said digital processing system, said second star schema corresponding to said snowflake schema, said generating comprising:
- maintaining in said ADF, in response to said developer specifying said links, a definition of said snowflake schema comprising said links between said metadata objects;
- receiving from a user, in said digital processing system, identifiers of metadata objects in said ADF corresponding to a first set of fact tables to be included in said second star schema, wherein said first set of fact tables is contained in said plurality of fact tables;
- inspecting, using said digital processing system, said definition of said snowflake schema maintained in said ADF, said inspecting determining metadata objects in said ADF identifying a first set of dimension tables, wherein said definition indicates that each of said first set of dimension tables is linked to at least one of said first set of fact tables, wherein said first set of dimension tables is contained in said plurality of dimension tables;
- including, using said digital processing system, said first set of fact tables and also said first set of dimension tables in the generation of said second star schema,
- converting data organized according to said snowflake schema in said data warehouse to data according to said second star schema, wherein said receiving, said inspecting and said including enable said user to ensure inclusion of data of the pertinent tables in said second star schema;
- and
- processing queries from said business application, said queries being designed to operate with the data organized as said second star schema.
9 more elements
A computing system comprising:
- a data warehouse configured to maintain data in a plurality of fact tables and a plurality of dimension tables, said plurality of fact tables and said plurality of dimension tables being initially organized in the form of a first star schema, each of some of said plurality of fact tables and said plurality of dimension tables having a corresponding set of key fields, which are enabled to be configured to change said first star schema to a snowflake schema;
- a server system configured to provide an application development framework (ADF) external to said data warehouse, said ADF containing metadata objects that form a layer of abstraction over said plurality of fact tables and said plurality of dimension tables present in said data warehouse;
- a developer system configured to enable a developer to specify links between said metadata objects in said ADF, which in turn updates the key fields of corresponding tables to cause the formation of said snowflake schema in said data warehouse,
- wherein said server system executes a business application developed using said ADF, said business application designed to operate with data organized only as star schemas in said data warehouse, thereby necessitating the generation of a second star schema corresponding to said snowflake schema;
- and
- a synchronization tool configured to generate said second star schema corresponding to said snowflake schema, said synchronization tool operable to:
- maintain in said ADF, in response to said developer specifying said links, a definition of said snowflake schema comprising said links between said metadata objects;
- receive from a user, identifiers of metadata objects in said ADF corresponding to a first set of fact tables to be included in said second star schema, wherein said first set of fact tables is contained in said plurality of fact tables;
- inspect said definition of said snowflake schema maintained in said ADF to determine metadata objects in said ADF identifying a first set of dimension tables, wherein said definition indicates that each of said first set of dimension tables is linked to at least one of said first set of fact tables, wherein said first set of dimension tables is contained in said plurality of dimension tables;
- include said first set of fact tables and also said first set of dimension tables in the generation of said second star schema,
- wherein said synchronization tool generates said star schema containing said first set of fact tables and said first set of dimension tables;
- and
- convert data organized according to said snowflake schema in said data warehouse to data according to said second star schema,
- wherein said synchronization tool enables said user to ensure inclusion of pertinent tables in said second star schema,
- wherein said server system processes queries from said business application, said queries being designed to operate with the data organized as said second star schema,
- wherein at least one of said data warehouse and said synchronization tool comprises a processor and a memory, said processor retrieving and executing instructions from said memory.
13 more elements
A non-transitory machine readable medium storing one or more sequences of instructions for causing a digital processing system to support business applications to operate based on data stored in data warehouses, wherein execution of said one or more sequences of instructions by one or more processors contained in said digital processing system causes said digital processing system to perform the actions of:
- storing data in a plurality of fact tables and a plurality of dimension tables of a data warehouse external to said digital processing system, said plurality of fact tables and said plurality of dimension tables being initially organized in the form of a first star schema, each of some of said plurality of fact tables and said plurality of dimension tables having a corresponding set of key fields, which are enabled to be configured to change said first star schema to a snowflake schema;
- providing an application development framework (ADF) in said digital processing system, said ADF containing metadata objects that form a layer of abstraction over said plurality of fact tables and said plurality of dimension tables present in said data warehouse,
- wherein a developer is enabled to specify links between said metadata objects in said ADF, which in turn updates the key fields of corresponding tables to cause the formation of said snowflake schema in said data warehouse,
- wherein a business application developed using said ADF is designed to operate with data organized only as star schemas in said data warehouse, thereby necessitating the generation of a second star schema corresponding to said snowflake schema;
- generating in said digital processing system, said second star schema corresponding to said snowflake schema, said generating comprising:
- maintaining in said ADF, in response to said developer specifying said links, a definition of said snowflake schema comprising said links between said metadata objects;
- receiving from a user, in said digital processing system, identifiers of metadata objects in said ADF corresponding to a first set of fact tables to be included in said second star schema, wherein said first set of fact tables is contained in said plurality of fact tables;
- inspecting, using said digital processing system, said definition of said snowflake schema maintained in said ADF, said inspecting determining metadata objects in said ADF identifying a first set of dimension tables, wherein said definition indicates that each of said first set of dimension tables is linked to at least one of said first set of fact tables, wherein said first set of dimension tables is contained in said plurality of dimension tables;
- including, using said digital processing system, said first set of fact tables and also said first set of dimension tables in the generation of said second star schema,
- converting data organized according to said snowflake schema in said data warehouse to data according to said second star schema, wherein said receiving, said inspecting and said including enable said user to ensure inclusion of data of the pertinent tables in said second star schema;
- and
- processing queries from said business application, said queries being designed to operate with the data organized as said second star schema.
9 more elements
Dependencies are inferred from the claim text.