Query semantics for multi-fact data model analysis using shared dimensions

Patent metadata

Field	Value
Publication number	US-12373467-B2
Application number	US-202418424619-A
Country	US
Kind code	B2
Filing date	Jan 26, 2024
Priority date	May 8, 2023
Publication date	Jul 29, 2025
Grant date	Jul 29, 2025

Abstract

A computing device receives user input specifying a first dimension data field and a second dimension data field. The device constructs a dimension subquery according to characteristics of the first dimension data field, the second dimension data field, a first object to which the first dimension data field belongs, and/or a second object to which the second dimension data field belongs, including determining a join type for combining (i) first data rows that include data values of the first dimension data field and (ii) second data rows that include data values of the second dimension data field. The device constructs the dimension subquery according to the determined join type, and executes the dimension subquery to retrieve first tuples. The device constructs measure subqueries and executes the measure subqueries to retrieve second tuples. The device forms extended tuples, and generates and displays the data visualization according to the extended tuples.

First claim

What is claimed is:

1. A method for generating data visualizations using multi-fact object models, comprising:
at computing device having a display, one or more processors and memory storing one or more programs configured for execution by the one or more processors:
receiving a first user input specifying a first dimension data field and a second dimension data field for generating a first data visualization;
determining that the first dimension data field belongs to a first object of an object model and the second dimension data field belongs to a second object of the object model, distinct from the first object;
constructing a dimension subquery according to characteristics of the first dimension data field, the second dimension data field, the first object, and the second object, including:
determining a join type for combining (i) first data rows that include data values of the first dimension data field and (ii) second data rows that include data values of the second dimension data field; and
constructing the dimension subquery according to the determined join type, the dimension subquery referencing the first object and the second object;

executing the dimension subquery against one or more data sources corresponding to the first dimension data field and the second dimension data field to retrieve first tuples that comprise unique ordered combinations of data values for the first dimension data field and the second dimension data field;
constructing one or more measure subqueries, each of the measure subqueries referencing one or more measure data fields in the object model;
executing the one or more measure subqueries to retrieve second tuples;
forming extended tuples by combining the retrieved first tuples and the retrieved second tuples; and
generating and displaying the first data visualization according to the extended tuples.

2. The method of claim 1 , wherein constructing the dimension subquery according to the characteristics of the first dimension data field, the second dimension data field, the first object, and the second object includes:
in accordance with a determination that (i) the first dimension data field can be traced to one root object and (ii) the second dimension data field can be traced to the same root object:
combining data columns of the first dimension data field and the second dimension data field using an inner join.

3. The method of claim 1 , wherein constructing the dimension subquery according to the characteristics of the first dimension data field, the second dimension data field, the first object, and the second object includes:
in accordance with a determination that (i) the first dimension data field can be traced to a first root object and (ii) the second dimension data field can be traced to a second root object that is distinct from the first root object:
forming a first object tree that includes the first object and the first root object, and combining data columns from objects of the first object tree according to data values of the first dimension data field using an inner join to form a first table;
forming a second object tree that includes the second object and the second root object, and combining data columns from objects of the second object tree according to data values of the second dimension data field using an inner join to form a second table; and
combining data columns of the first table and the second table via a cross join.

4. The method of claim 1 , wherein constructing the dimension subquery according to the characteristics of the first dimension data field, the second dimension data field, the first object, and the second object includes:
in accordance with a determination that the first dimension data field and the second dimension data field belong to the same object that is shared by two or more root objects:
combining data columns of the first dimension data field and the second dimension data field using an inner join.

5. The method of claim 1 , wherein constructing the dimension subquery according to the characteristics of the first dimension data field, the second dimension data field, the first object, and the second object includes:
in accordance with a determination that (i) the first object is shared by a first set of root objects and (ii) the second object is shared by a second set of root objects:
combining data columns of the first dimension data field and the second dimension data field using a cross join.

6. The method of claim 1 , wherein constructing the dimension subquery according to the characteristics of the first dimension data field, the second dimension data field, the first object, and the second object includes:
in accordance with a determination that (i) the first object is a first root object, (ii) the second object can be traced to the first root object, and the (iii) the second dimension data field is not shared by another root object:
combining data columns of the first dimension data field and the second dimension data field using an inner join.

7. The method of claim 1 , wherein at least one of the first dimension data field or the second dimension data field is a geographic data field.

8. The method of claim 1 , wherein at least one of the first dimension data field or the second dimension data field is a date/time data field.

9. A computing device, comprising:
one or more processors;
memory;
a display; and
one or more programs stored in the memory and configured for execution by the one or more processors, the one or more programs comprising instructions for:
receiving a first user input specifying a first dimension data field and a second dimension data field for generating a first data visualization;
determining that the first dimension data field belongs to a first object of an object model and the second dimension data field belongs to a second object of the object model, distinct from the first object;
constructing a dimension subquery according to characteristics of the first dimension data field, the second dimension data field, the first object, and the second object, including:
determining a join type for combining (i) first data rows that include data values of the first dimension data field and (ii) second data rows that include data values of the second dimension data field; and
constructing the dimension subquery according to the determined join type, the dimension subquery referencing the first object and the second object;

executing the dimension subquery against one or more data sources corresponding to the first dimension data field and the second dimension data field to retrieve first tuples that comprise unique ordered combinations of data values for the first dimension data field and the second dimension data field;
constructing one or more measure subqueries, each of the measure subqueries referencing one or more measure data fields in the object model;
executing the one or more measure subqueries to retrieve second tuples;
forming extended tuples by combining the retrieved first tuples and the retrieved second tuples; and
generating and displaying the first data visualization according to the extended tuples.

10. The computing device of claim 9 , wherein the instructions for constructing the dimension subquery according to the characteristics of the first dimension data field, the second dimension data field, the first object, and the second object include instructions for:
in accordance with a determination that (i) the first dimension data field can be traced to one root object and (ii) the second dimension data field can be traced to the same root object:
combining data columns of the first dimension data field and the second dimensio

Assignees

• Salesforce Inc

Inventors

• Nhan Thomas
• Martin Tyler
• Amador Franz Gustave
• Simo Boitel Marian
• Booth Jr Jeffrey Mark
• Paul-Jones Russell Steven
• Feng Jinbo

Classifications

• G06F3/04817

  using icons (graphical or visual programming using iconic symbols G06F8/34) · CPC title

• G06F3/04812

  Interaction techniques based on cursor appearance or behaviour, e.g. being affected by the presence of displayed objects · CPC title

• G06F16/9027 Primary

  Trees · CPC title

• G06F16/2453

  Query optimisation · CPC title

• G06F16/24535

  of sub-queries or views · CPC title