ETL+ Design Page

Controls how data is loaded into the target SQL Server based data warehouse. Allows data types to be changed and T-SQL expressions applied to the data loaded into the target table. Key+ columns/fields designate logical keys that influences how data is extracted.

This page allows the definition of logical keys (Key+ used for delta loads), column data types (VarChar, Dw Data Type) and transformations (T-SQL Expression).

1. Source - Table Name: Design page for Source - Table Name.

2. Scr PK. Keys designated in the data source.

3. Key+. Check boxes for the columns that define the logical keys used to process this target/data warehouse table. This logical key influences how the load process works.
   

   

• Column Name. Table’s column names.

• Source Data Type.

• VarChar. This forces the target data type to be varchar(max). This is helpful when the source data might cause SQL errors because SQL can’t accept some of the source’s values. For instance, a numeric source field has some rows with characters, or a source date field has invalid dates for SQL (click here to see SQL valid date ranges).

• DW Data Type. Allows the configuration of data warehouse data types:

  • Type in a new Dw Data Type, click Enter to accept.

  • Use a row’s dropdown list to pick your choice.

  • Depending on the source data type and what you change on this dropdown, ETL+ will automatically create a T-SQL Expression to cover the data (see the next step). Feel free to edit the expression.

8. T-SQL Expression: Enter MS SQL Expressions to transform the column’s data.
   E.g.; LEFT(ColumnName, 5).
   This transformation is applied after the raw data is loaded to the data warehouse.

9. Column names in bold: Their Dw Data Type has changed from the source’s data type. Ex above: City comes as a varchar(max) from OData, and the user changed it to varchar(50).

10. Confirm. Confirm changes currently done on this page.

11. Cancel. Cancel changes currently done on this page.

• ETL+ Build and 32-bit or 64-bit (Upper right corner of page)
  - Version number: Format vYYYY.MM.DDBB. E.g; v2021.08.2703 means the 3rd build on 08/27/2021.
  - 32 or 64 bits.

• Help ?: Link to knowledge page.

• User: your ETL+ username currently logged to ETL+. (Lower left corner of page)

• Entity: the Entity ID, Data Warehouse name connected to this ETL+ session.
  Example: abc,aa_dw3 refers to Entity: abc, Data warehouse name: aa_dw3.

Key+ Logical Key, Indexes, and Unique Keys

The impact of the Key+ settings on the load process varies according to the context and usage scenario

Effects of Key+ on the load process
Load Upsert. A unique key is required for the Load Upsert process.
OData. Extracting large tables from OData data sources. Identifying a unique key using Key+ may enhance load efficiency and/or prevent ETL+ failures. Designating a unique key with Key+ for a target table from any data source with any load type may: Prevent ETL+ failures due to timing out. Improve load times on tables with many columns. For more see ETL+ Source Driver for OData and Acumatica OData Extraction Issues / Trouble
Index. Creates a index on the target table in the data warehouse.

Related Pages

• ETL+ Extract, Transform and Load (ETL) Page – parent page

• ETL+ Extract SQL Statement Panel

• ETL+ Table Load Types

  • Design page Key+ designation for Load Upsert ETL+ Load Upsert

  • Design page Key+ designation for OData ETL+ Source Driver for OData

• Microsoft T-SQL – Guide to the functions and expressions in the T-SQL Expression column. DataSelf data warehouses run on T-SQL, the SQL variant used by Microsoft SQL Server.

  • T-SQL expressions include aggregate functions such as AVE, MIN, MAX, SUM and date functions such as DATEADD, GETDATE, EOMONTH, etc.

v2023.07 ✅