Changes in Execute 21.1.239

Execute now includes the following functionality in beta (pre-release) form. This functionality is optional (off by default) and has not yet undergone our usual stringent QA process. We have released it so that interested customers can start exploring it, planning adoption, and providing feedback.

We do not recommend using these new capabilities in production at this time.

The following capabilities are available for testing.

1. Publishing Execute reporting-type data to a Snowflake database
2. Publishing Execute reporting-type data to a SQL Server / Azure SQL database
3. Creating Document Synchronizations and Data Selectors against a Snowflake database

Publishing to a Data Warehouse

For BI, reporting and warehousing purposes, Execute now supports publishing all Execute data (documents) to either a client-owned/managed Snowflake database, or a client-owned/managed public-facing SQL Server (Azure SQL) database.

To enable the publishing of Execute data to Snowflake…

1. Create a new empty database in your Snowflake environment
2. Copy plugins_available\integration\product_integrations\warehouse\non_production\snowflake.config to your plugins_available folder or add as a new in-app plugin, and restart Execute.
3. Create a new “Connection String” (Tools > Configuration > Connection Strings) called “Snowflake” with the credentials and information for your Snowflake environment.
4. Run “Tools > Synchronization > Snowflake Schema Publisher” to create tables and helper views in your Snowflake database. This same process would also be run any time you want to update the helper views to include newly created fields in Execute.
5. Run “Tools > Synchronization > Snowflake Document Publisher” to pre-populate your Snowflake database with your existing data (latest version of each non-deleted document). This would typically only ever be run once.
6. Execute will periodically and automatically push incremental updates to Snowflake. You can adjust this timing by changing the “Integration/Snowflake/Snowflake Queue Wait” configuration setting.

To enable the publishing of Execute data to SQL Server/Azure SQL…

1. Create a new empty database in your SQL Server/Azure SQL environment
2. Ensure that the SQL Server / Azure SQL database can be access directly from Execute (no VPNs, etc.)
3. Copy plugins_available\integration\product_integrations\warehouse\non_production\sqlwarehouse.config to your plugins_available folder or add as a new in-app plugin, and restart Execute.
4. Create a new “Connection String” (Tools > Configuration > Connection Strings) called “SQLWarehouse” with the credentials and information for your database.
5. Run “Tools > Synchronization > SQL Warehouse Schema Publisher” to create tables and helper views in your database. This same process would also be run any time you want to update the helper views to include newly created fields in Execute.
6. Run “Tools > Synchronization > SQL Warehouse Document Publisher” to pre-populate your database with your existing data (latest version of each non-deleted document). This would typically only ever be run once.
7. Execute will periodically and automatically push incremental updates to your database. You can adjust this timing by changing the “Integration/SQL Warehouse/SQL Warehouse Queue Wait” configuration setting.

For both Snowflake and SQL Server, the published data is:

1. A table called EXECUTE_DOCUMENTS with up-to-date semi-structured JSON-based representations of all records (documents) in Execute (Wells, AFEs, Users, Accounts, Schedules, Approval Rules, …). If a document is deleted, the corresponding record in this table will not be removed but will have its “deleted” field set to “1” / “TRUE”.
2. (Optional) Helper views which reformat the data from EXECUTE_DOCUMENTS as normal tables (closely resembling the underlying tables in Execute). These helper views are intentionally simplified and do not include deleted records, nor all the complex document versioning tables/fields (_DOC, _DOC_V, _H, …) that made querying the underlying tables complex.

This functionality requires the “OData” module be licensed for your in environment.

Data Selectors and Synchronizations against Snowflake

Execute now supports creating connection strings to Snowflake databases in the Connection String editor. These connection strings can be used by Document Synchronizations and Data Selectors.

I think it’s safe to say that administrators with the new Manage Execute Plugins administrator privilege will be overjoyed that they can now (optionally) manage Execute plugins directly in Execute. This is primarily targeted at Execute SaaS customers, but can be helpful for on-prem installations as well.

NOTE: Plugins found in the plugins folder will continue to work and there is no requirement to migrate some or all of your plugins to in-app managed plugins. If you choose to migrate plugins from the plugins folder to the in-app plugins it is CRITICAL that the migrated plugins in the plugins folder are removed prior to adding them to the in-app plugins list (for SaaS environments, this will require a support ticket). Loading the same plugin twice never yields good results.

Administrators can find the list of in-app managed plugins under Tools > Configuration > Plugins. This screen may look familiar at this point because in-app plugins are treated just like any other record in Execute. They even have change tracking!

Viewing a plugin will show details about the plugin, as well as give the opportunity to edit the “Content” for the plugin.

In addition, we’ve made some improvements to error handling. In most cases, when a plugin fails to load properly, Execute will skip that plugin and continue startup. The “Loaded Successfully” column on the plugins screen will identify any plugins that failed to load (they will also have a red status bar the the left of the plugin row).

It is important to note that plugins managed in this way are included in the database and will get copied as part of the database (for example, when copying PROD to TEST). This can potentially cause some issues where a TEST Execute environment is inadvertently attached to a 3rd-party production system. There are two mechanisms to avoid this:

1. Connection strings and credentials should be stored in Execute’s (new) connection string storage or external credential storage. These values are NEVER copied between environments, and this eliminates the risk of a PROD>TEST migration accidentally causing TEST data to leak into a downstream PROD system.
2. You can tag plugins with a “Plugin Environment” which can limit which environment a plugin is valid for (based on the Environment Label). If unspecified, the plugin will load in any environment. Environment Labels are the little badge next to the “Q” icon (PROD environments are usually blank) »

We hope you are as excited by this functionality as we are. It will hugely streamline the process for managing Execute plugins.

Database connection strings can be managed by an admin with the “Edit External Credentials” privilege under Tools > Configuration > Connection Strings. New connection strings can be defined here and then referenced in your plugin files (rather than embedding database connection strings right in the plugin).

This is now the preferred way to manage database connections made directly from Execute to another system (Integration Agent connections are still managed separately).

• Multiple plugins can refer to the same connection string instead of a separate copy in every file
• The connection strings can be edited without restarting the Execute service (plugin file changes still require a restart)
• Connections can be tested from the UI, rather than having to restart the service and trying to operate the plugin before learning if the connection actually works
• These connection strings still support the @@@ notation for using the separate credential storage feature. It is recommended to use that for passwords, to ensure they are securely stored.

Most plugins that contain a connection string, such as this:

<ConnectionString>account=abc123-abc123;user=secureuser;password=securepassword123;db=EXECUTE;SCHEMA=PUBLIC;WAREHOUSE=COMPUTE_WH</ConnectionString>
<ProviderType>SnowflakeProvider</ProviderType>

Can then be updated to refer to the defined connection string like so. Note: The “ProviderType” is now captured in the connection string and should be removed from the plugin file.

<ConnectionString>Connection List=Server; Id=Snowflake</ConnectionString>
<ProviderType></ProviderType>

If you are migrating a plugin to an in-app managed plugin, you must move the connection string out of the plugin and into the connection string storage.

Please feel free to reach out to Execute Support if you need some help updating your existing plugin files to use this new functionality.