Systems and methods for query acceleration for use in data analytics environments

Abstract

According to one embodiment, systems and methods are described herein for providing query acceleration in a computing environment, such as, for example, a business intelligence environment, a database, a data warehouse, or other type of environment supporting data analysis. A middle tier is provided as a long-term table data storage format, and another acceleration format, i.e., an acceleration table, can be periodically regenerated from the middle tier, and it can be determined whether an accelerated table exists for a dataset table, and if so, the accelerated table is used to process the query.

Description

[Technical field]

[0001] Copyright Notice A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction of the patent document or the patent disclosure as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights.

[0002] Claiming priority This application claims the benefit of priority to U.S. provisional patent application entitled "SYSTEMS AND METHODS FOR BI QUERY ACCELERATION FOR USE IN DATA ANALYTICS ENVIRONMENTS" filed on August 27, 2021 (ser. no. 63 / 237,963), and to U.S. patent application entitled "SYSTEMS AND METHODS FOR QUERY ACCELERATION FOR USE IN DATA ANALYTICS ENVIRONMENTS" filed on August 25, 2022 (ser. no. 17 / 895,665), each of the above applications and the contents thereof are incorporated herein by reference.

[0003] Technical Field The embodiments described herein relate generally to computer data analysis and computer-based methods of providing business intelligence or other data, and in particular to systems and methods that provide query acceleration for use in such environments. [Background technology]

[0004] background Data analytics allows for the computer-based examination of large amounts of data, for example to derive conclusions or other information from the data. For example, business intelligence tools can provide users with business intelligence that describes enterprise data in a form that enables users to make strategic business decisions. Summary of the Invention [Means for solving the problem]

[0005] overview According to one embodiment, systems and methods are described herein for providing query acceleration in a computing environment, such as, for example, a business intelligence environment, a database, a data warehouse, or other type of environment supporting data analysis. A middle tier is provided as a long-term table data storage format, and another acceleration format, i.e., an acceleration table, can be periodically regenerated from the middle tier, and it can be determined whether an accelerated table exists for a dataset table, and if so, the accelerated table is used to process the query. [Brief description of the drawings]

[0006] [Figure 1] FIG. 1 illustrates an example of a data analysis environment according to one embodiment. [Diagram 2] FIG. 2 further illustrates an example of a data analytics environment according to one embodiment. [Diagram 3] FIG. 2 further illustrates an example of a data analytics environment according to one embodiment. [Figure 4] FIG. 2 further illustrates an example of a data analytics environment according to one embodiment. [Diagram 5] FIG. 2 further illustrates an example of a data analytics environment according to one embodiment. [Figure 6] FIG. 1 illustrates the use of the system to transform, analyze, or visualize data, according to one embodiment. [Figure 7] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 8] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 9] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 10] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 11]1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 12] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 13] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 14] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 15] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 16] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 17] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 18] 1A-1D illustrate various examples of user interfaces for use in a data analysis environment, according to one embodiment. [Figure 19] FIG. 1 illustrates the use of query acceleration in a data analytics environment, according to one embodiment. [Figure 20] FIG. 10 further illustrates the use of query acceleration, according to one embodiment. [Figure 21] FIG. 10 further illustrates the use of query acceleration, according to one embodiment. [Figure 22] FIG. 1 illustrates a lifecycle or state diagram of a dataset used in providing query acceleration, according to one embodiment. [Figure 23] FIG. 1 illustrates a query processing including the use of query acceleration, according to one embodiment. [Figure 24] FIG. 10 further illustrates query processing for various types of datasets, including the use of query acceleration, according to one embodiment. [Diagram 25]FIG. 10 further illustrates query processing for various types of datasets, including the use of query acceleration, according to one embodiment. [Figure 26] FIG. 10 further illustrates query processing for various types of datasets, including the use of query acceleration, according to one embodiment. [Figure 27] FIG. 1 illustrates a method for providing query acceleration in a data analytics environment, according to one embodiment. [Figure 28] FIG. 1 further illustrates a method for providing query acceleration in a data analytics environment, according to one embodiment. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0007] Detailed Description Typically, data analytics within an organization involves computer-based exploration of large amounts of data to derive conclusions or other information from the data. For example, business intelligence (BI) tools can provide users with business intelligence that describes enterprise data in a format that enables users to make strategic business decisions.

[0008] Examples of such business intelligence tools / servers include Oracle Business Intelligence Applications (OBIA), Oracle Business Intelligence Enterprise Edition (OBIEE), or Oracle Business Intelligence Server (OBIS), which provide a query, reporting, and analytics server that can interface with databases to support functions such as data mining, analysis, and analytical applications.

[0009] Data analytics is increasingly being delivered within the context of enterprise software application environments, such as Oracle Fusion Applications environments, or within the context of software-as-a-service (SaaS) offerings, or in cloud environments, such as Oracle Analytics Cloud or Oracle Cloud Infrastructure environments, or other types of analytical applications or cloud environments.

[0010] introduction According to one embodiment, a data warehouse environment or component (such as Oracle Autonomous Data Warehouse (ADW), Oracle Autonomous Data Warehouse Cloud (ADWC), or other type of data warehouse environment or component suitable for storing large amounts of data) can provide a central repository for storing data collected by one or more business applications.

[0011] For example, according to one embodiment, a data warehouse environment or component may be provided as a multidimensional database that uses online analytical processing (OLAP) or other techniques to generate business-related data from multiple disparate data sources. An organization can extract such business-related data from one or more vertical and / or horizontal business applications and inject the extracted data into a data warehouse instance associated with the organization.

[0012] Examples of horizontal business applications may include ERP, HCM, CX, SCM, and EPM as described above, providing broad functionality across various enterprise organizations.

[0013] Vertical business applications are typically narrower in scope than horizontal business applications, but provide access to data further up or down the data chain within a defined scope or industry. Examples of vertical business applications might include medical software or banking software for use within a particular organization.

[0014] While other enterprise software products or components, such as Oracle ADWC, can be delivered as one or more of SaaS, platform-as-a-service (PaaS), or hybrid subscriptions, and software vendors are increasingly offering their enterprise software products or components as SaaS or cloud-oriented offerings (such as Oracle Fusion Applications), enterprise users with traditional business intelligence applications and processes are typically faced with the task of extracting data from horizontal and vertical business applications and injecting the extracted data into a data warehouse, a process that can be both time and resource intensive.

[0015] According to one embodiment, the analytical application environment enables a customer (tenant) to develop computer-executable software analytical applications for use with a BI component (e.g., an OBIS environment or other type of BI component suitable for examining large amounts of data sourced from the customer (tenant) itself or from multiple third-party entities).

[0016] As another example, according to one embodiment, the analytical application environment can be used to pre-populate the reporting interface of a data warehouse instance with relevant metadata that describes business-related data objects in the context of various business productivity software applications, for example, to include pre-defined dashboards, key performance indicators (KPIs), or other types of reports.

[0017] Data analysis Generally, data analytics involves the computer-based examination or analysis of large amounts of data in order to derive conclusions or other information from the data, while business intelligence tools (BI) provide an organization's business users with information that describes their enterprise data in a form that allows them to make strategic business decisions.

[0018] Examples of data analysis environments and business intelligence tools / servers include Oracle Business Intelligence Server (OBIS), Oracle Analytics Cloud (OAC), and Fusion Analytics Warehouse (FAW), which support functions such as data mining, analysis, and analytical applications.

[0019] FIG. 1 illustrates an example of a data analysis environment according to one embodiment. The exemplary embodiment shown in Figure 1 is provided for the purpose of illustrating an example of a data analysis environment in which various embodiments described herein may be used. According to other embodiments and examples, the approaches described herein may be used in other types of data analysis, database, or data warehouse environments. The components and processes shown in Figure 1 and further described herein with respect to various other embodiments may be provided as software or program code executable by, for example, a cloud computing system or other suitably programmed computer system.

[0020] As shown in FIG. 1, according to one embodiment, a data analysis environment 100 may be provided by or operate on a computer system having computer hardware (e.g., processor, memory) 101, including one or more software components operating as a control plane 102 and a data plane 104, and providing access to a data warehouse, data warehouse instance 160 (database 161, or other type of data source).

[0021] According to one embodiment, the control plane operates to provide control of a cloud or other software product offered within the context of a SaaS or cloud environment, such as, for example, an Oracle Analytics Cloud environment, or other type of cloud environment. For example, according to one embodiment, the control plane may include a cloud environment having a console interface 110 that allows access by customers (tenants), and / or a provisioning component 111.

[0022] According to one embodiment, the console interface may enable access by customers (tenants) operating a graphical user interface (GUI) and / or a command-line interface (CLI) or other interface, and / or may include an interface used by a provider of a SaaS or cloud environment and its customers (tenants). For example, according to one embodiment, the console interface may provide an interface that allows a customer to provision services for use within its SaaS environment and configure the provisioned services.

[0023] According to one embodiment, a customer (tenant) can request provisioning of a customer schema in a data warehouse. Through a console interface, the customer can also provide a number of attributes associated with the data warehouse instance, including required attributes (e.g., login credentials) and optional attributes (e.g., size, speed, etc.). A provisioning component can then provision the requested data warehouse instance, including the data warehouse customer schema, and populate the data warehouse instance with the appropriate information provided by the customer.

[0024] According to one embodiment, the provisioning component may also be used to update or edit a data warehouse instance or ETL processes running on the data plane, for example, by changing or updating the requested frequency of execution of the ETL processes for a particular customer (tenant).

[0025] According to one embodiment, the data plane may include a data pipeline or processing layer 120 and a data transformation layer 134 that together process operational or transactional data from an organization's enterprise software applications or data environments (e.g., business productivity software applications provisioned in a customer's (tenant's) SaaS environment, etc.). The data pipeline or processing may include various functions to extract transactional data from business applications and databases provisioned in the SaaS environment and load the transformed data into a data warehouse.

[0026] According to one embodiment, the data transformation layer may include data models, such as knowledge models (KMs) or other types of data models that the system uses to transform transactional data received from business applications and corresponding transactional databases provisioned in the SaaS environment into a model format that the data analytics environment can understand. The model format may be provided in any data format suitable for storage in a data warehouse. According to one embodiment, the data plane may also include data and configuration user interfaces, and mapping and configuration databases.

[0027] According to one embodiment, the data plane is responsible for performing extract, transform, and load (ETL) operations, including extracting transactional data from an organization's enterprise software applications and data environments (e.g., business productivity software applications and corresponding transactional databases offered in a SaaS environment), transforming the extracted data into a model format, and loading the transformed data into customer schemas in a data warehouse.

[0028] For example, according to one embodiment, each customer (tenant) of the environment may be associated with its own customer tenant in the data warehouse that is associated with its own customer schema, and may further be provided with read-only access to a data analysis schema that may be updated periodically or on another basis by a data pipeline or process (such as an ETL process).

[0029] According to one embodiment, a data pipeline or process may be scheduled to run at regular intervals (e.g., hourly, daily, weekly) to extract transactional data from an enterprise software application or data environment, such as, for example, a business productivity software application and corresponding transactional database 106 provisioned in the SaaS environment.

[0030] According to one embodiment, an extraction process 108 can extract transactional data, and a data pipeline or extraction process can insert the extracted data into a data staging area, which can serve as a temporary staging area for the extracted data. To ensure the integrity of the extracted data, data quality and data protection components can be used. For example, according to one embodiment, a data quality component can perform validation of the extracted data while the data is temporarily held in the data staging area.

[0031] According to one embodiment, once the extraction process completes the extraction, a transformation process can begin using a data transformation layer to convert the extracted data into a model format that is loaded into a customer schema in the data warehouse.

[0032] According to one embodiment, the data pipeline or process may work in conjunction with a data transformation layer to transform data into a model format. A mapping and configuration database may store metadata and data mappings that define the data models used in data transformation. A data and configuration user interface (UI) facilitates access to and modification of the mapping and configuration database.

[0033] According to one embodiment, the data transformation layer can transform the extracted data into a format suitable for loading into a data warehouse customer schema, for example according to a data model. During transformation, the data transformation can perform dimension generation, fact generation, and aggregation generation as needed. Dimension generation can include generating dimensions or fields for loading into the data warehouse instance.

[0034] According to one embodiment, after transforming the extracted data, the data pipeline or process may execute a warehouse load step 150 to load the transformed data into a customer schema of a data warehouse instance. After the transformed data is loaded into the customer schema, the transformed data may be analyzed and used in a variety of additional business intelligence processes.

[0035] Different customers of the data analytics environment may have different requirements for how data is classified, aggregated, or transformed for the purposes of data analysis, providing business intelligence data, or developing software analytics applications. According to one embodiment, to support such varying requirements, semantic layer 180 may include data that defines a semantic model of customer data, which helps users understand and access that data using commonly understood business terms, and provides custom content to presentation layer 190.

[0036] According to one embodiment, a semantic model can be specified, for example in an Oracle environment, as a BI repository (RPD) file having metadata that specifies logical schemas, physical schemas, physical-to-logical mappings, aggregate table navigation, and / or other components that implement various physical layer, business model and mapping layer, and presentation layer aspects of the semantic model.

[0037] According to one embodiment, customers can make changes to the data source model, such as adding custom facts or dimensions associated with the data stored in the data warehouse instance, to support their specific requirements, and the system can extend the semantic model accordingly.

[0038] According to one embodiment, the presentation layer enables access to the data content, for example, using software analytics applications, user interfaces, dashboards, key performance indicators (KPIs), or other types of reports or interfaces provided by products, such as, for example, Oracle Analytics Cloud, Oracle Analytics for Applications, etc.

[0039] Business Intelligence Server According to one embodiment, query engine 18 (e.g., an OBIS instance) operates in the manner of a federated query engine, responding to analytical queries or requests from clients within the Oracle Analytics Cloud environment, e.g., directed to data stored in a database.

[0040] According to one embodiment, the OBIS instance can push down operations to supported databases according to a query execution plan 56, where a physical query includes database-specific statements that the query engine sends to a database to retrieve data when processing a logical query, while a logical query can include Structured Query Language (SQL) statements received from a client. In this manner, the OBIS instance translates business user queries into the appropriate database-specific query language (e.g., Oracle SQL, SQL Server SQL, DB2 SQL, Essbase MDX, etc.). The query engine (e.g., OBIS) may also support internal execution of SQL operators that cannot be pushed down to the database.

[0041] According to one embodiment, a user / developer can interact with a client computing device 10 that includes computing hardware 11 (e.g., processor, storage, memory), a user interface 12, and an application 14. A query engine or business intelligence server, such as OBIS, typically operates to handle inbound, e.g., SQL, requests against a database model, construct and execute one or more physical database queries, process the data appropriately, and return the data in response to the request.

[0042] To accomplish this, according to one embodiment, a query engine or business intelligence server may include various components or functions, such as a logical or business model or metadata that describes the data available as the subject area of ​​a query, a request generator that takes incoming queries and converts them into physical queries for use with connected data sources, and a navigator that receives incoming queries, navigates the logical model, and generates physical queries that optimally return the data needed for a particular query.

[0043] For example, according to one embodiment, a query engine or business intelligence server can create a simplified star-schema business model for various data sources, allowing users to query the data as if it were from a single source, using a logical model that is mapped to the data in the data warehouse. Information can then be returned to the presentation layer as subject areas according to the mapping rules in the business model layer.

[0044] According to one embodiment, a query engine (e.g., OBIS) can process queries against a database according to a query execution plan, which can include various child (leaf) nodes, e.g., generally referred to herein in various embodiments as RqList below. Execution plan: [[ RqList < <191986> > [for database 0:0,0] D102.c1 as c1 [for database 0:0,0], sum(D102.c2 by [ D102.c1] )as c2 [for database 0:0,0] Child Nodes(RqJoinSpec): < <192970> > [for database 0:0,0] RqJoinNode < <192969> > [] ( RqList < <193062> > [for database 0:0,0] D2.c2 as c1 [for database 0:0,0], D1.c2 as c2 [for database 0:0,0] Child Nodes(RqJoinSpec): < <193065> > [for database 0:0,0] RqJoinNode < <193061> > [] ( RqList < <192414> > [for database 0:0,118] T1000003.Customer_ID as c1 [for database 0:0,118], T1000003.TARGET as c2 [for database 0:0,118] Child Nodes(RqJoinSpec): < <192424> > [for database 0:0,118] RqJoinNode < <192423> > [] [users / administrator / dv_joins / multihub / input::##dataTarget] as T1000003 )as D1 LeftOuterJoin(Eager)< <192381> > On D1.c1 = D2.c1; actual join vectors:

[0000] =

[0000] ( RqList < <192443> > [for database 0:0,0] D104.c1 as c1 [for database 0:0,0], nullifnotunique(D104.c2 by [ D104.c1] )as c2 [for database 0:0,0] Child Nodes(RqJoinSpec): < <192928> > [for database 0:0,0] RqJoinNode < <192927> > [] ( RqList < <192852> > [for database 0:0,118] T1000006.Customer_ID as c1 [for database 0:0,118], T1000006.Customer_City as c2 [for database 0:0,118] Child Nodes(RqJoinSpec): < <192862> > [for database 0:0,118] RqJoinNode < <192861> > [] [users / administrator / dv_joins / my_customers / input::data] as T1000006 )as D104 GroupBy: [ D104.c1] [for database 0:0,0] sort OrderBy: c1, Aggs:[ nullifnotunique(D104.c2 by [ D104.c1] )] [for database 0:0,0] )as D2 )as D102 GroupBy: [ D102.c1] [for database 0:0,0] sort OrderBy: c1 asc, Aggs:[ sum(D102.c2 by [ D102.c1] )] [for database 0:0,0] Within a query execution plan, each execution plan component (RqList) represents a block of queries in the query execution plan and is typically translated into a SELECT statement. An RqList can contain nested child RqLists, similar to how a SELECT statement can select from a nested SELECT statement.

[0045] According to one embodiment, the query engine can interact with different databases and can use data source specific code generators for each of these databases. A common strategy is to send as many SQL statements as possible to the database by sending them as part of the physical query, thereby reducing the amount of information returned to the OBIS server.

[0046] According to one embodiment, during operation, the query engine or business intelligence server can create a query execution plan that can be further optimized, for example to perform aggregations of data necessary to respond to the request. Data can be joined and further calculations can be applied before the results are returned to the calling application, such as via an ODBC interface.

[0047] According to one embodiment, complex multi-pass requests involving multiple data sources may require a query engine or business intelligence server to decompose the query, determine which sources, multi-pass calculations and aggregations can be used, and generate a logical query execution plan that spans multiple databases and physical SQL statements; the results may then be returned and further combined or aggregated by the query engine or business intelligence server.

[0048] FIG. 2 further illustrates an example of a data analysis environment according to one embodiment. 2, according to one embodiment, the provisioning component may also include a provisioning application programming interface (API) 112, a number of workers 115, a metering manager 116, and a data plane API 118, as further described below. The console interface may communicate with the provisioning API by making API calls, for example, when commands, instructions, or other inputs are received at the console interface to provision services within the SaaS environment or to change the configuration of a provisioned service.

[0049] According to one embodiment, a data plane API can communicate with the data plane. For example, according to one embodiment, provisioning and configuration changes directed to services provided by the data plane can be communicated to the data plane via the data plane API.

[0050] According to one embodiment, the metering manager can include various functions for metering the usage of services and services provisioned via the control plane. For example, according to one embodiment, the metering manager can record the usage of processors provisioned via the control plane over time for a particular customer (tenant) for billing purposes. Similarly, the metering manager can record the amount of data warehouse storage space carved out for use by a customer of a SaaS environment for billing purposes.

[0051] According to one embodiment, the data pipeline or processing provided by the data plane may include a monitoring component 122, a data staging component 124, a data quality component 126, and a data projection component 128, as described further below.

[0052] According to one embodiment, the data transformation layer may include a dimension generation component 136, a fact generation component 138, and an aggregation generation component 140, as further described below. The data plane may also include a data and configuration user interface 130, and a mapping and configuration database 132.

[0053] According to one embodiment, the data warehouse may include a default data analysis schema (referred to herein as an analysis warehouse schema according to some embodiments) 162 and, for each customer (tenant) of the system, a customer schema 164.

[0054] According to one embodiment, to support multiple tenants, the system may enable the use of multiple data warehouses or data warehouse instances. For example, according to one embodiment, a first warehouse customer tenant for a first tenant may include a first database instance, a first staging area, and a first data warehouse instance of the multiple data warehouse or data warehouse instances, while a second customer tenant for a second tenant may include a second database instance, a second staging area, and a second data warehouse instance of the multiple data warehouse or data warehouse instances.

[0055] According to one embodiment, based on the data model defined in the mapping and configuration database, the monitoring component can determine dependencies of several different data sets (data sets) to be converted. Based on the determined dependencies, the monitoring component can determine which of the several different data sets needs to be converted to the model format first.

[0056] For example, according to one embodiment, if a first model dataset does not include dependencies on other model datasets, and if a second model dataset includes a dependency on the first model dataset, then the monitoring component can determine to transform the first dataset before the second dataset to accommodate the dependency of the second dataset on the first dataset.

[0057] For example, according to one embodiment, dimensions may include categories of data, such as, for example, "Name," "Address," or "Age." Fact generation involves generating values ​​or "measurements" that the data can take. Facts can be associated with appropriate dimensions in the data warehouse instance. Aggregation generation involves creating data mappings that calculate aggregates of the transformed data against existing data in a customer schema in the data warehouse instance.

[0058] According to one embodiment, if any transformations are performed (as specified by the data model), a data pipeline or process can read the source data, apply the transformations, and then push the data to a data warehouse instance.

[0059] According to one embodiment, data transformations can be expressed in rules and once transformations have occurred, values ​​can be held in an intermediate staging area where data quality and data projection components can validate and verify the integrity of the transformed data before it is uploaded to the customer schema of the data warehouse instance. For example, monitoring can be provided as the extract, transform and load processes run across multiple compute instances or virtual machines. Dependencies can also be maintained during the extract, transform and load processes and the data pipeline or process can accommodate such ordering decisions.

[0060] According to one embodiment, after transforming the extracted data, the data pipeline or process may execute a warehouse load procedure to load the transformed data into a customer schema of a data warehouse instance. After loading the transformed data into the customer schema, the transformed data may be analyzed and used in a variety of additional business intelligence processes.

[0061] FIG. 3 further illustrates an example of a data analysis environment according to one embodiment. As shown in FIG. 3, according to one embodiment, data may be sourced from a customer's (tenant's) enterprise software application or data environment (106), for example, using data pipeline processing, or as custom data 109 derived from one or more customer-specific applications 107, and loaded into the data warehouse instance, in some examples including using object storage 105 for storing the data.

[0062] In an embodiment of an analytics environment such as Oracle Analytics Cloud (OAC), a user can create a dataset that uses tables from different connections and schemas, and the system uses the relationships defined between these tables to create relationships or joins in the dataset.

[0063] According to one embodiment, for each customer (tenant), the system uses a data analysis schema maintained and updated by the system in the system / cloud tenant 114 to pre-populate the customer's data warehouse instance based on an analysis of data in the customer's enterprise application environment and in the customer tenant 117. The data analysis schema maintained by the system thus enables a data pipeline or process to retrieve data from the customer's environment and load it into the customer's data warehouse instance.

[0064] According to one embodiment, the system also provides, for each customer of the environment, a customer schema that is easily modifiable by the customer and allows the customer to supplement and utilize the data in their own data warehouse instance. For each customer, the resulting data warehouse instance acts as a database, the contents of which are partly controlled by the customer and partly controlled by the environment (system).

[0065] For example, according to one embodiment, a data warehouse (e.g., ADW) may include data analysis schemas and customer schemas sourced from enterprise software applications or data environments on a per customer / tenant basis. Data provisioned in a data warehouse tenant (e.g., ADW cloud tenant) is accessible only to that tenant while allowing access to various functions (e.g., ETL-related functions and other functions) of the shared environment.

[0066] According to one embodiment, the system allows for the use of multiple data warehouse instances to support multiple customers / tenants; for example, a first customer tenant may include a first database instance, a first staging area, and a first data warehouse instance, and a second customer tenant may include a second database instance, a second staging area, and a second data warehouse instance.

[0067] According to one embodiment, for a particular customer / tenant, upon extraction of that data, the data pipeline or process can insert the extracted data into the tenant's data staging area, which can serve as a temporary staging area for the extracted data. For example, data quality and data protection components can be used to ensure the integrity of the extracted data by performing validation of the extracted data while the data is temporarily held in the data staging area. Once the extraction process completes the extraction, a data transformation layer can be used to initiate a transformation process to convert the extracted data into a model format for loading into the customer schema in the data warehouse.

[0068] FIG. 4 further illustrates an example of a data analysis environment according to one embodiment. As shown in FIG. 4 , according to one embodiment, the process of extracting data from a customer's (tenant's) enterprise software applications or data environment, for example using the data pipeline process described above or as custom data obtained from one or more customer-specific applications, loading the data into a data warehouse instance, or updating the data in the data warehouse generally involves three broad stages performed by the ETP services 160 or processes including one or more extract services 163, transform services 165, and load / publish services 167 performed by one or more compute instances 170.

[0069] For example, according to one embodiment, a list of view objects for extraction can be sent to an Oracle BI Cloud Connector (BICC) component, e.g., via a REST call. The extracted files can be uploaded to an object storage component, such as an Oracle Storage Service (OSS) component, for storing the data. A transform process retrieves the data files from the object storage component (e.g., OSS) and applies business logic as it loads the data into a target data warehouse (e.g., an ADW database) that is internal to the data pipeline or process and not exposed to the customer (tenant). A load / publish service or process retrieves the data from the ADW database, warehouse, etc., and publishes it to a data warehouse instance that is accessible to the customer (tenant).

[0070] FIG. 5 further illustrates an example of a data analysis environment according to one embodiment. As shown in FIG. 5, which illustrates operation of a multiple tenant (customer) system in accordance with one embodiment, data can be obtained from each of the multiple customer (tenant) enterprise software applications or data environments and loaded into the data warehouse instance, for example, using the data pipeline process described above.

[0071] According to one embodiment, the data pipeline or process maintains a data analysis schema that is periodically updated for each of multiple customers (tenants), e.g., Customer A 180, Customer B 182, by the system following best practices for a particular analytical use case.

[0072] According to one embodiment, for each of multiple customers (e.g., Customer A, Customer B), the system populates the customer's data warehouse instance based on an analysis of data within that customer's enterprise application environment 106A, 106B and within each customer's tenant (e.g., Customer A's tenant 181, Customer B's tenant 183) using data analysis schemas 162A, 162B maintained and updated by the system such that data is obtained from the customer's environment via a data pipeline or process and loaded into the customer's data warehouse instance 160A, 160B.

[0073] According to one embodiment, the data analysis environment also provides, for each of the environment's multiple customers, customer schemas (e.g., customer A schema 164A, customer B schema 164B) that can be easily modified by the customer, allowing the customers to supplement and utilize data in their own data warehouse instance.

[0074] As described above, according to one embodiment, for each of multiple customers of the data analytics environment, the resulting data warehouse instance acts as a database whose contents are partially controlled by the customer and partially controlled by the data analytics environment (system), including appearing to be pre-populated with appropriate data obtained from the enterprise application environment to support various analytical use cases. Once the extraction process 108A, 108B for a particular customer has completed its extraction, a transformation process can be initiated using a data transformation layer to convert the extracted data into a model format that is loaded into the customer schema of the data warehouse.

[0075] According to one embodiment, activation plans 186 can be used to control the operation of data pipelines or processing services for a customer (tenant) for a particular functional area to address the customer's (tenant's) specific needs.

[0076] For example, according to one embodiment, an activation plan may prescribe a number of extract, transform, load (publish) services or steps to be executed in a particular order, at a particular time, and within a particular time frame.

[0077] According to one embodiment, each customer may be associated with a unique activation plan. For example, an activation plan for a first customer A may determine which tables to retrieve from the customer's enterprise software application environment (e.g., a Fusion Applications environment) and may determine the services and how their operations are sequenced. Meanwhile, an activation plan for a second customer B may similarly determine which tables to retrieve from the customer's enterprise software application environment and may determine the services and how their operations are sequenced.

[0078] FIG. 6 illustrates the use of the system to transform, analyze, or visualize data, according to one embodiment.

[0079] 6, according to one embodiment, the systems and methods disclosed herein can be used to provide a data visualization environment 192 that enables insight to a user of the analytical environment regarding analytical artifacts and the relationships between them. The model can then be used to visualize, for example, via a user interface, the relationships between such analytical artifacts as network charts and visualizations of the relationships and lineage between the artifacts (e.g., users, roles, DV projects, datasets, connections, data flows, sequences, ML models, ML scripts).

[0080] According to one embodiment, the client application may be implemented as software or computer readable program code executable by a computer system or processing device and has a user interface, such as a software application user interface or a web browser interface. The client application may obtain or access data via an Internet / HTTP or other type of network connection to the analysis system or, in the example of a cloud environment, via cloud services provided by the environment.

[0081] According to one embodiment, the user interface may include or provide access to various data flow action types that enable self-service text analysis including allowing a user to view data sets and manipulate the user interface to transform, analyze, and visualize data to generate graphs, charts, and other types of data analysis and data flow visualizations, as described in more detail below.

[0082] According to one embodiment, the analytics system is capable of retrieving, receiving, or preparing datasets from one or more data sources, e.g., via one or more data source connections. Examples of types of data that can be transformed, analyzed, or visualized using the systems and methods described herein include HCM, HR, or ERP data, email or text messages, or other free-form or unstructured text data provided in one or more databases, data storage services, or other types of data repositories or data sources.

[0083] For example, according to one embodiment, a request for data analysis or visualization information may be received via a client application and user interface as described above and communicated to an analysis system (via a cloud service in an example cloud environment). The system may retrieve an appropriate data set corresponding to the user / business context for use in generating and returning the requested data analysis or visualization information to the client. For example, the data analysis system may retrieve the data set using a SELECT statement, a logical SQL instruction, or the like.

[0084] According to one embodiment, the system can create a model or dataflow that reflects an understanding of the dataflow or set of input data by applying various algorithmic processes to generate visualizations or other types of useful information associated with the data. The model or dataflow can be further modified within the dataset editor 193 by applying various processes or techniques to the dataflow or set of input data, including, for example, one or more dataflow actions 194, 195 or steps that operate on the dataflow or set of input data. A user can interact with the system via a user interface to control the use of dataflow actions to generate data analysis, data visualization 196, or other types of useful information associated with the data.

[0085] According to one embodiment, a dataset is a self-service data model that users can build for their data visualization and analysis requirements. A dataset contains data source connection information, tables, columns, data enrichment and transformation. Users can use a dataset in multiple workbooks and data flows.

[0086] According to one embodiment, when a user creates and builds a dataset, the user can, for example, select from different types of connections or spreadsheets, create a dataset based on data from multiple tables in a database connection, an Oracle data source, or a local subject area, or create a dataset based on data from tables in different connections and subject areas.

[0087] For example, according to one embodiment, a user can build a dataset that includes tables from an Autonomous Data Warehouse connection, tables from a Spark connection, and tables from a local subject area, can specify joins between the tables, and can transform and enrich columns in the dataset.

[0088] According to one embodiment, additional deliverables, features and operations related to the dataset include, for example:

[0089] Viewing available connections: A dataset uses one or more connections to data sources to access and provide data for analysis and visualization. A user's list of connections includes connections that the user has built and connections that they have permission to access and use.

[0090] Creating a dataset from a connection: When a user creates a dataset, they can add tables, add joins, and enrich the data from one or more data source connections.

[0091] Adding multiple connections to a dataset: A dataset can contain multiple connections. Adding more connections allows the user to access and join all the tables and data required to build the dataset. User can add more connections to a dataset that supports multiple tables.

[0092] Creating Dataset Table Joins: Joins indicate relationships between tables in a dataset. If a user is creating a dataset based on facts and dimensions and joins already exist in the source tables, then joins are automatically created in the dataset. If a user is creating datasets from multiple connections and schemas, then the user can manually specify joins between tables.

[0093] According to one embodiment, a dataflow allows a user to create a dataset by combining, organizing, and integrating data. Dataflow allows a user to organize and integrate data to create a curated dataset that can be visualized by themselves or other users.

[0094] For example, according to one embodiment, a user may use a dataflow to create datasets, combine data from different sources, aggregate data, train machine learning models, and apply predictive machine learning models to data.

[0095] According to one embodiment, the dataset editor described above allows a user to add actions or steps, where each step performs a specific function, such as, for example, adding data, joining tables, merging columns, transforming data, or saving data. Each step is validated as the user adds or modifies the step. Once the dataflow is configured, it can be executed to generate or update a dataset.

[0096] According to one embodiment, users can curate data from a dataset, subject area, or database connection. Users can run dataflows individually or sequentially. Users can include multiple data sources in a dataflow and specify how they are joined. Users can save output data from a dataflow to a dataset or supported database type.

[0097] According to one embodiment, additional artifacts, functions and operations related to data flow include, for example:

[0098] Add Column: Add a custom column to the target dataset. Add Data: Add a data source to the dataflow. For example, if a user is joining two datasets, add both datasets to the dataflow.

[0099] Aggregation: Applying an aggregation function, for example count, sum, average, etc., to create totals for groups.

[0100] Branch: Creating multiple outputs from a dataflow. Filters: Users select only the data that interests them.

[0101] Join: Combining data from multiple data sources using database joins based on common columns.

[0102] Graph Analysis: Performing geospatial analysis such as calculating the distance or number of hops between two vertices. The above are provided as examples, and according to one embodiment, other types of steps can be added to the dataflow to transform data sets or provide data analysis and visualization.

[0103] Analyzing and visualizing the data set According to one embodiment, the system provides functionality that enables a user to generate datasets, analyses, or visualizations for display within a user interface, e.g., to explore datasets or data sourced from multiple data sources.

[0104] 7-18 show various examples of user interfaces for use in a data analysis environment, according to one embodiment.

[0105] The user interfaces and functionality illustrated in FIGS. 7-18 are provided as examples for purposes of illustrating the various features described herein, and alternative examples of user interfaces and functionality may be provided in accordance with various embodiments.

[0106] As shown in FIGS. 7-8, according to one embodiment, a user may access a data analysis environment, for example, to submit an analysis or query on an organization's data.

[0107] For example, according to one embodiment, a user can select from various types of connections to create a dataset based on data from tables such as database connections, Oracle subject areas, Oracle ADW connections, or from spreadsheets, files, or other types of data sources. In this manner, the dataset acts as a self-service data model from which users can build data analyses and visualizations.

[0108] As shown in Figures 9-10, according to one embodiment, the dataset editor can display a list of connections to which the user has access and enable the user to create or edit a dataset containing tables, joins, and / or enriched data. The editor can display the schemas and tables of the data source connections from which the user can drag and drop into the dataset diagram. If the particular connection itself does not provide a list of schemas and tables, the user can use a manual query for the appropriate tables. Once a connection is added, the relevant tables and data can be accessed and joined to build the dataset.

[0109] According to one embodiment, in the Dataset Editor, a join diagram displays the tables and joins in a dataset, as shown in Figures 11-12. Joins specified in a data source can be automatically created between tables in a dataset, for example, by creating joins based on column name matches found between tables.

[0110] According to one embodiment, when a user selects a table, a preview data area displays a sample of the table's data. Join links and icons are displayed to indicate which tables are joined and the type of join used. A user can create joins by dragging and dropping one table onto another, click on a join to view or update its configuration, or click on the type attribute of a column to change the type, for example, from measure to attribute.

[0111] According to one embodiment, the system can generate source-specific optimized queries for the visualization, where the dataset is treated as a data model and only the tables necessary to satisfy the visualization are used in the query.

[0112] By default, the granularity of a dataset is determined by the least granular table. Users can create measurements on any table in the dataset. However, this can result in duplicate measurements on one side of a one-to-many or many-to-many relationship. To address this, according to the embodiment shown in Figure 13, users can maintain granularity by setting a table on one side of the cardinality to maintain that level of detail.

[0113] As shown in FIG. 14, according to one embodiment, dataset tables can be associated with data access settings that determine whether the system loads the table into the cache or whether the table receives its data directly from the data source.

[0114] According to one embodiment, when auto-cache mode is selected for a table, the system loads or reloads the table data into the cache, which improves performance when the table's data is updated from a workbook, etc., and provides reload menu options at the table and dataset level.

[0115] According to one embodiment, when live mode is selected for a table, the system retrieves table data directly from the data source and the source system manages the data source queries for the table. This option is useful when data is stored in a high performance data warehouse such as Oracle ADW and ensures that the latest data is used.

[0116] According to one embodiment, if a dataset uses multiple tables, some tables may use automatic caching and other tables may contain live data, during a reload of multiple tables using the same connection, if reloading data for one table fails, the table currently set to use automatic caching is switched to retrieving data using live mode.

[0117] According to one embodiment, the system allows users to enrich and transform data before it is made available for analysis. When a workbook is created and a dataset is added to the workbook, the system performs column-level profiling on a representative sample of the data. After profiling the data, the user can implement the transformation and enrichment recommendations provided for recognizable columns in the dataset, such as, for example, GPS enrichment of city latitude and longitude, zip code, etc.

[0118] According to one embodiment, data transformation and enrichment changes applied to a dataset impact workbooks and data flows that use that dataset, for example, when a user opens a workbook that shares a dataset, they receive a message indicating that the workbook is using updated or refreshed data.

[0119] According to one embodiment, dataflows provide a means to organize and integrate data to generate curated data sets that users can visualize. For example, users can use dataflows to create data sets, combine data from various sources, aggregate data, train machine learning models, or apply predictive machine learning models to data.

[0120] 15, according to one embodiment, within a dataflow, each step performs a specific function, such as, for example, adding data, joining tables, merging columns, transforming data, or saving data. Once configured, a dataflow can be run to perform operations that generate or update a data set, such as using SQL operators such as BETWEEN, LIKE, IN, conditional expressions, and functions.

[0121] According to one embodiment, data flows can be used to merge data sets, cleanse data, and output results to new data sets. Data flows can be run individually or in sequence. If a data flow in a sequence fails, all changes made in the sequence are rolled back.

[0122] As shown in Figures 16-18, according to one embodiment, visualizations can be displayed within a user interface, for example to explore and add insight into data sets or data sourced from multiple data sources.

[0123] For example, according to one embodiment, a user can create a workbook, add a data set, and drag and drop its columns onto a canvas to create a visualization. The system can automatically generate the visualization based on the contents of the canvas, and one or more visualization types are automatically selected for the user to choose from. For example, if a user adds an income measure to the canvas, the data element is placed in the Value area of ​​the grammar panel and a Tile visualization type is selected. The user can continue to add data elements directly to the canvas to build the visualization.

[0124] According to one embodiment, the system can provide auto-generated data visualizations (auto-generated insights, auto-insights) by suggesting visualizations that are expected to provide the best insights for a particular data set. A user can see an auto-generated summary of insights, such as by hovering over the relevant visualization in the workbook canvas.

[0125] Query Acceleration According to one embodiment, systems and methods are described herein for providing query acceleration in a computing environment, such as, for example, a business intelligence environment, a database, a data warehouse, or other type of environment supporting data analysis. A middle tier is provided as a long-term table data storage format, and another acceleration format, i.e., an acceleration table, can be periodically regenerated from the middle tier, and it can be determined whether an accelerated table exists for a dataset table, and if so, the accelerated table is used to process the query.

[0126] According to one embodiment, data intended for use as tables within the data analysis environment and dataset editor can be obtained from a variety of sources, such as accessing or receiving data from a data source based on an ODBC / JDBC connection, such as a relational database, a SaaS data source, or a cloud service environment, such as Oracle Service Cloud.

[0127] When defining a dataset, a user can add one or more tables from these various sources to form the basis of an analysis or query, generate a data visualization, etc. For example, a user can first select an initial set of tables from the Sales, Customers, and Products data sources and then select additional related tables from these or other sources as required.

[0128] In an environment that does not use query acceleration or accelerated tables for query processing, a general approach to processing queries directed to one or more datasets may include, for example, receiving the original query from a user, accessing the data source, retrieving the dataset, and applying business rules as transformations each time the system processes that query (or functions to send the query to a data source).

[0129] However, this approach typically requires accessing, retrieving and processing large amounts of data, increases resource usage and increases the time it takes to generate data analytics.

[0130] According to one embodiment, when a query acceleration or acceleration table is provided for use with a dataset, the system can apply transformations and enrichments to queries sent to a data source (e.g., an ADW environment) such that the necessary transformations and enrichments are incorporated into the query.

[0131] Information received from data sources can be stored or cached in an interim format, such as in memory, including not only the underlying data but also any transformed / enhanced datasets required for analysis or to answer queries.

[0132] FIG. 19 is a diagram illustrating the use of query acceleration in a data analytics environment, according to one embodiment.

[0133] As shown in FIG. 19, according to one embodiment, a query engine (e.g., a BI instance, e.g., an OBIS instance) includes a data storage framework 210, e.g., an Apache Parquet component, working in conjunction with a cache (memory) 212 to store one or more accelerated tables 214 for accessing or receiving datasets and for responding to analyses and queries against the organization's data.

[0134] According to one embodiment, the system can operate in conjunction with, for example, an RPD data model, as described above. Additionally / alternatively, users can upload other types of data sets into the OAC environment as Excel spreadsheets, CSV files, or other types of data inputs for use in query acceleration.

[0135] The dataset lifecycle (shown, for example, in FIG. 22 and described further below) operates to access or receive datasets provided by users / customers and then cache the datasets in a high performance external query engine or external query accelerator (XQA) that provides access to accelerated tables.

[0136] According to one embodiment using the ADW as a component of a data analysis environment, the ADW component itself provides a query engine that can be used for query acceleration, with data sets stored as accelerated tables in either flash memory or an in-memory representation.

[0137] According to one embodiment, data flows can be used to populate a query accelerator with a dataset as an accelerated table. For example, in an OAC / ADW environment, to indicate that a dataset has been stored as an accelerated table, effectively cached, a user can use a reload function provided within OAC and its dataset editor to have the system access / receive the latest dataset, load the data into the ADW in an accelerated format, and update the metadata associated with it.

[0138] According to one embodiment, the query generation process can automatically modify the request / query from the user and use the accelerated_table instead for queries against that dataset, e.g., to generate data visualizations.

[0139] For example, if an accelerated table is present in cache / memory, the system can send the query to an external query accelerator (e.g. a BI engine) to perform various data transformations and processing steps. If the accelerated table is not present in cache / memory (or cannot be accessed for some reason), the system can fall back to using native tables (e.g. XSANative Tables (Local)), using columnar or file-based query processing, etc.

[0140] In this way, the acceleration process works to push dataset queries to faster engines (using accelerated tables) when available, and fall back to native functionality (using native tables) when necessary. Such fallback situations may arise, for example, due to system failures or timeouts, or other reasons when the external query accelerator is unable to process the query.

[0141] FIG. 20 further illustrates the use of query acceleration, according to one embodiment. As shown in FIG. 20, according to one embodiment, a query engine (e.g., a BI instance, an OBIS instance) is composed of multiple nodes (e.g., an OBIS node), some of which may reference the same tables to respond to analyses or queries.

[0142] According to one embodiment, to provide data access consistency, the system can manage or coordinate the use of those tables by various nodes. For example, tables may need to be manually pinned and unpinned in memory as a distributed least-recently-used (LRU) process. If a particular table is not being used across the cluster, it becomes a candidate for removal, but if any node is using it, it should be kept in cache / memory for now.

[0143] FIG. 21 further illustrates the use of query acceleration, according to one embodiment. As shown in FIG. 21, according to one embodiment and the example shown therein, multiple query engines or BI instances (e.g., a multi-tenant BI environment) share a single endpoint, e.g., a RAPID endpoint.

[0144] For example, as shown in Figure 21, multiple query engines or BI instances 1, 2, 3 share one endpoint A, while another multiple query engines or BI instances 4, 5 share another endpoint B. Sharing may be determined by various factors such as service level and available capacity. In an OAC environment, multiple OAC or BI nodes may be mapped to, for example, a RAPID endpoint.

[0145] According to one embodiment of LRU processing, when a first query engine or BI instance (user) acquires a lock on a particular table, other BI instances (users) may attempt to acquire the lock but fail, and the first BI instance / user can decide to promote the table to memory or release the lock.

[0146] For example, as shown in Figure 21, each access of Load B, Load D, and Load F represents a different set of data that can be loaded into memory. The LRU process daemon lock acts as a global lock that removes the least recently used table or the table that has not been used for the longest time.

[0147] According to one embodiment, advantages of using environments such as OAC / ADW include the availability of additional, e.g., machine learning (ML) libraries provided by those environments. For example, additional machine learning capabilities provided by OAC / ADW can be used to detect anomalies in the data, such as falloff, clustering, regression, etc.

[0148] According to various embodiments, additional features of LRU processing may include: The XSA cache is "global": cohort nodes may hit datasets previously cached by other nodes. LRU processing is independent of the external engine used.

[0149] There is no single LRU manager; instead, management responsibility is distributed across query engine nodes (such as OBIS instances) that share the endpoints. Each BI node or instance runs a daemon thread that periodically attempts to load and unload datasets in response to currently outstanding requests. When a BI node connects to multiple endpoints, it can run multiple daemon threads, one for each endpoint.

[0150] Only one daemon is active at a time. LRU state and concurrency are managed via a set of bookkeeping tables (maintained, for example, in MySQL®):

[0151] Request Queue - A list of pending requests and their arrival times. The daemon processes this table starting with the oldest requests.

[0152] In_memory_tables - A list of tables currently in memory, along with their in-memory size and last accessed time.

[0153] Daemon lock - The currently single running daemon and its last alive time. Only one entry (i.e. a daemon) is allowed at any given time. The last alive time is used to keep track of daemons that may have crashed, etc.

[0154] The load is idempotent: if the table is already loaded in memory, nothing is done. If a user wants to force a reload of a table (e.g. due to data changes), they can explicitly unload and (re)load the table.

[0155] If multiple BI nodes request the same table, those multiple requests are queued in the request queue table. Because of the above, only one daemon can perform the loading and unloading. This ensures that there is no contention during LRU management.

[0156] FIG. 22 illustrates a lifecycle or state diagram of a dataset used in providing query acceleration, according to one embodiment.

[0157] As shown in FIG. 22, according to one embodiment, a dataset lifecycle or state diagram uses a layered approach to describe the various form factors that a single dataset goes through during the processing of a query.

[0158] The initial state (denoted here by a circle) represents the state when no dataset exists, and the labels on the arcs represent operations, processes, or modules that act on the dataset, for example when a dataflow creates a new dataset in Parquet format from several other datasets.

[0159] According to one embodiment, as shown in FIG. The top layers (220, 222, 224) act as a "standard" data format for the dataset, with the specific format varying depending on how the dataset is used and queried (file-based, query-based, database table, etc.).

[0160] A data storage framework 210 (e.g., Parquet) acts as a middle tier that provides a long-term tabular data storage format for the dataset. A component such as Parquet may have an advantage over other formats in that it is relatively stable.

[0161] The bottom layer (226, 228, 230) holds one or more accelerated formats of the dataset. For acceleration purposes, the accelerated formats are deemed too unreliable or treated as untrustworthy to be used as long-term formats and are therefore periodically regenerated from the middle layer, such as during disaster recovery, when the XSA native format changes, or when the external acceleration engine is switched.

[0162] According to one embodiment, primary query acceleration may be provided via an external query or other type of data analysis query engine.

[0163] The XSA native format is provided as a fallback to be used if the primary query engine fails, e.g. for connectivity, stability, maintenance, or performance reasons.

[0164] XSA Native (Local) is provided for use when XSA Native (Object Storage) files are not ready. To provide concurrency, a data synchronization component (DataSync) can be provided that converts and stores XSA Native files in object storage.

[0165] According to various embodiments, the LRU process may behave differently for different external queries or other types of data analytics engines: for example, for an in-memory analytics engine such as Oracle RAPID, eviction may be done only from in memory, while for the InnoDB engine, eviction may be stored on disk for MySQL, while MonetDB may remove the table from the catalog entirely.

[0166] 23-26 illustrate query flows for various types of datasets used in providing query acceleration, according to one embodiment.

[0167] As shown in FIG. 23, according to one embodiment, when processing a dataset query, the system is adapted to first determine (240) whether a live mode (LIVE) or an automatic caching mode (AUTO / EXTRACTED) is selected for the dataset table.

[0168] As shown in FIG. 24, according to one embodiment, if the mode is determined to be LIVE, the system may determine if the dataset is enriched (242), and if so, null out all enrichment columns (244), apply transformations (246), apply output expressions (248), and process the query (250).

[0169] 25, according to one embodiment, if the mode is determined to be AUTO / EXTRACTED, it may be determined whether an accelerated table exists (252). If so, the accelerated table may be used (254) and depending on whether the dataset has been enriched (256), the system may apply transformations (258) and / or output expressions (260) to process the query.

[0170] As shown in Figure 26, according to one embodiment, if the system determines that an accelerated table does not exist, it can fall back to using XSA native. It is determined (262) whether a local XSA native exists, and if so, the query is processed.

[0171] According to one embodiment, if the system determines that a local XSA native does not exist, it can determine (264) whether a remote (object storage) XSA native exists, and if so, the (remote) XSA native is downloaded (266) and the query is processed.

[0172] According to one embodiment, if the system determines that remote (object storage) XSA Native does not exist, it can determine whether the dataset is enriched 268. If the dataset is enriched, a prepared format (e.g., prep_parquet) is downloaded 270 and the system can apply the output formula 272 and convert to (local) XSA Native 274 so that the query can be processed.

[0173] According to one embodiment, if the system determines that the dataset is not enriched, it can determine whether the source format (e.g., src-parquet) is present (276). If present, src-parquet is downloaded (278) and the system can apply transformations (280) and / or output expressions, convert to (local) XSA native, and process the query.

[0174] According to one embodiment, if the system determines that the source format (e.g., src-parquet) is not present (282), then depending on the mode (e.g., AUTO / EXTRACTED), the system reads from the CSV if file-based, from the connection if external source (284), or reads from the CSV if file-based (286), respectively, and provides an error if external source, and then applies transformations (288) and / or output expressions, converts to (local) XSA native, and can process the query.

[0175] The examples shown and described in Figures 23-26 are provided for the purpose of illustrating example dataset lifecycles and query flows. According to other embodiments and examples, other types of dataset lifecycles and query flows can be supported using the approaches described herein.

[0176] FIG. 27 illustrates a method for providing query acceleration in a data analytics environment, according to one embodiment.

[0177] As shown in FIG. 27, according to one embodiment, in step 302, an analytical environment is provided where a user can create a dataset using tables from various connections, and the system uses the relationships defined between the tables to create joins within the dataset for use in providing data analysis or visualization.

[0178] In step 304, a middle tier (e.g., Parquet) is provided as a long-term table data storage format, and another acceleration format, i.e., acceleration tables, can be periodically regenerated from the middle tier.

[0179] If, in step 306, it is determined that the current mode is AUTO / EXTRACTED, then a determination can be made as to whether an accelerated table exists.

[0180] In step 308, if an accelerated table exists, the accelerated table can be used and depending on whether the dataset is enriched, the system can apply transformations and / or output expressions to process the query.

[0181] FIG. 28 further illustrates a method for providing query acceleration in a data analytics environment, according to one embodiment.

[0182] As shown in FIG. 28, according to one embodiment, if in step 312 the system determines that an acceleration table does not exist, it can fall back to using XSA native, determining whether a local XSA native exists, and if so, processing the query.

[0183] If, in step 314, the system determines that a local XSA native does not exist, then it can determine whether a remote (object storage) XSA native exists, and if so, the (remote) XSA native is downloaded and the query is processed.

[0184] If, in step 316, the system determines that remote (object storage) XSA native does not exist, then it can determine whether the dataset has been enriched, and if the dataset has been enriched, a prepared format (e.g., prep_parquet) is downloaded and the system can apply the output formula and convert it to (local) XSA native to process the query.

[0185] In step 318, if the system determines that the source format (e.g., src-parquet) is not present, then depending on the mode (e.g., AUTO / EXTRACTED), the system can read, e.g., from CSV if file-based or from the connection if external source, respectively, then apply transformations and / or output expressions, convert to (local) XSA native, and process the query.

[0186] According to various embodiments, the teachings herein may be conveniently implemented using one or more conventional general-purpose or special-purpose computers, computing devices, machines, or microprocessors, including one or more processors, memory, and / or computer-readable storage media programmed according to the teachings of the present disclosure. As will be apparent to those skilled in the software art, appropriate software coding may be readily produced by skilled programmers based on the teachings of the present disclosure.

[0187] In some embodiments, the teachings herein may include a computer program product that is a non-transitory computer-readable storage medium(s) having stored thereon instructions that can be used to program a computer to perform any of the processes of the present teachings. Examples of such storage media include, but are not limited to, hard disk drives, hard disks, hard drives, fixed disks or other electromechanical data storage devices, floppy disks, optical disks, DVDs, CD-ROMs, microdrives, and magneto-optical disks, ROMs, RAMs, EPROMs, EEPROMs, DRAMs, VRAMs, flash memory devices, magnetic or optical cards, nanosystems, or other types of storage media or devices suitable for non-transitory storage of instructions and / or data.

[0188] The foregoing description has been provided for purposes of illustration and description. It is not intended to be exhaustive or to limit the scope of protection to the precise form disclosed. Many modifications and variations will be apparent to those skilled in the art.

[0189] For example, although some of the examples provided herein illustrate operation of an analytics application environment with an enterprise software application or data environment (e.g., such as an Oracle Fusion Applications environment) or within the context of a Software as a Service (SaaS) or cloud environment (such as an Oracle Analytics Cloud or Oracle Cloud Infrastructure environment), according to various embodiments, the systems and methods described herein may be used with other types of enterprise software application or data environments, cloud environments, cloud services, cloud computing, or other computing environments.

[0190] The embodiments have been chosen and described in order to best explain the principles of the present teachings and their practical application, so that others skilled in the art can appreciate various embodiments with various modifications suited to the particular uses contemplated. It is intended that the scope be defined by the following claims and their equivalents.

Claims

1. 1. A system for providing query acceleration in a computing environment supporting data analytics, comprising: a computer having a processor and memory; and a data analysis environment operating on the computer, the data analysis environment including a server providing access to data available for queries and receiving and processing incoming queries; The data associated with the dataset is Stored in the middle tier as a long-term table data storage format, as one or more accelerated forms or tables that are periodically regenerated from the intermediate layer; the system provides query acceleration displayed in graphical format in connection with use of reports, analyses, projects, and / or dashboards used to explore a dataset, including use of a dataset lifecycle and one or more query flows; In response to a query, a system is configured to determine whether an accelerated table exists for a dataset table, and if so, use the accelerated table to process the query.

2. The system of claim 1 , wherein after determining that an accelerated table does not exist, the system determines whether a local native table exists, and if so, the query is processed.

3. 3. The system of claim 2, wherein after determining that a local native table does not exist, the system determines whether a remote native table exists, and if so, the remote native table is downloaded and the query is processed.

4. 4. The system of claim 3, wherein after determining that no remote native tables exist, the system determines whether the dataset is enriched, and if so, a prepared form is downloaded, an output expression is applied, and the dataset is converted to a remote native table to process the query.

5. The system according to any one of claims 1 to 4, wherein the system is provided in a cloud environment.

6. 1. A method for providing query acceleration in a computing environment supporting data analytics, comprising: providing a data analysis environment operating on a computer having a processor and memory, the data analysis environment including a server for providing the computer with access to data available for queries and for receiving and processing incoming queries; The data associated with the dataset is In the middle tier as a long-term table data storage format, and storing as one or more accelerated forms or tables that are periodically regenerated from the intermediate tier; 1. A method comprising: performing acceleration of queries displayed in a graphical format in association with use of reports, analyses, projects, and / or dashboards used to explore a dataset, the acceleration including use of a dataset lifecycle and one or more query flows; and in response to a query, determining whether an accelerated table exists for a dataset table, and if so, using the accelerated table to process the query.

7. 7. The method of claim 6, wherein after it is determined that no accelerated table exists, it is determined whether a local native table exists, and if so, the query is processed.

8. 8. The method of claim 7, wherein after it is determined that a local native table does not exist, it is determined whether a remote native table exists, and if so, the remote native table is downloaded and the query is processed.

9. 9. The method of claim 8, further comprising: after determining that no remote native tables exist, determining whether the dataset is enriched, and if so, downloading a prepared form, applying an output expression, and converting the dataset into a remote native table to process the query.

10. The method according to any one of claims 6 to 9, wherein the method is provided in a cloud environment.

11. A computer readable program having instructions that, when read and executed by a computer including one or more processors, cause the computer to: providing a data analysis environment operating on said computer, the data analysis environment including a server providing access to data available for queries and receiving and processing incoming queries; The data associated with the dataset is In the middle tier as a long-term table data storage format, and storing as one or more accelerated forms or tables that are periodically regenerated from the intermediate tier; 1. A computer readable program that causes a computer to execute a method comprising: performing acceleration of queries displayed in a graphical format in connection with use of reports, analyses, projects, and / or dashboards used to explore a dataset, including use of a dataset lifecycle and one or more query flows; and in response to a query, determining whether an accelerated table exists for a dataset table, and if so, using the accelerated table to process the query.

12. 12. The computer readable program of claim 11, wherein after it is determined that no accelerated table exists, it is determined whether a local native table exists, and if so, the query is processed.

13. 13. The computer readable program of claim 12, wherein after it is determined that a local native table does not exist, it is determined whether a remote native table exists, and if so, the remote native table is downloaded and the query is processed.

14. 14. The computer readable program of claim 13, wherein after determining that no remote native table exists, it is determined whether the dataset is enriched, and if so, a prepared form is downloaded, an output expression is applied, and the dataset is converted into a remote native table to process the query.

15. The computer readable program of any one of claims 11 to 14, wherein the method is provided in a cloud environment.

Images