Data Warehouse Column Formatting: Double-click a data warehouse table to enable column formatting and primary key display mode (a +
is enabled by the tables).
Data Warehouse Column Formatting: Click +
/ -
by a table name to expand/collapse its column formatting.
Data Warehouse Column Formatting: icon for regular columns.
Data Warehouse Column Formatting: icon for transformed columns.
Data Warehouse Column Formatting: icon for primary key columns.
Data Warehouse Column Formatting: icon for primary key transformed columns.
Transform: New page to apply transformations after the load process. Reason: Many sources don’t allow transformations during the extraction or when extraction transformations are limited and/or slow. The data is extracted and loaded into a temporary data warehouse table, and then transformed into to official table. These transformations happen in the data warehouse engine, therefore they are fast, efficient and provide a broad range of MS SQL transformation functions.
Transform: Select a data warehouse table → Edit Columns
to open the Transform page.
Transform: New page shows PK
(primary key), Column Name
, Source Data Type
, Dw Data Type
, Transform MS SQL Expression
.
Transform - PK: Check the PK check boxes to define the table’s primary key columns. Currently, ETL+ doesn’t validate if the PK columns are the correct primary key.
Transform - PK: ETL+ creates the SQL PK columns set to Not Null.
Transform - PK: For text-based sources (like OData), when checking the PK checkbox for a varchar(max) column, ETL+ automatically changes its to varchar(50) and pops up: "PK cannot use varchar(Max). Format changed to varchar(50). We recommend reviewing the varchar size." -> Ok.
Transform - PK: ETL+ logs the following when MS SQL can’t create/recalculate a PK: "Primary Key failure. PK requires rows with unique values and no NULLs. Review the PK fields.". This means the selected PK columns are not the table’s correct primary key - review/fix the PK configuration.
Transform - PK: When adding new MS SQL and OData tables with PKs, ETL+ automatically configures the PK columns. User can change the PK configuration afterwards.
Transform - PK: From text-based sources (like OData), PK columns with varchar(max) format are automatically converted to varchar(50). User can change the PK configuration afterwards.
Transform - PK: When upgrading an ETL+ entity, Primary Index
columns automatically become Primary Key
columns. Oct version?
Transform: To view the source’s Column format, click the Refresh
icon on top of Source Data Type
. Depending on your source, this can take time (even minutes from OData v4).
Transform: To change a Column’s format, use its Dw Data Type
dropdown menu, or double-click the dropdown and type in the new format.
Transform: Column MS SQL transformations available on Trasnform Transform MS SQL Expression
. Ex.: LEFT(ColumnName,5).
Transform: Click the column header for Column Name
or Source Data Type
to sort the list.
Source Properties: Renamed Dw Schema
to Schema
. This checkbox sets Alias as the schema for tables imported from this source.
Settings - PK: Ability to create data warehouse physical PKs. Reason: PKs can be taxing for data load and storage (CPU, RAM and disk space), but might dramatically improve query performance.
Settings - PK: New “Create PK” dropdown with options:
Update Only: Default option. Only creates physical data warehouse PKs for tables with Update delta feature.
Always: Creates physical data warehouse PKs for all tables with PKs.
Never: Doesn’t create physical data warehouse PKs.
ETL Page - Update: Renamed Primary Index
to Primary Key
.
ETL Page - Update: Primary Key
lists the PK columns (from the Transform
page).
ETL Page - Update: To edit the PK columns, click the Edit
icon by the Primary Key or the Edit Columns
icon on the table.
ETL Page - Replace with Control: Now with more control options:
>= Max value from the data warehouse
>= Constant
>= SQL Expression. Ex. 1: getdate()-90. Ex. 2: max(ColumnName)-1000)
Load Engine - Replace All: The load happens on a temporary table to allow users to query the official table with minimum interruption. Once the load completes, the temporary table becomes the official one. The temporary table is named Schema.TableName_TEMP.
Load Engine - Replace All: The transition between temporary to official table happens in a fraction of a second quickly when there are no transformations performed (delete official -> , rename temporary to official, create <0_EntityID> when applicable).
Load Engine - Replace All: The transition between temporary to official table happens quickly when there are transformations performed (delete official -> bulk , insert into official from temp table applying transformations). Avg cloud bulk insert speed: 200MB/s.
Load Engine: Ability to apply transformations after the load (previously, only available during extraction when the source SQL syntax allowed).
Data Warehouse Tables: All tables now show schema , (including dbo). <Schema>.<TableName>.
Data Warehouse Tables: Renaming a table only applies to its name, not the schema (schemas might be changed in Source’s Properties and/or Data Warehouse Settings).
Data Warehouse Source: The data warehouse automatically shows as a data source for additional data preparations and transformations.
Data Warehouse Source: This source provides access to all data warehouse tables and views for single-tenant data warehouses (DataSelf Analytics Pro and Enterprise Editions).
Data Warehouse Source: This source provides access to data warehouse tables and views where <SQL schema> = <EntityID> or <SQL schema> = <dbo> in multi-tenant data warehouses (DataSelf Analytics Advanced Edition).
Data Warehouse - Source: Source’s Properties
disabled.
Data Warehouse - Source: Source 'sDelete
disabled.
Data Warehouse - Source: Use Settings
-> Data Warehouse
for configuration.
Data Warehouse - Source: Source credentials are sync’ed up its data warehouse configuration.
Data Warehouse - Center Panel: Tables imported from the data warehouse are automatically renamed to <TableName_DW>. Users can then rename them as needed.
Data Warehouse - Center Panel: Tables imported from the data warehouse keep the same SQL schema as from the original source. Ex.: Abc table imported from ERP source using alias as schema will be stored in <Schema>.<TableName>=<ERP>.<Abc>. Upon re-importing it from the data warehouse, this table will be automatically set to <ERP>.<Abc_DW>.
Data Warehouse - Center Panel: When importing a data warehouse table into itself (usually, a mistake), ETL+ will log: "Warning: Data warehouse source table imported into itself. This is usually a mistake. Fix it by renaming the table.". Oct version?
Sources: Addition to native extraction from cloud Microsoft Dynamics CRM.
Source Properties: Checkbox for 0_UnionID. For this version, this field will be populated with “AAA”.
Remove Log Level 1 from local disk. Retrieve live log from the cloud instead. Oct version?
Refresh Batch - WTS: Repeat Task checkbox unchecked by default.