Method and system for handling multiple dimensions in relational databases
First Claim
1. Method for structuring data in a computerized relational database system comprising a storage device (5) for storing the relational database in the form of a number of tables, at least one central processor (1) connected to said storage device (5), and a database manager (3) comprising instructions capable of making the processor perform queries on said database, characterized by the steps of generating a dimension type table (dim_type) of descriptions of the dimensions present in the database, each row of which including at least a dimension identifier (dim_type_id) and a dimension name (dim_type_name), generating a dimensional item table (dim_item) of dimensional items, each row of which including at least an internal item identifier (dim_item_id), a presentation name (dim_item_name), and an identifier of a dimensional description (dim_type_id) in said dimension type table, generating a location table (location) of locations representing intersection points between the various dimensions, each row of which including at least a location identifier (location_id), and generating a connection table (dim_conn) defining connections between each location and dimensional items with which they are associated, each row of which including at least a location identifier (location_id) and a dimensional item identifier (dim_item_id)
1 Assignment
0 Petitions
Accused Products
Abstract
The invention relates to a method for structuring data in a computerized relational database system. The method includes generating a dimension type table (dim_type) of descriptions of dimensions, a dimensional item table (dim_item) of dimensional items, a location table (location) of locations type representing intersection points between the various dimensions, and a connection table (dim_conn) defining connections between each location and dimensional items with which they are associated. Preferably, the values stored in the database are entered in a separate data value table (data_value). The invention also relates to a database system with data structured in accordance with the method. The invented data structure makes it possible to achieve flexibility and reuse of code without sacrificing scalability and performance of searching and reporting. Particularly, the structure facilitates a method for building queries for generating reports focusing on a subset of the data in the database limited by various criteria simultaneously involving an arbitrary number of dimensions, and a method for retrieving the items contained in a regular dimension classified according
52 Citations
44 Claims
-
1. Method for structuring data in a computerized relational database system comprising a storage device (5) for storing the relational database in the form of a number of tables, at least one central processor (1) connected to said storage device (5), and a database manager (3) comprising instructions capable of making the processor perform queries on said database, characterized by the steps of
generating a dimension type table (dim_type) of descriptions of the dimensions present in the database, each row of which including at least a dimension identifier (dim_type_id) and a dimension name (dim_type_name), generating a dimensional item table (dim_item) of dimensional items, each row of which including at least an internal item identifier (dim_item_id), a presentation name (dim_item_name), and an identifier of a dimensional description (dim_type_id) in said dimension type table, generating a location table (location) of locations representing intersection points between the various dimensions, each row of which including at least a location identifier (location_id), and generating a connection table (dim_conn) defining connections between each location and dimensional items with which they are associated, each row of which including at least a location identifier (location_id) and a dimensional item identifier (dim_item_id)
-
8. Computerized database system comprising a storage device (5) upon which is stored a relational database in the form of a number of tables, at least one central processor (1) connected to said storage device (5), and a database manager (3) comprising instructions capable of making the processor perform queries on said database, characterized by said tables including
a dimension type table (dim_type) of descriptions of the dimensions present in the database, each row of which including at least a dimension identifier (dim_type_id) and a dimension name (dim_type_name), a dimensional item table (dim_item) of dimensional items, each row of which including at least an internal item identifier (dim_item_id), a presentation name (dim_item_name), and an identifier of a dimensional description (dim_type_id) in said dimension type table, a location table (location) of locations representing intersection points between the various dimensions, each row of which including at least a location identifier (location_id), and a connection table (dim_conn) defining connections between each location and dimensional items with which they are associated, each row of which including at least a location identifier (location_id) and a dimensional item identifier (dim_item_id).
-
15. Method for performing a computerized query in a relational database, where the database at least includes a dimension type table (dim_type) of dimensional types, a dimensional item table (dim_item) of dimensional items, a location table (location) of locations representing intersection points between the various dimensions, and a connection table (dim_conn) defining connections between each location and dimensional items with which they are associated, characterized by the steps of
specifying a query focus specification defining the subset of the database that the query should retrieve its results from consisting of a list of dimensional focus specifications, each specifying rules to identify a subset of dimensional items from one dimension of the database, generating a first working table defining a query focus (query_focus), each row of which including a structural identifier (struct_id) identifying the dimensional focus specification the row belongs to, a focused item identifier (focus_dim_item_id) identifying an item in said dimensional item table (dim_item) of dimensional items that is in the query focus, and performing a query in said relational database based on the focus defined in said first working table (query_focus), collecting data associated with locations connected to at least one of the focused items (focus_dim_item_id) identified for each structural identifier (struct_id) defined by the dimensional focus specifications contained in the query focus specification.
-
29. Method for performing a computerized query in a relational database systems, where the database at least includes a dimension type table (dim_type) of dimensional types, a dimensional item table (dim_item) of dimensional items, a location table (location) of locations representing intersection points between the various dimensions, and a connection table (dim_conn) defining connections between each location and dimensional items with which they are associated, characterized by the steps of
entering in said dimension type table (dim_type) one or more rows defining analysis dimensions, entering in said dimensional item table (dim_item) one or more rows defining analysis items belonging to said analysis dimensions, defining connections between items in said analysis dimensions and items belonging to regular dimensions, and performing queries that limit and/or aggregate results according to specifications on the analysis dimensions.
Specification