¶ 1. Overview
The Client Gateway is an executable created by T6 Planning to be installed in the client's environment. It enables remote data load tasks. It acts as an intermediary between the server and the client, fetching data according to the configuration provided by the server and returning the requested data.
¶ 2. Installation
The Client Gateway is available for Windows and Linux operating systems. Install it according to your operating system. There is no need to install other packages as prerequisites.
The Client Gateway is an application that will run in the background in the terminal. To terminate the application, simply switch to the window where it is running and send the CTRL+C command.
¶ 2.1. Prerequisites
Before starting the installation, it is necessary to download the Client Gateway file according to your operating system, Linux or Windows. However, the OS must support one of the TLS 1.2 or 1.3 ciphers used by T6 Planning:
Client Gateway for version v11.11:
Client Gateway for version v12.0:
TLS 1.2:
- TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (0xc02f) ECDH x25519 (eq. 3072 bits RSA) FS;
- TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (0xc030) ECDH x25519 (eq. 3072 bits RSA) FS;
- TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (0xcca8) ECDH x25519 (eq. 3072 bits RSA) FS.
TLS 1.3:
- TLS_AES_256_GCM_SHA384 (0x1302) ECDH x25519 (eq. 3072 bits RSA) FS;
- TLS_CHACHA20_POLY1305_SHA256 (0x1303) ECDH x25519 (eq. 3072 bits RSA) FS;
- TLS_AES_128_GCM_SHA256 (0x1301) ECDH x25519 (eq. 3072 bits RSA) FS.
It is necessary to allow port 443 through the firewall.
¶ 3. Configuration
In the unzipped folder where the Client Gateway files are located, look for the appsettings.json file. Open it in a text editor, preferably one that reads JSON files.
Below is an image framing all the parameters currently in the Client Gateway, based on the version used in this manual.
{
"DataLoadGatewayConfig": {
"LogFile": "ppGateway.log",
"HealthFile": "ppHealth.log",
"HealthInterval": 20,
"Url": "http://localhost/Sysphera/dataloadhub",
"Key": "",
"Issuer": "https://sysphera.com/dataloadhub/issuer",
"Audience": "https://sysphera.com/dataloadhub/audience",
"ClientId": "",
"DataSources": [
{
"DataSourceName": "",
"DataSourceType": "",
"StringConnection": ""
},
]
}
}
Parameter Descriptions:
- LogFile: Enter a name for the log file here. This file will record recurring information for the Client Gateway;
- HealthFile: Enter the name of the file that will be used for the health check (performs regular checks of security, performance, and data integrity);
Both
LogFileandHealthFileaccept full paths where the files are expected to be saved. However, relative paths are also accepted, and in this case, the folder from which the Client Gateway was called will be considered to form the absolute path.
- HealthInterval: Interval between health check checks. Optional parameter with a default value of 20 (minutes).
- Url: Enter the URL of the T6 application here, ending with
/dataloadhub/. This parameter informs where the server-side part is located, with which the Client Gateway will communicate.
We have 3 parameters that do not need to be changed:
- Key: the value of this key will be provided by the deployment team;
- Issuer: https://Sysphera.com/dataloadhub/issuer;
- Audience: https://Sysphera.com/dataloadhub/audience;
For the next parameters, we will need to access T6 and create a Remote Data Load object.
- ClientId: We will enter the code generated when saving the Remote Data Load object;
- DataSources: Each datasource has 3 pieces of information that need to be filled in according to the values used in the creation of the Remote Data Load. They are:
- DataSourceName: ” ” (This is the name of the database we have in the database.)
- DataSourceType: “ ” (This is the name added when creating the Data Load)
- StringConnection: ” ” (We can get connectionstring references from the site https://www.connectionstrings.com/)
We can have multiple datasources. Just add them to the file respecting the JSON format.
We can connect through SQLserver, Oracle, REST, MySQL, PostgreSQL, and ODBC.
To obtain the data to fill in the parameters mentioned above, we will need to follow these steps:
- After logging into T6, go to the main menu and access Explorer;
- On the explorer page, in the ribbon, click on New Item;
- Click on Data Load and select Remote Data Load;
- A new object will be created. When opening the created object, a side panel will be displayed, where we will have Code and Sources;
- Code: It will display
00000000-0000-0000-0000-000000000000because no datasource has been saved; - Sources: Displays the list of sources added to the object;
- Code: It will display
- A new object will be created. When opening the created object, a side panel will be displayed, where we will have Code and Sources;
- In the panel, next to Sources, we will click on
; - A form will be displayed where we will fill in: Name, User, Password, and Interval
- Name: This is the name of the database in the database, which we will add to the
DataSourceNameparameter of theappsettings.jsonfile; - User: This is the username that accesses the database and which we will add to the
StringConnectionparameter of theappsettings.jsonfile; - Password: This is the user password that accesses the database and which we will add to the
StringConnectionparameter of theappsettings.jsonfile; - Interval: We can define the wait time, in seconds, for the data load to be completed (default value 0);
- Name: This is the name of the database in the database, which we will add to the
- After filling in the data, we will click on Confirm, and we will return to the initial page of the panel;
- Back in the remote data load panel, the code will remain zeroed, and the created source will be displayed;
- By clicking on Save, the panel will be closed. Open the object again to reopen the panel;
- When opening the object after saving, the code will have been generated. To copy it, we can click on:
We will assign these values to the mentioned parameters. Below is an example using SQLSERVER, ORACLE, POSTGRE, MySQL, CosmosDB, and DATALOADREST data sources:
{
"ClientId": "A1A1A1A1-B2B2-C3C3-D4D4-E5E5E5E5E5E5",
"DataSources": [
{
"DataSourceName": "SQLServer",
"DataSourceType": "SQLSERVER",
"StringConnection": "Data Source=SQLServerConnection;Initial Catalog=DataBaseName;Trusted_Connection=false;Persist Security Info=True;User ID=User;Password=pwd"
},
{
"DataSourceName": "Oracle",
"DataSourceType": "ORACLE",
"StringConnection": "Data Source=OracleConnection;User ID=user;Password=pwd"
},
{
"DataSourceName": "PostgreSQL",
"DataSourceType": "POSTGRESQL",
"StringConnection": "Server=host.docker.internal;Port=5432;Database=mydb;User Id=user;Password=pwd;"
},
{
"DataSourceName": "MySQL",
"DataSourceType": "MYSQL",
"StringConnection": "Server=host.docker.internal;Database=myDataBase;Uid=user;Pwd=pwd;"
},
{
"DataSourceName": "CosmosDB",
"DataSourceType": "COSMOSDB",
"StringConnection": "AccountEndpoint=https://cosmodb-example.documents.azure.com:443/;AccountKey=bpuRR4tLLQO6M2RB1Jop1KBJbByDm1zLGp2LJHeZJLaSSkft5aCApY9T2tUKx9qg591XyaSLZ0hRDDiACDb44k1vkA==",
"Configuration": {
"Database": "SampleDB",
"Container": "SampleContainer"
}
},
{
"DataSourceName": "Data Load Rest",
"DataSourceType": "DATALOADREST"
}
]
}
¶ CosmosDB Configuration Details:
- For CosmosDB, the Azure engine provides the Connection String, which combines your URL and Primary Key (or Secondary Key) displayed in the Keys tab of your Azure portal.
- Additionally, the Configuration field is required (unlike other data sources). Populate this field with:
- Database: Name of the database created in the Azure portal.
- Container: Name of the container used in the CosmosDB.
Note: To configure CosmosDB in T6, the configurations remain the same. You only need to specify the Name field (the same as the
DataSourceNameparameter).
¶ 3.1. Remote Data Load with CosmosDB
Using remote data loading with a connection to a NoSQL database (CosmosDB), we can ensure the existence of a column, even if it contains no data:
To illustrate, let’s create a Workflow process:
-
Create a Workflow object and add a task;
-
Assign any name to the task;
-
In Task Type, select Remote Data Load and click Next;
-
In Remote Data Load, select the previously created remote data load object (CosmosDB);
-
In SQL Command, insert the desired SQL statement to execute, for example:
SELECT Container.ID AS itemId, Container.price, Container.tags, Container.custom ? (Container.custom) : null AS custom FROM Container;This syntax will query the database and include a column named "custom," whether it exists in the database or not. If it exists and contains data, the data will be returned. If not, the column will return a value of "null."
-
In Destination, select New:
- When New is selected as the destination and the process executes successfully, a Data Table is created according to the metadata;
-
Click Finish to create a Data Table with the name of the connection established in the remote data load object.
You can also select an existing data table in the system as the Destination, but the number of columns and their headers must match those of the source table.
By checking the Automatically Restructure Data Table checkbox, the destination table will be automatically adjusted without requiring the same number and names of columns as the source table.
-
Save and publish the workflow process.
¶ 4 Client Gateway via Docker
First, we need to prepare a container for the gateway;
- Open a terminal from the
srcfolder of the PowerPlanning project cd C:...\powerplanning\src;
- We will need to use a public image of the gateway, which we can obtain from a Docker image repository or from DockerHub;
- We will select the image within the repository (for example purposes, we will name it
PublicImage/ClientGateway:latest); - In the terminal, we will execute the command docker pull PublicImage/ClientGateway:latest to download the public image from DockerHub;
Replace
PublicImage/ClientGateway:latestwith the correct name of the public image you are using.
- Copy the example
.envfile cp ClientGateway/.env.sample ./;- This command will copy the content from the project ClientGateway/.env.sample to the
srcfolder;
- This command will copy the content from the project ClientGateway/.env.sample to the
- Edit the
.env.samplefile with your configurations code .env.sample;
The clientID is obtained through T6. When we create a "Remote Data Load" object, upon opening it, we will have a code that should be inserted in the configuration part
DataLoadGatewayConfig__ClientId=********-****-****-****-***********;
- Run the Gateway via Docker
docker run --env-file .env.sample --name ClientGateway -d PublicImage/ClientGateway:latest
- In T6, we will open a Remote Data Load object, a side panel will open where we will click on Add;
- It will be necessary to provide Name, User, and Password;
- Still in T6, we will create a Workflow process;
- We will add a task to the workflow process, in this example, we will name it Load Postgres, of type Remote Data Load;
- We will click on Next;
- In Remote Data Load, we will select Postgres;
- In SQL Command, we will insert: SELECT * FROM employee ("employee" is the name of a table in the database);
- In Destination, we will select "New";
A Data Table will be created within T6 with the name of the connection created in the Remote Data Load Load Postgres object;
We can check the Automatically restructure data table checkbox, which will automatically adjust the destination table without needing to have the same number and name of columns as the source table;
- We will create a trigger to execute the workflow process;
- We will execute the trigger;
- After executing the trigger, we will create a Data Form, linking it to the created Data Table;
By adding data to the table externally (through the database) and executing the trigger again, these changes will be reflected in the data table created in T6.
¶ 5. Connecting Client Gateway with MSSQL (ODBC)
- Let's configure our Client Gateway by accessing the
appsettings.jsonfile inside the ClientGateway folder;
In the configuration, we have 2 main parameters that we must pay attention to:
- URL: It must necessarily contain the endpoint /dataloadhub
- ClientID: This is the code generated when we save the Remote Data Load object in T6;
- We will create an ODBC data source to connect to MSSQL. Inside the
appsettings.jsonfile, we will insert another block of information:
{
"DataSourceName": "ODBC MSSQL",
"DataSourceType": "ODBC",
"StringConnection": "Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;"
}
- To run the ClientGateway again, we will execute the command dotnet run;
After configuring, we will return to T6, where we will create a Workflow Process to visualize the operation.
- Create a Workflow object and add a task;
- We will enter any name for the task;
- In the task type, we will select Remote Data Load and click next;
- In Remote Data Load, we will select the previously created remote data load object;
- In SQL Command, we will enter the SQL call we want to execute, for example, SELECT * FROM REP_APPLICATION;
- In Destination, we will select New;
- When we select New in the destination and the process works, a Data Table is created according to the metadata;
- We will click Finish, a Data Table will be created with the name of the connection created in the remote data load object;
We can select in Destination a data table already present in our system, but it is necessary that the number of columns and the names in their headers are the same as the source table;
We can check the Automatically restructure data table checkbox, which will automatically adjust the destination table without needing to have the same number and name of columns as the source table;
- We will save and publish the workflow process;
- We will return to the explorer and create a Workflow Trigger, where we will select the created process and execute the trigger;
- Back in the explorer, we will now create a Data Form;
- When opening the form, we will link the created data table, select the rows and columns to be displayed, and save the form;
- After doing this, we will return to the explorer and open the created data form;
- When we open the form, the selected rows and columns present in the MSSQL database brought via ODBC will be displayed;
¶ 6. Connecting Client Gateway with Excel (ODBC)
We will create a Remote Data Load object in T6 (or add another connection if you already have such an object);
- Let's configure our Client Gateway by accessing the
appsettings.jsonfile inside the ClientGateway folder; - We will create an ODBC data source to connect to Excel. Inside the
appsettings.jsonfile, we will insert another block of information:
{
"DataSourceName": "ODBC EXCEL",
"DataSourceType": "ODBC",
"StringConnection": "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\MyExcel.xls;"
}
In StringConnection, DBQ is the location where the Excel file is saved
- To run the ClientGateway again, we will execute the command dotnet run;
After configuring, we will return to T6, where we will create a Workflow Process to visualize the operation.
- Create a Workflow object and add a task;
- We will enter any name for the task;
- In the task type, we will select Remote Data Load and click next;
- In Remote Data Load, we will select the previously created remote data load object;
- In SQL Command, we will enter the SQL call we want to execute, for example, SELECT * FROM [Sheet1$];
- Sheet1 is the table in the Excel file. $ is added to inform the driver that it is a table.
- In Destination, we will select New;
- When we select New in the destination and the process works, a Data Table is created according to the metadata;
- We will click Finish, a Data Table will be created with the name of the connection created in the remote data load object;
- We will save and publish the workflow process;
- We will return to the explorer and create a Workflow Trigger, where we will select the created process and execute the trigger;
- Back in the explorer, we will now create a Data Form;
- When opening the form, we will link the created data table, select the rows and columns to be displayed, and save the form;
- After doing this, we will return to the explorer and open the created data form;
- When we open the form, the selected rows and columns present in the Excel file brought via ODBC will be displayed;
- By adding data to the columns of the Excel table and saving, we can return to the system and execute the trigger again, and the changes will be reflected in the data form.
An error will occur if there are changes to the existing columns in the Excel file. The same column names must be maintained in the header, and the number of columns should not be altered.
¶ 7. How to Run a Load via REST
- Inside the
DataLoadGatewayConfigconfiguration file, it is necessary to include a new entry:
DataSourceName: "Data Load Rest",
DataSourceType: "DATALOADREST"
- DataSourceName: can be any name, as long as it contains "rest" in its composition;
- DataSourceType: must be this name, it should not be changed;
¶ 8. Running the Client Gateway as a Service (Windows)
Steps to register the Client Gateway as a Windows service.
- Open a Windows terminal with administrator privileges.
- At the command prompt, type the following syntax: sc.exe create NewService binpath= c:\windows\system32\NewServ.exe
- NewService: is the name of the service you want to create;
- binpath: we will assign the full path where the Client Gateway executable is located;
- Press Enter on the typed line.
- The new service will appear listed on the Windows services screen.
SC.exe has other parameters that can be used when creating a service. In the step-by-step described above, these are the minimum required parameters to register a service. You can learn more about the other parameters by clicking the link: Microsoft Learn - SC.exe create
To remove the created service, the command is: sc delete ServiceName, where ServiceName is the name of the service we want to remove.
¶ 9. Running the Client Gateway as a Service (Linux)
After downloading the ClientGateway file for Linux in section 2.1 of this manual, we will need to create a service in the /etc/systemd/system/ directory, which we will name ClientGateway.service.
- Let's open a text editor with superuser privileges;
- Editors like
nano,vi,vimcan be used. In this manual, we will usenano;
- After opening the editor, we will execute the following command to create the service:
sudo nano /etc/systemd/system/ClientGateway.service
- We will add the content of the service file:
# ClientGateway.service
[Unit]
Description=T6 Enterprise Client Gateway
[Service]
ExecStart=dotnet /home/azureuser/ClientGateway/ClientGateway.dll
SyslogIdentifier=T6ClientGateway
User=azureuser # Name of the Linux user who will run the service
Restart=always
RestartSec=5
[Install]
WantedBy=multi-user.target
- After adding the content, we will save it (using
nanowe can do this by pressing CTRL + O); - After finishing, we can close the editor (using
nanowe can do this by pressing CTRL + X);
- We will need to enable and start the ClientGateway service;
- To enable the service, still in the text editor, we will execute the following command: sudo systemctl enable ClientGateway;
- To start the service, still in the text editor, we will execute the following command: sudo systemctl start ClientGateway;
To ensure that the service is working correctly, we can check its status by running the following command: sudo systemctl status ClientGateway.
¶ 10. Incremental Data Load
The incremental data load replaces the data in the database table by first deleting the data and then inserting it according to the selected period.
To use the incremental data load, we will follow these steps:
- We will create a Workflow process object;
- This process should contain 3 parameters, one Text parameter to name the data table, and two Date parameters, one indicating the start date of the desired records and one indicating the end date of the selected records. For example purposes, we will name them
TableName,StartDate, andEndDate; (For additional information on Workflow, Triggers, and Parameters, visit: Workflow BPM)
- We will enter any name for the task;
- In the task type, we will select Remote Data Load and click next;
- In Remote Data Load, we will select the previously created remote data load object;
- In SQL Command, we will enter the SQL call we want to execute;
- In Destination, we will select New;
- When we select New in the destination and the process works, a Data Table is created according to the metadata;
- We will click Finish, a Data Table will be created with the name of the connection created in the remote data load object;
We can select in Destination a data table already present in our system, but it is necessary that the number of columns and the names in their headers are the same as the source table;
We can check the Automatically restructure data table checkbox, which will automatically adjust the destination table without needing to have the same number and name of columns as the source table;
- We will check the Incremental Load checkbox;
- To load the available columns according to the SQL command entered above, we will need to click the
, whenever the SQL command is changed, it is necessary to click the button to reload the available columns;
- In the Column name with dates field, we will select the column we will use as a reference for the load;
- Below, we will have Parameter with the start date, where we will select the
StartDateparameter, referring to the start date of the records we want; - In Parameter with the end date, we will select the
EndDateparameter, referring to the end date of the selected records;
These settings will only define the records that will be deleted from the destination table. The insertion part will depend on what is defined in the SQL command above (if it only does the "SELECT", all records will be loaded, regardless of the quantity).
Example of the SQL command applying the parameters:
SELECT * FROM REP_EXPLORER_OBJECT
WHERE
datModified >= COALESCE(NULLIF('Instance(StartDate)',"),'1970-01-01')
AND
datModified < COALESCE(NULLIF('Instance(EndDate)',"),'5000-01-01')
- In the process trigger, we will define the
StartDateandEndDateparameters, with the start date and end date, respectively; - To finish, after entering the start date and end date of the records, we will trigger the trigger.
Whenever the T6 version is updated, it is strictly necessary to update the Client Gateway version.
¶ 11. Dynamic Data Table Creation
To use data loading for dynamically creating a data table, follow these steps:
-
Create a Workflow Process object:
- This process must be configured with a parameter of type Text to name the data table. Name it
TableName(This parameter will be used in step 8 of this topic).
(For additional information about Workflow, Triggers, and Parameters, see: Workflow BPM)
- This process must be configured with a parameter of type Text to name the data table. Name it
-
Create a new task and assign any name to it.
-
In Task Type, select Remote Data Load and click Next.
-
In Remote Data Load, select the previously created remote data load object.
-
In SQL Command, insert the desired SQL query to execute.
-
In Destination, select any option from the dropdown menu (regardless of the data table selected, it will only be created when the process trigger is fired).
-
Check the Automatically Restructure Data Table checkbox to enable automatic adjustments to the destination table.
-
In Parameter for Dynamic Table Name, select the previously created
TableNametext parameter:- When the task is executed, the value entered in the
TableNameparameter will define the name of the destination data table.
If no data table exists with the defined name, it will be dynamically created and saved in the Root folder of T6.
Warning: If a data table already exists with the name defined in the parameter, its data will be overwritten.
- When the task is executed, the value entered in the
-
Once the process configuration is complete, click Save and publish the cube.
-
In the process trigger, set the
TableNameparameter to the desired name of the destination data table. -
Finally, after defining the parameter value, fire the trigger.