Deploy tables

Deployment takes the table definitions that were created in the TimeXtender Data Integration and uses these to generate scripts that are then run to create the tables and their associated stored procedures in the data area storage.

“Deploy and Execution” are two separate processes that are commonly performed one after the other via the “Deploy and Execute” command, and together these two processes are used to create and populate tables in the SQL database.

However, Deploy and Execute are different processes that can be run separately, and each process has its own options and circumstances. Click on the following link for more information about executing tables. 

Deployment of the data area is needed to apply any new changes to the metadata of a Prepare instance. Deployment of changes can be done at different levels of the Prepare instance, so that changes may be applied in a more focused manner to just those parts of the instance that includes the changes. After any data area changes have been deployed, it is common to then execute the table, which will attempt to transfer and cleanse the data.

Deployment

Once a change has been made to a table, TimeXtender Data Integration uses visible cues, such as a red font and an asterisks (*) next to the object name, to highlight that those objects have changes that have yet to be deployed.

Deployment scope is determined by where or what level the deployment is initiated from.

1. To limit the deployment scope to a single table, right-click on a table that contains pending changes and select “Deploy” or “Deploy and Execute” from the menu.  

2. To deploy changes to an entire data area, right-click on a data area and select “Deploy” or “Deploy and Execute” from the menu. Deployment at this level will ensure that the changes to all the tables in that data area are deployed.

   

    

3. To deploy changes to an entire Prepare instance, right-click on the Prepare instance and select “Deploy” or “Deploy and Execute” from the menu. Deployment on the Prepare instance level will apply changes to all the objects within all the data areas that comprise the Prepare instance.

   

    

When deployment is done on the table level, then only the changes to that table are deployed, which might affect other tables that relate to the deployed table. In that scenario, deploying changes on the data area level may be better as it will deploy any changes that are needed to all the tables in the data area.

Tables may also be related to tables that reside in different data areas, and when that is the case deploying on the Prepare instance level may be the best option to ensure that all related tables are deployed.

The Deploy and/or Execute dialog

The following two deploy options are selected by default in the Deploy and/or Execute dialog.

1. Enable differential deployment
   Using this option will focus the deployment so that only those objects that have been changed will be deployed. In cases where a table is made up of objects from different sources, only those objects that have been changed will be deployed. Turning off differential deployment allows the process to perform all the deployment steps. Note that incremental loading still applies and is not affected by enabling or disabling differential deployment.

2. Enable managed deployment
   Managed deployment will deploy the changes in the order of relations. If no relations are present, then the deployment will be done from the top to the bottom of the data area.

Before starting the deployment, it is possible to see the specific tasks that make up the deployment by clicking the “Review Tasks” button. In the Deploy -> Tasks window, tasks can be unchecked or additional tasks may be checked to further customize the deployment, should that be needed.   

For a Prepare instance deployment, the Deploy->Tasks window will list out all the tables selected along with their associated deployment steps. 

Click Start to begin the deployment.

Data Area Table component parts

Deployment creates the tables, views, and stored procedures that form the basis of your data estate.

In SQL Server Management Studio, a group of deployed SQL tables that comprise a data area table may appear similar to the following:

1. The Raw Table (_R) - Stores the raw copy of the source table.
2. The Transformation View (_T) - Performs all the needed transformations using CAST and AS, along with other operators.
3. The List Table (_L) - Holds the list DW_IDs (row identifiers) for all the rows in the raw table that were flagged by a data validation rule.
4. The Messages Table (_M) - relates to the _L table and includes the error messages for the rule violations.
5. The Data Cleansing Stored Procedure (_Clean) - script that performs different deployment steps, including but limited to the following:
   1. Processing conditional lookups.
   2. Enforcing data validation rules.
   3. Managing History.
   4. Processing incremental loading.
6. The Valid Table (no postfix or _V if enabled) - All resulting "Valid" records are inserted here.

Depending on the features that may be enabled on a table, the following additional tables may be created as part of the deployment:

1. The Custom Data table (_CustomData) – TimeXtender Data Integration will use the Custom Data table to store any rows that need to be inserted with custom data that may have been manually entered by the user.
2. The Custom Data stored procedure (_CustomDataFill) - stored procedure that contains the data transfer script responsible for inserting the custom data into the Valid table.
3. The Incremental table (_I) - Stores the last max incremental selection value needed for enabling incremental loads.

Guard on deployment

Enable the “Guard on deployment” option to ensure that a table will not be deployed or accidentally truncated, even if the table is marked as “dirty”. 

The Guard on deployment option is available in the lower left of Table Settings.

A guarded table may appear to start running if you attempt to deploy it, but upon completion a message will appear stating that the table is guarded.  

Once deployment is complete, it is common to then Execute the table so that the data in the table will be current with the new changes. Click on the following link for a detailed information on Executing tables.