Systems and methods for automated generation of BI models using data introspection and curation

Abstract

Systems and methods for automatic generation of business intelligence (BI) data models using data introspection and curation, such as may be used in an enterprise resource planning (ERP) or other enterprise computing or data analytics environment.SOLUTION: The method derives a target BI data model using a combination of manually curated artifacts and automatic generation of a model of the source data environment via data introspection, evaluates, in a pipeline generator framework, dimensions of transaction types, degenerate attributes, and application metrics, and uses the output of this process to create an output target model and a pipeline or load plan.EFFECT: To provide technical improvement in terms of construction of a new target area or BI data model in a short period of time.SELECTED DRAWING: Figure 17

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 anyone copying or reproducing the patent document or patent disclosure as it appears in the Patent and Trademark Office file or records, but otherwise reserves all copyright rights whatsoever.

[0002] Priority claim: This application is a continuation of U.S. Provisional Patent Application No. 62 / 979,269, filed February 20, 2020, entitled "SYSTEM AND METHOD FOR AUTOMATIC GENERATION OF BI MODELS USING DATA INTROSPECTION AND CURATION," and the related U.S. Provisional Patent Application No. 2020 / 2022 / 269, filed February 20, 2020, entitled "SYSTEM AND METHOD FOR AUTOMATIC GENERATION OF BI MODELS USING DATA INTROSPECTION AND CURATION." No. 16 / 868,081, filed March 6, 2000, entitled "SYSTEM AND METHOD FOR CUSTOMIZATION IN AN ANALYTIC APPLICATIONS ENVIRONMENT," The benefit of priority to the patent applications is claimed, each of which is incorporated herein by reference.

[0003] Technical fields: The embodiments described herein relate generally to computer data analytics, business intelligence (BI), and enterprise resource planning (ERP) or other enterprise computing environments, and more particularly to systems and methods for the automated generation of BI data models for use in such environments using data introspection and curation. [Background technology]

[0004] background: Data analytics allows for the examination of large amounts of data to derive conclusions or other information from the data, while business intelligence (BI) tools provide business users with information that describes the data in a format that enables them to make strategic business decisions.

[0005] There is growing interest in developing software applications that leverage the use of data analytics in the context of an organization's enterprise resource planning (ERP) or other enterprise computing environments, or in the context of software-as-a-service (SaaS) or cloud environments. However, traditional approaches to preparing BI data models do not work well when dealing with the complex schemas used in modern enterprise computing environments. Summary of the Invention [Means for solving the problem]

[0006] overview: According to one embodiment, a system and method are described herein for the automated generation of business intelligence (BI) data models using data introspection and curation, such as may be used in, for example, enterprise resource planning (ERP) or other enterprise computing or data analytics environments. The proposed approach derives a target BI data model using a combination of manually curated artifacts and automated generation of a model of the source data environment via data introspection. For example, a pipeline generator framework can evaluate transaction type dimensions, reduction attributes, and application measures, and use the output of this process to create an output target model and pipeline or load plan. The systems and methods described herein provide a technological advancement in building new subject areas or BI data models in a much shorter timeframe. [Brief explanation of the drawings]

[0007] [Figure 1] FIG. 1 illustrates a system for providing an analytical application environment according to one embodiment. [Figure 2] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 3] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 4] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 5] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 6] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 7] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 8] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 9] FIG. 1 further illustrates a system for providing an analytical application environment, according to one embodiment. [Figure 10]FIG. 1 illustrates a flowchart of a method for providing an analytical application environment according to one embodiment. [Figure 11] FIG. 1 illustrates an analytical application environment that allows for extensibility and customization, according to one embodiment. [Figure 12] FIG. 1 illustrates a self-service data model, according to one embodiment. [Figure 13] FIG. 1 illustrates a curated data model according to one embodiment. [Figure 14] FIG. 1 illustrates a system for automatic generation of BI data models using data introspection and curation, according to one embodiment. [Figure 15] FIG. 1 further illustrates a system for automatic generation of BI data models using data introspection and curation, according to one embodiment. [Figure 16] FIG. 1 illustrates an exemplary pipeline generator framework for use in the automated generation of BI data models, according to one embodiment. [Figure 17] FIG. 1 illustrates an exemplary flowchart of a process for use in the automated generation of a BI data model, according to one embodiment. [Figure 18] FIG. 10 further illustrates an exemplary flowchart of a process for use in the automated generation of a BI data model, according to one embodiment. [Figure 19] FIG. 10 illustrates an exemplary list of transaction types, according to one embodiment. [Figure 20] FIG. 1 illustrates an exemplary transaction column list according to one embodiment. [Figure 21] FIG. 1 illustrates an exemplary dimensional-to-logical dimension map according to one embodiment. [Figure 22] FIG. 2 illustrates an exemplary physical-to-logical attribute map according to one embodiment. [Figure 23] FIG. 2 illustrates an exemplary physical-to-logical scale map according to one embodiment. [Figure 24]FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 25] FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 26] FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 27] FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 28] FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 29] FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 30] FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 31] FIG. 1 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment. [Figure 32] FIG. 1 illustrates a flowchart of a method for providing automated generation of BI data models using data introspection and curation, according to one embodiment. DETAILED DESCRIPTION OF THE INVENTION

[0008] Detailed Description: As noted above, within an organization, data analytics enables the computer-based examination or analysis of large amounts of data to derive conclusions or other information from the data, while business intelligence tools provide an organization's business users with information describing enterprise data in a format that enables them to make strategic business decisions.

[0009] There is growing interest in developing software applications that leverage the use of data analytics within the context of an organization's enterprise software application or data environment (e.g., an Oracle Fusion Applications environment or other type of enterprise software application or data environment), or within the context of a Software as a Service (SaaS) or cloud environment (e.g., an Oracle Analytics Cloud or Oracle Cloud Infrastructure environment or other type of cloud environment).

[0010] According to one embodiment, the analytic application environment enables data analytics in the context of an organization's enterprise software application or data environment, or in the context of a software-as-a-service or other type of cloud environment, and supports the development of computer-executable software analytic applications.

[0011] According to one embodiment, a data pipeline or process, such as an extract-transform-load process, may operate according to an analytical application schema adapted to address a particular analytical use case or best practice to receive data from a customer's (tenant's) enterprise software application or data environment for loading into a data warehouse instance.

[0012] According to one embodiment, each customer (tenant) can be further associated with a customer tenancy and a customer schema. The data warehouse instance and database tables are populated with data received from the enterprise software applications or data environment defined by a combination of analytical application schemas and customer schemas.

[0013] According to one embodiment, a technical advantage of the described system and method is that by using a system-wide or shared analytical application schema or data model maintained in an analytical application environment (cloud) tenancy, together with tenant-specific customer schemas maintained in customer tenancies, each customer's (tenant's) data warehouse instance or database table can be automatically or periodically (e.g., hourly / daily / weekly, etc.) populated with or associated with live data (live tables) received from enterprise software applications or data environments, reflecting best practices for a particular analytical use case. Examples of such analytical use cases can include enterprise resource planning (ERP), human capital management (HCM), customer experience (CX), supply chain management (SCM), enterprise performance management (EPM), or other types of analytical use cases. The populated data warehouse instance or database tables can then be used to create computer-executable software analytical applications or to determine data analytics or other information associated with the data.

[0014] According to one embodiment, a computer-executable software analytics application may be associated with a data pipeline or process, such as an extract-transform-load (ETL) process or an extract-load-transform (ELT) process, maintained by a data integration component, such as an Oracle Data Integrator (ODI) environment or other type of data integration component.

[0015] According to one embodiment, the analytical application environment can operate in conjunction with a data warehouse environment or component, such as, for example, Oracle Autonomous Data Warehouse (ADW), Oracle Autonomous Data Warehouse Cloud (ADWC), or other types of data warehouse environments or components adapted to store large amounts of data, which may be ingested via a star schema sourced from an enterprise software application or data environment, such as, for example, Oracle Fusion Applications, or other types of enterprise software application or data environments. Data available to each customer (tenant) of the analytical application environment can be provisioned in, for example, an ADWC tenancy associated with and accessible only to that customer (tenant), while providing access to other features of the shared infrastructure.

[0016] For example, according to one embodiment, the analytical application environment may include a data pipeline or process layer that enables customers (tenants) to ingest data extracted from the Oracle Fusion application environment and load it into a data warehouse instance within their ADWC tenancy, including support for multiple data warehouse schemas, data extract and target schemas, and features such as monitoring of data pipeline or process stages, coupled to a shared data pipeline or process infrastructure that provides a common transformation map or repository.

[0017] preface According to one embodiment, a data warehouse environment or component, such as, for example, an Oracle Autonomous Data Warehouse (ADW), an Oracle Autonomous Data Warehouse Cloud (ADWC), or any other type of data warehouse environment or component adapted to store large amounts of data, can provide a central repository for storage of data collected by one or more business applications.

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

[0019] Examples of horizontal business applications include the above-mentioned ERP, HCM, CX, SCM, and EPM, which can provide a wide range of functionality across various corporate organizations.

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

[0021] As software vendors increasingly offer enterprise software products or components as SaaS or cloud-oriented offerings, such as Oracle Fusion Applications, while other enterprise software products or components, such as Oracle ADWC, may be offered as one or more of SaaS, Platform as a Service (PaaS), or hybrid subscriptions, enterprise users of traditional business intelligence (BI) applications and processes are generally faced with the task of extracting data from horizontal and vertical business applications and introducing the extracted data into a data warehouse, a process that can be time-consuming and resource-consuming.

[0022] According to one embodiment, the analytical application environment enables customers (tenants) to develop computer-executable software analytical applications for use with a BI component, such as, for example, an Oracle Business Intelligence Applications (OBIA) environment, or other type of BI component adapted to examine large amounts of data supplied by the customers (tenants) themselves or from multiple third-party entities.

[0023] For example, according to one embodiment, the analytical application environment, when used with a SaaS business productivity software product suite that includes a data warehouse component, can be used to populate the data warehouse component with data from the business productivity software applications of the suite. Pre-defined data integration flows can automate the ETL processing of data between the business productivity software applications and the data warehouse, which might otherwise be performed traditionally or manually by users of those services.

[0024] As another example, according to one embodiment, the analytical application environment may be pre-configured with a database schema for storing integrated data distributed across various business productivity software applications of a SaaS product suite. Pre-configured database schemas can be used to provide uniformity across productivity software applications and corresponding transactional databases offered in a SaaS product suite, while allowing users to avoid the process of manually designing, tuning, and modeling a provided data warehouse.

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

[0026] Analytical Application Environment FIG. 1 illustrates a system for providing an analytical application environment according to one embodiment.

[0027] As shown in FIG. 1 , according to one embodiment, an analytical application environment 100 can be provided by or can operate on a computer system having computer hardware (e.g., processor, memory) 101 and including one or more software components operating as a control plane 102 and a data plane 104 to provide access to a data warehouse or data warehouse instance 160.

[0028] According to one embodiment, the components and processes illustrated in FIG. 1 and further described herein with respect to various other embodiments may be provided as software or program code executable by a computer system or other type of processing device.

[0029] For example, according to one embodiment, the components and processes described herein may be provided by a cloud computing system or other suitably programmed computer system.

[0030] According to one embodiment, the control plane operates to provide control over cloud or other software products offered in the context of a SaaS or cloud environment, such as, for example, an Oracle Analytics Cloud or Oracle Cloud Infrastructure environment, or other type of cloud environment.

[0031] For example, according to one embodiment, the control plane may include a cloud environment having a console interface 110 and / or provisioning components 111 that allow access by a client computing device 10 having device hardware 12, management applications 14, and user interfaces 16 under the control of a customer (tenant) 20.

[0032] According to one embodiment, the console interface may allow 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 for use by a SaaS or cloud environment provider and its customers (tenants).

[0033] For example, according to one embodiment, the console interface may provision services for a customer to use within the SaaS environment and manage those provisioned services. An interface may be provided that allows the service to be configured.

[0034] According to one embodiment, the provisioning component may include various functions for provisioning the service specified by the provisioning command.

[0035] For example, according to one embodiment, the provisioning component can be accessed and utilized by customers (tenants) via a console interface to purchase one or more of a suite of business productivity software applications along with a data warehouse instance for use with those software applications.

[0036] According to one embodiment, a customer (tenant) can request provisioning of a customer schema 164 in a data warehouse. The customer can also provide several attributes associated with the data warehouse instance through a console interface, including required attributes (e.g., login credentials) and optional attributes (e.g., size or velocity). The 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.

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

[0038] According to one embodiment, the provisioning component may also include a provisioning application programming interface (API) 112, several workers 115, a metering manager 116, and a data plane API 118, which are further described below. The console interface may communicate with the provisioning API, for example, by making API calls when commands, instructions, or other inputs are received at the console interface, to provision services within the SaaS environment or to make configuration changes to provisioned services.

[0039] According to one embodiment, a data plane API can communicate with the data plane.

[0040] 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 a data plane API.

[0041] According to one embodiment, the metering manager may include various functions for metering services provisioned via the control plane and usage of the services.

[0042] 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 partitioned data warehouse storage space for use by a customer of a SaaS environment for billing purposes.

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

[0044] According to one embodiment, the data transformation layer may include a data model, such as a knowledge model (KM) or other type of data model, that the system uses to transform transaction data received from business applications and corresponding transaction databases provisioned in the SaaS environment into a model format understood by the analytical application environment. This model format can be provided in any data format suitable for storage in a data warehouse.

[0045] According to one embodiment, the data pipeline or process 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, which are further described below.

[0046] 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, which are further described below. The data plane may also include a data and configuration user interface 130 and a mapping and configuration database 132.

[0047] According to one embodiment, the data warehouse includes a default analytics application schema (referred to herein, according to some embodiments, as an analytics warehouse schema) 162 and may include the customer schemas described above for each customer (tenant) of the system.

[0048] According to one embodiment, the data plane is responsible for performing extract, transform, and load (ETL) operations that include extracting transactional data from an organization's enterprise software applications or data environment, such as 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.

[0049] For example, according to one embodiment, each customer (tenant) of the environment can be associated with its own customer tenancy in the data warehouse associated with its own customer schema, and can further have read-only access to analytical application schemas that can be updated periodically or at other paces by a data pipeline or process, e.g., an ETL process.

[0050] According to one embodiment, to support multiple tenants, the system may enable the use of multiple data warehouses or data warehouse instances.

[0051] For example, according to one embodiment, a first warehouse customer tenancy for a first tenant may include a first database instance, a first staging area, and a first data warehouse instance of a plurality of data warehouses or data warehouse instances, while a second customer tenancy for a second tenant may include a second database instance, a second staging area, and a second data warehouse instance of a plurality of data warehouses or data warehouse instances.

[0052] According to one embodiment, a data pipeline or process can be scheduled to run at 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 a SaaS environment.

[0053] According to one embodiment, an extraction process 108 can extract transactional data, and once the extraction is performed, a data pipeline or process can insert the extracted data into a data staging area, which can serve as a temporary staging area for the extracted data. Data quality and data protection components can be used to ensure the integrity of the extracted data.

[0054] For example, according to one embodiment, a data quality component can perform validation on the extracted data while the data is temporarily held in a data staging area.

[0055] According to one embodiment, once the extraction process completes its extraction, a transformation process can be initiated using a data transformation layer to convert the extracted data into a model format and load it into a customer schema in a data warehouse.

[0056] As noted above, according to one embodiment, a data pipeline or process can operate in combination with a data transformation layer to transform data into a model format. A mapping and configuration database can store metadata and data mappings that define the data model used by the data transformation. A data and configuration user interface (UI) can facilitate access to and modification of the mapping and configuration database.

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

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

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

[0060] For example, according to one embodiment, a dimension may include categories of data, such as "name," "address," or "age." Fact generation involves generating values ​​or "measures" that data can take. Facts are associated with appropriate dimensions in the data warehouse instance. Aggregation generation involves creating data mappings that compute aggregates of transformed data for existing data in the customer schema 164 of the data warehouse instance.

[0061] According to one embodiment, once any transformations are implemented (as defined by the data model), a data pipeline or process can read the source data, apply the transformations, and then push the data to the data warehouse instance.

[0062] According to one embodiment, data transformations can be expressed as rules, and once transformed, values ​​can be held in a staging area where data quality and data projection components can inspect and verify the integrity of the transformed data before it is uploaded to customer schemas in the data warehouse instance. Monitoring can be provided, for example, as an extract-transform-load process runs on several compute instances or virtual machines. Dependencies can be maintained during the extract-transform-load process, and the data pipeline or process can address such ordering decisions.

[0063] According to one embodiment, after transforming the extracted data, the data pipeline or process may execute a warehouse load procedure 150 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 can be analyzed and used in various further business intelligence processes.

[0064] Horizontally and vertically integrated business software applications are generally oriented towards capturing data in real time, as they are typically used in daily workflows to store data in transactional databases, meaning that typically only the most recent data is stored in such databases.

[0065] For example, if an employee moves offices, an HCM application may update the record associated with that employee, but such an HCM application would not typically maintain a record of each office that the employee worked in during their tenure with the company. Thus, a BI-related query attempting to determine employee mobility within the company would not have sufficient records in the transactional database to complete such a query.

[0066] According to one embodiment, by storing current data as well as historical data generated by horizontally and vertically integrated business software applications in a context that is easily understandable by BI applications, a data warehouse instance populated using the above techniques provides resources for BI applications to process such queries using interfaces provided, for example, by business productivity and analytics product suites or the customer's SQL tool of choice.

[0067] Data Pipeline Process FIG. 2 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0068] As shown in FIG. 2 , according to one embodiment, data can be sourced using the above-described data pipeline process, for example, from a customer's (tenant's) enterprise software application or data environment (106) or as custom data 109 sourced from one or more customer-specific applications 107, and loaded into a data warehouse instance, which in some examples includes the use of object storage 105 for storing data.

[0069] According to one embodiment, a data pipeline or process maintains, for each customer (tenant), an analytical application schema, for example as a star schema, which is updated by the system periodically or at some other pace according to best practices for a particular analytical use case, for example, human capital management (HCM) analytics or enterprise resource planning (ERP) analytics.

[0070] According to one embodiment, for each customer (tenant), the system uses an analytical application schema maintained and updated by the system within the analytical application environment (cloud) tenancy 114 to pre-populate a data warehouse instance for that customer based on analysis of data within that customer's enterprise application environment and within the customer tenancy 117. Thus, the analytical application schema maintained by the system enables data to be retrieved from the customer's environment by a data pipeline or process and loaded into the customer's data warehouse instance in a "live" manner.

[0071] According to one embodiment, the analytic application environment also provides, for each customer of the environment, a customer schema that is easily modifiable by the customer, allowing the customer to supplement and utilize the data in the data warehouse instance. For each customer of the analytic application environment, the resulting data warehouse instance acts as a database whose contents are controlled partly by the customer and partly by the analytic application environment (system), including the appearance of the database pre-populated with appropriate data retrieved from the enterprise application environment to address various analytic use cases, e.g., HCM analytics or ERP analytics.

[0072] For example, according to one embodiment, a data warehouse (e.g., an Oracle Autonomous Data Warehouse (ADWC)) may include analytical application schemas and, for each customer / tenant, customer schemas sourced from enterprise software applications or data environments. Data provisioned in the data warehouse tenancy (e.g., an ADWC tenancy) is accessible only to that tenant while simultaneously enabling access to various, e.g., ETL-related or other, features of the shared analytical application environment.

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

[0074] According to one embodiment, for a particular customer / tenant, upon extraction of data, a data pipeline or process can insert the extracted data into a data staging area for that tenant, which can serve as a temporary staging area for the extracted data. Data quality and data protection components can be used to ensure the integrity of the extracted data, for example, by performing validation on the extracted data while it is temporarily held in the data staging area. Once the extraction process completes its 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 a customer schema in a data warehouse.

[0075] Extract, Transform, Load / Publish FIG. 3 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0076] As shown in FIG. 3 , according to one embodiment, the process of extracting data using the above-described data pipeline process, e.g., from a customer's (tenant's) enterprise software application or data environment or as custom data sourced from one or more customer-specific applications, and loading or refreshing that data in a data warehouse instance generally involves three broad stages, which are performed by ETP services 160 or processes, including one or more extract services 163, transform services 165, and load / publish services 167, executed by one or more compute instances 170.

[0077] Extraction: According to one embodiment, a list of view objects for extraction can be submitted to an Oracle BI Cloud Connector (BICC) component, for example, via a ReST call. The extracted files can be uploaded to an object storage component, such as an Oracle Storage Services (OSS) component, for data storage.

[0078] Transformation: According to one embodiment, the transformation process applies business logic while taking data files from an object storage component (e.g., OSS) and loading them into a target data warehouse, e.g., an ADWC database, which is internal to the data pipeline or process and not exposed to the customer (tenant).

[0079] Load / Publish: According to one embodiment, a load / publish service or process takes data from, for example, an ADWC database or warehouse and publishes it to a data warehouse instance accessible to customers (tenants).

[0080] Multiple customers (tenants) FIG. 4 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0081] As shown in FIG. 4, which illustrates the operation of a system with multiple tenants (customers) according to one embodiment, data can be sourced from, for example, each of the multiple customer's (tenants') enterprise software applications or data environments and loaded into a data warehouse instance using the data pipeline process described above.

[0082] According to one embodiment, a data pipeline or process maintains, for each of multiple customers (tenants), e.g., Customer A 180, Customer B 182, an analytical application schema that is updated by the system periodically or at some other pace according to best practices for the particular analytical use case.

[0083] According to one embodiment, for each of multiple customers (e.g., Customer A, Customer B), the system pre-populates a data warehouse instance for the customer based on analysis of data within that customer's enterprise application environment 106A, 106B and within each customer's tenancy (e.g., Customer A tenancy 181, Customer B tenancy 183) using analytical application schemas 162A, 162B maintained and updated by the system, whereby a data pipeline or process retrieves data from the customer's environment and loads it into the customer's data warehouse instance 160A, 160B.

[0084] According to one embodiment, the analytical application environment also provides a customer schema (e.g., Customer A schema 164A, Customer B schema 164B) for each of the environment's multiple customers, which customer schemas are easily modifiable by the customers to enable them to supplement and utilize the data in their data warehouse instance.

[0085] As noted above, according to one embodiment, for each of multiple customers of the analytical application environment, the resulting data warehouse instance acts as a database whose contents are controlled in part by the customer and in part by the analytical application environment (system), including that the database appears pre-populated with appropriate data retrieved from the enterprise application environment to address 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 for loading into the customer schema of the data warehouse.

[0086] Launch Plan FIG. 5 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0087] According to one embodiment, launch plans 186 can be used to control the operation of a customer's (tenant's) data pipeline or process services for specific functional areas to address their specific needs.

[0088] For example, according to one embodiment, a launch plan may define several extract-transform and load (publish) services or steps to be executed in a particular order, at a particular time, and within a particular time window.

[0089] According to one embodiment, each customer can be associated with its own launch plan. For example, a launch plan for a first customer A can determine which tables are retrieved from that customer's enterprise software application environment (e.g., a Fusion application environment) or how services and their processes are sequenced, and a launch plan for a second customer B can similarly determine which tables are retrieved from that customer's enterprise software application environment or how services and their processes are sequenced.

[0090] According to one embodiment, the launch plan is stored in a mapping and configuration database. and is customizable by customers via a data and configuration UI. Each customer can have several launch plans. The compute instances / services (virtual machines) that run the ETL processes for various customers according to the launch plans can be dedicated to a particular service for use by the launch plan and then released for use by other services and launch plans.

[0091] According to one embodiment, based on the determination of historical performance data recorded over a period of time, the system can optimize the execution of launch plans, for example, for one or more functional areas associated with a particular tenant or across a set of launch plans associated with those tenants, to address VM and service level agreement (SLA) utilization for multiple tenants. Such historical data may include load volume and load time statistics.

[0092] For example, according to one embodiment, the historical data may include extract size, number of extracts, extract time, warehouse size, transformation time, publish (load) time, view object extract size, view object extract record count, view object extract time, warehouse table count, number of records processed for a table, warehouse table transformation time, publish table count, and publish time. Such historical data can be used to estimate and plan current and future launch plans, for example, to orchestrate various tasks to run sequentially or in parallel to arrive at the shortest time for executing the launch plan. The collected historical data can also be used to optimize across multiple launch plans for a tenant. In some embodiments, optimization of a launch plan (i.e., a particular sequence of jobs, such as ETL) based on historical data may be automatic.

[0093] ETL process flow FIG. 6 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0094] As shown in FIG. 6 , according to one embodiment, the system enables the flow of data controlled by a data configuration / management / ETL / / status service 190 within a (e.g., Oracle) management tenancy to and from each customer's enterprise software application environment (e.g., Fusion application environment) (including, in this example, BICC components) via a storage cloud service 192, e.g., OSS, to a data warehouse instance.

[0095] As noted above, according to one embodiment, the flow of this data can be managed by one or more services, including, for example, the extraction and transformation services described above, with reference to an ETL repository 193, which retrieves data from the storage cloud service and loads the data into an internal target data warehouse (e.g., an ADWC database) 194, which is internal to the data pipeline or process and not exposed to the customer.

[0096] According to one embodiment, data is moved in stages to a data warehouse and then to database table change log 195, from which a load / publish service can load customer data into a target data warehouse instance in the customer tenancy that is associated with and accessible to the customer.

[0097] ETL Stages FIG. 7 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0098] According to one embodiment, extracting, transforming, and loading data from enterprise applications into a data warehouse instance involves multiple stages, each of which may have several sequential or parallel jobs and may run on different spaces / hardware, including different staging areas 196, 198 for each customer.

[0099] Analyze Application Environment Metrics FIG. 8 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0100] As shown in FIG. 8, according to one embodiment, the metering manager may include functionality to meter provisioned services and service usage via the control plane to provide provisioned metrics 142.

[0101] For example, the metering manager may record, for billing purposes, the usage of processors provisioned through the control plane over time for a particular customer. Similarly, the metering manager may record, for billing purposes, the amount of data warehouse storage space partitioned for use by a customer of a SaaS environment.

[0102] Customization of the analytical application environment FIG. 9 further illustrates a system for providing an analytical application environment, according to one embodiment.

[0103] 9 , according to one embodiment, using the data pipeline process described above, in addition to data that may be sourced, for example, from a customer's enterprise software applications or data environment, one or more additional custom data 109A, 109B sourced from one or more customer-specific applications 107A, 107B can also be extracted, transformed, and loaded into the data warehouse instance using either the data pipeline process described above (including, in some examples, the use of object storage for storing data) and / or custom ETL or other processes 144 that are mutable from the customer's perspective. Once the data is loaded into the data warehouse instance, the customer can create business database views that combine tables from the customer schema with tables from the software analysis application schema, and can query the data warehouse instance using interfaces provided, for example, by a business productivity and analytics product suite or the customer's SQL tool of choice.

[0104] Analysis Application Environment Method FIG. 10 illustrates a flowchart of a method for providing an analytical application environment according to one embodiment.

[0105] As shown in FIG. 10, according to one embodiment, in step 200, the analytical application environment provides access to a data warehouse for storage of data by multiple tenants, the data warehouse being associated with an analytical application schema.

[0106] In step 202, each tenant of the plurality of tenants receives a customer tenancy and a customer schema for use by the tenant in populating the data warehouse instance. associated with the

[0107] In step 204, the data warehouse instance is populated with data received from the enterprise software application or data environment, and data associated with a particular tenant of the analytic application environment is provisioned in a data warehouse instance associated with and accessible to that particular tenant in accordance with the analytic application schema and the customer schema associated with that particular tenant.

[0108] Extensibility and Customization Different customers in data analytics environments may have different requirements regarding how data is classified, aggregated, or transformed for the purposes of providing data analytics or business intelligence data or for the purposes of developing software analytics applications.

[0109] To support such different requirements, according to one embodiment, the system may include a semantic layer that enables extending the semantic data model (semantic model) using custom semantic extensions to provide custom content at the presentation layer. An extension wizard or development environment can guide a user in extending or customizing the semantic model through the definition of branches and steps using custom semantic extensions, and then promoting the extended or customized semantic model to a production environment.

[0110] According to various embodiments, technical advantages of the described approach include support for additional types of data sources. For example, a user can perform data analytics based on a combination of ERP data sourced from a first vendor's product and HCM data sourced from a second, different vendor's product, or based on a combination of data received from multiple data sources with different regulatory requirements. User-defined extensions or customizations can withstand patches, updates, or other changes to the underlying system.

[0111] FIG. 11 illustrates a system for supporting extensibility and customization in an analytical application environment according to one embodiment.

[0112] According to one embodiment, the semantic layer may include data that defines a semantic model of a customer's data, which is useful in helping users understand and access that data using commonly understood business terms. The semantic layer may include a physical layer that maps to a physical data model or data plane, a logical layer that acts as a mapping or transformation layer in which calculations can be defined, and a presentation layer that allows users to access the data as content.

[0113] 11, according to one embodiment, semantic layer 230 may include a packaged (out-of-the-box, initial) semantic model 232 that can be used to provide packaged content 234. For example, the system may use the ETL or other data pipeline or process described above to load data from a customer's enterprise software application or data environment into a data warehouse instance, and then use the packaged semantic model to provide packaged content to the presentation layer.

[0114] According to one embodiment, the semantic layer may also be associated with one or more semantic extensions 236 that can be used to extend the packaged semantic model and provide custom content 238 to the presentation layer 240.

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

[0116] According to one embodiment, in addition to data sourced from customer environments using the ETL or other data pipelines or processes described above, customer data can be loaded into the data warehouse instance using a variety of data models or scenarios that provide opportunities for further extensibility and customization.

[0117] FIG. 12 is a diagram illustrating a self-service data model according to one embodiment. As shown in Figure 12, according to one embodiment, a self-service data model or scenario allows a customer to load external or custom data as a custom dataset using an ETL or other data pipeline or process provided by the system that provides dimensional conformance. The customer can define one or more "live" datasets that are populated by the system and combine the "live" datasets with external datasets to create a combined dataset that can be queried. In this scenario, customer responsibilities generally include manual refreshing of datasets and hardening of dataset security.

[0118] FIG. 13 is a diagram illustrating a curated data model according to one embodiment. As shown in Figure 13, according to one embodiment, a curated data model or scenario provides a centralized or managed analytics environment, where a system-provided ETL or other data pipeline or process publishes customer data into an immutable analytical application schema, and the customer loads external or custom data into the customer schema using a custom ETL or other data pipeline or process. The customer can create business database views that combine system-managed tables and custom tables and query the combined data using a tool of their choice. In this scenario, the customer's responsibilities generally include managing the loading and refreshing of data into the customer schema using a custom ETL or other data pipeline or process.

[0119] The above examples of curated and self-service data models or scenarios are provided by way of example. According to various embodiments, the system may support other types of data models or scenarios.

[0120] Auto-generation of BI data models As noted above, there is growing interest in developing software applications that leverage the use of data analytics, for example, in the context of an organization's enterprise resource planning (ERP) or other enterprise computing environments, but traditional approaches to preparing BI data models do not work very well when dealing with the complex schemas used in modern enterprise computing environments.

[0121] For example, different enterprise customers may have specific requirements regarding how data should be categorized, aggregated, or transformed for purposes of providing key performance indicators, data analytics, or other types of business intelligence data. For example, a customer may choose to modify the data source model associated with the data, e.g., by adding custom facts or dimensions.

[0122] According to various embodiments, to support different customer requirements, the system may include a semantic layer that allows extending the semantic data model (semantic model) using custom semantic extensions to provide custom content at the presentation layer. The semantic layer may include a physical layer that maps to a physical data model or data plane, a logical layer that acts as a mapping or transformation layer where computations can be defined, and a presentation layer that allows users to access data as content.

[0123] For example, according to one embodiment, a semantic model extension process may introspect a customer's data stored, for example, in a data warehouse instance, and evaluate metadata associated with that customer data to determine custom facts, custom dimensions, and / or other types of data source model extensions to extend or customize the semantic model according to the customer's requirements.

[0124] In some environments, customers may also use a BI product or environment, such as, for example, Oracle NetSuite, which generally provides an ERP computing environment targeted at medium to large enterprises supporting front-office and back-office processes, such as, for example, financial management, revenue management, fixed assets, order management, billing, and inventory management, which processes may have additional requirements and require further modification of the semantic model to enable the customer's, for example, NetSuite data to be used within the analytics environment.

[0125] According to one embodiment, a system and method are described herein for the automated generation of business intelligence (BI) data models using data introspection and curation, such as may be used in enterprise resource planning (ERP) or other enterprise computing or data analytics environments. The described approach derives a target BI data model using a combination of manually curated artifacts and the automated generation of a model of a source data environment via data introspection. For example, a pipeline generator framework can evaluate transaction type dimensions, reduced attributes, and application measures, and use the output of this process to create an output target model and pipeline or load plan. The systems and methods described herein provide a technological improvement in terms of building new subject areas or BI data models in a much shorter timeframe.

[0126] Broadly described, according to one embodiment, a system comprises a pipeline or snapshot (ETL) generator, component, or process that is used to automatically generate one or more maps by referencing or looking at a source model; for example, the automatic generation process may include the use of manually curated artifacts and automatically determined or interpreted variables.

[0127] A semantic model (RPD) generator, generator, component, or process generates a data model for a transaction type, e.g., an RPD generation process uses the determined dimensions and facts to generate a semantic model, e.g., It can be generated as a BI repository (RPD) file.

[0128] A security artifact generator, component, or process overlays the generated semantic model with any necessary security artifacts, e.g., those described in the source model; for example, the security artifact generation process can create security filters and application roles that control data visibility.

[0129] A human readable format (HRF) generator, component or process may be used to generate human readable format data for subsequent use thereof, for example to create a BI report.

[0130] FIG. 14 illustrates a system for automatic generation of BI data models using data introspection and curation according to one embodiment.

[0131] As shown in FIG. 14 , according to one embodiment, a customer may use a BI environment, such as, for example, Oracle NetSuite, which is provided in a BI data center 310 and includes, in this example, a NetSuite Oracle database 312 having a NetSuite (NS) customer schema 314 and a provisioning component 316 that enables the customer's, for example, NetSuite data to be provided to the analytical application environment.

[0132] According to one embodiment, in an analytical application environment, a BI provisioning component 300 enables a customer's (e.g., NetSuite or other BI or ERP environment) data to be received from the customer's enterprise software application or data environment, loaded into a data warehouse instance, associated with the customer's (e.g., NSAW) data schema 320, and then uses semantic models to surface the packaged content from the customer's source data to the presentation layer.

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

[0134] According to one embodiment, a customer may perform modifications to the data source model or NetSuite or other BI or ERP product or environment to support specific requirements, for example, by adding custom facts or dimensions associated with data stored in the data warehouse instance, and the system can extend the semantic model accordingly.

[0135] For example, according to one embodiment, the system can use a semantic model extension process to programmatically introspect a customer's data to determine custom facts, custom dimensions, or other customizations or extensions made to the data source model, and then use an appropriate flow to automatically modify or extend the semantic model to support those customizations or extensions.

[0136] FIG. 15 further illustrates a system for automatic generation of BI data models using data introspection and curation, according to one embodiment.

[0137] Some ERP or other enterprise computing or BI environments, such as NetSuite, utilize a data model whereby different modules, such as a sales or purchase order module, can use different transaction tables for storing data.

[0138] According to one embodiment, when the analytical application environment is used with a BI provisioning component that allows NetSuite data to be accepted into the system, NetSuite data model 340 can be used to map the NetSuite data model, which includes various business entities stored in a set of transactional tables.

[0139] At a high level, transaction tables in a NetSuite environment are striped by a field called Transaction Type, which stores an indication of what kind of entity the record represents. Transaction tables have a superset of all columns and attributes required for all transaction types, with only columns relevant to the transaction stored in each record. For example, a purchase order transaction has the vendor column populated but the customer blank, and vice versa.

[0140] Given this network of transaction tables, a list of applicable dimensions and attributes can be determined by introspecting the data in the various fields to determine, for example:

[0141] Purchase Order: This may include applicable dimensions such as vendor, time, product, subsidiary, etc.

[0142] Sales Order: This may include applicable dimensions such as customer, time, product, subsidiary, etc.

[0143] Given the list of applicable dimensions and attributes, data models, pipelines and semantic models can be constructed for that, for example, star schema.

[0144] First, curation: While various star schemas can be constructed through introspection with pipelines and semantic models, dimensions must be seeded into the data model and pipelines through manual curation, and the code generation process must know a superset of supported dimensions and dimension attributes that can be introspected to include or exclude from the model.

[0145] Security: The generator creates default security groups and security filters and application rules to control security filters for each subject area. Customers can then assign specific user membership to enterprise roles, and security filters are automatically invoked in the semantic model to restrict visibility to the assigned set of rows for that user.

[0146] Pipeline Generator Framework FIG. 16 illustrates an exemplary pipeline generator framework for use in the automated generation of BI data models, according to one embodiment.

[0147] As shown in FIG. 16, in accordance with one embodiment, a pipeline generator framework The work can run processes to create output target models and pipelines or load plans, for example a seed (e.g., ODI) repository 352 that provides seeded dimensions associated with data models and pipelines and provided via manual curation; a pipeline and snapshot generator 356 that performs the following process to automatically generate one or more maps by referencing or viewing a source model; an API 354 that receives information from a customer's (e.g., NetSuite or other BI or ERP environment (e.g., NetSuite UMD); A generated (e.g. ODI) repository 364 that is created based on the seeded dimensions, and a Human Readable Format (HRF) generator 366 adapted to generate human readable format data for subsequent use thereof, for example to create a BI report or other HRF document 370; one or more decision files 358; an RPD generator 360 adapted to generate a data model for the transaction type, e.g., the rpd generation process uses the determined dimensions and facts to generate a semantic model based on a seed rpd (seed.rpd) 362, e.g., as a BI repository (RPD) file, and provides the generated RPD 368 as an output; It includes a security generator 380 adapted to overlay any necessary security artifacts, e.g., those described in the source model, onto the generated semantic model; for example, the security artifact generation process can create security filters and application roles that control data visibility and prepare a secured RPD (secured.rpd) 382.

[0148] According to one embodiment, the pipeline generator framework may include, for example, multiple components or functions.

[0149] 1. Pipeline generation According to one embodiment, a pipeline or snapshot (ETL) generator, component or process is used to automatically generate one or more maps by referencing or looking at a source model.

[0150] For example, the automated generation process may involve the use of manually curated artifacts and automatically determined or interpreted variables. A seed repository contains manually curated artifacts, such as base dimensions associated with an environment, for use by the pipeline generator. Other transaction dimensions, columns, or security artifacts, etc., are then automatically generated by the framework.

[0151] 2. Semantic Model Generation According to one embodiment, a semantic model (RPD) generator, generator, component, or process generates a data model for a transaction type. For example, the RPD generation process can use the determined dimensions and facts to generate a semantic model, for example, as a BI repository (RPD) file. It uses the output of the previous step and also uses a template rpd xml file.

[0152] As mentioned above, a seed repository is a repository for use by the RPD generator, e.g. Includes manually curated artifacts such as base dimensions associated with the environment.

[0153] Step 1: Start processing degenerate columns - Process degenerate columns that are not needed in the fact table.

[0154] Step 2: Begin processing unused fact columns - process and retain only the facts or measures required by the transaction type.

[0155] Step 3: Begin processing dimensions - Process and retain only the dimension columns required by the transaction type.

[0156] Step 4: Physical layer modification - Create a physical layer table in the rpd. Step 5: Start creating new subject area objects i.e. LTS, Date dimension, Key, Measure definitions, including Step 5.1: LTS, Step 5.2: Logical Table, Step 5.3: Logical Column, Step 5.4: Logical Key, Step 5.5: Measure, Step 5.6: Complex Logical Join, Step 5.7: Dimension, Step 5.8: Logical Level.

[0157] Step 6: Initiate display changes - Create presentation layer objects for the transaction type.

[0158] 3. Security Generation According to one embodiment, a security artifact generator, component, or process overlays the generated semantic model with any necessary security artifacts, such as those described in the source model. For example, the security artifact generation process can create security filters and application roles that control data visibility.

[0159] According to one embodiment, a security artifact generator or process overlays the generated semantic model with any necessary security artifacts, e.g., those described in the source model. According to one embodiment, the result of the above steps or processes is the creation of a pipeline from a source data environment or system, e.g., NetSuite ERP or other enterprise computing environment, to, e.g., one or more BI reports. This pipeline can then be used to retrieve data from the source data environment and subsequently run BI reports against the retrieved data.

[0160] 4.Generate readable format data According to one embodiment, a human readable format (HRF) generator, component or process may be used to generate human readable format data for subsequent use thereof, for example, to create a BI report.

[0161] For example, in an Oracle Analytics for Applications (OAX), Fusion Analytics Warehouse, or Oracle Cloud Integration (OCI) environment, an HRF generation process can generate an HRF format or mapping format that is used, for example, by the OAX team to manage the ODI repository. According to one embodiment, a human readable format (HRF) generator or process can be used to generate human readable format data for its subsequent use.

[0162] FIG. 17 illustrates an exemplary flowchart of a process for use in the automated generation of a BI data model, according to one embodiment.

[0163] As shown in FIG. 17, according to environment 400, once an input transaction type (e.g., a sales order transaction type) is determined, a process can access corresponding, for example, NetSuite tables, introspect or look at the data in those tables, determine dimensions and attributes, and generate a target model and load plan.

[0164] For example, in step 402, an input transaction type is received (eg, PurchOrd).

[0165] In step 404, the system connects to NetSuite or other BI or ERP environment and reverse engineers the tables found therein to create aliases for each transaction type, e.g., Transaction Purchase Order (Transaction_PurchOrd), Transaction Line Purchase (TransactionLine_PurchOrd), and Transaction Accounting Line (TransactionAccountingLine_PurchOrd).

[0166] In step 406, the system creates a staging table in the data warehouse for each of the above, eg, Transaction_PurchOrd, TransactionLine_PurchOrd, and TransactionAccountingLine_PurchOrd.

[0167] In step 408, the system creates ODI mappings to stage data from each of these into their respective tables, including automatically adding incremental filters if a last modified date column is found, assigning the appropriate knowledge modules to the ODIs, and generating scenarios (compiled versions of the mappings).

[0168] In step 410, the system introspects the data in these three tables to determine applicable dimensions and creates the rejectedDimensions.txt file.

[0169] In step 412, the system introspects the data in these three tables to determine degenerate attributes and creates a rejectedAttributes.txt file.

[0170] In step 414, the system introspects the data in these three tables to determine applicable measures and creates the rejectedMeasures.txt file.

[0171] In step 416, the system creates a target fact table model, for example, DW_PURCHASEORDER_F.

[0172] In step 418, the system creates an ODI mapping to load data from the staging table into the fact table.

[0173] In step 420, the system updates the daily load plan to include the (runtime) ODI scenario.

[0174] In step 422, the system creates a snapshot table with snapshot_dt if snapshotBuild=true.

[0175] In step 424, the system converts the fact table to a snapshot table. Create an ODI mapping to load the data.

[0176] In step 426, the system updates the snapshot load plan to include the (runtime) ODI scenario.

[0177] FIG. 18 further illustrates an exemplary flowchart of a process for use in the automated generation of a BI data model, according to one embodiment.

[0178] As shown in FIG. 18 , in accordance with embodiment 440, once an input transaction type has been determined and the dimensions and attributes for that transaction type have been determined by introspecting the data as described above, the process may access, for example, a template star schema to create an appropriate, e.g., sales order star schema, by referencing or looking at the introspection data.

[0179] In step 442, an input transaction type is received (eg, PurchOrd), as described above, for example, rejectedDimensions.txt, rejectedAttributes.txt, and rejectedMeasures.txt.

[0180] In step 444, the system creates a copy of the seeded region of interest (eg, DW_SUBJAREA_F).

[0181] In step 446, the system replaces all occurrences of the _SUBJAREA_ string (in this example) with the transaction code, eg, PURCHASEORDER.

[0182] In step 448, the system trims all dimensions listed in rejectedDimensions.txt.

[0183] In step 450, the system trims all attributes listed in rejectedAttributes.txt.

[0184] In step 452, the system trims all attributes listed in rejectedMeasures.txt.

[0185] In step 454, the system creates an unsecured rpd, for example, NSFinal.rpd.

[0186] In step 456, the system creates an unsecured rpd (NSFinal.rpd).

[0187] In step 458, the system creates a visibility role for the region of interest. In step 460, the system creates a data security role for each secured dimension.

[0188] In step 462, the system creates a secured rpd (NSFinalSecured.rpd).

[0189] According to one embodiment, and as further shown in FIG. 18, a security artifact generator can then be used to create appropriate visibility or data security roles for each secured dimension.

[0190] According to one embodiment, the described approach uses a combination of manual model curation and automated generation of source data environments via data introspection to derive a target BI data model, providing a technological improvement in terms of building new subject areas or BI data models in a much shorter timeframe. The various steps, components or processes described above can be provided as software or program code executable by a computer system or other type of processing device.

[0191] Example Pipeline Generator Input According to various embodiments, example inputs to a pipeline generator are shown and described below.

[0192] 1. List of transaction types FIG. 19 illustrates a list of exemplary transaction types, according to one embodiment.

[0193] As shown in Figure 19, according to one embodiment, the list of transaction types 510 can be captured in a file, for example, as subjectArea.csv. The file is used to control the transaction types that are processed at runtime, their short names, business-friendly names, and the security groups to which the transaction types belong.

[0194] 2. Transaction Column List FIG. 20 illustrates an exemplary transaction queue list according to one embodiment.

[0195] 20, according to one embodiment, transaction column list 520 can be provided as an input file, which is a static file that captures a list of all columns in the transaction table, whether they are treated as facts, dimensions, or measures. This file can be updated once per release, for example, as the BI / ERP system adds or removes a new set of columns.

[0196] 3. Dimensional-Logical Dimension Map FIG. 21 is a diagram illustrating an exemplary dimensional-logical dimension map according to one embodiment.

[0197] As shown in FIG. 21, according to one embodiment, the dimension-to-logical dimension map 530 can be provided as a file that provides logical names for all dimensions used in the model.

[0198] 4. Physical-to-logical attribute map FIG. 22 is a diagram illustrating an exemplary physical-to-logical attribute map according to one embodiment.

[0199] 22, according to one embodiment, the physical-to-logical attribute map 540 can be provided as a file that provides logical names for the physical attributes in the transaction table. The logical names are used in the semantic model.

[0200] 5. Physical-logical scale map FIG. 23 is a diagram illustrating an exemplary physical-to-logical scale map according to one embodiment.

[0201] As shown in FIG. 23, according to one embodiment, a physical-to-logical scale map 550 It can be provided as a file that provides logical names for the physical measures in the transaction table. The logical names are used in the semantic model.

[0202] 6. Template Semantic Model According to one embodiment, a template semantic model has definitions of all curated dimensions and sample fact tables and is used as a model to create a semantic model for a particular transaction type.

[0203] 7. Template ODI Repository According to one embodiment, a template ODI repository has definitions of all curated dimensions and is used to create an ODI repository model for a specific transaction type.

[0204] Exemplary User Interface for Automatic Generation of BI Data Models 24-31 are diagrams illustrating example user interfaces associated with the automatic generation of a BI data model, according to various embodiments. For example:

[0205] FIG. 24 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, according to one embodiment.

[0206] FIG. 25 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, including a display of a package, according to one embodiment.

[0207] FIG. 26 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, including a display of a load plan, according to one embodiment.

[0208] FIG. 27 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, including a display of a generated rpd file, according to one embodiment.

[0209] FIG. 28 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, including an example of a business model associated with an rpd file, according to one embodiment.

[0210] FIG. 29 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, including example security filters, according to one embodiment.

[0211] FIG. 30 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, including an example of a displayed model, according to one embodiment.

[0212] FIG. 31 illustrates an exemplary user interface for use in a system for automatic generation of BI data models, including example mappings, according to one embodiment.

[0213] Auto-generation of BI data model processes FIG. 32 illustrates a flowchart of a method for providing automated generation of BI data models using data introspection and curation, according to one embodiment.

[0214] As shown in FIG. 32, according to one embodiment, in step 602, the analytical application environment stores data in a data warehouse.

[0215] In step 604, a pipeline or snapshot (ETL) generator, component, or process is used to automatically generate one or more maps by referencing or looking at the source model; for example, the automatic generation process may include the use of manually curated artifacts and automatically determined or interpreted variables.

[0216] In step 606, a semantic model (RPD) generator, generator, component, or process generates a data model for the transaction type, for example, an RPD generation process can use the determined dimensions and facts to generate the semantic model, for example, as a BI repository (RPD) file.

[0217] In step 608, the security artifact generator, component, or process overlays the generated semantic model with any necessary security artifacts, such as those described in the source model; for example, the security artifact generation process may create security filters and application roles that control data visibility.

[0218] In step 610, a human readable format (HRF) generator, component or process can be used to generate human readable format data for its subsequent use.

[0219] 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. Appropriate software coding may be readily prepared by skilled programmers based on the teachings of the present disclosure, as will be apparent to those skilled in the software arts.

[0220] In some embodiments, the teachings herein may include a computer program product, which is a non-transitory computer-readable storage medium 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 may 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.

[0221] The above 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.

[0222] For example, various embodiments of the systems and methods described herein may be implemented in various enterprise resource planning (ERP) or other enterprise computing or data analytics environments, such as, for example, NetSuite or Fusion applications. Although shown for use in an ERP system, various embodiments may be used in other types of ERP, cloud computing, enterprise computing, or other computing environments.

[0223] The embodiments were chosen and described to best explain the principles of the present teachings and their practical application, and to enable those skilled in the art to appreciate various embodiments and variations thereof suited to the particular uses intended, the scope of which is intended to be defined by the following claims and their equivalents.

Claims

1. 1. A system for automated generation of data models using data introspection and curation, comprising: a computer including one or more processors, the computer providing access to a data warehouse by an analytical application environment for storage of data by multiple tenants; The system provides a generator framework, the generator framework comprising: and operable to automatically generate one or more data maps associated with a source data environment by referencing a source model associated with said source data environment, wherein the automatic generation process includes use of curated artifacts and automatically determined or interpreted variables, said generator framework further comprising: Operable to generate a data model for a transaction type associated with the source data environment, including determining dimensions and facts associated with the source data to generate a semantic model; Operable to overlay security artefacts that control data visibility onto the generated semantic model; A system operable to generate readable format data for use in generating reports.

2. 10. The system of claim 1, wherein the system executes an extract-transform-load data pipeline or process according to an analytical application schema and / or a customer schema associated with a tenant to receive data from the tenant's enterprise software application or data environment for loading into a data warehouse instance.

3. generating one or more extract-transform-load (ETL) maps includes receiving the curated artifacts from a seed repository, the curated artifacts including base dimensions associated with the source data environment; The system of claim 1 or 2, wherein further transaction dimensions, columns or security artifacts are then automatically generated by the generator framework.

4. The system of any one of claims 1 to 3, wherein the generated semantic model is stored as a Business Intelligence (BI) Repository (RPD) file.

5. The system of any one of claims 1 to 4, wherein the source data environment is one of NetSuite, Business Intelligence (BI), Enterprise Resource Planning (ERP), cloud computing, enterprise computing or other computing environments.

6. 1. A method for automated generation of data models using data introspection and curation, comprising: a computer including one or more processors providing access to a data warehouse by an analytical application environment for storage of data by multiple tenants; By referencing a source model associated with a source data environment, and automatically generating one or more data maps associated with the data environment, wherein the automatic generation process includes use of curated artifacts and automatically determined or interpreted variables, the method further comprising: generating a data model for a transaction type associated with the source data environment, the data model including determining dimensions and facts associated with the source data to generate a semantic model; overlaying the generated semantic model with security artifacts that control data visibility; generating readable format data for use in generating a report.

7. 10. The method of claim 6, further comprising: executing an extract-transform-load data pipeline or process in accordance with an analytical application schema and / or a customer schema associated with a tenant to receive data from the tenant's enterprise software application or data environment for loading into the data warehouse instance.

8. generating one or more extract-transform-load (ETL) maps includes receiving the curated artifacts from a seed repository, the curated artifacts including base dimensions associated with the source data environment; The method of claim 6 or 7, wherein further transaction dimensions, columns or security artifacts are then automatically generated by the generator framework.

9. The method according to any one of claims 6 to 8, wherein the generated semantic model is stored as a Business Intelligence (BI) Repository (RPD) file.

10. The method of any one of claims 6 to 9, wherein the source data environment is one of NetSuite, Business Intelligence (BI), Enterprise Resource Planning (ERP), cloud computing, enterprise computing or other computing environments.

11. A non-transitory computer-readable storage medium containing instructions stored thereon, the instructions, when read and executed by one or more computers, causing the one or more computers to perform a method, the method comprising: providing access to the data warehouse by an analytical application environment for storage of data by multiple tenants; and automatically generating one or more data maps associated with the source data environment by referencing a source model associated with the source data environment, wherein the automatic generation process includes use of curated artifacts and automatically determined or interpreted variables, the method further comprising: generating a data model for a transaction type associated with the source data environment, the data model including determining dimensions and facts associated with the source data to generate a semantic model; overlaying the generated semantic model with security artifacts that control data visibility; and generating readable format data for use in creating a report.

12. The analytics application schema and / or customer schema associated with the tenant 12. The non-transitory computer-readable storage medium of claim 11, further comprising: executing an extract-transform-load data pipeline or process according to to receive data from the tenant's enterprise software application or data environment for loading into a data warehouse instance.

13. generating one or more extract-transform-load (ETL) maps includes receiving the curated artifacts from a seed repository, the curated artifacts including base dimensions associated with the source data environment; The non-transitory computer-readable storage medium of claim 11 or 12, wherein further transaction dimensions, columns or security artifacts are then automatically generated by the generator framework.

14. The non-transitory computer-readable storage medium of any one of claims 11 to 13, wherein the generated semantic model is stored as a Business Intelligence (BI) Repository (RPD) file.

15. 15. The non-transitory computer-readable storage medium of claim 11, wherein the source data environment is one of NetSuite, Business Intelligence (BI), Enterprise Resource Planning (ERP), cloud computing, enterprise computing or other computing environments.

Images