US6768986B2
Expired / Lapsed
US
⏱️ 0.0 years remaining
Mapping of an RDBMS schema onto a multidimensional data model
Assignee: Business Objects Software Ltd
•
Filing: 2001-04-03
•
Granted: 2004-07-27
•
Expires: 2021-04-03
Abstract
Abstract A Relational Database Management System (RDBMS) having any arbitrary structure is translated into a multi-dimensional data model suitable for performing OLAP operations upon. If a relational table defining the relational model includes any tables with cardinality of 1,1 or 0,1, the tables are merged into a single table. If the relational table is not normalized, then normalization is performed and a relationship between the original table and the normalized table is created. If the relational table is normalized, but not by dependence between columns, such as in the dimension table in a snowflake schema, the normalization process is performed using the foreign key in order to generate the normalized table. Once the normalized table is generated, OLAP measures are derived from the normalized relational table by an automated method. In addition, OLAP dimensions are derived from the normalized relational table and the results of the OLAP measures derivation by an automated method according to the present invention. According to an aspect, it is possible to associate a member of a dimension to another member of the same or another dimension. According to another aspect, it is possible to create a new dimension of analysis, the members of which are all the different values that a scalar expression can take on. According to yet another aspect, it is possible to access the various instances of a Reporting Object as members in an OLAP dimension. According to the yet another aspect, it is possible to apply opaque filters or a combination of them to the data that underlies analysis.
Inventors
- 👤 Jean-Yves Cras
- 👤 Henri Biestro
- 👤 Ricardo Polo-Malouvier
Bibliographic Details
Application Number
—
Jurisdiction
US
Data Source
google_patents
Last Synchronized
2026-11-09 14:37
CPC Classifications
G; G06; G06F; G06F16/00; G06F16/20; G06F16/28; G06F16/283; G06F16/24; G06F16/242; G06F16/2428; Y; Y10; Y10S; Y10S707/00; Y10S707/99931; Y10S707/99932; Y10S707/99941; Y10S707/99943; Y10S707/99944; Y10S707/99945
Claims Analysis
Total Claims: 44
Claim 1
Independent Claim
102 words
A method of translating a relational model defined by a relational table into a multi-dimensional data model, the method comprising the steps of: (a) if the relational table is not normalized, creating a normalized table from the relational table and defining a relationship between the relational table and the normalized table, and if the relational table is normalized, referring to the relational table as the normalized table; (b) transforming the normalized table into an OLAP model; and (c) prior to step (a), if the relational table is normalized, but not by dependence between columns, redefining the relational table by a foreign key.
Claim 2
Independent Claim
102 words
A method of translating a relational model defined by a relational table into a multi-dimensional data model, the method comprising the steps of: (a) if the relational table is not normalized, creating a normalized table from the relational table and defining a relationship between the relational table and the normalized table, and if the relational table is normalized, referring to the relational table as the normalized table; (b) transforming the normalized table into an OLAP model; and (c) prior to step (a), if the relational table is normalized, but not by dependence between columns, redefining the relational table by a foreign key.
Claim 3
Dependent Claim
43 words
A method as in claim 1 , further comprising the step of: prior to step (a), for each pair of tables in the relational model having cardinality 1,1 or 0,1 in the relational model, merging the pair of tables into a single table.
Claim 4
Dependent Claim
68 words
A method as in claim 1 , further comprising the steps of: prior to step (a), for each pair of tables in the relational model having cardinality 1,1 or 0,1 in the relational model, merging the pair of tables into a single table; and prior to step (a), if the relational table is normalized, but not by dependence between columns, redefining the relational table by a foreign key.
Claim 5
Dependent Claim
18 words
A method as in claim 1 , wherein step (b) includes only a single iteration for recursive relations.
Claim 6
Independent Claim
82 words
A method of translating a relational model into a multi-dimensional data model, the method comprising the steps of: (a) creating a list B having all tables in the relational model that do not reference a foreign key from another table, excluding recursive relations; and (b) creating a list M from amongst all columns of the tables in the list B containing one or more aggregation measures for each column that is not a primary or a foreign key and that is numerical.
Claim 7
Independent Claim
82 words
A method of translating a relational model into a multi-dimensional data model, the method comprising the steps of: (a) creating a list B having all tables in the relational model that do not reference a foreign key from another table, excluding recursive relations; and (b) creating a list M from amongst all columns of the tables in the list B containing one or more aggregation measures for each column that is not a primary or a foreign key and that is numerical.
Claim 8
Dependent Claim
54 words
A method as in claim 5 , further comprising the steps of: (c) adding a count measure of a first category in the list M for each table in the list B; and (d) adding a count measure of a second category in the list M for each table not in the list B.
Claim 9
Dependent Claim
34 words
A method as in claim 6 , further comprising the step of: (e) adding an aggregation measure of a second category for each column of each table that is not in the list B.
Claim 10
Dependent Claim
27 words
A method as in claim 7 , further comprising the step of: (f) naming each measure with a column name and an aggregation used on the column.
Claim 11
Dependent Claim
36 words
A method as in claim 6 , wherein each count measure of the second category added to the list M in step (d) corresponds to a number of members of an upper level in a dimension.
Claim 12
Dependent Claim
38 words
A method as in claim 5 , wherein the step (b) comprises the steps of: applying a sum, average, minimum, or maximum aggregation to each column that is not the primary or foreign key and that is numerical.
Claim 13
Independent Claim
127 words
A method of translating a relational model into a multi-dimensional data model, the method comprising the steps of: (a) creating a list A with all tables in the relational model that do not have any foreign keys, excluding recursive relations; (b) defining a hierarchy for each table found in the list A, and associating the hierarchy to the table; (c) for each hierarchy, adding a first level ALL; (d) creating a list B having all tables in the relational model that do not reference a foreign key from another table, excluding recursive relations, and for each table not in the list B, creating a level; and (e) completing all hierarchies by using relations between tables, such that levels are placed in a correct order under each hierarchy.
Claim 14
Independent Claim
127 words
A method of translating a relational model into a multi-dimensional data model, the method comprising the steps of: (a) creating a list A with all tables in the relational model that do not have any foreign keys, excluding recursive relations; (b) defining a hierarchy for each table found in the list A, and associating the hierarchy to the table; (c) for each hierarchy, adding a first level ALL; (d) creating a list B having all tables in the relational model that do not reference a foreign key from another table, excluding recursive relations, and for each table not in the list B, creating a level; and (e) completing all hierarchies by using relations between tables, such that levels are placed in a correct order under each hierarchy.
Claim 15
Dependent Claim
25 words
A method as in claim 11 , further comprising the step of: (f) creating a dimension for each hierarchy that has a different leaf level.
Claim 16
Dependent Claim
106 words
A method as in claim 12 , further comprising the steps of: creating a list M from amongst all columns of the tables in the list B containing one or more aggregation measures for each column that is not a primary or a foreign key and that is numerical; adding a count measure of a first category in the list M for each table in the list B; adding a count measure of a second category in the list M for each table not in the list B; and (g) when a measure of category two is defined, revising each dimension with more than one hierarchy.
Claim 17
Dependent Claim
26 words
A method as in claim 13 , further comprising the step of: (h) naming each hierarchy with the name of the table associated to the hierarchy.
Claim 18
Dependent Claim
30 words
A method as in claim 14 , further comprising the step of: (i) naming each dimension with the name of the table of the leaf level common to all hierarchies.
Claim 19
Dependent Claim
37 words
A method as in claim 15 , further comprising the step of: when a measure of the second category is present, naming the dimension with the name of the table of the level upper to this measure.
Claim 20
Dependent Claim
30 words
A method as in claim 16 , further comprising the step of: when more than one measure of the second category is present, using the upper level for all measures.
Claim 21
Dependent Claim
81 words
A method as in claim 13 , wherein step (g) comprises the steps of: for each measure of the second category, finding a level built on a same table; for each level found, identifying a dimension composed of more than one hierarchy and used by at least one hierarchy; and if a level above the level in which the measure is located is not common to all hierarchies, creating a new dimension with all hierarchies that do not share this level.
Claim 22
Dependent Claim
25 words
A method as in claim 18 , wherein step (g) further comprises the step of: deleting all hierarchies which have only the first level ALL.
Claim 23
Dependent Claim
34 words
A method as in claim 11 , further comprising the step of: for each table not in the list B, adding attributes to the corresponding level created in step (d) if attributes are available.
Claim 24
Dependent Claim
51 words
A method as in claim 11 , wherein step (d) comprises the steps of: for each table not in the list B, creating a level having a column that defines a member key that uniquely identifies a member inside the level, and having a column that defines a level member name.
Claim 25
Dependent Claim
21 words
A method as in claim 21 , wherein the member key for each level is a primary key of the table.
Claim 26
Dependent Claim
47 words
A method as in claim 22 , wherein in the case that there is more than one column in the primary key of the table, using a column from among the more than one column that has a larger number of distinct values for the member key.
Claim 27
Dependent Claim
36 words
A method as in claim 23 , wherein columns that are not primary or foreign keys, that are not used by the level member, and that are not used to define a measure define an attribute.
Claim 28
Dependent Claim
34 words
A method as in claim 24 , wherein a level name is a name of a table in which the level is defined, and a name of the attribute is a member name column.
Claim 29
Dependent Claim
81 words
A method as in claim 11 , wherein step (e) comprises the steps of: for each hierarchy, (j) finding a level on a same table as a table associated to the hierarchy in step (a); (k) adding the level under the ALL level of the hierarchy that was assigned to hierarchy in step (c); and (l) if a table of a last level has any foreign keys on any other tables, adding the levels of this table under the last level.
Claim 30
Dependent Claim
37 words
A method as in claim 26 , wherein step (e) further comprises the steps of: (m) for each hierarchy that has more than one level on a leaf, creating a copy of the hierarchy for each leaf.
Claim 31
Dependent Claim
18 words
A method as in claim 27 , further comprising the step of: recursively repeating steps (j) through (m).
Claim 32
Independent Claim
87 words
A method of translating a relational model into a multi-dimensional data model, the method comprising the steps of: (a) calculating a number of distinct values for each column in a table; (b) collecting as a level or an attribute each column that has a number of distinct values that is very similar to a number of rows; (c) for each column not collected by step (b), grouping columns that are similar; and (d) creating a list ORD by ordering groups by an ascending number of distinct values.
Claim 33
Independent Claim
87 words
A method of translating a relational model into a multi-dimensional data model, the method comprising the steps of: (a) calculating a number of distinct values for each column in a table; (b) collecting as a level or an attribute each column that has a number of distinct values that is very similar to a number of rows; (c) for each column not collected by step (b), grouping columns that are similar; and (d) creating a list ORD by ordering groups by an ascending number of distinct values.
Claim 34
Dependent Claim
28 words
A method as in claim 29 , further comprising the step of: (e) removing a first group from the list ORD and adding it to a list TOP.
Claim 35
Dependent Claim
41 words
A method as in claim 30 , further comprising the step of: (f) checking a distribution of groups on the list TOP along groups in the list ORD, using a column with a greatest number of distinct values in each group.
Claim 36
Dependent Claim
25 words
A method as in claim 31 , wherein step (f) comprises the steps of: (g) if the list TOP is empty, returning to step (e).
Claim 37
Dependent Claim
37 words
A method as in claim 32 , wherein step (f) further comprises the steps of: (h) if the list TOP is not empty, removing a group off the list TOP, and assigning the group as a PARENT.
Claim 38
Dependent Claim
44 words
A method as in claim 33 , further comprising the step of: (i) assigning as CHILDREN a first group off the list ORD having more distinct values than the group PARENT and not already defined as children associated to the group or previously used.
Claim 39
Dependent Claim
38 words
A method as in claim 34 , further comprising the step of: (j) if no group is available in the list ORD and the group PARENT does not define a relationship with another table, returning to step (g).
Claim 40
Dependent Claim
43 words
A method as in claim 35 , further comprising the step of: (k) if a distribution of distinct values from the group CHILDREN along with the group PARENT are uniform, if a table is already defined for the group, returning to step (g).
Claim 41
Dependent Claim
53 words
A method as in claim 36 , further comprising the step of: (l) if the distribution of distinct values from the group CHILDREN along with the group PARENT are uniform, if a table is not already defined for the group, reassigning the group CHILDREN as the group PARENT, and returning to step (i).
Claim 42
Dependent Claim
90 words
A method as in claim 37 , further comprising the step of: (m) if the distribution of distinct values from the group CHILDREN along the group PARENT define a repetition, and if the group CHILDREN define a table with a relation with a parent group, adding the parent group to the list TOP only if the CHILDREN group is not already used by the PARENT group, otherwise adding the CHILDREN group on the list TOP and removing the group CHILDREN from the list ORD; and (n) returning to step (i).
Claim 43
Dependent Claim
39 words
A method as in claim 30 , wherein step (e) further comprises the step of: if the list ORD is empty and the list TOP contains only one group, creating a table with a relationship to the relationship table.
Claim 44
Dependent Claim
35 words
A method as in claim 29 , wherein step (d) further comprises the step of: when a group is composed of more than one column, using the column with the greatest number of distinct values.
References Cited (Backward Citations)
USPTO Annuity & Maintenance Windows
| Annuity Stage | Window Opens | Payment Deadline | Surcharge Period Until | Estimated Fee | Status |
|---|---|---|---|---|---|
| 3.5 Years (Year 4) | 2007-07-27 | 2008-01-26 | 2008-07-27 | $2000.00 | Window Closed / Lapsed |
| 7.5 Years (Year 8) | 2011-07-27 | 2012-01-26 | 2012-07-27 | $3760.00 | Window Closed / Lapsed |
| 11.5 Years (Year 12) | 2015-07-27 | 2016-01-26 | 2016-07-27 | $7700.00 | Window Closed / Lapsed |