Translating a relational query to a multidimensional query

• US 8,606,803 B2
• Filed: 04/01/2008
• Issued: 12/10/2013
• Est. Priority Date: 04/01/2008
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Bhatawdekar, Ameya, Hebert, Alan, Subramanyam, Karthik, Shah, Mauli, Li, Jian H.
• Primary Examiner(s): GORTAYO, DANGELINO N
• Application Number:
  US12/060,279
• Publication Number:
  US 20090249125A1
• Time in Patent Office: 2,079 Days
• Field of Search: 707/758, 707/759, 707/705, 707/1, 707/2, 707/3, 707/100, 707/999.001, 707/999.002, 707/999.003, 707/999.1, 707/713, 707/736, 707/769
• US Class Current: 707/759
• 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 translating a relational database query into a multidimensional expression language (MDX) database query comprising:

parsing a relational database query into one or more relational database query tokens;

filtering zero or more of the one or more relational database query tokens based at least in part on business logic to create a set of filtered relational database query tokens, the filtering comprising;

removing a relational database query token, from the one or more relational database query tokens, responsive to determining that the relational database query token corresponds to a field not supported by a multidimensional database and is associated with a return of a dataset that exceeds a threshold;

identifying relevant metadata, from a metadata store, associated with metadata associated with the set of filtered relational database query tokens, the relevant metadata comprising current multidimensional metadata and trend related multidimensional metadata;

translating at least some relational database query tokens of the set of filtered relational database query tokens into one or more work item query language (WIQL) tokens;

retrieving a first group of one or more MDX tokens based at least in part on the relevant metadata;

retrieving a second group of one or more MDX tokens based at least in part on the one or more WIQL tokens;

arranging at least some of the one or more MDX tokens of the first group and at least some of the one or more MDX tokens of the second group based at least in part on the metadata associated with the set of filtered relational database query tokens; and

generating one or more MDX database queries for trend related information and current information based at least in part on at least some of the arranged MDX tokens, at least some of the parsing, the filtering, the identifying, the translating, the retrieving a first group, the retrieving a second group, the arranging, and the generating implemented at least in part via a processing unit.

2. The method of claim 1, comprising arranging at least some of the one or more relational database query tokens into one or more buckets.

3. The method of claim 1, the relevant metadata based at least in part on one or more schemas.

4. The method of claim 1, the one or more MDX database queries comprising at least one of a first MDX query relating to current data or a second MDX query relating to historical data.

5. The method of claim 1, comprising arranging at least some of the one or more relational database query tokens into at least one of a first bucket corresponding to filter operators, a second bucket corresponding to filter criteria, a third bucket corresponding to filter fields, or a fourth bucket corresponding to results fields.

6. The method of claim 1, the relational database query associated with a structured query language.

7. The method of claim 1, the metadata store comprising metadata associated with at least one of a filter operator, a filter criteria, a filter field, or a result field.

8. The method of claim 1, the multidimensional database comprising an online analytical processing cube.

9. The method of claim 1, comprising flagging at least one of an error or a warning.

10. The method of claim 1, comprising customizing at least one of the one or more MDX database queries to generate a customized MDX database query that trims data retrieved from the multidimensional database.

11. A system for translating a relational database query into a multidimensional expression language (MDX) database query comprising:

a component configured to;

parse a relational database query into one or more relational database query tokens;

filter zero or more of the one or more relational database query tokens based at least in part on business logic to create a set of filtered relational database query tokens, the filtering comprising;

removing a relational database query token, from the one or more relational database query tokens, responsive to determining that the relational database query token corresponds to a field not supported by a multidimensional database or is associated with a return of a dataset that exceeds a threshold;

identify relevant metadata, from a metadata store, associated with metadata associated with the set of filtered relational database query tokens, the relevant metadata comprising current multidimensional metadata and trend related multidimensional metadata;

translate at least some relational database query tokens of the set of filtered relational database query tokens into one or more work item query language (WIQL) tokens;

retrieve a first group of one or more MDX tokens based at least in part on the relevant metadata;

retrieve a second group of one or more MDX tokens based at least in part on the one or more WIQL tokens;

arrange at least some of the one or more MDX tokens of the first group and at least some of the one or more MDX tokens of the second group based at least in part on the metadata associated with the set of filtered relational database query tokens; and

generate one or more MDX database queries for trend related information and current information based at least in part on at least some of the arranged MDX tokens.

12. The system of claim 11, the relevant metadata comprising at least one of one or more definitions of schema, one or more relationships between one or more tables and one or more columns, or data relating to how relational database data is pushed to the multidimensional database.

13. The system of claim 11, the multidimensional database comprising an online analytical processing cube.

14. The system of claim 11, the relational database query associated with a structured query language.

15. The system of claim 11, the business logic comprising at least one of a homogenous criteria or a heterogeneous criteria.

16. The system of claim 11, the component configured to flag at least one of an error or a warning.

17. A computer-readable storage device comprising computer-executable instructions, which when executed at least in part via a processor on a computer perform acts, comprising:

parsing a relational database query into one or more relational database query tokens;

filtering zero or more of the one or more relational database query tokens based at least in part on business logic to create a set of filtered relational database query tokens, the filtering comprising;

removing a relational database query token, from the one or more relational database query tokens, responsive to determining that the relational database query token at least one of corresponds to a field not supported by a multidimensional database or is associated with a return of a dataset that exceeds a threshold;

identifying relevant metadata, from a metadata store, associated with metadata associated with the set of filtered relational database query tokens, the relevant metadata comprising current multidimensional metadata and trend related multidimensional metadata;

translating at least some relational database query tokens of the set of filtered relational database query tokens into one or more work item query language (WIQL) tokens;

retrieving a first group of one or more multidimensional expression language (MDX) tokens based at least in part on the relevant metadata;

retrieving a second group of one or more MDX tokens based at least in part on the one or more WIQL tokens;

arranging at least some of the one or more MDX tokens of the first group and at least some of the one or more MDX tokens of the second group based at least in part on the metadata associated with the set of filtered relational database query tokens; and

generating one or more MDX database queries for trend related information and current information based at least in part on at least some of the arranged MDX tokens.

18. The computer-readable storage device of claim 17, comprising arranging at least one of the one or more relational database query tokens into one or more buckets.

19. The computer-readable storage device of claim 17, the relational database query associated with a structured query language.

20. The computer-readable storage device of claim 17, the relevant metadata comprising at least one of one or more definitions of schema, one or more relationships between one or more tables and one or more columns, or data relating to how relational database data is pushed to the multidimensional database.