System, method, and data structure for automatically generating database queries which are data model independent and cardinality independent

• US 8,825,633 B2
• Filed: 05/15/2012
• Issued: 09/02/2014
• Est. Priority Date: 05/15/2012
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Weber, Thomas P., Levine, Frederick J., Kelley, James P.
• Primary Examiner(s): Mahmoudi, Tony
• Assistant Examiner(s): Gurmu, Muluemebet
• Application Number:
  US13/471,548
• Publication Number:
  US 20130311442A1
• Time in Patent Office: 840 Days
• Field of Search: 707/714
• US Class Current: 707/714
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 16/24526 Internal representations for queries
• CPC Class Codes: G06F 16/248 Presentation of query results

Abstract

Systems and methods are provided for performing a database query. After receiving an identification of data item components and associations between the data item components, wherein the data item components include a measure and a category, a query generation system creates and executes a database query that comprises one or more Uqueries, wherein each Uquery comprises a Mx segment subquery and a U0 segment subquery. The Mx segment subquery is associated with a table that contains the measure, a table that contains columns for a calculated measure, or a correlated table, and the U0 segment subquery is associated with a table that contains the category, a table that contains columns for a calculated category, a dependent measure table, or a dependent filter table. The query generation system can generate a virtual results table by aggregating Uquery results from one Uquery with Uquery results from another Uquery.

Claims

1. A processor-implemented method for performing a query, comprising:

receiving an identification of data item components and an identification of an association between the data item components, wherein the data item components include a measure and a category, and wherein the identified association indicates that the measure is independent of another data item component, indicates that the measure is dependent on another data item component, or indicates that two or more data item components are correlated;

creating a database query for retrieving data item components, wherein the database query includes a first Uquery, wherein the first Uquery includes a Mx segment subquery and a U0 segment subquery, wherein the Mx segment subquery is operable to gather measures and is associated with an Mx segment table that contains the measure included in the data item components, columns for a calculated measure, or correlated measures, and wherein the U0 segment subquery is associated with a table that contains the category, a table that contains columns for a calculated category, a dependent measure table, or a dependent filter table, and wherein the U0 segment subquery further facilitates selecting a set of categories and join keys, the join keys of the set including join keys that join directly to the Mx segment subquery;

executing the Mx segment subquery to generate Mx segment subquery results;

executing the U0 segment subquery to generate U0 segment subquery results; and

joining the Mx segment subquery results with the U0 segment subquery results to generate Uquery results.

2. The method of claim 1 further comprising:

generating Uquery results for a second Uquery;

generating a virtual results table by aggregating the Uquery results from the first Uquery with Uquery results from the second Uquery; and

reporting the virtual results table.

3. The method of claim 2, wherein generating the virtual results table includes joining the Uquery results from the first Uquery with Uquery results from the second Uquery.

4. The method of claim 2, wherein generating the virtual results table includes unioning the Uquery results from the first Uquery with Uquery results from the second Uquery and applying an aggregating query to the unioned results to generate the virtual results table.

5. The method of claim 2, wherein the data item components further include a filter and further comprising receiving an identified association that indicates that a data item component is dependent on the filter.

6. The method of claim 2, wherein the data item components further include a filter and further comprising receiving an identified association that indicates that a data item component is not dependent on the filter.

7. The method of claim 2, further comprising receiving an identified association that indicates that a data item component is not dependent on the category.

8. The method of claim 2, further comprising receiving an identified association that indicates that a category is independent of a measure.

9. The method of claim 2, further comprising receiving an identified association that indicates that a category is independent of a filter.

10. The method of claim 2, wherein multiple cardinality exists between tables associated with the Uqueries.

11. The method of claim 1, wherein the first Uquery includes multiple U0 subquery segments.

12. The method of claim 11, wherein results from the multiple U0 subquery segments are joined with the Mx segment results.

13. A processor-implemented method for performing a query, comprising:

receiving an identification of data item components and an identification of associations between the data item components, wherein the data item components include measures and a category, and wherein the identified associations include at least two different associations from the associations comprising an indication that one measure is independent of other measures, an indication that one measure is dependent on another measure, and an indication that two or more measures are correlated;

creating a database query for retrieving data item components, wherein the database query includes a first Uquery, wherein the first Uquery includes a Mx segment subquery and a U0 segment subquery, wherein the Mx segment subquery is operable to gather measures and is associated with an Mx segment table that contains the measures included in the data item components, columns for a calculated measure, or correlated measures, and wherein the U0 segment subquery is associated with a table that contains the category, a table that contains columns for a calculated category, a dependent measure table, or a dependent filter table, and wherein the U0 segment subquery further facilitates selecting a set of categories and join keys, the join keys of the set including join keys that join directly to the Mx segment subquery;

executing each Mx segment subquery to generate Mx segment subquery results;

executing each U0 segment subquery to generate U0 segment subquery results;

joining Mx segment subquery results from each Uquery with the U0 segment subquery results from the same Uquery to generate Uquery results;

generating a virtual results table by aggregating the Uquery results from one Uquery with Uquery results from another Uquery; and

reporting the virtual results table.

14. The method of claim 13, wherein the data item components further include a filter.

15. The method of claim 14, wherein the identified associations further include an indication that a measure is dependent on the filter.

16. The method of claim 15, wherein the identified associations further include an indication that a measure is not dependent on the filter.

17. The method of claim 13, wherein the identified associations further include an indication that a measure is not dependent on the category.

18. The method of claim 13, wherein at least one Mx segment subquery is associated with a table that contains a transfer table or a category table.

19. The method of claim 13, wherein at least one U0 segment subquery is associated with a transfer table between category tables or a transfer table between a category table and a measure table.

20. The method of claim 13, wherein at least one U0 segment subquery is associated with a dependent required table.

21. The method of claim 13, wherein generating the virtual results table includes joining the Uquery results with the results from another Uquery.

22. The method of claim 13, wherein generating the virtual results table includes unioning the Uquery results with the results from another Uquery and applying an aggregating query to the unioned results to generate the virtual results table.

23. A system for performing a query, comprising:

one or more data processors;

a computer-readable storage medium encoded with instructions for commanding the one or more data processors to execute operations including;

receiving an identification of data item components and an identification of an association between the data item components, wherein the data item components include a measure and a category, and wherein the identified association indicates that the measure is independent of another data item component, indicates that the measure is dependent on another data item component, or indicates that two or more data item components are correlated;

creating a database query for retrieving data item components, wherein the database query includes a first Uquery, wherein the first Uquery includes a Mx segment subquery and a U0 segment subquery, wherein the Mx segment subquery is operable to gather measures and is associated with an Mx segment table that contains the measure included in the data item components, columns for a calculated measure, or correlated measures, and wherein the U0 segment subquery is associated with a table that contains the category, a table that contains columns for a calculated category, a dependent measure table, or a dependent filter table, and wherein the U0 segment subquery further facilitates selecting a set of categories and join keys, the join keys of the set including join keys that join directly to the Mx segment subquery;

executing the Mx segment subquery to generate Mx segment subquery results;

executing the U0 segment subquery to generate U0 segment subquery results; and

joining the Mx segment subquery results with the U0 segment subquery results to generate Uquery results.

24. The system of claim 23, wherein the computer-readable storage medium is further encoded with instructions for commanding the one or more data processors to execute operations including:

generating Uquery results for a second Uquery;

generating a virtual results table by aggregating the Uquery results from the first Uquery with Uquery results from the second Uquery; and

reporting the virtual results table.

25. The system of claim 24, wherein generating the virtual results table includes joining the Uquery results from the first Uquery with Uquery results from the second Uquery.

26. The system of claim 24, wherein generating the virtual results table includes unioning the Uquery results from the first Uquery with Uquery results from the second Uquery and applying an aggregating query to the unioned results to generate the virtual results table.

27. The system of claim 24, wherein the data item components further include a filter and further comprising an identified association that indicates that a data item component is dependent on the filter.

28. The system of claim 24, wherein the data item components further include a filter and further comprising an identified association that indicates that a data item component is not dependent on the filter.

29. The system of claim 24, wherein the computer-readable storage medium is further encoded with instructions for commanding the one or more processors to receive an identified association that indicates that a data item component is not dependent on the category.

30. The system of claim 24, wherein the computer-readable storage medium is further encoded with instructions for commanding the one or more processors to execute receive an identified association that indicates that a category is independent of a measure.

31. The system of claim 24, wherein the computer-readable storage medium is further encoded with instructions for commanding the one or more processors to receive an identified association that indicates that a category is independent of a filter.

32. The system of claim 24, wherein the instructions allow for multiple cardinality to exist between tables associated with the Uqueries.

33. The system of claim 23, wherein the instructions allow for the first Uquery to include multiple U0 subquery segments.

34. The system of claim 33, wherein the instructions provide for results from the multiple U0 subquery segments to be joined with the Mx segment results.

35. A non-transitory computer-readable storage medium comprising programming instructions for performing a query, the programming instructions to cause a processing system to execute operations comprising:

receiving an identification of data item components and an indication of an association between the data item components, wherein the data item components include a measure and a category, and wherein the identified association indicates that the measure is independent of another data item component, indicates that the measure is dependent on another data item component, or indicates that two or more data item components are correlated;

creating a database query for retrieving data item components, wherein the database query includes a first Uquery, wherein the first Uquery includes a Mx segment subquery and a U0 segment subquery, wherein the Mx segment subquery is operable to gather measures and is associated with an Mx segment table that contains the measure included in the data item components, columns for a calculated measure, or correlated measures, and wherein the U0 segment subquery is associated with a table that contains the category, a table that contains columns for a calculated category, a dependent measure table, or a dependent filter table, and wherein the U0 segment subquery further facilitates selecting a set of categories and join keys, the join keys of the set including join keys that join directly to the Mx segment subquery;

executing the Mx segment subquery to generate Mx segment subquery results;

executing the U0 segment subquery to generate U0 segment subquery results; and

joining the Mx segment subquery results with the U0 segment subquery results to generate Uquery results.