Smart Connector - Delivery Types overview

What are the Smart Connector Delivery types?

When creating a Smart Connector, you will note there are several different ways to bring data into the system. These are called Delivery Types and show up on the drop down with that name. The following Delivery Types are currently supported:

• Manual Upload (.csv file or similar)
• Google Drive
• Goole Sheet
• Google BigQuery
• SFTP
• FTP
• Box
• Amazon S3
• Microsoft OneDrive
• Dropbox
• MySQL
• Salesforce SOQL
• PostgreSQL 
• Microsoft Azure SQL Server
• Snowflake
• Amazon Redshift
• TapAccess

 

 

MySQL Connection

The MySQL connection using the Smart Connector enables a new source of data in the system. Data is fetched based on a predefined SQL query and would work similar to a MySQL workbench , allowing only data read operation. The requirements and functionality is similar to  other Smart Connector Connection Type / Delivery Type options.

To Create a Smart Connector:

1. Open TapClicks and in the left pane, click Administrator.
2. Click Smart Connector under Data Settings.
3. Click the  icon next to Smart Connector on the top of the screen.
4. Enter a name and select MySQL from the Delivery type dropdown.

5. After selecting the MySQL, the user will be prompted to connect to the database using credentials and other necessary information. Please enter the information as given below.  

MySQL Host	IP address of the host on which the database is running / hosted
Port	Port number on which the database can be accessed | Default : 3306 for MySQL
Database Name	Name of database from which data has to be fetched
Username	Username used to access the database
Password	Password used to access the database

 

Only MYSQL Databases for which external connections over TCP/IP are allowed can be connected. The server on which the database rests must allow external connection.

6. Before creating an actual connection, check if the database is accessible by clicking test connection. Once the connection is established, the check sign on the test connection turns green.

7. If the connection is unsuccessful, an error message appears on the screen. Some common things to check if this occurs are:

• Required Fields: Host, Username, Password, Database Name, Port Number
• Permissions: Check if you have the necessary permissions to access the host on which the Database rests

8. After you enter the information for Smart Connector, fields to create a Query request becomes visible. You need to create a Query request to get the required data from the remote MySQL Database. Smart Connectors support both basic and advanced READ queries, like SELECT and CALL (this procedure is permitted, but please inform your Customer Success Manager if you intend to use this command to ensure that the Smart Connector meets this advanced use case).

 

Note: Only columns selected in the query will be fetched by the Smart Connector. Therefore, users need to make sure they are including all necessary columns in the query.

9. The only commands that are not supported include INSERT, UPDATE and DELETE. In order to achieve memory optimization, the system will automatically paginate the data in batches of rows. Adding a LIMIT at the end of your query will prompt the system to have that many number of rows in the batch. However, because the order in which the rows are fetched from the database could vary from query to query, it’s best to add in your SQL statement an ORDER BY to ensure the data integrity. For example : SELECT campaign_name, calls, leads, date_created FROM my_campaigns WHERE `date_created` BETWEEN '%START_DATE%' AND '%END_DATE%'", we would suggest you to update it that way : "SELECT campaign_name, call, leads, date_created FROM my_campaigns WHERE `date_created` BETWEEN '%START_DATE%' AND '%END_DATE%' ORDER BY id

10. Data types of all columns are detected automatically. It is recommended that users check all the data types before saving the configuration to avoid incorrect data types. Please see the Field Type column in the image below:

 

For example: Auto detection may select a column with number based on what is in that first row of data but that might not be how you intend the field to be used. Id - as data type Number since it comes through as a 3 in the sample file, but it should be stored as Text as no mathematical operations such as Sum/Average is expected on an Id.

11. Frequency of fetch

• Historic Fetch and Daily fetches.
  • Full: data within the last 6 months is fetched
  • Partial: data within the last 3 days is fetched.
    This is consistent with all Smart Connector delivery type options.

Note: This will depend on whether your Smart Connector is configured to fetch data based on Date or based on the Last Modified Date.

12. Data Update

If certain fields in the query needs to be modified (unique fields, dates, and assigning configurations), the user would need to delete the data loaded to date through this Smart Connector before making the modifications.  This is done through the Delete which is placed next to the Smart Connector names in the Smart Connector list.

However, a user can include additional fields on the query similar to if we were to add a new field from a .csv file to a Smart Connector. User can do this without deleting the previously loaded data.  Standard partial / full fetch apply to existing and new assigning in regards to filling the database with these new fields.  This functionality is in line with all other Connection Type / Delivery Type options in the system today.

After creating the Smart Connector, users will have to map their Smart Connector data to clients in TapAnalytics and click Update Dashboard before the data starts pouring in the system.

 

 

 

Salesforce SOQL Connections

Salesforce SOQL  Smart Connector enables you to integrate data from Salesforce SOQL in  TapClicks platform.  

To Create a Salesforce SOQL  Smart Connector,

1. Open TapClicks and in the left pane, click Administrator.
2. Click Smart Connector under Data Settings.
3. Click the icon next to Smart Connector on the top of the screen.
4. Enter a name and select Salesforce SOQL from the Delivery type drop down.   Note: The Salesforce Object Query Language (SOQL) can be used to search organization’s Salesforce data for specific information. SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL) but is designed specifically for Salesforce data.
5. After selecting the Salesforce SOQL option, the users will be redirected to the Salesforce login screen to authenticate the connection.
6. Enter your valid Salesforce credentials and click Log In. If you are unable to connect, please contact your Account Manager.
7. After entering the information for Smart Connector, fields to create a Query request becomes visible. You need to create a Query request to get the required data from the remote MySQL Database. Smart Connectors support only READ queries, like queries with SELECT.     Note: Only columns selected in the query will be fetched by the Smart Connectors so users need to make sure that they are including all necessary columns in the query.
8. In order to achieve memory optimization , the system will automatically paginate the data in batches of rows. Adding a LIMIT at the end of your query will prompt the system to have that many number of rows in the batch. However, because the order in which the rows are fetched from the database could vary from query to query, it’s best to add in your SQL statement in ORDER BY to ensure the data integrity.
   For example,
   Select Name FROM Account WHERE Industry = Media ORDER BY Account.Name LIMIT10000
9. Data types of all columns are detected automatically. It is recommended that users should check all the data types before saving the configuration to avoid incorrect data types. Please see the  Field Type column in the image below: Note: Auto detection may select a column with number based on what is in that first row of data but that might not be how you intend the field to be used. For example, Id - as data type Number since it comes through as a 3 in the sample file, but it should be stored as Text as no mathematical operations such as  Sum/Average is expected on an Id.
10. Frequency of fetch
    • Historic and Daily fetches.
      • Full: data within the last 6 months is fetched
      • Partial: data within the last 3 days is fetched
    • This is consistent with all Smart Connector delivery type options.

Note: This will depend on whether your Smart Connector is configured to fetch data based on Date or based on Last Modified Date

11. Data Update
    If certain fields in the query needs to be modified (unique fields, dates, and assigning configurations), the user would need to delete the data loaded to date through this Smart Connector before making the modifications. This can be done by clicking Delete, which is next to the Smart Connector names in the Smart Connector list.
    
    However, a user can include additional fields on the query similar to if we were to add a new field from a .csv file to a  Smart Connector. User can do this without deleting the previously loaded data.
    Partial / full fetch apply to existing and new assigns in regards to filling the database with these new fields.  This functionality is in line with all other Connection Type / Delivery Type options in the system today.

After creating the Smart Connector, users will have to map their Smart Connector data to clients in TapAnalytics and click Update Dashboard before the data starts getting in the system.

 

 

 

PostgreSQL Connection

The PostgreSQL connection using the Smart Connector enables a new source of data in the system. Data is fetched based on a predefined SQL query and would work similar to a pgAdmin SQL client but allowing only data read operation. The requirements and functionality is similar to other Smart Connector Connection Type / Delivery Type options.

To Create a PostgreSQL Smart Connector,

1. Open TapClicks and in the left pane, click Administrator.
2. Click Smart Connector under Data Settings.
3. Click the  icon next to Smart Connector on the top of the screen.
4. Enter a name and select PostgreSQL from the Delivery type drop down.

5. After selecting the PostgreSQL option, the user will be prompted to connect to the database using credentials and other necessary information. Please enter the information as given in the table below

PostgreSQL Host	IP address of the host on which the database is running / hosted
Port	Port number on which the database can be accessed | Default Port : 5432
Database Name	Name of database from which data has to be fetched
Username	Username used to access the database
Password	Password used to access the database

Note: Only Postgresql Databases for which external connections over TCP/IP are allowed can be connected. The server on which the database rests must allow external connection

6. Before creating an actual connection, check if the database is accessible by clicking test connection. Once the connection is established, the check sign on the test connection turns green.

Unverified Connection ​

Verified Connection ( Verified by the Green tick next to the TEST CONNECTION Button)

If the connection is unsuccessful, an error message will appear on the top-right corner.

7. In case of unsuccessful connection, some common things to check:

• Required Fields: Host Address, Port Name, Database Name, Username, Password
• Permissions on your host to access the database externally from other sources

8. After you enter the information for Smart Connector, fields to create a Query request becomes visible. You need to create a Query request to get the required data from the remote PostgreSQL Database. Smart Connectors support both basic and advanced READ queries using SELECT statement.

• SELECT
• CALL (this procedure is permitted, but please inform your Customer Success Manager if you intend to use this command to ensure that the Smart Connector meets this advanced use case)

Note: Only columns selected in the query will be fetched by the Smart Connector. Therefore, users need to make sure they are including all necessary columns in the query.

10.The only commands that are not supported include INSERT , UPDATE and DELETE. In order to achieve memory optimization, the system will automatically paginate the data in batches of rows. Adding a LIMIT at the end of your query will prompt the system to have that many number of rows in the batch. However, because the order in which the rows are fetched from the database could vary from query to query, it’s best to add in your SQL statement an ORDER BY to ensure the data integrity.

For example: SELECT * FROM customer_value WHERE (effective_to_date BETWEEN '%START_DATE%' AND '%END_DATE%') OR 1=1 ORDER BY effective_to_date

11. Data types of all columns are detected automatically. It is recommended that users check all the data types before saving the configuration to avoid incorrect data types. Please see the Field Type column in the image below:

For example, Auto detection may select a column with number based on what is in that first row of data but that might not be how you intend the field to be used.   For example: Id - as data type Number since it comes through as a 3 in the sample file, but it should be stored as Text as no mathematical operations such as Sum/Average is expected on a Id

12. Frequency of fetch

• Historic fetch and Daily fetches.
  • Full: data within the last 6 months is fetched
  • Partial: data within the last 3 days is fetched  

Note : This will depend on whether your Smart Connector is configured to fetch data based on Date or based on Last Modified Date.

13. Data Update

If certain fields in the query needs to be modified (unique fields, dates, and assigning configurations), the user would need to delete the data loaded to date through this Smart Connector before making the modifications. This can be done by clicking  Delete , which is next to the Smart Connector names in the Smart Connector list.

However, a user can include additional fields on the query similar to if we were to add a new field from a .csv file to a Smart Connector. User can do this without deleting the previously loaded data.  

Partial / full fetch apply to existing and new assigning in regards to filling the database with these new fields. This functionality is in line with all other Connection Type / Delivery Type options in the system today.

After creating the Smart Connector, users will have to map their Smart Connector data to clients in TapAnalytics and click Update Dashboard before the data starts getting in the system.