Systems and methods for set filtering of data

• US 7,970,782 B1
• Filed: 04/30/2002
• Issued: 06/28/2011
• Est. Priority Date: 01/14/2002
• Status: Expired due to Fees

• Current Assignee: Microstrategy Incorporated
• Original Assignee: Microstrategy Incorporated
• Inventors: Yuan, Jun, Sahasrabudhe, Sadanand, Martin, Fabrice, Hurwood, William, Bedell, Jeffrey A., Li, Benjamin Z.
• Primary Examiner(s): Truong; Cam Y T
• Application Number:
  US10/134,677
• Time in Patent Office: 3,346 Days
• Field of Search: 707/10, 707/100, 707/6, 707 2- 4, 707/102
• US Class Current: 707/760
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Systems and methods are provided for filtering data in a database using an execution plan. The execution plan has a first processing pass against the database that calculates a filter set using a filter criteria, and a second processing pass against the filter set that calculates a report result.

Claims

1. A non-transitory computer processor readable storage medium storing executable instructions to generate a report, the non-transitory computer processor readable storage medium comprising:

a relationship set filter object stored on the non-transitory computer processor readable storage medium, wherein the filter object comprises at least one qualifying predicate;

an output level;

at least one relationship used by a computer processor to generate the report by automatically instantiating the relationship set filter object to automatically determine how to apply the relationship set filter object to input data to generate a result at a level defined by the output level, the report including the input data and the relationship set filter object;

wherein the relationship used by the computer processor to automatically determine specifies multiple actions selected from a group of multiple passes against the plurality of databases or against intermediate results determined by a prior pass against the plurality of databases;

a query engine configured to;

determine whether a query is processable by one or more of the plurality of databases;

transmit the query directly to one or more of the plurality of databases if the query is processable by one or more of the plurality of databases;

translate the query to a syntax compatible with one or more of the plurality of databases if the query is not processable by one or more of the plurality of databases;

verify at least one pass against the plurality of databases, the verification including;

parsing the query associated with the at least one pass to identify invalid formatting, the invalid formatting including invalid database query formatting;

trapping identified invalid formatting if the invalid formatting is identified, wherein the generating the report is based on the verifying at least one pass against a plurality of databases; and

a transmission facility configured to transmit the report to one or more types of user devices, wherein a user receives the transmitted report if a user specified condition has been satisfied.

2. The medium of claim 1, wherein the at least one relationship comprises a table.

3. The medium of claim 1 wherein the at least one relationship comprises an expression.

4. The medium of claim 1 wherein the at least one relationship comprises a metric.

5. The medium of claim 1 wherein the at least one relationship comprises an attribute.

6. The medium of claim 1 wherein the output level comprises one or more attributes.

7. The medium of claim 1 wherein the filter object comprises a plurality of qualifying predicates and at least one logical operators.

8. The medium of claim 1 wherein the at least one relationship is required to resolve ambiguity on how to instantiate the relationship set filter object.

9. A method of generating a report from a plurality of databases, the report being based on input data from a database and at least one relationship set filter object, the method comprising the steps of:

defining the at least one relationship set filter object to including a filter object, an output level, and at least one relationship, wherein the relationship set filter object comprises at least one qualifying predicate;

defining the report to include at least the input data and the at least one relationship set filter object;

generating the report by automatically instantiating, using a computer processor, the relationship set filter object against the input data to determine how to apply the relationship set filter object to the input data to generate contents of the output level, the act of determining resulting in multiple actions selected from a group of multiple passes against the plurality of databases or against intermediate results determined by a prior pass against the plurality of databases;

determining whether a query is processable by one or more of the plurality of databases;

transmitting the query directly to one or more of the plurality of databases if the query is processable by one or more of the plurality of databases;

translating the query to a syntax compatible with one or more of the plurality of databases if the query is not processable by one or more of the plurality of databases;

verifying, using a query engine, at least one pass against the plurality of databases, the verification including;

parsing the query associated with the at least one pass to identify invalid formatting, the invalid formatting including invalid database query formatting;

trapping identified invalid formatting if the invalid formatting is identified, wherein the generating the report is based on the verifying at least one pass against a plurality of databases; and

transmitting the report via a transmission facility to one or more types of user devices, wherein a user receives the transmitted report if a user specified condition has been satisfied.

10. The method of claim 9 wherein the at least one relationship comprises a table.

11. The method of claim 9 wherein the at least one relationship comprises an expression.

12. The method of claim 9 wherein the at least one relationship comprises a metric.

13. The method of claim 9 wherein the at least one relationship comprises an attribute.

14. The method of claim 9 wherein the output level comprises at least one attribute.

15. The method of claim 9 wherein the relationship set filter object comprises a plurality of qualifying predicates and at least one logical operators.

16. The method of claim 9 wherein the at least one relationship is required to resolve ambiguity on how to instantiate the relationship set filter object.

17. A non-transitory recording medium storing processor readable program instructions for causing a processor to generate a report, the instructions enabling a user of the program to perform:

defining at least one relationship set filter object to including a filter object, an output level, and at least one relationship, wherein the relationship set filter object comprises at least one qualifying predicate;

defining the report to include at least input data and the at least one relationship set filter object;

determining whether a query is processable by one or more of the plurality of databases;

transmitting the query directly to one or more of the plurality of databases if the query is processable by one or more of the plurality of databases; and

translating the query to a syntax compatible with one or more of the plurality of databases if the query is not processable by one or more of the plurality of databases;

generating the report by automatically instantiating the relationship set filter object against the input data to determine how to apply the filter object to the input data to generate contents of the output level, the act of determining resulting in multiple actions selected from the group of multiple passes against a plurality of databases or against the intermediate results determined by a prior pass against the plurality of databases;

verifying, using a query engine, at least one pass against the plurality of databases, the verification including;

parsing the transmitted query associated with the at least one pass to identify invalid formatting, the invalid formatting including invalid database query formatting;

trapping identified invalid formatting if invalid formatting is identified, wherein the generating the report is based on the verifying at least one pass against a plurality of databases;

and

transmitting the report via a transmission facility to one or more types of user devices, wherein a user receives the transmitted report if a user specified condition has been satisfied.

18. The medium of claim 17 wherein the at least one relationship comprises a table.

19. The medium of claim 17 wherein the at least one relationship comprises an expression.

20. The medium of claim 17 wherein the at least one relationship comprises a metric.

21. The medium of claim 17 wherein the at least one relationship comprises an attribute.

22. The medium of claim 17 wherein the output level comprises at least one attribute.

23. The medium of claim 17 wherein the filter object comprises a plurality of qualifying predicates and at least one logical operators.

24. The medium of claim 17 wherein the relationship is required to resolve ambiguity on how to instantiate the relationship set filter object.