Execute Tables

Executing tables performs all the steps needed to transfer and cleanse data into tables that have already been deployed. Deploy and Execute are two separate processes that are commonly performed together via the Deploy and Execute command. Together they create and populate tables in the SQL database.

However, Deploy and Execute can also be run separately — each process has its own options and circumstances. After data area changes have been deployed, execution can be performed to transfer and cleanse the data.

Execution of tables

Execution loads data into the selected area using the following steps:

1. By default, Jet Analytics Data Integration truncates the Raw table. This behaviour can be disabled under Table Settings > Data Extraction.
2. Transfer data from Ingest instance storage to Raw tables. Data selection rules and incremental loading rules apply during bulk loading and transfer. The transfer is performed using one of the following methods:
   1. If the Prepare instance is configured to use Azure Data Factory (ADF) for the transfer, ADF is responsible for the data transfer. When multiple tables are being transferred, ADF may use the Data Factory Merge option to perform all transfers in a single step.
   2. If ADF is not configured, ADO.NET performs the data transfer. Because ADO.NET runs on the server where the Jet Analytics Ingest Service is running, the state of that server can affect execution performance.
3. Transfer data from one data area to the Raw table of another data area. This is done using the Direct Read method, which uses stored procedures to transfer data internally within the database.
4. If enabled, Custom Data is inserted into the custom data table using the _CustomDataFill stored procedure.
5. By default, Jet Analytics Data Integration truncates the Valid table. This can be configured under Table Settings > Data Extraction. Table truncation must be disabled if any of the following are enabled:
   1. Simple Mode
   2. History
   3. Incremental Loading
6. Data Cleansing — validates data against business rules and moves validated data to the Valid table. Status information is generated at this point. The specific processing steps are:
   1. Disable indexes on the Valid table.
   2. Process Incremental Loading.
   3. Process conditional lookups and insert values into the Raw table.
   4. Apply all transformations through the Transformation view (_T).
   5. Log violations of data cleansing rules. Violations — including primary and foreign key failures and custom field validations — are logged by inserting the DW_ID of the violating row into the List table (_L), which references the associated error message in the Messages table (_M).
   6. Implement all functions required for History.
   7. Load all records from the Transformation view into the Valid table.
   8. Rebuild table indexes as needed.
7. Batch Data Cleansing — for large data sets, breaks the data cleansing steps into batches to improve performance.
8. Verify data against checkpoints — checks the data being processed against any configured checkpoints. Checkpoints are validation rules that halt execution when rules are not met, which can prevent a problematic execution from overwriting current data in the Prepare instance with unreliable data.

The Deploy and/or Execute dialog

The following dialog appears when clicking Execute or Deploy and Execute at either the table level or the Prepare instance level.

When executing at the Prepare instance level, the Affected objects section offers only All — all objects in the data area will be executed.

When executing at the table level, the Affected objects section offers three options:

1. All — executes all steps in the selected area.
2. Only modified tables and views — executes only those steps required for deployment. Works best when both Differential and Managed deployment are selected, as only the tables required for execution will be included.
3. Only work items — executes only objects that are marked as work items. Even if other tables also need to be deployed, only work item objects will be executed.

The Send to Execution Queue option pushes the execution to the Execution Queue, allowing the user to continue working while the execution runs in the background. See the next section for details.

Executing objects with the Execution Queue

The Execution Queue enables users to continue working while execution of tables or the entire instance runs in the background. To open the Execution Queue window:

1. Start an execution and check the Send to execution queue box in the Deploy and Execute dialog.
2. On the Tools menu, click Execution Queue.

Add an object to the Execution Queue

Drag a table, perspective, execution package, or other executable object into the Execution Queue window. After dropping the object into the Queued area, a window appears allowing the selection of which execution steps to add to the queue.

Select Add all steps or Add selected steps, choose the steps to include, and click Add. The object is now queued. If no other items are in the queue, execution begins immediately and the object moves to In Progress.

Pause and resume the queue

Click Pause to prevent further objects from being executed. Pausing does not stop an object that is currently in progress. Click Resume to resume executing the queue. The button toggles between the two labels.

Move and remove queued items

The Queued list shows items waiting to be executed. Select an item and use Move up or Move down to reorder it — the top item is next to be executed. Click Remove to remove a selected item, or Clear to remove all queued items.

Stop current execution

In Progress shows the object currently being executed. Click Stop to halt execution of the current object and pause the queue.

Review completed items and errors

The Completed list shows all executed objects with their Status, Duration, and Start Time. Completed items can have one of three statuses:

• Success: The object was executed without errors.
• Failed: Execution ended prematurely due to an error.
• Stopped: Execution was stopped by the user before it completed.

Select a failed item and click View Error to open the error message and review the related error information.

Close the Execution Queue window

Click the X in the top right corner to close the Execution Queue window. Closing the window — or closing the entire instance — does not stop or pause any queued executions. The queue continues running in the background. Re-open the Execution Queue window at any time to check the status of queued objects or add more items.

When Jet Analytics Data Integration is closed, the Execution Queue stops along with the rest of the application.

Guard on execution

The Guard on Execution option prevents a table from being executed. It can be enabled in the lower-left area of the Table Settings dialog.

Attempting to execute a guarded table will appear to start running, but once completed, a message indicating the table "Is Guarded" is displayed.

Guarding a table prevents it from being deployed and executed, which can help avoid overwriting data that should be preserved or that may no longer be available from the source.