DRAFT Hybrid ETL Process
Purpose: What is the goal?
Server: On-prem Server with a data connection to Sage 100.
Out: Dw<Clientsname>_OnPremCopy , On-prem server.
Is the data flow:
Data source → On-prem DW
On-prem DW → Cloud DW
Cloud DW → Tableau extract (Cloud Tab server)
The refresh process of the Hybrid installations starts from ETL Plus.
Software: ETL+ and Windows Task Scheduler (WTS)
Servers: Client’s on-premises server, DataSelf cloud servers DW3, DW2
Client server (on-prem) = Server with a data connection to Sage 100, ETL+, SQL Server
DW3 = home for ETL+ cloud scripts
DW2 =
Testing Connections to DS Cloud Server
Phase 1
Steps for Job 1 for WTS. Includes execution of cloud script “NightlyRefresh”.
Not needed?
Job 1 runs on-premises through the Agent, where the Sage 100 connection is. This job:
extracts data from Sage 100 data and loads it into the “Dw<Clientsname>_OnPremCopy” database.
Executes a cloud script named NightlyRefresh.
Job 1 also includes a Cloud Script named “NightlyRefresh”, this Cloud Script exists on the dw3 server.
DW3 Server:
The path to find the script on dw3 is the following: C:\DataSelf\CloudScript
The Cloud Script is a bat file and the structure of its name is: entity name + underscore + name of the Cloud Script in ETL Plus. entityname_NightlyRefresh.
The bat file has a command to run the Windows Task Scheduler task on the dw2 server.
DW2 Server:
The bat file on dw3 calls a task in WTS on dw2 to run a different bat file that exists on the dw2 server:
Path to find the dw2 bat files:
The first thing that the bat file does is to run the ETL1 refresh. The structure is: “path to the executable” “Batch Number” “Project name”
The command in the bat file runs the executable below.
The ETL1 refresh takes the data from the “DWClientsname_OnPremCopy” database and puts it into the “DWClientsName_ERPSystemName” database.
After the ETL1 refresh is over, the next step is to run the Tableau refreshes. There are two ways to run the Tableau refresh:
Phase 2: Tableau Refresh
After the ETL1 refresh is over, the next step is to run the Tableau refreshes. There are two ways to run the Tableau refresh:
Tableau Refresh - OLD WAY
The old way is to have all the tableau data sources refresh information in the bat file:
Tableau Refresh - RECOMMENDED
The current way is to have a command in the bat file that calls a job in ETL Plus that refreshes the Tableau scripts, in this case we are using Job 4.
The following link has the information on how the command line interface works: https://kb.dataself.com/ds/etl-command-line-interface
This job is not scheduled to run automatically in ETL Plus because it is going to be refreshed through the command in the bat file. The Agent runs that job on dw2.
Notes:
Database Naming Convention
ETL Plus database:
Old: The old database name convention for the hybrid installations is: “<DWClientsname>_OnPremCopy”
Current: “<DWClientsName_ETLPlus>_<ERPSystemName>”
ETL1 database:
Old: The old database name convention for the hybrid installations is: “DWClientsName_ERPSystemName”
Current: “<DWClientsName_ETL1>_<ERPSystemName>”
In the future, we are going to use the “DWClientsName_ETLPlus_ERPSystemName” ETL Plus database only.
We are going to stop using ETL1, and everything that ETL1 does is going to work within SQL instead.
Related Pages