Blending and Visualizing Data from Multiple Data Sources

Abstract

A user selects a first group of one or more dimension fields that specifies a primary aggregation for a data visualization, where the one or more dimension fields are in a first data source. When one or more first dimension fields in the first group are not dimension fields in a second data source, a client device joins the first data source with the second data source at a first level of detail that is more granular than the primary aggregation to form a single combined data set that includes the one or more dimension fields specified in the first group and one or more measure data fields aggregated according to the first group. The client device rolls up the combined data set, including the one or more measure data fields, to form a final data set and displays the data visualization using the data from the final data.

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 user selection of two or more distinct data sources, each data source having a respective set of data fields and a respective set of data rows; 
 receiving user selection of a first group of one or more dimension fields that specifies a primary aggregation for a data visualization, wherein each of the one or more dimension fields in the first group is a data field in a first data source of the two or more distinct data sources; 
 in accordance with a determination that one or more first dimension fields in the first group are not dimension fields in a second data source of the two or more distinct data sources:
 joining the first data source with the second data source at a first level of detail that is more granular than the primary aggregation specified in the first group to form a single combined data set that includes the one or more dimension fields specified in the first group and one or more measure data fields aggregated according to the first group; 
 rolling up the combined data set, including the one or more measure data fields, to form a final data set based on the one or more dimension fields from the first group that specifies the primary aggregation for the data visualization; and 
 
 displaying the data visualization using the data from the final data set. 
   
     
     
         2 . 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. 
     
     
         3 . The method of  claim 1 , wherein the first data source of the two or more data sources is designated as a primary data source and each of the other data sources of the two or more data sources is designated as a secondary data source. 
     
     
         4 . The method of  claim 3 , wherein joining the first data source with the second data source comprises performing an outer join locally at the client device with an intermediate data set from the primary data source as the outer data set with respect to all other intermediate data sets. 
     
     
         5 . The method of  claim 3 , further comprising:
 receiving user specification of filtering based on one or more fields in one or more secondary data sources;   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. 
   
     
     
         6 . The method of  claim 3 , wherein rolling up the combined data set comprises aggregating measure fields from the second data source, including generating a query for the primary data source that includes adding one or more linking fields, wherein the linking fields correspond to fields in the secondary data sources. 
     
     
         7 . The method of  claim 3 , further comprising identifying a second group of one or more dimension fields, each of which is in each of the data sources, the second group specifying an intermediate aggregation that is more granular than the primary aggregation. 
     
     
         8 . The method of  claim 7 , 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. 
     
     
         9 . The method of  claim 3 , 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. 
     
     
         10 . The method of  claim 1 , wherein the first data source comprises one or more tables in a relational database. 
     
     
         11 . The method of  claim 1 , wherein the first data source is a data cube. 
     
     
         12 . The method of  claim 1 , wherein at least two data sources of the two or more distinct data sources are not collocated. 
     
     
         13 . The method of  claim 1 , wherein at least two data sources are accessed using distinct software applications. 
     
     
         14 . 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 user selection of two or more distinct data sources, each data source having a respective set of data fields and a respective set of data rows; 
 receiving user selection of a first group of one or more dimension fields that specifies a primary aggregation for a data visualization, wherein each of the one or more dimension fields in the first group is a data field in a first data source of the two or more distinct data sources; 
 in accordance with a determination that one or more first dimension fields in the first group are not dimension fields in a second data source of the two or more distinct data sources:
 joining the first data source with the second data source at a first level of detail that is more granular than the primary aggregation specified in the first group to form a single combined data set that includes the one or more dimension fields specified in the first group and one or more measure data fields aggregated according to the first group; 
 rolling up the combined data set, including the one or more measure data fields, to form a final data set based on the one or more dimension fields from the first group that specifies the primary aggregation for the data visualization; and 
 
   displaying the data visualization using the data from the final data.   
     
     
         15 . The client device of  claim 14 , wherein the first data source of the two or more data sources is designated as a primary data source and each of the other data sources of the two or more data sources is designated as a secondary data source. 
     
     
         16 . The client device of  claim 15 , wherein joining the first data source with the second data source comprises performing an outer join locally at the client device with an intermediate data set from the primary data source as the outer data set with respect to all other intermediate data sets. 
     
     
         17 . The client device of  claim 15 , further comprising:
 receiving user specification of filtering based on one or more fields in one or more secondary data sources;   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. 
   
     
     
         18 . The client device of  claim 15 , wherein rolling up the combined data set comprises aggregating measure fields from the second data source, including generating a query for the primary data source that includes adding one or more linking fields, wherein the linking fields correspond to fields in the secondary data sources. 
     
     
         19 . The client device of  claim 14 , wherein at least two data sources of the two or more distinct data sources are not collocated. 
     
     
         20 . A non-transitory computer readable storage medium storing one or more programs configured for execution by a client device having one or more processors and memory storing one or more programs for execution by the one or more processors, the one or more programs comprising instructions for:
 receiving user selection of two or more distinct data sources, each data source having a respective set of data fields and a respective set of data rows;
 receiving user selection of a first group of one or more dimension fields that specifies a primary aggregation for a data visualization, wherein each of the one or more dimension fields in the first group is a data field in a first data source of the two or more distinct data sources; 
 in accordance with a determination that one or more first dimension fields in the first group are not dimension fields in a second data source of the two or more distinct data sources:
 joining the first data source with the second data source at a first level of detail that is more granular than the primary aggregation specified in the first group to form a single combined data set that includes the one or more dimension fields specified in the first group and one or more measure data fields aggregated according to the first group; 
 rolling up the combined data set, including the one or more measure data fields, to form a final data set based on the one or more dimension fields from the first group that specifies the primary aggregation for the data visualization; and 
 
 displaying the data visualization using the data from the final data.