Introduction and Navigation
Overview of ETL+ screens
Based on ETL+ version v2022.08.2901
A. Source (Left panel)
This is the connection to your ERP or CRM source. If it is a cloud source, the ETL+ app can be in the cloud.
If the source is an on-premises system, the ETL+ app must be accessed on a local PC that can read the source system. The example above shows Acumatica as the ERP source connection
There is another source called zData Warehouse. This is provided by DataSelf. This source lists the existing Data Warehouse SQL tables uploaded from the ERP data. This allows you to create custom SQL queries using existing Data Warehouse tables without needing to re-import the data from the ERP source. As seen in the example below, the SQL query statements can be complex. Note the data source in the FROM part of the Query with a fully qualified schema-table path.
B. Data Warehouse (Center panel)
These are the data tables in the DataSelf SQL Data Warehouse. The data is loaded from the Source to the Data Warehouse when the ETL+ refresh process runs.
The first Data Warehouse group is the ERP data loaded into the SQL Data Warehouse tables. In the example below it is from the Acumatica ERP with the data tables listed that have been configured to pull from the ERP source to the SQL Data Warehouse. Not all ERP source tables are necessarily loaded into the SQL Data Warehouse, only your chosen ones. You have the option of making the Data Warehouse table name different than the ERP source table name.
The second Data Warehouse group, called zData Warehouse, is provided by DataSelf. This is where you store custom table queries you have created based on the existing data in the SQL Data Warehouse.
C. ETL SQL Statement (Right panel)
This is the guts of the application where the actual work happens.
In the example below, the ETL SQL Statement is the script ETL+ runs to extract the data from the source ERP or zData Warehouse and load it to the SQL Data Warehouse. This is designed to use industry standard SQL syntax rather than a proprietary software language. Someone who understands SQL can modify an existing statement or create new tables with their own custom SQL statement.
D. Load Types: also see ETL+ Load - DataSelf - Confluence (atlassian.net)
Load All ETL+ Table Load Types | Load-All
The simplest Load Type. It deletes the existing SQL Data Warehouse table and re-loads everything from the data source.Replace ETL+ Table Load Types | Replace
Uses a filter to only add/replace selected source data rather than all source data.
i.e. Only add Invoice records if the Invoice Date is >= to the latest date in the existing SQL data.
The process first scans the existing SQL Data Warehouse data, then only loads ERP source records that fit the filter criteria.Upsert ETL+ Table Load Types | Upsert
This is the most complex Load Type, but can also provide the most performance improvement.
Upsert will add new records, or replace existing records, based on a LastModifiedDateTime field.
If your source table does not have a LastModifiedDateTime field, then this will not work for you.To determine whether to add a new record or replace an existing record, the process scans the primary key (that you determine) to determine if that unique value exists in the SQL data or not. Determining the field(s) that make up the primary key value requires an adequate understanding of the data structure.
i.e. load any customer record where the LastModifiedDateTime is less than or equal to (<=) the current date/time. If the unique primary key value of the record already exists, then replace the existing record with the new data. If the primary key value does not exist, add a new record.
Append ETL+ Table Load Types | Append
The second simplest Load Type. It adds whatever is in the source to the existing SQL data table.
i.e. You have an excel file for all the orders entered in another system that is created daily. And you want to accumulate that daily information in DataSelf.