Autonomous decision support system using configuration inflation based ETL and content modeling

A computer-implemented method is disclosed which detects a change in a set of data sources of a DSS, such as addition of a new data source, removal of an existing data source, or a schema change of an existing data source in the set. A first set of DSS updates for pending ETL jobs of an ETL engine of the DSS are automatically determined based on the change in the set of data sources. The method automatically updates, without canceling, the pending ETL jobs of the ETL engine based on the first set of DSS updates. A second set of DSS updates for a semantic layer of a BI engine of the DSS are automatically determined based on the change in the set of data sources and based on the first set of DSS updates. The semantic layer is automatically updated based on the second set of DSS updates.

TECHNICAL FIELD

The present disclosure relates to decision support systems (DSSs), and more particularly to automatically updating a DSS based on a detected change in a set of data sources of the DSS.

BACKGROUND

A decision support system (DSS) is a computer-based information system that supports business or organizational decision-making activities. DSSs can serve the management, operations, and planning levels of an organization (usually mid and higher management) and help to make decisions, which may be rapidly changing and not easily specified in advance. One example DSS is CLARITY from CA TECHNOLOGIES. DSSs often have a business intelligence (BI) component which contains BI rules to help with the decision making.

A DSS includes a data warehouse, one or more semantic layers, and a presentation layer. The data warehouse includes data aggregated from a number of data sources. Most DSSs typically consist of one or many data warehouses for the backend and one or many BI frontends. The data warehouses are used to consolidate data from a set of one or multiple sources over a long period of time transformed to be optimized for quick retrieval. The BI frontend(s) may be used to enable data visualization, analysis, self-service, data broadcast, sandboxing, etc.

Data may be imported into the data warehouse from the data sources via an Extract, Transform, Load (ETL) tool, for example. An ETL engine may extract data from various data sources, transform the data for storage in a proper format and/or structure for querying and analysis, and load the data into its final target in the data warehouse.

The semantic layer is a business translation layer that sits between the data warehouse and end users. The semantic layer acts as a translator of sorts by mapping complex metadata (e.g., data types and names of fields) from the data warehouse to business intelligence (BI) software fields in a way that business users can understand and utilize. Because BI software fields are more understandable by business users, the semantic layer isolates business users from the technical complexities of the data warehouse. By using common business terms, rather than data language, the semantic layer makes it easier for business users to access, manipulate, and organize information, and simplifies the complexity of business data. The presentation layer (which may be part of a BI frontend) creates BI output based on the BI fields in the semantic layer, such as charts, reports, dashboards, etc.

SUMMARY

According to one aspect of the present disclosure, a computer-implemented method is disclosed which detects a change in a set of data sources of a decision support system (DSS). The change comprises addition of a new data source to the set, removal of an existing data source from the set, or a schema change of an existing data source in the set. A first set of DSS updates for pending extract, transform, load (ETL) jobs of an ETL engine of the DSS are automatically determined based on the change in the set of data sources. The pending ETL jobs import data from the set of data sources into a data warehouse. The computer implementing the method automatically updates, without canceling, the pending ETL jobs of the ETL engine based on the first set of DSS updates. A second set of DSS updates for a semantic layer of a business intelligence (BI) engine of the DSS are automatically determined based on the change in the set of data sources and based on the first set of DSS updates. The semantic layer comprises a mapping of fields from the data warehouse to reporting fields of the BI engine. The semantic layer of the BI engine is automatically updated based on the second set of DSS updates.

According to another aspect of the present disclosure, a computing device is disclosed that comprises a communication interface and a processing circuit. The communication interface is configured to communicate with an ETL engine of a DSS, a BI engine of the DSS, or both the ETL engine and the BI engine. The processing circuit is communicatively connected to the communication interface and is configured to detect a change in a set of data sources of the DSS, with the change comprising addition of a new data source to the set, removal of an existing data source from the set, or a schema change of an existing data source in the set. The processing circuit is further configured to automatically determine a first set of DSS updates for pending ETL jobs of the ETL engine based on the change in the set of data sources, wherein the pending ETL jobs import data from the set of data sources into a data warehouse. The processing circuit is further configured to automatically update, without canceling, the pending ETL jobs of the ETL engine based on the first set of DSS updates. The processing circuit is further configured to automatically determine a second set of DSS updates for a semantic layer of the BI engine based on the change in the set of data sources and based on the first set of DSS updates, wherein the semantic layer comprises a mapping of fields from the data warehouse to reporting fields of the BI engine. The processing circuit is further configured to automatically update the semantic layer of the BI engine based on the second set of DSS updates.

According to another aspect of the present disclosure, a computer program product is disclosed which comprises a computer readable storage medium having computer readable program code embodied therewith. The computer readable program code comprises computer readable program code configured to detect a change in a set of data sources of a DSS, the change comprising addition of a new data source to the set, removal of an existing data source from the set, or a schema change of an existing data source in the set. The computer readable program code further comprises computer readable program code configured to automatically determine a first set of DSS updates for pending ETL jobs of an ETL engine of the DSS based on the change in the set of data sources, wherein the pending ETL jobs import data from the set of data sources into a data warehouse. The computer readable program code further comprises computer readable program code configured to automatically update, without canceling, the pending ETL jobs of the ETL engine based on the first set of DSS updates. The computer readable program code further comprises computer readable program code configured to automatically determine a second set of DSS updates for a semantic layer of a BI engine of the DSS based on the change in the set of data sources and based on the first set of DSS updates, wherein the semantic layer comprises a mapping of fields from the data warehouse to reporting fields of the BI engine. The computer readable program code further comprises computer readable program code configured to automatically update the semantic layer of the BI engine based on the second set of DSS updates.

Of course, the inventive embodiments of the present disclosure are not limited to the above features and advantages. Indeed, those skilled in the art will recognize additional features and advantages upon reading the following detailed description, and upon viewing the accompanying drawings.

DETAILED DESCRIPTION

In the past, configuration of a DSS has been quite costly and time-consuming. Even after a DSS was configured, support, maintenance, and enhancement of the DSS could be a costly affair. Enhancements to DSSs tend to fall mostly in areas such as source system changes (e.g., addition of new data sources, addition/modification of business logic, addition/modification of BI components like semantic layers, dashboards, reports or similar requirements, etc.). This resulted in DSS users being heavily reliant on software service providers and consultants to make any system changes. Another issue is that the production life of many DSSs was shorter than their respective development/enhancement cycles. By the time a DSS was developed, there may be a need to update the data sources of the DSS, which can lead to another prolonged development cycle. These problems were amplified by the fact that DSSs are rigid, in that changes in source schema like new table additions are often rejected or not gracefully handled. As a result, modifications to source schema could cause catastrophic system failure. Hence, upgrades, enhancements and modifications to the Decision Support Systems were costly, slow and risky in terms of cost, effort and ROI

The present disclosure describes implementation of automatic updates for a DSS based upon changes in the data sources of the DSS. These automatic updates include automatically updating, without canceling, pending DSS updates of an ETL engine of the DSS, as well as automatically updating a semantic layer of the DSS. The automatic updates are determined using artificial intelligence (AI) models that determine desired DSS updates based on the data source changes and/or based on analysis of historical DSS updates performed for previous changes to the data sources of the DSS (e.g., through a supervised learning AI algorithm). The change to the data sources does not refer, e.g., to adding new records to an existing table, but instead refers to addition of a new data source, removal of an existing data source, or a schema change of an existing data source.

The automatic updates that are performed may depend on a confidence metric determined for a given update. If a value of the confidence metric is too low, additional/alternative DSS updates may be determined. The confidence metric is more likely to be lower where the changes to the data sources of the DSS are not reflected in historical data for the DSS. In various ones of the embodiments discussed below, an ETL engine of the DSS uses a configuration inflation mode of operation based on instructions contained in XML files to implement the automatic updates. These automatic updating features can be used to provide an autonomous, adaptable, intelligent, resilient, self-governing DSS. In one or more embodiments, the DSS uses configuration inflation based ETL, and also uses BI content modeling via APIs to provide such benefits. This could include custom BI APIs, or out of the box BI APIs supported by leading BI software such as COGNOS, BUSINESS OBJECTS, JASPER, PENTAHO BI, etc.

This can impart an autonomous/self-governing/adaptability to a DSS. This can also cut down the time to upgrade, modify, and enhance the DSS and significantly reduce risks, costs, and effort, which can increase the life of a DSS. Also, the same behavior can make it simpler for layperson users to manage the DSS, reducing the need for hiring a team of DSS administration experts.

With this in mind,FIG. 1illustrates an example DSS20that imports data from a set30of data sources into a data warehouse40. The set30may include data sources such as Line of Business (LOB) applications32, databases34, and/or individual files36. The data sources in the set30could include a plurality of tables containing data (e.g., transaction tables, dimension tables, lookup tables, etc.), for example. An ETL engine44provides Extract, Transform, Load (ETL) functionality and is used to import data from the set30of data sources into the data warehouse40that includes a data repository42. The term “data warehouse” is intended to broadly cover a group of one or more databases (and thus, is intended to cover so-called “data marts”). Additionally, although data warehouse is used in the singular form, it is understood that the DSS20could include multiple data warehouses for which the techniques below could be applied.

The ETL engine44extracts data from various data sources, transforms the data for storage in a proper format and/or structure for querying and analysis, and loads the data into its final target in the data warehouse. The ETL engine44performs these tasks based on a series of ETL jobs46that instruct the ETL engine44on how the data from the set30of data sources should be processed. In some embodiments, a given ETL job may include a plurality of ETL transformations that are grouped together. The ETL engine44handles data warehouse population for the DSS20. The ETL jobs46performed by the ETL engine44comprise one or more configuration files (e.g., XML configuration files).

As an example of ETL engine44operation, consider that a data source may include raw data for hundreds or thousands of customer orders, with each order including price and customer information. In one example, the ETL engine44may group orders by some criteria (e.g., group all orders from a given customer, or by customers in a given geographical region), determine a sum of the orders by that criteria (e.g., total amount spent by the customer, or by customers in the geographical region), and then create or update a table in the data warehouse40to reflect that aggregated information. A pending ETL job may be executed periodically (e.g., to update the aggregated table based on newly placed orders). The semantic layer50maps fields from such a table to BI fields, and the presentation layer60may generate reports based on that information (e.g., a report of purchases from customers in a given geographical region over time).

The ETL engine44operates based on instructions included in its ETL jobs46, which indicate what data to extract from sources, how to transform that data, and how to load that data into the data warehouse40. In one or more embodiments, the ETL engine44is a configuration inflation based ETL engine whose operation is based on configuration files that are formatted according to open standards (e.g., XML). In such embodiments, ETL jobs46are stored in one or more configuration files, and the ETL engine44processes its corresponding configuration file(s) to build runtime code for execution of a given task. In particular, when the ETL engine44is executing its ETL jobs, the ETL engine44would compile and/or inflate its configuration file(s) (from its ETL jobs46) into runtime code for execution. The open source ETL engine from PENTAHO, as an example, compiles/inflates its jobs into JAVA runtime code. In these and other embodiments, the AI engine70of the DSS can modify the ETL jobs46of the ETL engine44dynamically, optionally during runtime of the ETL engine44, by modifying the configuration files of the ETL jobs46. These automatic updates may be performed in a “silent mode” with no human interaction.

In such embodiments, the ETL engine44(which may either be custom built ETL Engine or a third party ETL tool) uses an open standard compliant configuration file inflation based operation and leverages the fact that the ETL jobs46can be changed at runtime to adapt to the changes in the source systems in a silent-mode of operation without any human intervention by the AI engine70. The AI engine70could be a separate from the ETL engine44and BI engine80, or could combined with one of the ETL engine44or BI engine80, for example.

Of course, although XML is mentioned above, it is understood that this is a non-limiting example, and that other types of markup languages (or even text files) could be used for ETL engine44configuration files. Some third party ETL tools that could be used as the ETL engine44include the one from PENTAHO (mentioned above), and one from JASPER REPORTS. Alternatively, a custom built ETL engine44could be used.

The DSS20also includes one or more semantic layers50each comprising a respective mapping52of fields from the data warehouse40to business intelligence (BI) reporting fields. Presentation layer60creates BI output based on the BI fields in the semantic layer(s), such as charts62, reports64, and dashboards66. The DSS also includes the AI engine70and a BI engine80. Although the singular term “semantic layer” is used below, it is understood that a DSS20may include a plurality of semantic layers, and that the semantic layer updates described below may be performed for multiple semantic layers of a given DSS in some embodiments.

The AI engine70is a software program that manages the sequence of actions to take while the DSS20is under operation. This includes handling what operations need to be done for various changes to the set30of data sources. The AI engine70includes a plurality of AI models. In particular, it includes a secondary AI model (i.e., fail-safe AI model76), and a primary AI model (e.g., one of a hard coded construct (HCC) AI model71, a rule-based AI model72, or a case-based AI model74). Which of the AI models71,72,74is used by the AI engine70as the primary AI model may depend on the complexity of the DSS20being built. For a simple DSS implementation where the number of data sources in the set30is low and all the upgrades to the source schema follow fixed rules, the AI engine70may use the HCC AI model71, which may include relatively simple if-then statements. One example HCC could correspond to IF a new table is added to one of the data sources30, THEN copy the new table in its entirety to the data warehouse40AND in a dimension table in the data warehouse40that corresponds to the new table include a reference key column for the new table.

In the case of a more complex DSS20, the AI engine70would also need to be more complex, and to include more involved AI features such as supervised learning algorithms that are trained on patterns of changes to the sources, addition of new sources, etc. In these more complex cases, a rule-based AI model72or a case-based AI model74is more likely to be needed.

As an illustrative example of AI models, consider a weather prediction model. Assume that there are three possible weather predictions that can be predicted: sunshine, rain, or snow. Assume that the inputs to the model include temperature, wind speed, and humidity. If one wanted to write AI for a single city, one could generalize relatively easily and create a few IF THEN ELSE statements to predict the output. For the single city example, the HCC AI model71could be appropriate because the outcomes and the inputs have very fixed possibilities. However, if one wanted to write and AI for a country or the entire world, then there would be a very large number of input possibilities. In such cases, it is not humanly possible to generalize a few IF THEN ELSE statements. In these cases a supervised learning model would be more appropriate, as it could be trained over the input samples and based on that training a program could be built that provide predictions. Whether to use the rule-based AI model72or the case-based AI model74is dependent on the skew in the input sample data. Rule-based models are weak against skew but run faster, whereas case-based models are robust in skewed conditions but perform slowly. “Skew” in this context refers to a disparity in the quantity of various types of historical data. Using the weather example above, if there were thousands of cases of sunshine and rain but only a handful of cases of snow that would be considered skew.

Referring again toFIG. 1, the BI engine80handles data visualization, data retrieval, data querying, and data security aspects of the DSS20. The BI engine80can be a custom application, or can be based on commercially available software like SAP BUSINESS OBJECTS, JASPER REPORTS, etc., which expose BI APIs that can be used by the AI engine70to alter/enhance BI content. As an illustrative example of how business logic could be applied to data in the data warehouse40, in some embodiments business logic could be used to transform source transactional data into aggregated data (e.g., individual sales invoices could be aggregated to monthly sales for a plurality of sales locations).

The presentation layer60includes an associated user interface (UI) frontend that enables layperson users to administer/manage the DSS20without the need for BI or ETL subject matter expert (SME) teams. Since the DSS20updates itself automatically and autonomously based on changes to its set30of data sources, users may not agree with the automatic updates. In one or more embodiments, the UI enables users to command/override the behavior of the AI engine70by rolling back or modifying changes that were automatically made. For example, addition of a new key performance indicator (KPI) measure would give users an option of specifying whether it is an additive/semi-additive/non-additive measure and the AI engine70will carry out the next steps for population to the data warehouse40. For example, if a new column is added to one of the data sources, and the AI engine70correspondingly creates a new KPI for that measure that sums an item from the column, a user may choose to override that determination to an average instead of a sum. Users can manually override/command the AI engine70to correct such mistakes. The UI frontend makes the DSS20easier to manage such that even layperson users can administer aspects of the DSS20.

The AI engine70keeps track of changes that are made in the data warehouse50(via the ETL engine44) and translates those changes into relevant API calls to update the semantic layer50and possibly also BI content of the presentation layer60. This can make BI content resilient with respect to data warehouse40and data source updates. This entails automatic modeling of the semantic layer50, and making newly-added fields reportable in the semantic layer50. The automatic modeling of the semantic layer50involves including newly added fields and tables to the semantic layer50, so that they are referenced automatically. The term “automatic modeling” refers to automatically resolving the relationships and cardinality of the new fields, which in the prior art has been performed through manual examination of the logical schema of a given data source. Making the new fields reportable involves making the fields available for the customers to create reports (in presentation layer60) out of the semantic layer50for ad hoc reporting purposes. In some embodiments, existing reports also can be automatically modified to include the fields that are added/deleted/modified by the changes done in a changed data source.

In prior art ETL systems, minor changes to data sources could cause a variety of problems. For example, assume that one of the data sources in the set30included a table “PROJECT_TABLE” having the following fields:

Project ID

Project Name

Start Date

End Date

Financial Status

Department

Location

As part of this example, assume that the field “508 Compliance” was deleted from the data source in question. In the prior art, without the intervention of an ETL expert to reconfigure pending ETL jobs, this change could cause ETL logic in a pending ETL job to fail, such that no ETL jobs would run at all, and data in the DSS would quickly become stale.

As another example, assume that there is a legal mandate on an organization to add a new field named “HIPAA_COMPLIANCE_STATUS” on all projects that are being executed. If this field was added to the data sources of a DSS, but pending ETL jobs were not configured to address it, then the data of this field would be omitted from the data warehouse40entirely in the prior art.

The improved DSS20disclosed herein responds automatically to changes to its data sources by updating its ETL engine44and semantic layer50. In one example, if a new data source is added to the set30, the AI engine70checks the metadata of the updated data source either from a configuration table or by settings in a UI of the DSS to understand what changed in the schema of the source, and starts updating the data warehouse40based on predefined rules set to handle new data source additions. The AI engine70, upon detecting a new source, will make changes to the ETL jobs46or create new ETL jobs46to update the data warehouse40. After this, the AI engine70could make corresponding changes to the semantic layer50and optionally also the presentation layer60by passing instructions to the BI engine80.

In another example, if a data source is removed from the set30of data sources, the AI engine70would implement the appropriate set of operations to unhook the mappings between the dropped data source and the data warehouse40by passing instructions to the ETL engine44or directly modifying the ETL jobs46of the ETL engine44. After this, the AI engine70may alter the semantic layer50and optionally also the presentation layer60by interacting with the BI engine80.

In another example, assume that a schema of an existing data source changes in that a new column is added. When a new column in a data source is added, the AI engine70handles this change by making the appropriate changes to the ETL jobs46directly or by passing instructions to the ETL engine44. The AI engine70also updates the data warehouse40schema to handle the new additions. The AI engine70also updates the semantic layer50and optionally also updates BI content of the presentation layer60by, e.g., using exposed BI APIs or by sending appropriate instructions to the BI engine80.

In another example, assume that a schema of an existing data source in the set30changes in that an existing column is deleted. When an existing column is dropped, the AI engine70will take the necessary steps to modify the ETL jobs46, data warehouse40schema, and optionally also the BI content (e.g., reporting templates) of the presentation layer60. This can be done directly or indirectly by sending the instructions accordingly to the ETL engine44and BI engine80. If there are changes to the structure of the existing columns, these could be handled in the same way.

In the examples discussed above, and others discussed throughout this application, the presentation layer60updates are described as being optional. This is because users may not want their reports automatically updated. For example, reports may still be desired for historic data from a deleted data source. In other instances, such as when a field of a table is modified, then it is more likely that an automatic update of the presentation layer60would be desired (e.g., of the reporting templates that provide the charts62, reports64, and/or dashboards66for displaying BI content).

FIG. 2illustrates an example method100for automatically updating a DSS20based on a detected change in a set30of data sources of the DSS20. The AI engine70monitors for changes in its associated set30of data sources (block102). This could correspond to an active monitoring (e.g., periodic parsing metadata of the set30of data sources), or could correspond to a passive monitoring (e.g., waiting to receive a change notification from a monitoring agent). The change could include addition of a new data source to the set30, removal of an existing data source from the set30, or a schema change of an existing data source in the set30, for example.

If no change is detected (a “No” to block104), then the ETL engine44executes its ETL jobs46without updating the ETL jobs46(block106). However, if a change is detected by the AI engine70(a “Yes” to block104″), then the AI engine70automatically determines DSS updates to implement based on the detected change (block108). Based on this determining, The AI engine70sends instructions to the ETL engine44and the BI engine80(block110). The ETL engine44receives its instructions, and correspondingly updates a schema of the data warehouse40(block112). The schema update could include the ETL engine44executing Data Definition Language (DDL) commands, for example.

Based on the instructions received from the AI engine70, the ETL engine44updates, without canceling, it pending ETL jobs46and then executes the ETL jobs46(block114). In an alternate embodiment, the AI engine70could directly modify the ETL jobs46instead of doing so indirectly via the ETL engine44. The BI engine80, based on its instructions from AI engine70, updates the semantic layer50(block116), and updates the presentation layer (block118). These updates are automatically performed so that the DSS20can dynamically update itself based upon changes to its set30of data sources. In some embodiments, the update of the semantic layer50and/or presentation layer60is performed by using the exposed APIs of the BI engine80of the DSS20. Unlike the prior art, where a data source update could cause a DSS to omit data or simply stop operating, the method100ofFIG. 2implements dynamic DSS updates to avoid these issues.

FIG. 3illustrates an example method150which incorporates aspects of the method100ofFIG. 2. In particular,FIG. 3illustrates example implementations of blocks102-110ofFIG. 3(see notation along right side ofFIG. 3). The method150ofFIG. 3is implemented by the AI engine70. The AI engine70determines a change to the set30of data sources (e.g., addition of a data source to the set30, removal of an existing data source in the set30, and/or a schema change of an existing data source in the set30) (block152). Based on the determined change, the AI engine70attempts to determine DSS updates using a primary AI model (e.g., AI model71,72, or74) (block154). If that attempt is successful and updates are determined using the primary AI model which have a confidence metric above a predefined threshold (a “Yes” to block156), the AI engine70sends DSS update instructions to the ETL engine44and BI engine80based on the determined changes (block158). Otherwise, if updates cannot be determined using the primary AI model, or if updates are determined but they have a confidence metric that is below the predefined threshold (a “No” to block156), then the AI engine70determines alternate DSS updates using a secondary AI model (the fail-safe AI model76) (block160), and sends corresponding DSS update instructions to the ETL engine44and BI engine80based on the alternate DSS updates (block162).

To determine potential DSS updates (block154), the hard coded construct (HCC) AI model71, rule-based AI model72, or case-based AI model74will be used. These AI models are trained on data related to the most common or expected data sources. The HCC AI model71(which uses programming logic in place of supervised learning algorithms) is most useful in data warehouses where the range of changes is finite and known. This is most useful for smaller and/or simpler data warehouses where the sources are fixed and only minimalistic changes are expected. The HCC AI model71can work very well with single source data warehouses (e.g., such as the one used by “CA PPM,” formerly known as “CA CLARITY PPM,” from CA TECHNOLOGIES).

In data warehouses that include data from a single data source, the customizations that customers may implement are more predictable, which lends itself well to the rule-based and/or case-based AI models72,74. For example, if the data warehouse40uses a “dimensional” data model and a new attribute is added on the table “investment”, then a rule or case could add that attribute to all the investment-related dimensions. As another example, if the data warehouse40uses a satellite-hub data model and a new column is being added to table “SRM_RESOURCES,” then it is likely that this new column should go to the satellite of the hub that contains a RESOURCE_ID from past cases for the DSS20(where the RESOURCE_ID is a unique identifier for the SRM_RESOURCES table in the source database). In some instances, as discussed above, rules and/or cases can be hardcoded for simpler data warehouse40using the HCC AI model71.

An example implementation of blocks154and164of the method150will now be discussed in which the primary AI model is the rule-based AI model72. For this discussion, assume that a new field named “HIPAA_COMPLIANCE_STATUS” has been added to a table named “PROJECT_TABLE” in one of the data sources in the set30. The AI engine70detects this change to the set30of data sources (block152), and based on that detection attempts to determine DSS updates using the rule-based AI model72(block154). In particular, the AI engine70determines whether it has a corresponding rule that applies to the change. This determination may be based, for example, on any combination of the following criteria:a data model of the data source containing “PROJECT_TABLE” (e.g., satellite hub modeling or dimension modeling);a table type of “PROJECT_TABLE” (e.g., transaction or lookup);a change made to the data source containing “PROJECT_TABLE” (e.g., table addition, table deletion, or table modification);a data type of a field that was added (e.g., decimal, string, etc.); anda parent table of “PROJECT_TABLE”

Of course, it is understood these are only example criteria could be used, and that alternate criteria could also be used.

Some example rules for the rule-based AI model72are shown in tables 1-4 below. In these example rules, the symbol “II” represents concatenation, and “DWH” refers to the data warehouse40.

Tables 3-4 below illustrate additional example rules for the rule-based AI model72. In particular, Table 3 illustrates the rule of Table 1 applied to the addition of “HIPAA_COMPLIANCE_STATUS” in which the DSS20uses a “dimensional” data model, and Table 4 applies to the addition of “HIPAA_COMPLIANCE_STATUS” in which the data warehouse40uses a “satellite hub” data model.

If a matching rule is determined using the rule-based AI model72, the AI engine70would send instructions to the ETL engine44and BI engine80based on the DSS updates (block164). In particular, the AI engine70would instruct the ETL engine44to:update, without canceling, its pending ETL jobs46that import data from “PROJECT_TABLE” to also include “HIPAA_COMPLIANCE_STATUS;” andupdate the data warehouse40so that tables including data from “PROJECT_TABLE” also include “HIPAA_COMPLIANCE_STATUS.”

The AI engine70would send instructions to the BI engine80by invoking BI engine APIs which instruct the BI engine80to:update semantic layer50so that “HIPAA_COMPLIANCE_STATUS” in the data warehouse40is mapped to a BI field available to BI users; andupdate reports that include data from “PROJECT_TABLE” to also include “HIPAA_COMPLIANCE_STATUS.”

In some embodiments of the AI engine70, a decision tree is used to assist in the determining of which rule to apply in the rule-based AI model72based on the criteria for rule selection (e.g., table type, data type, source change, data model).FIG. 4illustrates an example decision tree that incorporates the following criteria: data model (182), source table type (184), and source change (186). If the data model is “dimensional,” and the source table type is “lookup,” a plurality of specific rules188A-E are mapped to the different types of source changes186. Each rule may have an associated lift, support, and confidence value. To use the analogy of the weather prediction model, “support” could refer to how many cases were available, “confidence” could refer to how many times a given set of input values resulted in snow, and “lift” could be indicative of data skew (i.e., one outcome having considerably less cases than other outcomes). Although not shown, rules of the rule-based AI model72could also be mapped to the other source changes and table types.

Hardcoded rules in the HCC AI model71are generally used in simpler DSS implementations. In such implementations, the rules could be either coded (e.g., as JAVA or C control structures or could be maintained in a table, for example. For a supervised learning algorithm (e.g., the rule-based AI model72), rules may be made available as a decision tree (seeFIG. 4). For the case-based AI model74, outcomes may be decided using a Naïve Bayes algorithm or k-nearest neighbor algorithm, for example. A difference between supervised learning algorithms (e.g., rule-based and case-based) and the HCC AI model71is that the supervised learning algorithms are trained on historical data, whereas the hard coded constructs of the HCC AI model71are encoded manually based on experience and known aspects of the data warehouse40and its set of data sources30. Manual updates to the HCC AI model71could be performed base on user feedback to add/correct the HCC model's rules, whereas updates to the supervised learning algorithms would be more automated.

Referring again toFIG. 3, an example implementation of blocks158-162and166-168of the method150will now be discussed in connection with the case-based AI model74and the fail-safe AI model76. The case-based AI model74uses case-based reasoning. It contains a case history and “feature set” based on historical DSS updates and the attributes of the set30of data sources (e.g., tables, columns, etc.). The feature set serves as criteria for case selection in the case-based AI model74and could include, for example, any combination of the following:

data type of column;

table of the column

usage pattern

Of course, these are only example criteria, and it is understood that other criteria could be used.

The dependent variable of the case-based or rule-based AI model is the change that needs to be implemented in the data warehouse40(e.g., add/alter/drop from dimension/fact/satellite/hub tables). The final dependent variable is chosen based on a confidence metric that represents the support for a given case (see block156inFIG. 3). The confidence metric is indicative of how likely it is that the determined outcome is a correct one. For the rule-based AI model72, the confidence metric may be based on any one of or combination of lift, support, or confidence. For the case-based AI model74, the confidence metric can be determined based on case probabilities (as discussed below in connection withFIGS. 5A-C). The case-based and rule-based AI models adapt to historical DSS updates, such that when a change is triggered in the data sources, the case-based AI model or rule-based AI model can determine an outcome that has the strongest basis in historical DSS update data (e.g., determining a number of cases that match the change, and selecting an outcome as the dependent variable that has the most occurrences in the matched cases).

FIGS. 5A-Cillustrate an example application of a Naïve Bayes case-based AI model.FIG. 5Aillustrates a table200that lists a plurality of samples202of historical DSS update data, each of which has an associated data model204, source table type206, source change208, and output210(i.e., DSS update). Using Naïve Bayes techniques, the plurality of cases in the table200are converted into a reverse probability table220(seeFIG. 5B).

The concept of the reverse probability table can be understood in the context of the weather prediction model discussed above. Assume that there are three possible weather outcomes: sunshine, rain, or snow. Also continue to assume that the inputs to the model include temperature, wind speed, and humidity. The reverse probability table220could be used to determine the probability of the temperature being “X” when the outcome was snow. In the example of record222A ofFIG. 5B, the reverse probability 0.924908343 indicates the probability of the data model being “dimensional modeling” when the output (column210ofFIG. 5A) is “alter table add column.”

FIG. 5Cis derived fromFIG. 5B, and illustrates probabilities of given outcomes occurring based on certain inputs. Stated in the context of the weather predictor model,FIG. 5Cwould indicate the probabilities for: sunshine, rain, or snow occurring. Referring now to the actual data ofFIG. 5C, what is shown is the probability of various DSS updates being performed based on a source change of “input columns.” The highest probability value is selected (which in this case is 0.412—the second row ofFIG. 5C). For the case-based AI model74, the confidence metric could be a probability value as shown inFIG. 5C, and the threshold to which it is compared could be a probability threshold, for example.

Referring again toFIG. 3, if the case-based AI model74or rule-based AI model72yields DSS updates having a confidence metric that exceeds the predefined threshold (a “Yes” to block162), then the DSS updates determined using the case-based AI model74or rule-based AI model72are implemented (block164). Otherwise, if the case-based AI model74or rule-based AI model72yields DSS updates having a confidence metric below the threshold, then the fail-safe AI model76is used to determine alternate DSS updates (block166).

The fail-safe AI model76can include hard code constructs and/or may be further based on a supervised learning algorithm (e.g., rule-based or case-based). Which one of the hardcoded construct or a supervised algorithm is used for the fail-safe AI model depends on the possible outcomes. For example, a hardcoded construct based model may be used if one does not want to have varied outcomes based on exceptions. Consider the following example. assume that one does not want to take many actions due to some source changes for which DSS updates determined using the primary AI model yielded a very low confidence metric. If the input change is addition of a table that does not have references in any existing tables in the data warehouse40, then the fail-safe AI model76may use hardcoded constructs to just copy that table as is to the data warehouse40. Similarly, if a new source is added which is a folder of flat files, the fail-safe AI model76may try to parse the files as comma separated values and load those files to the data warehouse40.

In some instances, it may be desirable for the fail-safe AI model76to use a supervised learning model where an evolving fail-safe model is desired which constantly learns based on the past data. Assume that in such an embodiment a new source folder of files was added and the primary AI model failed to predict an outcome. Based on this failure and based on initial learning data, the fail-safe AI model76may predict that the files should be treated as comma separated values. A system user may then correct this to treat the files as tab separated values using a feedback UI. This feedback would be incorporated as training data of the fail-safe AI model76which could then be used to periodically retrain the fail-safe AI model.

In some embodiments, the fail-safe AI model76may be a complex AI model which uses a collection of lower level AI models that predict an outcome with a confidence metric for complex DSS or a simple exception handler for a simple DSS. The multiple lower level AI models could be used to predict (possibly simultaneously) DSS updates in different ways. For example, different subsets of input columns could be assigned to respective lower level AI models to predict outcomes based on those input columns. Then, a master algorithm (i.e., meta-learner), which itself is a supervised learning model, could determine a DSS update based on the original input columns at issue and the outcomes of the lower level algorithms. In other words, the fail-safe AI model need not be one single algorithm, it could be a collection of few simple algorithms whose results intermediate results are then consolidated and orchestrated by the master algorithm/meta learner to determine DSS updates. In such embodiments, this entire group of algorithms for the fail-safe AI model76could be collectively referred to as a “complex AI model.”

This differs from the rule-based AI model72and case-based AI model74which are less complex. With the fail-safe AI model76, the final outcome is chosen from the model with the highest confidence, or by a stacked AI model with a meta-learner model. The fail-safe AI model76may have a collection of models that work on features derived from data warehousing fields like source table, source table type, source type, source column, source column data type, levels in source column, source table/column usage statistics from a database, etc. Features for these models may be extracted from various inputs such as database usage statistics, data source metadata, training data from previous data warehouse projects, etc.

The collection of lower level models are a collection of supervised and/or unsupervised machine learning algorithms based on the requirement. If the data warehouse40is supposed to target complex use cases (i.e., complex DSS updates), then unsupervised models like clustering could be preferable. If the requirements are simpler, then a simple decision tree may be more appropriate for predicting the decision outcome for the change. Multiple classifications models can be used, one for each table, so that the dependent variable that is being predicted predicts what action is to be taken for a change in the set of data sources30based on the model corresponding to the table in which the change occurs. On top of classification/prediction, clustering can help identify all the columns that belong in a table in the data warehouse40(e.g., so that each column becomes an entity which needs to be clustered into a group).

For simpler data warehouses40, the AI engine70may chooses the final decision based on a confidence metric of its lower level AI models. As discussed above, stacked AI models with a meta-learner may be used to improve accuracy over time and for complex data warehouses. The meta-learner model learns over time on the lower models and improves accuracy with time. For highly simple but concise data warehouses reinforcement learning can be used to train the AI to take decisions in the AI engine.

FIG. 6illustrates a computer-implemented method300that is implemented by the AI engine70. The method300is a broader formulation of what is described inFIGS. 2-3above. The AI engine70detects a change in a set30of data sources of DSS20(block302). The change comprises addition of a new data source to the set30, removal of an existing data source from the set30, or a schema change of an existing data source in the set30. Some example schema changes of an existing data source could include, e.g., column addition, column addition, column renaming, etc. The AI engine70automatically determines a first set of DSS updates for pending ETL jobs46of ETL engine44of the DSS20(block304) based on the change in the set of data sources. The pending ETL jobs46import data from the set30of data sources into a data warehouse40. The AI engine70automatically updates, without canceling, the pending ETL jobs46of the ETL engine44based on the first set of DSS updates (block306). The AI engine308also automatically determines a second set of DSS updates for semantic layer50of BI engine80of the DSS20based on the change in the set of data sources, and based on the first set of DSS updates (block308). The semantic layer50comprises a mapping of fields from the data warehouse40to reporting fields of the BI engine80. The AI engine70automatically updates the semantic layer50of the BI engine80based on the second set of DSS updates (block310). Thus, in the method300the “first set” of DSS updates are for the ETL engine44, whereas the “second set” of DSS updates are for the BI engine80. Each “set” of updates could include multiple updates, or a single update.

In some embodiments of the method300, the automatic updating of the pending ETL jobs46of the ETL engine (block306) comprises automatically transmitting DSS reconfiguration instructions to the ETL engine44that instruct the ETL engine44to update the pending ETL jobs46without cancelation of the pending ETL jobs46. Such embodiments may occur where the AI engine70and ETL engine44are hosted on separate computing devices. In other embodiments, the AI engine70directly modifies the ETL jobs46instead of doing so indirectly through the ETL engine44.

As discussed above, the ETL engine44may be configured to execute its pending ETL jobs46in a configuration inflation mode of operation.

In some embodiments of the method300, the AI engine70automatically transmits schema update instructions to the ETL engine44based on the first set of DSS updates, wherein the schema update instructions instruct the ETL engine44to update a schema of the data warehouse40to reflect the change in the set30of data sources. In some such embodiments, the schema updates of the data warehouse40would occur before corresponding ETL jobs that import data according to that schema are executed. Thus, for example, if a new column was being added, the ETL engine would, in some embodiments, update the schema of the data warehouse40to reflect the addition of the column before importing data contained in the new column into the data warehouse40.

In some embodiments of the method300, automatically updating the semantic layer50of the BI engine80(block310) includes automatically invoking an API of the BI engine80to cause the BI engine80to update its semantic layer50.

In some embodiments of the method300, automatically updating the semantic layer50of the BI engine80yields an updated semantic layer, and the method300includes automatically invoking an API of the BI engine80to cause the BI engine80to update, based on the updated semantic layer, its existing report templates that comprise data from data repository42in the data warehouse40.

In some embodiments of the method300, automatically determining the first set of DSS updates (block304) for pending ETL jobs46of the ETL engine includes analyzing historical DSS updates performed for previous changes to the set30of data sources; attempting to determine first potential DSS updates using a primary AI model; and if the attempting identifies first potential DSS updates that have a confidence metric that is above a predefined threshold, including the first potential DSS updates in the first set of DSS updates. In this regard the “primary” AI model could include the rule-based AI model72or case-based AI model74.

If the attempting to determine first potential DSS updates fails, or if first potential DSS updates are determined but a confidence metric of the first potential DSS updates is below a predefined threshold, the AI engine70uses a secondary AI model (e.g., the fail-safe AI model76) to determine second potential DSS updates, and includes the second potential DSS updates in the first set of DSS updates.

In some embodiments of the method300, the data source that is added, removed, or has its schema changed is a table, and the automatic determining of the first set of DSS updates (block304) based on the change in the set of data sources comprises:determining a DSS update rule for the change in the set30of data sources as a function of a data model of the data warehouse, a table type of the table, and the change that was detected in the table; anddetermining the first set of DSS updates (block304) based on the DSS update rule.

The detecting of the change in the set of data sources (block304) may include analyzing metadata of the set30of data sources of the DSS20, and detecting the change in the set30of data sources based on a change in the metadata of the set30of data sources. In some embodiments, the AI engine70receives an alert that specifically indicates what data source has changed and/or what that change is. In other embodiments, the AI engine70does not receive such alerts, but must instead proactively monitor the set30of data sources for changes (e.g., periodically queries them to detect if they have been changed).

FIGS. 7A-Dillustrate a plurality of example hardware configurations for AI engine701. InFIG. 7A, the AI engine70, BI engine80, and ETL engine44each reside on separate servers90. In the example ofFIG. 7B, however, the AI engine70and ETL engine44reside on a single server90, which is separate from a computing device90on which the BI engine80resides. As shown inFIG. 7B, the AI engine70may be part of the ETL engine44. In some such embodiments, logic of the AI engine may be embedded into the ETL jobs themselves (e.g., if-then-else constructs of the HCC AI model71could be included in the ETL jobs46). Other configurations could also be possible. For example, in so-called data federation embodiments in which runtime ETL is provided from the BI engine80(i.e., the BI engine80acts as the ETL engine), a same computing device may act as the BI engine, AI engine, and ETL engine.

FIG. 8illustrates a computing device400configured as the AI engine70ofFIG. 1. The computing device400may be configured to implement any combination of the techniques discussed above. The computing device400includes a communication interface404and one or more processing circuits (shown as “processing circuit”402) that are communicatively connected to the communication interface404. The communication interface404is configured to communicate with ETL engine44of a DSS20, BI engine80of the DSS20, or both the ETL engine44and the BI engine80. The processing circuit402is configured to detect a change in a set30of data sources of the DSS20, with the change comprising addition of a new data source to the set30, removal of an existing data source from the set30, or a schema change of an existing data source in the set30.

The processing circuit402is further configured to automatically determine a first set of DSS updates for pending ETL jobs46of the ETL engine44based on the change in the set30of data sources, wherein the pending ETL jobs46import data from the set30of data sources into data warehouse40. The processing circuit402is further configured to automatically update, without canceling, the pending ETL jobs46of the ETL engine44based on the first set of DSS updates. The processing circuit402is further configured to automatically determine a second set of DSS updates for semantic layer50of the BI engine80based on the change in the set30of data sources and based on the first set of DSS updates. The semantic layer50comprises a mapping of fields from the data warehouse40to reporting fields of the BI engine80. The processing circuit402is further configured to automatically update the semantic layer50of the BI engine80based on the second set of DSS updates. As discussed above, the AI engine70may be executed by its own server, or may share a server with one or both of the ETL engine44and BI engine80.

The computing device400also includes a memory circuit406which is a computer readable storage medium that stores instructions for operation of the AI engine70. The memory circuit406may also store data describing historical DSS updates performed for previous changes to the set30of data sources, which could serve as the basis for the utilization of the case-based AI model74and/or fail-safe AI model76.

In one or more embodiments, the memory circuit406includes a computer program product408. The computer program product406comprises a computer readable storage medium (e.g., memory circuit406, or some other storage device) having computer readable program code embodied therewith, the computer readable program code comprises:computer readable program code configured to detect a change in a set30of data sources of DSS20, the change comprising addition of a new data source to the set30, removal of an existing data source from the set30, or a schema change of an existing data source in the set30;computer readable program code configured to automatically determine a first set of DSS updates for pending ETL jobs46of an ETL engine44of the DSS20based on the change in the set of data sources, wherein the pending ETL jobs46import data from the set30of data sources into a data warehouse40;computer readable program code configured to automatically update, without canceling, the pending ETL jobs46of the ETL engine44based on the first set of DSS updates;computer readable program code configured to automatically determine a second set of DSS updates for a semantic layer50of BI engine80of the DSS20based on the change in the set30of data sources and based on the first set of DSS updates, wherein the semantic layer50comprises a mapping of fields from the data warehouse40to reporting fields of the BI engine80; andcomputer readable program code configured to automatically update the semantic layer50of the BI engine80based on the second set of DSS updates.

The embodiments discussed herein offer a number of improvements over the prior art. The DSS20described above is intelligent, resilient, and self-governing in that it automatically adapts to changes in its source systems (i.e., data sources). Enhancements to a data warehouse (e.g., new dimensions, measures, utilities) are automatic or at least wouldn't require substantially less efforts than these updates would have required in the prior art. It is believed these features will lead to reduction of DSS support and maintenance activities to negligible levels. Also, the techniques described above may be utilized such that BI components are automatically up-to-date for a longer duration of time, which can justify their huge licensing costs. The down-time for data source upgrades and enhancements could be reduced or possibly even non-existent. Thus, DSSs, which have traditionally been difficult to maintain, can be made much more agile. The techniques discussed above could also make it more feasible to have ETL and BI features be cloud-based and/or software as a service (SaaS) based, which traditionally has not been the possible due to the highly customized nature of DSSs. However, using the techniques described above, a DSS can be made more self-governing, and it would be feasible to have a software-as-a-service (SaaS) model so that a DSS for an organization could be cloud based and/or could be maintained and distributed as a service.

The DSS automatic updating techniques discussed above could also be used in connection with schema and data migration between databases (e.g., by tweaking a DSS20into thinking that one of the databases is a data source, and that the other of the databases is a target database). Thus, the techniques described above could be used, e.g., to migrate an ORACLE database to a SQL SERVER database.

In some of the embodiments discussed above, the DSS20may provide a rollback/undo feature which would enable a user to undo an automatic change to the DSS20that is undesired. For example, if the fail-safe AI model76resulted in DSS updates that were incorrect, this rollback/undo feature would enable a user to undo the changes, such that the correct change could be implemented. These errors would be fed to the fail-safe AI model76to further train the model76.

Thus, the foregoing description and the accompanying drawings represent non-limiting examples of the methods and apparatus taught herein. As such, the present invention is not limited by the foregoing description and accompanying drawings. Instead, the present invention is limited only by the following claims and their legal equivalents.