Method for Efficient Backend Evaluation of Aggregates on Composite Relationships in a Relationally Mapped Er Model

1. A computer-implemented method for executing aggregate queries of composite relationships from an Entity-Relationship (ER) model which sources data from a plurality of heterogeneous and decentralised databases, comprising the steps of: a. maintaining an ER model comprising a plurality of entities E n for which data exists in one or more databases, and one or more relationships r n between those entities; b. receiving an aggregate query relating to a composite relationship cr(r1, r2) involving entities E1, E2 and E3 wherein r1 is a relation between E1 and E2, and wherein r2 is a relation between E2 and E3; c. optimizing the computation of the aggregate query by selecting an aggregation strategy according to the relation between entities E1, E2 and E3, such that: i. if all entities E1, E2 and E3 are defined in tables of a common database system supporting join and aggregate operations, selecting a join strategy defining a join operation between the tables containing E1, E2 and E3 and aggregating the join results based on one dimension of E2; ii. if entities E1 and E3 are each mapped to foreign key attributes of E2, selecting a single table aggregation strategy defining a regular aggregation of entities E2; iii. if the entity E2 is mapped to a foreign key attribute in both entities E1 and E3, selecting a semi-join aggregation strategy defining a semi-join operation between tables containing E1 and E3, and filtering records from E3 using record identifiers of E2 from the foreign key attribute of E1; and iv. if the entity E3 is mapped to a foreign key attribute of entity E2, selecting a semi-join aggregation strategy defining a semi-join operation between tables containing E1 and E2, and a regular aggregation based on the foreign key attribute from E2 that is mapped to E3.

2. The method of claim 1 , further comprising generating said ER model by receiving metadata information about data and schema from said one or more databases and integrating it into an ER model, comprising one or more entity instances, and one or more relationships between those entity instances.

3. The method of claim 1 , further comprising generating a list of recommended composite relationships ranked by relevance and presenting said list to a user.

4. The method of claim 3 , wherein generating said list of recommended composite relationships comprises ranking composite relationships, based on metadata stored in the ER model and on an estimation of their relation cardinality.

5. The method of claim 4 , wherein the cardinality of a relationship r1 between two entities E1 and E2 is an average number of unique values for the attribute r1 in the relation <E1, r1, E2> across all entity instances of type E1, and wherein the estimation of the cardinality of a composite relationship comprises combining the cardinality of the individual relationships using the product operator (Π), such that card(<E1, r1, E2, r2, E3>)=Π(card(<E1, r1, E2>), card(E2, r2, E3>)).

6. The method of claim 4 , further comprising filtering out non-aggregatable composite relationships that do not bring additional information once aggregated, based on metadata stored in the ER model.

7. A computer-implemented method for executing aggregate queries of composite relationships from an Entity-Relationship (ER) model which sources data from a plurality of heterogeneous and decentralised databases, comprising the steps of: a. maintaining an ER model comprising a plurality of entities E n for which data exists in one or more databases, and one or more relationships r n between those entities; b. generating a list of recommended composite relationships cr(r1, r2) involving entities E1, E2 and E3 wherein r1 is a relation between E1 and E2, and wherein r2 is a relation between E2 and E3, wherein said list is ranked by relevance; c. presenting said list to a user; d. receiving an aggregate query relating to a composite relationship in said list; and e. evaluating said aggregate query.

8. The method of claim 7 , wherein generating said list of recommended composite relationships comprises ranking composite relationships, based on metadata stored in the ER model and on an estimation of their relation cardinality.

9. The method of claim 8 , wherein the cardinality of a relationship r1 between two entities E1 and E2 is an average number of unique values for the attribute r1 in the relation <E1, r1, E2> across all entity instances of type E1, and wherein the estimation of the cardinality of a composite relationship comprises combining the cardinality of the individual relationships using the product operator (Π), such that card(<E1, r1, E2, r2, E3>)=Π(card(<E1, r1, E2>), card(E2, r2, E3>)).

10. The method of claim 7 , further comprising filtering out non-aggregatable composite relationships that do not bring additional information once aggregated, based on metadata stored in the ER model.

11. The method of claim 7 , wherein evaluating said aggregate query comprises optimizing the computation of the aggregate query by selecting an aggregation strategy according to the relation between entities E1, E2 and E3, such that: i. if all entities E1, E2 and E3 are defined in tables of a common database system supporting join and aggregate operations, selecting a join strategy defining a join operation between the tables containing E1, E2 and E3 and aggregating the join results based on one dimension of E2; ii. if entities E1 and E3 are each mapped to foreign key attributes of E2, selecting a single table aggregation strategy defining a regular aggregation of entities E2; iii. if the entity E2 is mapped to a foreign key attribute in both entities E1 and E3, selecting a semi-join aggregation strategy defining a semi-join operation between tables containing E1 and E3, and filtering records from E3 using record identifiers of E2 from the foreign key attribute of E1; and iv. if the entity E3 is mapped to a foreign key attribute of entity E2, selecting a semi-join aggregation strategy defining a semi-join operation between tables containing E1 and E2, and a regular aggregation based on the foreign key attribute from E2 that is mapped to E3.

12. A computer program product for executing aggregate queries of composite relationships from an Entity-Relationship (ER) model which sources data from a plurality of heterogeneous and decentralised databases, the computer program product comprising instructions which when executed in a processor of a computer system cause said system to operate by: a. maintaining an ER model comprising a plurality of entities E n for which data exists in one or more databases, and one or more relationships r n between those entities; b. receiving an aggregate query relating to a composite relationship cr(r1, r2) involving entities E1, E2 and E3 wherein r1 is a relation between E1 and E2, and wherein r2 is a relation between E2 and E3; c. optimizing the computation of the aggregate query by selecting an aggregation strategy according to the relation between entities E1, E2 and E3, such that: i. if all entities E1, E2 and E3 are defined in tables of a common database system supporting join and aggregate operations, selecting a join strategy defining a join operation between the tables containing E1, E2 and E3 and aggregating the join results based on one dimension of E2; ii. if entities E1 and E3 are each mapped to foreign key attributes of E2, selecting a single table aggregation strategy defining a regular aggregation of entities E2; iii. if the entity E2 is mapped to a foreign key attribute in both entities E1 and E3, selecting a semi-join aggregation strategy defining a semi-join operation between tables containing E1 and E3, and filtering records from E3 using record identifiers of E2 from the foreign key attribute of E1; and iv. if the entity E3 is mapped to a foreign key attribute of entity E2, selecting a semi-join aggregation strategy defining a semi-join operation between tables containing E1 and E2, and a regular aggregation based on the foreign key attribute from E2 that is mapped to E3.

13. A computer program product for executing aggregate queries of composite relationships from an Entity-Relationship (ER) model which sources data from a plurality of heterogeneous and decentralised databases, the computer program product comprising instructions which when executed in a processor of a computer system cause said system to operate by: a. maintaining an ER model comprising a plurality of entities E n for which data exists in one or more databases, and one or more relationships r n between those entities; b. generating a list of recommended composite relationships cr(r1, r2) involving entities E1, E2 and E3 wherein r1 is a relation between E1 and E2, and wherein r2 is a relation between E2 and E3, wherein said list is ranked by relevance; c. presenting said list to a user; d. receiving an aggregate query relating to a composite relationship in said list; and e. evaluating said aggregate query.