ADVANCED TECHNIQUES FOR SQL GENERATION OF PERFORMANCEPOINT BUSINESS RULES

• US 20090006370A1
• Filed: 06/29/2007
• Published: 01/01/2009
• Est. Priority Date: 06/29/2007
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Yan, George, Yang, Mark, Li, Baomin
• Granted Patent: US 7,702,622 B2
• Time in Patent Office: Days
• 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;

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, further comprising converting the rule into object-based representations corresponding to objects included in the multidimensional database.

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;

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;

converting the one or more calculations defined in the business rule into a format adapted to be performed on the specified data as stored in the intermediary table; and

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

9. The computer-implemented method of claim 8, further comprising converting the business rule into object-based representations corresponding to objects included the database.

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;

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, further comprising instructions for converting the business rule into object-based representations corresponding to objects included the database.

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.