Advanced techniques for SQL generation of performancepoint business rules

• US 7,702,622 B2
• Filed: 06/29/2007
• Issued: 04/20/2010
• Est. Priority Date: 06/29/2007
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Yan, George, Yang, Mark, Li, Baomin
• Primary Examiner(s): LeRoux; Etienne P
• Assistant Examiner(s): Le; Jessica N
• Application Number:
  US11/771,523
• Publication Number:
  US 20090006370A1
• Time in Patent Office: 1,026 Days
• Field of Search: 707 3- 5
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

Computer-implemented methods and computer-readable storage media are disclosed to facilitate the application of business rules. A rule is received, the rule defining one or more calculations to be performed on specified data stored in a multidimensional database to yield at least one result. At least one database query is generated seeking the specified data to be retrieved from the multidimensional database. An intermediary table is created to accommodate the specified data retrieved from the multidimensional database. The specified data is stored in the intermediary table and the specified data is manipulated when the data is retrieved or after the specified data is stored in the intermediary table.

Claims

1. A computer-implemented method, comprising:

receiving a rule defining one or more calculations to be performed on specified data stored in a multidimensional database to yield at least one result;

wherein the rule comprises a scope definition that describes;

the dimension when the rule applies to a whole dimension of the multidimensional database;

members within a dimension when the rule does not apply to the whole dimension; and

an expressions definition that defines operations to perform on the specified data;

converting the rule to an object-oriented form that comprises a scope node and an expressions node at a first hierarchical level;

wherein the scope node comprises child elements identifying the data to retrieve from the multidimensional database;

wherein the expressions node comprises child elements identifying operations to perform;

generating a relational database query seeking the specified data relevant to the rule to be retrieved from the multidimensional database;

creating an intermediary table to accommodate the specified data retrieved from the multidimensional database;

storing the specified data in the intermediary table; and

manipulating the specified data in the intermediary table as specified by the rule to determine the at least one result.

2. The computer-implemented method of claim 1, wherein the object oriented form comprises a rules node that is a parent node to the scope node and the expressions node.

3. The computer-implemented method of claim 1, further comprising, when the rule identified the specified data in a symbolic form indirectly identifying a group of members, converting the symbolic form into a member list identifying the group of members.

4. The computer-implemented method of claim 1, wherein creating the intermediary table includes:

creating a data space configured to store one or more values in the selected data; and

creating a calculation space configured to store a result of one or more calculations to be performed using the one or more values in the selected data.

5. The computer-implemented method of claim 4, wherein the data space and the calculation space each are presented as additional columns in the intermediary table.

6. The computer-implemented method of claim 1, further comprising, for each of the values stored in the data spaces, generating and executing queries to facilitate the retrieving of the data from the database.

7. The computer-implemented method of claim 6, further comprising performing any additional calculations indicated in the business rule to determine the at least one result.

8. A computer-implemented method, comprising:

receiving a business rule defining one or more calculations to be performed on specified data stored in a database to yield at least one result;

converting the business rule to an object-oriented form that comprises a scope node and an expressions node at a first hierarchical level;

wherein the scope node comprises child elements identifying the data to retrieve from the database;

wherein the expressions node comprises child elements identifying the one or more calculations to perform;

creating at least one intermediary table to accommodate the specified data;

generating a plurality of queries for one or more of;

retrieving the specified data from the database; and

manipulating the specified data by performing a function on the specified data retrieved from the database;

storing the specified data in the intermediary table;

performing the one or more calculations on the specified data stored in the intermediary table.

9. The computer-implemented method of claim 8, wherein the object oriented form comprises a rules node that is a parent node to the scope node and the expressions node.

10. The computer-implemented method of claim 8, further comprising, when the request recites the specified data in a symbolic form, converting the symbolic form into a concrete member list including each item in the specified data.

11. The computer-implemented method of claim 8, wherein creating the intermediary table includes for each dimension in the specified data includes:

creating a data space configured to store values of the specified data; and

creating a calculation space configured to store at least one of a formula and a formula result based on the one or more calculations to be applied to the values.

12. The computer-implemented method of claim 11, wherein the data space and the calculation space each are presented as additional columns in the calculation space.

13. The computer-implemented method of claim 8, further comprising for each of the values stored in the data space:

creating a join condition to facilitate the retrieving of the specified data from the database; and

generating queries to facilitate the retrieving of the data from the database.

14. The computer-implemented method of claim 13, further comprising, when the data column includes entries resulting from an aggregation function:

for any missing dimensions not included in the database, filling in a current member as a default;

building a plurality of subqueries using hierarchy information about the entries in the data column; and

merging results of the plurality of subqueries into the join condition to facilitate the retrieving of the specified data from the database.

15. The computer-implemented method of claim 13, further comprising, when the data column includes entries derived from an assumptions model, one of:

for one or more leveled hierarchies, adjusting a granularity of the entries in the assumptions model to a specified granularity specified by the request;

for one or more shared hierarchies not included in the rule, adding a current member as a default; and

for model dimensions not specified in the rule, inserting a default value.

16. The computer-implemented method of claim 8, upon the retrieving of the data from the database, further comprising validating the data.

17. The computer-implemented method of claim 8, further comprising updating a fact table with a result of the one or more calculations on the specified data stored in the calculation space.

18. A computer-readable storage media maintaining a plurality of stored computer-executable instructions, the stored instructions comprising instructions for:

receiving a business rule defining one or more calculations to be performed on specified data stored in a database to yield at least one result;

converting the business rule to an object-oriented form that comprises a scope node and an expressions node at a first hierarchical level;

wherein the scope node comprises child elements identifying the data to retrieve from the database;

wherein the expressions node comprises child elements identifying the one or more calculations to perform;

creating at least one intermediary table to accommodate the specified data;

generating a plurality of queries for one or more of;

retrieving the specified data from the database; and

manipulating the specified data by performing a function on the specified data retrieved from the database;

storing the specified data in the intermediary table; and

determining the result of the business rule.

19. The computer-readable storage medium of claim 18, wherein the object oriented form comprises a rules node that is a parent node to the scope node and the expressions node.

20. The computer-readable storage medium of claim 18, wherein the instructions for creating the intermediary table include for each dimension in the specified data instructions for:

creating a data space configured to store values of the specified data; and

creating a calculation space configured to store at least one of a formula and a formula result based on the one or more calculations to be applied to the values.