Blending and visualizing data from multiple data sources

Patent metadata

Field	Value
Publication number	US-9633076-B1
Application number	US-201314054803-A
Country	US
Kind code	B1
Filing date	Oct 15, 2013
Priority date	Oct 15, 2012
Publication date	Apr 25, 2017
Grant date	Apr 25, 2017

Abstract

A method dynamically combines data from multiple data sources. A visual specification for a data visualization requires data from a primary data source and one or more secondary data sources. The primary data source is independent of the secondary data sources. The visual specification includes a first set of dimension fields that specify a hierarchical level for the data visualization. A second set of dimension fields specify a hierarchical level for joining data sets from the data sources. The hierarchical level for joining data sets is more granular than the hierarchical level for the data visualization. For each data source, a query is generated, and a data set is retrieved from the data source using the query. A single combined data set is formed that includes the dimension fields in the first set. The combined data set is rolled-up and a data visualization is displayed using the rolled-up data.

First claim

What is claimed is:

1. A method for dynamically combining data from multiple data sources, comprising:
at a client device having one or more processors and memory storing one or more programs for execution by the one or more processors:
receiving a visual specification for a data visualization from a user, wherein the data visualization requires data from two or more distinct data sources;
identifying one of the distinct data sources as a primary data source and identifying the remaining distinct data sources as secondary data sources, wherein the primary data source is independent of the secondary data sources;
receiving user selection of a first group of one or more dimension fields that specify a primary aggregation for the data visualization, wherein each of the one or more dimension fields is a data field in at least one of the data sources;
in accordance with a determination that one or more first dimension fields in the first group are not data fields in one of the data sources, identifying a second group of one or more dimension fields based on the first group by replacing the one or more first dimension fields in the first group with one or more second dimension fields, wherein the second group specifies an intermediate aggregation for joining data sets that is more granular than the primary aggregation specified by the first group;
for each distinct data source:
generating a respective query based on the visual specification, wherein the respective query specifies aggregation of measure fields based on the second group of one or more dimension fields; and
aggregating data from the respective distinct data source using the respective query to form a respective intermediate data set that is aggregated according to the second group of one or more dimension fields;

forming a single combined data set from the intermediate data sets, including the one or more dimension fields specified in the first group;
aggregating each measure field in the combined data set using the primary aggregation according to the one or more dimension fields specified in the first group, thereby rolling up the combined data set to form a final data set based on the one or more dimension fields from the first group; and
displaying a data visualization according to the visual specification using the data from the final data set.

2. The method of claim 1 , wherein forming the single combined data set comprises performing an outer join locally at the client device with the retrieved data set from the primary data source as the outer data set with respect to all other data sets.

3. The method of claim 1 , wherein the visual specification specifies filtering based on one or more fields in one or more secondary data sources, the method further comprising:
determining whether a computing system hosting the primary data source supports receiving and storing temporary tables collocated with the primary data source;
when the computing system hosting the primary data source supports receiving and storing temporary tables collocated with the primary data source:
creating a mapping table using records from one or more of the secondary data sources and transmitting the mapping table to storage collocated with the primary data source;
wherein retrieving the respective data set from the primary data source includes executing the respective query for the primary data source, which filters data from the primary data source using data from the mapping table not present in the primary data source; and

when the computing system hosting the primary data source does not support receiving and storing temporary tables collocated with the primary data source:
generating the query for the primary data source that does not filter the primary data source based on the one or more fields in the one or more secondary data sources; and
after retrieving the data set from the primary data source, filtering the data set from the primary data source locally at the client device, using a local mapping table generated using records from one or more of the secondary data sources.

4. The method of claim 1 , wherein generating the query for the primary data source includes adding one or more linking fields to the query, wherein the linking fields correspond to fields in the secondary data sources and the linking fields are not present in the visual specification.

5. The method of claim 1 , wherein identifying the second group of one or more dimension fields comprises matching data fields in the primary data source to data fields in each secondary data source based on field names and data types corresponding to each field in the primary and secondary data sources.

6. The method of claim 1 , wherein identifying the second group of one or more dimension fields comprises prompting the user to identify linking fields between the primary data source and each of the secondary data sources when linking fields cannot be determined automatically based on field names and data types.

7. The method of claim 1 , wherein the primary data source comprises one or more tables in a relational database.

8. The method of claim 1 , wherein the primary data source is a data cube.

9. The method of claim 1 , wherein one or more of the data sources are selected from the group consisting of spreadsheets, text files, and CSV files.

10. The method of claim 1 , wherein the primary data source is not collocated with any of the secondary data sources.

11. The method of claim 1 , wherein the primary data source and secondary data sources are accessed using distinct software applications.

12. A client device, comprising:
one or more processors;
memory; and
one or more programs stored in the memory for execution by the one or more processors, the one or more programs comprising instructions for:
receiving a visual specification for a data visualization from a user, wherein the data visualization requires data from two or more distinct data sources;
identifying one of the distinct data sources as a primary data source and identifying the remaining distinct data sources as secondary data sources, wherein the primary data source is independent of the secondary data sources;
receiving user selection of a first group of one or more dimension fields that specify a primary aggregation for the data visualization, wherein each of the one or more dimension fields is a data field in at least one of the data sources;
in accordance with a determination that one or more first dimension fields in the first group are not data fields in one of the data sources, identifying a second group of one or more dimension fields based on the first group by replacing the one or more first dimension fields in the first group with one or more second dimension fields, wherein the second group specifies an intermediate aggregation for joining data sets that is more granular than the primary aggregation specified by the first group;
for each distinct data source:
generating a respective query based on the visual specification, wherein the respective query specifies aggregation of measure fields based on the second group of one or more dimension fields; and
aggregating data from the respective distinct data source using the respective query to form a respective intermediate data set that is aggregated according to the second group of one or more dimension fields;

forming a single combined data set from the intermediate data sets, including the one or more dimension fields specified in the first group;
aggregating each measure field in the combined data set using the primary aggregation according to the one or more dimension fields specified in the first group, thereby rollin

Assignees

• Tableau Software Inc

Inventors

• Morton Robert
• Bunker Ross
• Shaw Marianne
• Terlecki Pawel

Classifications

• G06F17/30466 Primary

  Physics · mapped topic

• G06F16/24556

  Aggregation; Duplicate elimination · CPC title

• G06F16/283

  Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP · CPC title

• G06F16/9535

  Search customisation based on user profiles and personalisation · CPC title

• G06F16/284

  Relational databases · CPC title