Mapping of an RDBMS schema onto a multidimensional data model

• US 6,768,986 B2
• Filed: 04/03/2001
• Issued: 07/27/2004
• Est. Priority Date: 04/03/2000
• Status: Active Grant

• Current Assignee: Business Objects Incorporated (SAP SE)
• Original Assignee: Business Objects SA (SAP SE)
• Inventors: Polo-Malouvier, Ricardo, Biestro, Henri, Cras, Jean-Yves
• Primary Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/826,425
• Publication Number:
  US 20020087516A1
• Time in Patent Office: 1,211 Days
• Field of Search: 707/100-104, 707/2, 707/102, 707/103.R, 715/501.1, 715/513, 709/200, 709/201, 709/232, 709/224, 702/62, 345/719
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

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.

Claims

1. 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.

2. 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.

3. 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.

4. A method as in claim 1, wherein step (b) includes only a single iteration for recursive relations.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. 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.

11. 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.

12. A method as in claim 11, further comprising the step of:

(f) creating a dimension for each hierarchy that has a different leaf level.

13. 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.

14. 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.

15. 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.

16. 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.

17. 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.

18. 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.

19. A method as in claim 18, wherein step (g) further comprises the step of:

deleting all hierarchies which have only the first level ALL.

20. 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.

21. 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.

22. A method as in claim 21,

wherein the member key for each level is a primary key of the table.

23. 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.

24. 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.

25. 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.

26. 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.

27. 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.

28. A method as in claim 27, further comprising the step of:

recursively repeating steps (j) through (m).

29. 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.

30. 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.

31. 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.

32. A method as in claim 31, wherein step (f) comprises the steps of:

(g) if the list TOP is empty, returning to step (e).

33. 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.

34. 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.

35. 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).

36. 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).

37. 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).

38. 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).

39. 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.

40. 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.