DATABASE QUERYING

• US 20090249125A1
• Filed: 04/01/2008
• Published: 10/01/2009
• Est. Priority Date: 04/01/2008
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Shah, Mauli, Li, Jian H., Hebert, Alan, Bhatawdekar, Ameya, Subramanyam, Karthik
• Granted Patent: US 8,606,803 B2
• Time in Patent Office: Days
• US Class Current: 714/39
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: H04L 51/00 User-to-user messaging in packet-switching networks, transmitted according to store-and-forward or real-time protocols, e.g. e-mail

Abstract

Data stored in relational databases can be retrieved using a relational database query language, while data stored in a multidimensional database is typically retrieved using a multidimensional database query language. However, most users do not have a functional working knowledge of multidimensional database query languages, which leaves large amounts of data inaccessible. Further, while some relational database query languages may be translated into a multidimensional database language, the information generated by such translations is often unusable, or returns large numbers of errors. In order to obtain effective translation of a relational database query language to a multidimensional database query language effective translation and filtering needs to occur. Using effective mapping and retrieval of database metadata along with effective, customizable business logic filtering of query components, more effective and reliable results may be achieved.

Claims

1. A method for querying a multidimensional database management system using a relational database management system based language query, the method comprising:

identifying one or more relational database query tokens in a relational database query;

operating business logic rules on the one or more relational database query tokens;

identifying relevant metadata from a metadata store, the metadata comprising;

metadata associated the one or more relational database query tokens; and

metadata associated with one or more multidimensional database query tokens;

retrieving one or more corresponding multidimensional database query tokens for the respective identified relevant metadata; and

generating one or more multidimensional database queries using the one or more multidimensional database query tokens.

2. The method of claim 1, the relational database query comprising a structured query language for relational database management systems.

3. The method of claim 1, identifying one or more relational database query tokens comprising:

parsing the relational database query into respective components; and

identifying components of the relational database query

4. The method of claim 3, the components comprising at least one of:

relational database query filter operators;

relational database query filter criteria;

relational database query filter fields; and

relational database query results fields.

5. The method of claim 1, comprising generating one or more relational database queries from one or more work item queries.

6. The method of claim 5, the generating one or more relational database queries comprising translating the one or more work item query language tokens into one or more relational database tokens.

7. The method of claim 1, the business logic rules comprising:

determining whether functional results are returned from a multidimensional database using the one or more relational database tokens; and

upon determining that functional results are not returned from a multidimensional database using the one or more relational database tokens, performing at least one of;

assigning an error to the one or more relational database tokens; and

adding a default multidimensional database query token.

8. The method of claim 7, determining whether functional results are returned from a multidimensional database using the one or more relational database tokens comprising:

determining whether the relational database query token corresponds to a field that does not exist in the multidimensional database;

determining whether the relational database query token may translate into a multidimensional database query that may produce an error; and

determining whether the relational database query token translates into a functional corresponding multidimensional database query token.

9. The method of claim 7, comprising:

determining an amount of data that may be retrieved by the one or more relational database query tokens; and

determining whether the one or more relational database query tokens retrieve a functional amount of data.

10. The method of claim 9, the functional amount of data determined by customizable process guidance.

11. The method of claim 1, the metadata associated with one or more multidimensional database query tokens comprising:

metadata relating to current data in a multidimensional database; and

metadata relating to historical data in a multidimensional database.

12. The method of claim 1, retrieving one or more corresponding multidimensional database query tokens comprising:

using the metadata associated with one or more multidimensional database query tokens to generate corresponding multidimensional database query tokens; and

arranging respective multidimensional database query tokens into respective components of a multidimensional database query.

13. The method of claim 1, generating the one or more multidimensional database queries comprising:

generating a multidimensional database query for current data in a multidimensional database; and

generating a multidimensional database query for historical data in a multidimensional database.

14. The method of claim 1, the multidimensional database queries comprising a multidimensional expressions based language.

15. A system for querying a multidimensional database management system using a relational database management system language query, the system comprising:

a query token identifier configured to;

identify relational database query fields; and

parse the relational database query into respective relational database query tokens;

a business logic operator configured to apply business logic rules to the relational database query tokens;

a metadata retriever configured to retrieve metadata from a metadata storage;

a multidimensional database token retriever configured to retrieve multidimensional database query tokens corresponding to the retrieved metadata; and

a multidimensional database query generator configured to generate one or more multidimensional database queries using the multidimensional database query tokens.

16. The system of claim 15, the relational database query fields comprising:

filter fields;

filter criteria;

results fields; and

operators.

17. The system of claim 15, the business logic operator comprising a work item query language operator configured to translate work item query language queries into relational database language queries.

18. The system of claim 15, the metadata retriever configured to retrieve meta data, the metadata comprising:

metadata associated the one or more relational database query tokens; and

metadata associated with one or more multidimensional database query tokens.

19. The system of claim 15, the one or more multidimensional database queries comprising:

a multidimensional database query for current data in a multidimensional database; and

a multidimensional database query for historical data in a multidimensional database.

20. A method for querying a multidimensional database management system using a relational database management system based language query, the method comprising:

identifying one or more relational database query tokens in a relational database query, the relational database query comprising a structured query language;

operating business logic rules on the one or more relational database query tokens, the business logic rules comprising;

determining whether functional results are returned from a multidimensional database using the one or more relational database tokens, the determining comprising;

determining whether the relational database query token corresponds to a field that does not exist in the multidimensional database;

determining whether the relational database query token may translate into a multidimensional database query that may produce an error; and

determining whether the relational database query token translates into a functional corresponding multidimensional database query token; and

upon determining that functional results are not returned from a multidimensional database using the one or more relational database tokens, assigning an error to the one or more relational database tokens;

identifying relevant metadata from a metadata store, the metadata comprising;

metadata associated the one or more relational database query tokens; and

metadata associated with one or more multidimensional database query tokens;

retrieving one or more corresponding multidimensional database query tokens for the respective identified relevant metadata, the multidimensional database query tokens comprising multidimensional expression language tokens; and

generating one or more multidimensional database queries using the one or more multidimensional database query tokens, the one or more multidimensional database queries comprising;

a multidimensional expression language query for current data in a multidimensional database; and

a multidimensional expression language query for historical data in a multidimensional database.