SPECIFICATION OF DATABASE TABLE RELATIONSHIPS FOR CALCULATION

• US 20160232203A1
• Filed: 04/20/2016
• Published: 08/11/2016
• Est. Priority Date: 06/16/2011
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Dickerman, Howard, Min, Vjacheslav, Dumitru, Marius
• Granted Patent: US 10,102,243 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2448 for particular applications; for extensibility, e.g. user defined types
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/902 using more than one table in sequence, i.e. systems with three or more layers

Abstract

A relationship amongst multiple relationships between database tables can be specified independent of a query. More specifically, a function (USERELATIONSHIP) can be introduced to the DAX language (Data Analysis Expressions), which provides a way to author formulas that are not evaluated immediately, but that can be evaluated dynamically and concurrently in many different contexts. The function enables a single relationship to be specified in the calculation formula away from the query. This provides a mechanism within the formula that specifies specific relationship(s) that are to be followed when the dynamic expression is evaluated.

Claims

1. A computer-implemented method, comprising:

receiving a data analysis expression comprising a calculation and a relationship to use for the calculation from multiple relationships between data of two tables of a database;

overriding a default relationship setting of the database with the relationship;

retrieving data from the two tables based on the relationship;

initiating execution of the calculation on the data; and

restoring the default relationship after execution of the calculation is complete.

2. The method of claim 1 further comprises receiving a function as part of the data analysis expression that includes arguments that identify the relationship.

3. The method of claim 2 further comprises overriding the default relationship setting of the database with an innermost invocation of nested invocations of the function.

4. The method of claim 2 further comprises invoking the function multiple times to specify the multiple relationships.

5. The method of claim 1 further comprises assigning weighted priorities to the multiple relationships and calculating a path weight based on the priorities.

6. The method of claim 1 further comprises receiving a function as part of the data analysis expression that specifies the relationship as a first name of an existing column in a first table and a second name of an existing column in a second table.

7. The method of claim 1 further comprises initiating the execution of the calculation in response to evaluation of a query that invokes the calculation.

8. A system, comprising:

a processor coupled to a memory, the processor configured to execute computer-executable instructions stored in the memory that when executed perform the following acts;

receive a data analysis expression comprising a calculation and a relationship from multiple relationships between data of two tables of a database to employ with respect to the calculation;

override a default relationship setting of the database with the relationship;

retrieve data from the two tables based on the relationship;

initiate execution of the calculation on the data; and

restore the default relationship after execution of the calculation is complete.

9. The system of claim 8, the relationship is specified by a function in which function arguments represent two endpoints of the relationship.

10. The system of claim 8, the multiple relationships are assigned weighted priorities and an ambiguity is resolved based on the weighted priorities.

11. The system of claim 8, the relationship includes a first name of an existing column in a first table and a second name of an existing column in a second table.

12. The system of claim 11, the relationship is one-to-many, wherein the first name of an existing column in the first table represents a many side of the relationship and the second name of an existing column in the second table represents a one side of the relationship.

13. The system of claim 8, the calculation is a function that takes the relationship as a filter.

14. The system of claim 8, execution of the calculation is initiated in response to evaluation of a query that invokes the calculation.

15. A computer-readable storage medium having instructions stored thereon that enable at least one processor to perform a method upon execution of the instructions, the method comprising:

receiving a data analysis expression comprising a calculation and an explicit relationship from multiple relationships between data of two tables of a database to employ with respect to the calculation;

overriding a default relationship setting of the database with the explicit relationship;

retrieving data from the two tables based on the explicit relationship;

initiating execution of the calculation on the data; and

restoring the default relationship after execution of the calculation is complete.

16. The computer-readable storage medium of claim 15, the method further comprises initiating the execution of the calculation in response to evaluation of a query that employs the calculation.

17. The computer-readable storage medium of claim 15, the method further comprises employing an innermost explicit relationship for nested data analysis expressions.

18. The computer-readable storage medium of claim 15, the method further comprises identifying the explicit relationship from arguments of a data expression function, wherein the arguments are a first name of an existing column in a first table and a second name of a column in a second table.

19. The computer-readable storage medium of claim 15, the method of overriding the default relationship setting comprises setting the state of the explicit relationship as active temporarily.

20. The computer-readable storage medium of claim 15, the method further comprises setting the state of remaining relationships of the multiple relationships as inactive.