About the tool

Preset Types

Create a preset

Map the columns

Create a preset for SFTP uploads.

Enable the new preset for the users

Additional Features in the preset Configurator

Update a preset

Delete a preset

Export Table

Schema and Tables in the Database

About the tool

This tool allows the user to configure a preset that allows the end user to use it to upload their data to a specific table in the database. Each preset is connected to a specific table in the database.

Preset Types

The preset has two types:

1. Synchronization: When this option is chosen during preset creation, the uploaded data will be synchronized according to the primary key(s) of the table. For instance, if the employee ID is used as a unique identifier for employee records, whenever this ID is uploaded, the data will be synchronized with the corresponding row that contains that ID, rather than being added as a new record.

📌 Note:
If you select synchronization, you should create a replica of the original table in ETL Schema with 
name [X_Tablename]. 
Example: If the table name is dbo.employee, you should create a replica of this table ETL.X_employee

2. Addition: If this option is selected during preset creation, the uploaded data will be added to the table in new rows.

Create a Preset

1. Go to https://asasdrive.com/#/login

2. Login with your credentials

3. From menu, click on 'Data Management and Logs' > ETL

4. Under 'All' tab or 'application' tab, double-click on ETL Preset configurator

5. Click 'Add'

6. Enter preset name and preset description 

7. Click 'save'.  A new button'Add' will be displayed.

8. Click 'Add' button to configure the details

9. Enter Sheet name as 'Sheet1'

10. Select the Table from the drop-down list of Table Name. 

Once the table is selected, all columns of the table and their properties will be populated

11. Select The Import type (Synchronization, Addition)

12. Click 'Save'

The above preset works if the columns names in the table same as columns names in the Excel sheet.

Map the Columns

If the columns names in the DB table are different from the columns names in the Excel sheet, you need to map the columns as follows:

1. Click Read columns

2. Browse the file that include the columns you want to map. The sheet name in Excel sheet should be 'Sheet1'.

A drop-down list will open in front of each field, and you can map the name in Excel file with the name in the database.

3. Click 'Save'

Create a preset for SFTP upload

If the preset is created to drop files to an SFTP server without saving data, follow the below steps:

1. Go to https://asasdrive.com/#/login

2. Login with your credentials

3. From menu, click on 'Data Management and Logs' > ETL

4. Under 'All' tab or 'application' tab, double-click on ETL Preset configurator

5. Click 'Add'

6. Check 'SFTP file transfer' on the top right

7. Enter the preset name, preset description

8. Select the serve name from the drop-down list 

9. Enter the folder name and its directory.

10. Enter the preset code

11. Click 'Save'

The servers connections should be set in the database in table PresetConfigurator.SftpProfile

Enable the new Preset to the users

To enable the preset to the users

1. Go to settings

2. Select the role, or roles that you want to assign the preset to.

3. Double-click on the role to open the edit mode

4. from the preset section, drag the new preset from the left side to the right side

5. Click 'Save'

Additional Features in the Preset

1. Data Mapping

In some cases, the dealer uploads data according to his system or codes, but we need to map these data to our own codes or unique IDs. The mappings are configured in two tables called [datamapping] and [datamappingdetails]

If the dealer for example uploads department IDs in column name Deprtmnet_ID, and we need to get in the table ASAS mapping. So we add a new column to the table called ASAS_Deprtmnet_ID

In the preset configurator, and In front of column ASAS_Deprtmnet_ID we select from the drop-down list the relevant mapping as seen below.

So when the dealer uploads a value in Deprtmnet_ID columns, the ETL will automatically get Asas mapped value and save it in the table automatically.

2. Log

For analysis concerns, it would be useful to get information related to who uploaded the data, the data related to which company and date upload date.

All the above points can be set automatically in the preset using log settings

User: By selecting user, the corresponding column will be automatically filled with the user ID of the logged-in user who uploaded the data

Company: By selecting company, the corresponding column will be automatically filled with the company ID of the logged-in user who uploaded the data

Current Date: By selecting current date, the corresponding column will be automatically filled with the current date when the data has been uploaded

Custom Value: By selecting custom value, the corresponding column will be automatically filled with the current date when the data has been uploaded

3. Change Date Format

By default, ETL accepts the date format YYYY-MM-DD or MM-DD-YYYY.

If the dealer's system generates any other date formate, we use this option 'Convert Date Format' to convert the date format in the file to the accepted format in the database.

Update the Preset

If any changes occur in the original tables, we should ensure the following actions:

1. Update the corresponding table in the ETL schema with the same changes applied to the original table (if the preset is synchronizing type)

2. Open the preset in edit mode and click 'Save' to update the preset with the new changes.

3. If there is a new template, and you need to do the mapping, click 'read columns' and do the required mapping, then click 'Save'

Upload Template

You can upload the template file that the user should use to upload the data.

1. Click 'Upload template'

2. Browse for the template file

3. Click 'Save'

The user can download the template from the ETL as seen below:

Delete Preset

Select one or more preset, and click 'Delete'

To select more than one preset, press CTRL and click on the row

Export Table

Select only one preset and click 'Export' to export the header of the table.

Database Related to Preset Configurator

The tables that of preset configurator are:

[PresetConfigurator].[Preset]
[PresetConfigurator].[SheetHeader]
[PresetConfigurator].[SheetDetail]
[PresetConfigurator].[SftpProfile]
[PresetConfigurator].[SheetFileType]
[PresetConfigurator].[uploadlog]