Specification of database table relationships for calculation

Patent metadata

Field	Value
Publication number	US-10102243-B2
Application number	US-201615134124-A
Country	US
Kind code	B2
Filing date	Apr 20, 2016
Priority date	Jun 16, 2011
Publication date	Oct 16, 2018
Grant date	Oct 16, 2018

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.

First claim

What is claimed is:

1. A method performed by a system comprising at least one processor coupled to a memory storing machine-executable instructions, which, when executed by the processor, controls the system to perform acts, 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 of the database with the relationship by setting the relationship as active and other relationships of the multiple relationships as inactive;
retrieving data from the two tables for the calculation based on the active relationship;
initiating execution of the calculation on the data, wherein execution of the calculation comprises evaluating an expression specified by the calculation on the data retrieved from the two tables;
restoring the default relationship after execution of the calculation is complete by setting the default relationship as active and other relationships of the multiple relationships as inactive; and
returning a result of execution of the calculation.

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 of the database with the relationship by setting the default relationship as active and other relationships of the multiple relationships as inactive;
retrieve data from the two tables for the calculation based on the active relationship, wherein the active relationship enables acquisition of data based on a particular column of matching data in the two tables;
initiate execution of the calculation on the data, wherein execution of the calculation comprises evaluating an expression specified by the calculation on the data retrieved from the two tables;
restore the default relationship after execution of the calculation is complete by setting the default relationship as active and other relationships of the multiple relationships as inactive; and
return a result of execution of the calculation.

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 by setting the explicit relationship as active and other relationships of the multiple relationships as inactive;
retrieving data from the two tables for the calculation based on the active relationship, wherein the active relationship enables acquisition of data based on a particular column of matching data in the two tables;
initiating execution of the calculation on the data, wherein execution of the calculation comprises evaluating an expression specified by the calculation on the data retrieved from the two tables; and
restoring the default relationship after execution of the calculation is complete by setting the default relationship as active and other relationships of the multiple relationships as inactive; and
returning a result of execution of the calculation.

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 method of claim 1 , returning the result comprises returning a computed aggregate measure.

20. The system of claim 8 , the result comprises a computed aggregate measure.

Assignees

• Microsoft Technology Licensing Llc

Inventors

• Dickerman Howard
• Min Vjacheslav
• Dumitru Marius

Classifications

• G06F17/30955

  Physics · mapped topic

• G06F17/3056

  Physics · mapped topic

• G06F17/30595

  Physics · mapped topic

• G06F17/30421 Primary

  Physics · mapped topic

• G06F16/902 Primary

  using more than one table in sequence, i.e. systems with three or more layers · CPC title