Systems and methods for transforming datasets

• US 10,157,234 B1
• Filed: 02/27/2015
• Issued: 12/18/2018
• Est. Priority Date: 02/27/2015
• Status: Active Grant

• Current Assignee: Quest Software, Inc.
• Original Assignee: Quest Software, Inc.
• Inventors: Alagappan, Kannan, Jonsson, Jan Henrik
• Primary Examiner(s): Featherstone, Mark D
• Assistant Examiner(s): Wu, Tony
• Application Number:
  US14/633,826
• Time in Patent Office: 1,390 Days
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/955 using information identifiers, e.g. uniform resource locators [URL]

Abstract

In one embodiment, a method includes receiving a data-transformation request in relation to a visualization of a first dataset, wherein the data-transformation request expresses at least one transformation of the first dataset without using Structured Query Language (SQL) syntax, wherein the visualization is characterized by a first SQL statement. In addition, the method includes automatically determining based, at least in part, on a mapping of the visualization to the first SQL statement, a second SQL statement operable to return a second dataset. Further, the method includes causing the second SQL statement to be executed on one or more databases. Moreover, the method includes receiving, in response to the causing, the second dataset. The method also includes updating the visualization to reflect the second dataset.

Claims

1. A method comprising, by a computer system:

receiving a data-transformation request in relation to a visualization of a first dataset, wherein the visualization represents columns of data, wherein the data-transformation request expresses at least one transformation of the first dataset without using Structured Query Language (SQL) syntax, wherein the visualization is characterized by a first SQL statement maintained by the computer system, wherein the columns of the visualization are mapped to constituent parts of the first SQL statement in which the columns are referenced;

automatically determining a second SQL statement configured to return a second dataset, wherein the second dataset implements the at least one transformation of the first dataset, the automatically determining comprising;

looking up an identifier of the at least one transformation in a repository;

responsive to a determination that there exists, in the repository for the identifier, an applicable SQL generation rule other than a default rule, automatically building the second SQL statement based, at least in part, on the mapping of the visualization to the constituent parts of the first SQL statement, using SQL syntax specified by the applicable SQL generation rule; and

responsive to a determination that there does not exist, in the repository for the identifier, an applicable SQL generation rule other than the default rule, automatically building the second SQL statement based, at least in part, on the mapping of the visualization to the constituent parts of the first SQL statement, using SQL syntax specified by the default rule, the building comprising including the first SQL statement as a subquery in a clause of the second SQL statement;

causing the second SQL statement to be executed on one or more databases;

receiving the second dataset in response to the causing; and

updating the visualization to reflect the second dataset.

2. The method of claim 1, comprising, prior to the receiving of the second dataset:

determining the first SQL statement;

causing the first SQL statement to be executed on the one or more databases;

receiving, in response to the causing, the first dataset;

generating the visualization;

mapping parts of the visualization to elements of the first SQL statement; and

publishing the visualization to a user.

3. The method of claim 1, comprising publishing the updated visualization to a user.

4. The method of claim 1, comprising:

storing the second SQL statement;

retrieving the second SQL statement upon user request during a subsequent session;

causing the second SQL statement to be executed on the one or more databases;

receiving the second dataset;

generating a subsequent visualization of the second dataset; and

publishing the subsequent visualization to a requesting user.

5. The method of claim 1, comprising:

mapping parts of the updated visualization to elements of the second SQL statement;

receiving a subsequent data-transformation request in relation to the visualization, wherein the data-transformation request expresses at least one transformation of the second dataset using non-SQL syntax;

automatically determining based, at least in part, on a result of the mapping, a third SQL statement configured to return a third dataset, wherein the third dataset implements the at least one transformation of the second dataset;

causing the third SQL statement to be executed on the one or more databases;

receiving, in response to the causing of the third SQL statement to be executed, the third dataset; and

updating the visualization to reflect the third dataset.

6. The method of claim 1, wherein the building comprises adding at least one of a clause and an expression to the first SQL statement.

7. The method of claim 1, comprising:

prior to the receiving of the data-transformation request, performing a pattern analysis of the first dataset;

identifying a recommended data transformation based, at least in part, on the pattern analysis; and

publishing the recommended data transformation to a user in relation to the visualization of the first dataset; and

wherein the at least one transformation comprises the recommended data transformation.

8. The method of claim 1, comprising:

prior to the receiving, determining a locale of a user;

identifying a recommended data transformation based, at least in part, on the locale;

publishing the recommended data transformation to the user in relation to the visualization of the first dataset; and

wherein the at least one transformation comprises the recommended data transformation.

9. The method of claim 5, comprising:

publishing the at least one transformation of the first dataset and the at least one transformation of the second dataset as a sequence of steps;

allowing a user to select one or more of the sequence of steps for storage;

responsive to user selection of at least one step of the sequence of steps, determining a SQL statement which corresponds to the at least one step; and

storing the determined SQL statement which corresponds to the at least one step for later retrieval.

10. An information handling system comprising at least one processor, wherein the at least one processor is configured to implement a method comprising:

receiving a data-transformation request in relation to a visualization of a first dataset, wherein the visualization represents columns of data, wherein the data-transformation request expresses at least one transformation of the first dataset without using Structured Query Language (SQL) syntax, wherein the visualization is characterized by a first SQL statement maintained by the information handling system, wherein the columns of the visualization are mapped to constituent parts of the first SQL statement in which the columns are referenced;

automatically determining a second SQL statement configured to return a second dataset, wherein the second dataset implements the at least one transformation of the first dataset, the automatically determining comprising;

looking up an identifier of the at least one transformation in a repository;

responsive to a determination that there exists, in the repository for the identifier, an applicable SQL generation rule other than a default rule, automatically building the second SQL statement based, at least in part, on the mapping of the visualization to the constituent parts of the first SQL statement, using SQL syntax specified by the applicable SQL generation rule; and

responsive to a determination that there does not exist, in the repository for the identifier, an applicable SQL generation rule other than the default rule, automatically building the second SQL statement based, at least in part, on the mapping of the visualization to the constituent parts of the first SQL statement, using SQL syntax specified by the default rule, the building comprising including the first SQL statement as a subquery in a clause of the second SQL statement;

causing the second SQL statement to be executed on one or more databases;

receiving the second dataset in response to the causing; and

updating the visualization to reflect the second dataset.

11. The information handling system of claim 10, the method comprising, prior to the receiving of the second dataset:

determining the first SQL statement;

causing the first SQL statement to be executed on the one or more databases;

receiving, in response to the causing, the first dataset;

generating the visualization;

mapping parts of the visualization to elements of the first SQL statement; and

publishing the visualization to a user.

12. The information handling system of claim 10, the method comprising publishing the updated visualization to a user.

13. The information handling system of claim 10, the method comprising:

storing the second SQL statement;

retrieving the second SQL statement upon user request during a subsequent session;

causing the second SQL statement to be executed on the one or more databases;

receiving the second dataset;

generating a subsequent visualization of the second dataset; and

publishing the subsequent visualization to a requesting user.

14. The information handling system of claim 10, the method comprising:

mapping parts of the updated visualization to elements of the second SQL statement;

receiving a subsequent data-transformation request in relation to the visualization, wherein the data-transformation request expresses at least one transformation of the second dataset using non-SQL syntax;

automatically determining based, at least in part, on a result of the mapping, a third SQL statement configured to return a third dataset, wherein the third dataset implements the at least one transformation of the second dataset;

causing the third SQL statement to be executed on the one or more databases;

receiving, in response to the causing of the third SQL statement to be executed, the third dataset; and

updating the visualization to reflect the third dataset.

15. The information handling system of claim 10, the method comprising:

prior to the receiving of the data-transformation request, performing a pattern analysis of the first dataset;

identifying a recommended data transformation based, at least in part, on the pattern analysis; and

publishing the recommended data transformation to a user in relation to the visualization of the first dataset; and

wherein the at least one transformation comprises the recommended data transformation.

16. The information handling system of claim 14, the method comprising:

publishing the at least one transformation of the first dataset and the at least one transformation of the second dataset as a sequence of steps;

allowing a user to select one or more of the sequence of steps for storage;

responsive to user selection of at least one step of the sequence of steps, determining a SQL statement which corresponds to the at least one step; and

storing the determined SQL statement which corresponds to the at least one step for later retrieval.

17. A computer-program product comprising a non-transitory computer-usable medium having computer-readable program code embodied therein, the computer-readable program code adapted to be executed to implement a method comprising:

receiving a data-transformation request in relation to a visualization of a first dataset, wherein the visualization represents columns of data, wherein the data-transformation request expresses at least one transformation of the first dataset without using Structured Query Language (SQL) syntax, wherein the visualization is characterized by a first SQL statement maintained by a computer system, wherein the columns of the visualization are mapped to constituent parts of the first SQL statement in which the columns are referenced;

automatically determining a second SQL statement configured to return a second dataset, wherein the second dataset implements the at least one transformation of the first dataset, the automatically determining comprising;

looking up an identifier of the at least one transformation in a repository;

responsive to a determination that there exists, in the repository for the identifier, an applicable SQL generation rule other than a default rule, automatically building the second SQL statement based, at least in part, on the mapping of the visualization to the constituent parts of the first SQL statement, using SQL syntax specified by the applicable SQL generation rule; and

responsive to a determination that there does not exist, in the repository for the identifier, an applicable SQL generation rule other than the default rule, automatically building the second SQL statement based, at least in part, on the mapping of the visualization to the constituent parts of the first SQL statement, using SQL syntax specified by the default rule, the building comprising including the first SQL statement as a subquery in a clause of the second SQL statement;

causing the second SQL statement to be executed on one or more databases;

receiving the second dataset in response to the causing; and

updating the visualization to reflect the second dataset.