Systems, methods, and apparatuses for populating a table having null values using a predictive query interface

• US 9,753,962 B2
• Filed: 01/11/2016
• Issued: 09/05/2017
• Est. Priority Date: 03/13/2013
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Petschulat, Cap Christian, Jonas, Eric Michael
• Primary Examiner(s): Uddin, Md. I
• Application Number:
  US14/992,925
• Publication Number:
  US 20160125011A1
• Time in Patent Office: 603 Days
• Field of Search: 707737, 707739, 707741, 707722, 707736
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/23 Updating
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/24553 of query operations
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/24558 Binary matching operations
• CPC Class Codes: G06F 16/24578 using ranking
• CPC Class Codes: G06F 16/2458 Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/285 Clustering or classification
• CPC Class Codes: G06F 16/316 Indexing structures
• CPC Class Codes: G06F 17/18 for evaluating statistical data , e.g. average values, frequency distributions, probability functions, regression analysis forecasting specially adapted for a specific administrative, business or logistic context G06Q10/04
• CPC Class Codes: G06F 3/04842 : Selection of displayed objects or displayed text elements G06F3/0482 takes precedence
• CPC Class Codes: G06F 3/04847 : Interaction techniques to control parameter settings, e.g. interaction with sliders or dials
• CPC Class Codes: G06F 40/18 : of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06N 20/00 : Machine learning
• CPC Class Codes: G06N 7/01 : Probabilistic graphical models, e.g. probabilistic networks
• CPC Class Codes: G06Q 30/0201 : Market modelling; Market analysis; Collecting market data
• CPC Class Codes: G06Q 30/0202 : Market predictions or forecasting for commercial activities

Abstract

Disclosed herein are systems and methods for populating a table having null values using a predictive query interface including means for receiving a tabular dataset from a user as input, the tabular dataset having data values organized as columns and rows; identifying a plurality of null values within the tabular dataset, the null values being dispersed across multiple rows and multiple columns of the tabular dataset; generating indices from the tabular dataset of columns and rows, the indices representing probabilistic relationships between the rows and the columns of the tabular dataset; displaying the tabular dataset as output to the user, the displayed output including the data values depicted as known values and the null values depicted as unknown values; receiving input from the user to populate at least a portion of the unknown values within the displayed tabular dataset with predicted values; querying the indices for the predicted values; and displaying the predicted values as updated output to the user. Other related embodiments are further disclosed.

Claims

1. A method in a host organization having at least a processor and a memory therein to execute instructions, the method comprising:

receiving a request at the host organization from a user device to display a tabular dataset;

retrieving the tabular dataset from a database system executing at the host organization;

displaying the tabular dataset as output to the user device, the displayed output including a plurality of data values depicted as known values and a plurality of null values depicted as unknown values;

receiving input from the user device to populate the tabular dataset to a specified fill percentage;

querying the database system for predicted values to populate a portion of the null values of the tabular dataset, wherein querying the database system comprises issuing a PREDICT command term and passing as a parameter one or more specified columns of the tabular dataset to be predicted;

receiving a distribution for every one of the plurality of null values within the tabular dataset responsive to querying the indices for the predicted values;

calculating a credible interval for each distribution received;

populating the portion of the null values of the tabular dataset with the predicted values until the specified fill percentage is reached; and

displaying the tabular dataset having the predicted values populated therein as updated output to the user device by displaying selected ones of the predicted values that correspond to a calculated credible interval in excess of a minimum threshold.

2. The method of claim 1, further comprising:

receiving the tabular dataset from the user device as input, the tabular dataset having data values organized as columns and rows;

identifying the plurality of null values within the tabular dataset, the null values being dispersed across multiple rows and multiple columns of the tabular dataset;

generating indices from the tabular dataset of columns and rows, the indices representing probabilistic relationships between the rows and the columns of the tabular dataset; and

storing the indices within the database system of the host organization.

3. The method of claim 1:

wherein querying the database system for the predicted values comprises constructing a query for the database system specifying at least (i) the PREDICT command term, (ii) the one or more specified columns of the tabular dataset to be predicted, and (iii) one or more column name=value pairs specifying column names to be fixed and corresponding values by which to fix the column names.

4. The method of claim 1, further comprising:

generating a predictive record set responsive to the querying;

wherein the predictive record set comprises a plurality of elements therein, each of the plurality of elements specifying a value for each of the one or more specified columns of the tabular dataset to be predicted per row of the tabular dataset; and

returning the predictive record set responsive to the query.

5. The method of claim 1, wherein querying the database system comprises issuing the query to a Predictive Query Language Application Programming Interface (PreQL API) exposed by the host organization by passing a PreQL query to the database system via the PreQL API, the PreQL query having a query syntax of:

the PREDICT command term as a required term;

a required TARGET term specifying the one or more specified columns of the tabular dataset to be predicted;

a required WHERE term that specifies column names to be fixed and the values by which to fix them as the one or more column name=value pairs, wherein the required WHERE term restricts output of the query to a predictive record set having returned elements that are probabilistically related to the one or more columns to be fixed and the corresponding values by which to fix the column names as specified; and

an optional FROM term specifying the tabular dataset as the data source to be queried within the database system.

6. The method of claim 1, further comprising:

receiving a confidence indicator for every one of the plurality of null values within the tabular dataset responsive to querying the database system for the predicted values, the confidence indicator based on a comparison of known results corresponding to known and non-null values within the dataset with the predicted values; and

wherein displaying the tabular dataset having the predicted values populated therein as updated output to the user device further includes displaying the selected ones of the predicted values which correspond to the confidence indicator being in excess of a default minimum confidence threshold or a user specified minimum confidence threshold when present.

7. The method of claim 1, wherein displaying the tabular dataset further comprises:

displaying the known values using black text within cells of a spreadsheet;

displaying the unknown values as blank cells within the spreadsheet; and

displaying the predicted values using colored or grayscale text within the cells of the spreadsheet.

8. The method of claim 1:

wherein displaying the tabular dataset having the predicted values populated therein as updated output to the user device comprises displaying the updated output within a spreadsheet or table at a Graphical User Interface (GUI);

wherein the known values are displayed as populated cells within the spreadsheet or table at the GUI in a first type of text;

wherein predicted values are displayed as populated cells within the spreadsheet or table at the GUI in a second type of text discernable from the first type of text corresponding to the known values; and

wherein any remaining unknown values are displayed as empty cells within the spreadsheet or table at the GUI.

9. The method of claim 1:

wherein displaying the tabular dataset having the predicted values populated therein as updated output to the user device comprises displaying the tabular dataset and the predicted values within a spreadsheet or table at a Graphical User Interface (GUI);

wherein the GUI further comprises a slider interface controllable by the user to specify the fill percentage at GUI displayed to the user device; and

wherein receiving the input from the user device to populate the tabular dataset to the specified fill percentage comprises receiving the specified fill percentage as input from the user device via the slider interface.

10. The method of claim 9, further comprising:

receiving second input from the user device indicating a minimum acceptable degree of uncertainty; and

wherein populating the portion of the null values of the tabular dataset with the predicted values until the specified fill percentage is reached comprises populating the portion of the null values of the tabular dataset with predicted values having a confidence indicator returned by the query in excess of the minimum acceptable degree of uncertainty as specified via the second input from the user device.

11. The method of claim 1, wherein the specified fill percentage corresponds to a percentage of known values within the tabular dataset from a sum of all null values and all known values for the tabular dataset.

12. The method of claim 1, wherein the input for the specified fill percentage controllable by the user is restricted to a range encompassing a minimum fill percentage based on a quantity of the known values within the tabular dataset and a maximum degree of uncertainty necessary to populate the displayed tabular dataset to the specified fill percentage as specified via the input from the user device.

13. The method of claim 12, further comprising:

populating the tabular dataset to a 100% fill percentage responsive to the input from the user device specifying a maximum acceptable degree of uncertainty and specifying a 100% fill percentage via the input; and

populating all null values by degrading a required confidence threshold for each of the predicted values until a predicted value is available for every one of the plurality of null values within the tabular dataset.

14. The method of claim 1, wherein displaying the tabular dataset having the predicted values populated therein as updated output to the user device further comprises:

displaying the known values as a first text type within cells of a spreadsheet;

displaying the predicted values as a second text type within the cells of the spreadsheet, wherein the second text type has a displayed opacity in proportion to a confidence indicator returned by the query for the predicted value displayed.

15. The method of claim 1, further comprising:

displaying a prediction difficulty score for every column of the tabular dataset displayed as output to the user on a per-column basis, wherein the prediction difficulty score is calculated for each column of the tabular dataset by;

(i) identifying all unknown values within the column;

(ii) querying the indices for a predicted value corresponding to each of the unknown values identified within the column;

(iii) receiving the confidence indicator for each of the unknown values identified within the column; and

(iv) calculating the prediction difficulty score for the column based on the confidence indicators received for the unknown values identified within the column.

16. A non-transitory computer readable storage medium having instructions stored thereon that, when executed by a processor in a host organization, the instructions cause the host organization to perform operations comprising:

receiving a request at the host organization from a user device to display a tabular dataset;

retrieving the tabular dataset from a database system executing at the host organization;

displaying the tabular dataset as output to the user device, the displayed output including a plurality of data values depicted as known values and a plurality of null values depicted as unknown values;

receiving input from the user device to populate the tabular dataset to a specified fill percentage;

querying the database system for predicted values to populate a portion of the null values of the tabular dataset, wherein querying the database system comprises issuing a PREDICT command term and passing as a parameter one or more specified columns of the tabular dataset to be predicted;

receiving a distribution for every one of the plurality of null values within the tabular dataset responsive to querying the indices for the predicted values;

calculating a credible interval for each distribution received;

populating the portion of the null values of the tabular dataset with the predicted values until the specified fill percentage is reached; and

displaying the tabular dataset having the predicted values populated therein as updated output to the user device by displaying selected ones of the predicted values that correspond to a calculated credible interval in excess of a minimum threshold.

17. The non-transitory computer readable storage medium of claim 16, wherein querying the database system for the predicted values comprises constructing a query for the database system specifying at least (i) the PREDICT command term, (ii) the one or more specified columns of the tabular dataset to be predicted, and (iii) one or more column name=value pairs specifying column names to be fixed and corresponding values by which to fix the column names.

18. A system to operate within a host organization, the system comprising:

a processor to execute instructions stored in memory of the system;

a request interface to receive a request at the host organization from a user device to display a tabular dataset;

a query interface to retrieve the tabular dataset from a database system executing at the host organization;

the request interface to further display the tabular dataset as output to the user device, the displayed output including a plurality of data values depicted as known values and a plurality of null values depicted as unknown values;

the request interface to further receive input from the user device to populate the tabular dataset to a specified fill percentage;

the query interface to further query the database system for predicted values to populate a portion of the null values of the tabular dataset, wherein querying the database system comprises issuing a PREDICT command term and passing as a parameter one or more specified columns of the tabular dataset to be predicted, wherein the query interface is to receive a distribution for every one of the plurality of null values within the tabular dataset responsive to the query;

an analysis engine to calculate a credible interval for each distribution received and wherein the analysis engine is to further populate the portion of the null values of the tabular dataset with the predicted values until the specified fill percentage is reached; and

wherein the request interface is to further display the tabular dataset having the predicted values populated therein as updated output to the user device by displaying selected ones of the predicted values that correspond to a calculated credible interval in excess of a minimum threshold.

19. The system of claim 18:

wherein the query interface is to query the database system comprises the query interface construct a query for the database system specifying at least (i) the PREDICT command term, (ii) the one or more specified columns of the tabular dataset to be predicted, and (iii) one or more column name=value pairs specifying column names to be fixed and corresponding values by which to fix the column names.