US-12619611-B1 - Systems and methods for generating and executing a query on a column-oriented database

US12619611B1US 12619611 B1US12619611 B1US 12619611B1US-12619611-B1

Abstract

A method for generating and populating a digital file includes modifying case data to restructure the case data from a first data structure to a column-oriented data structure such that each case data record is maintained in the modified case data. The method further includes receiving a request identifying a query template and a digital file template and selecting the query template including a query from the first repository. The method further includes executing the query to select modified case data. The method further includes selecting the digital file template including at least one filter and a digital file layout from the second repository and filtering the selected portion of the modified case data. The method further includes generating the digital file based on the digital file layout of the digital file template and the filtered case data and outputting the digital file.

Inventors

Peter Gassner
Tristram Arthur Liddiard
Peter Alexander Murray
Colin Sham

Assignees

VEEVA SYSTEMS INC.

Dates

Publication Date: 20260505
Application Date: 20241127

Claims (20)

1 . A method for generating and populating a digital file in a case processing and analysis system, wherein the case processing and analysis system comprises a provider computing system including a column-oriented repository for storing modified case data, a first repository for storing query templates, a second repository for storing digital file templates, and a third repository for storing a plurality of case data records including case data, the method comprising: receiving, by a network interface of the provider computing system, a first request to add the case data of the plurality of case data records of the third repository to the column-oriented repository; modifying, by a processing circuit of the provider computing system, the case data to restructure the case data from a first data structure to a column-oriented data structure, wherein the case data is restructured such that each case data record of the plurality of case data records is maintained in the modified case data; storing, by the processing circuit, the modified case data in the column-oriented repository; receiving, by the network interface, a second request identifying a query template and a digital file template; selecting, by the processing circuit, the query template including a query from the first repository; executing, by the processing circuit, the query of the query template on the column-oriented repository to select a portion of the modified case data stored in the column-oriented repository; selecting, by processing circuit, the digital file template including at least one filter and a digital file layout from the second repository; filtering, by the processing circuit, the selected portion of the modified case data based on the at least one filter of the digital file template to generate filtered case data; generating, by the processing circuit, the digital file based on the digital file layout of the digital file template and the filtered case data; and outputting, by the network interface, the digital file.
2 . The method of claim 1 , wherein the portion of the modified case data is a first portion of the modified case data, wherein the filtered case data is first filtered case data, and wherein the method further comprises: executing, by the processing circuit, the query of the query template on the column-oriented repository to generate a second portion of case data based on the first portion of the modified case data; and filtering, by the processing circuit, the second portion of case data based on the at least one filter of the digital file template to generate second filtered case data, and wherein the digital file is generated based on the digital file layout of the digital file template, the first filtered case data, and the second filtered case data.
3 . The method of claim 2 , wherein the modified case data is structured into a plurality of columns, wherein each column of the plurality of columns includes a plurality of values, wherein the query of the query template includes a sum clause, and wherein the second portion of case data is generated by summing each value of the plurality of values of a column of the plurality of columns.
4 . The method of claim 1 , further comprising: receiving, by the network interface, the digital file template; and storing, by the processing circuit, the digital file template in the second repository.
5 . The method of claim 4 , further comprising: outputting, by the network interface circuit, the digital file template to a client computing device to enable display on a user interface, wherein the user interface includes a digital file template page comprising: a filter preference section; and a layout file upload button, wherein the at least one filter of the digital file template is received via the filter preference section; and wherein the digital file layout of the digital file template is received as a layout file in response to a selection of the layout file upload button.
6 . The method of claim 4 , further comprising: outputting, by the network interface, the digital file template to a client computing device to enable display on a user interface, wherein the user interface includes a digital file template page comprising: a filter preference section; and a digital file layout section, wherein the at least one filter of the digital file template is received via the filter preference section, and wherein the digital file layout of the digital file template is received via the digital file layout section.
7 . The method of claim 1 , further comprising: receiving, by the network interface, the query template; and storing, by the processing circuit, the query template in the first repository.
8 . The method of claim 7 , further comprising: outputting, by the processing circuit, the query template to a client computing device to enable display on a user interface, wherein the user interface includes a query template page comprising: a query section, wherein the query of the query template is received via the query section in a Structured Query Language (SQL).
9 . The method of claim 1 , wherein the query template includes metadata of the modified case data, and wherein the method further comprises: organizing, by the processing circuit, the selected portion of the modified case data based on the metadata of the query template.
10 . The method of claim 1 , wherein the second request further identifies a page template, and wherein the method further comprises: selecting, by processing circuit, the page template including a first modular component associated with a second filter and a second modular component associated with a third filter; filtering, by the processing circuit, the selected portion of the modified case data based on the second filter of the first modular component to generate second filtered case data; filtering, by the processing circuit, the selected portion of the modified case data based on the third filter of the second modular component to generate third filtered case data; and outputting, by the network interface, the second filtered case data and the third filtered case data to a client computing device to enable display on a user interface based on the page template, wherein the user interface includes the page comprising the first modular component and the second modular component.
11 . A method for generating and displaying a page of a graphical user interface in a case processing and analysis system, wherein the case processing and analysis system comprises a provider computing system including a column-oriented repository for storing modified case data, a first repository for storing query templates, a second repository for storing page templates, and a third repository for storing a plurality of case data records including case data, the method comprising: receiving, by a network interface of the provider computing system, a first request to add the case data of the plurality of case data records of the third repository to the column-oriented repository; modifying, by a processing circuit of the provider computing system, the case data to restructure the case data from a first data structure to a column-oriented data structure, wherein the case data is restructured such that each case data record of the plurality of case data records is maintained in the modified case data; storing, by the processing circuit, the modified case data in the column-oriented repository; receiving, by the network interface, a second request identifying a query template and a page template; selecting, by the processing circuit, the query template including a query from the first repository; executing, by the processing circuit, the query of the query template on the column-oriented repository to select a portion of the modified case data stored in the column-oriented repository; selecting, by processing circuit, the page template including a first modular component associated with a first filter and a second modular component associated with a second filter; filtering, by the processing circuit, the selected portion of the modified case data based on the first filter of the first modular component to generate first filtered case data; filtering, by the processing circuit, the selected portion of the modified case data based on the second filter of the second modular component to generate second filtered case data; and outputting, by the network interface, the first filtered case data and the second filtered case data to a client computing device to enable display on a user interface based on the page template, wherein the user interface includes the page comprising a data viewer section including at least one of: the first modular component or the second modular component.
12 . The method of claim 11 , wherein the portion of the modified case data is a first portion, and wherein the method further comprises: executing, by the processing circuit, the query of the query template on the column-oriented repository to generate a second portion of case data based on the first portion of the modified case data; and filtering, by the processing circuit, the second portion of case data based on the first filter of the first modular component to generate third filtered case data; and filtering, by the processing circuit, the second portion of case data based on the second filter of the second modular component to generate fourth filtered case data, and wherein the network interface outputs the first filtered case data, the second filtered case data, the third filtered case data, and the fourth filtered case data.
13 . The method of claim 12 , wherein the modified case data is structured into a plurality of columns, wherein each column of the plurality of columns includes a plurality of values, wherein the query of the query template includes a sum clause, and wherein the second portion of case data is generated by summing each value of the plurality of values of a column of the plurality of columns.
14 . The method of claim 11 , wherein the page template includes a first tab comprising the first modular component, and wherein the page template includes a second tab comprising the second modular component, wherein the page of the graphical user interface includes a first selectable option associated with the first tab and a second selectable option associated with the second tab, and wherein: in response to a selection of the first selectable option, the data viewer section includes the first modular component of the first tab; and in response to a selection of the second selectable option, the data viewer section includes the second modular component of the second tab.
15 . The method of claim 11 , wherein the first modular component includes a table type, and wherein the second modular component includes a line chart type, wherein the first modular component is displayed as a table on the data viewer section, and wherein the second modular component is displayed as a line chart on the data viewer section.
16 . The method of claim 11 , wherein the page template includes an arrangement of the first modular component and the second modular component, and wherein the first modular component and the second modular component are arranged on the data viewer section based on the arrangement of the page template.
17 . The method of claim 11 , further comprising: receiving, by the network interface, the query template; and storing, by the processing circuit, the query template in the first repository.
18 . The method of claim 17 , further comprising: outputting, by the processing circuit, the query template to a client computing device to enable display on a user interface, wherein the user interface includes a query template page comprising: a query section, wherein the query of the query template is received via the query section in a Structured Query Language (SQL).
19 . The method of claim 11 , wherein the query template includes metadata of the modified case data, and wherein the method further comprises: organizing, by the processing circuit, the selected portion of the modified case data based on the metadata of the query template.
20 . The method of claim 11 , wherein the second request further identifies a digital file template, and wherein the method further comprises: selecting, by processing circuit, the digital file template including at least one third filter and a digital file layout from the second repository; filtering, by the processing circuit, the selected portion of the modified case data based on the at least one third filter of the digital file template; generating, by the processing circuit, the digital file based on the digital file layout of the digital file template and the filtered portion of the modified case data; and outputting, by the network interface, the digital file.

Description

CROSS-REFERENCE TO RELATED APPLICATIONS This application claims priority to U.S. Provisional Patent Application No. 63/572,425, filed Apr. 1, 2024, which is incorporated herein by reference in its entirety. TECHNICAL FIELD The present disclosure relates to systems and methods for generating and executing a query on a column-oriented database. BACKGROUND Researchers, scientists, industry players, academics, government regulators, and other stakeholders are increasingly in need of efficient and simple ways to generate and execute queries on column-oriented repositories (e.g., databases) to perform data analytics. SUMMARY One embodiment relates to a method for generating and populating a digital file in a case processing and analysis system. The case dataset transmission system includes a provider computing system. The case processing and analysis system includes a provider computing system. The provider computing system includes a column-oriented repository for storing modified case data, a first repository for storing query templates, a second repository for storing digital file templates, and a third repository for storing multiple case data records including case data. The method includes receiving a first request to add the case data of the multiple case data records of the third repository to the column-oriented repository. The method further includes modifying the case data to restructure the case data from a first data structure to a column-oriented data structure. The case data is restructured such that each case data record of the plurality of case data records is maintained in the modified case data. The method further includes storing the modified case data in the column-oriented repository. The method further includes receiving a second request identifying a query template and a digital file template and selecting the query template including a query from the first repository. The method further includes executing the query of the query template on the column-oriented repository to select a portion of the modified case data stored in the column-oriented repository. The method further includes selecting the digital file template including at least one filter and a digital file layout from the second repository and filtering the selected portion of the modified case data based on the at least one filter of the digital file template to generate filtered case data. The method further includes generating the digital file based on the digital file layout of the digital file template and the filtered case data and outputting the digital file. Another embodiment relates to a method for generating and a page of a graphical user interface in a case processing and analysis system. The case dataset transmission system includes a provider computing system. The case processing and analysis system includes a provider computing system. The provider computing system includes a column-oriented repository for storing modified case data, a first repository for storing query templates, a second repository for storing digital file templates, and a third repository for storing multiple case data records including case data. The method includes receiving a first request to add the case data of the multiple case data records of the third repository to the column-oriented repository. The method further includes modifying the case data to restructure the case data from a first data structure to a column-oriented data structure. The case data is restructured such that each case data record of the plurality of case data records is maintained in the modified case data. The method further includes storing the modified case data in the column-oriented repository. The method further includes receiving a second request identifying a query template and a page template and selecting the query template including a query from the first repository. The method further includes executing the query of the query template on the column-oriented repository to select a portion of the modified case data stored in the column-oriented repository. The method further includes the page template including a first modular component associated with a first filter and a second modular component associated with a second filter. The method further includes the selected portion of the modified case data based on the first filter of the first modular component to generate first filtered case data and the selected portion of the modified case data based on the second filter of the second modular component to generate second filtered case data. The method further includes outputting the first filtered case data and the second filtered case data to a client computing device to enable display on a user interface based on the page template. The user interface includes the page comprising a data viewer section including at least one of: the first modular component or the second modular component. This summary is illustrative only and is not intended to be in any