Skip to main content
Sign in

Data Sources

  • Updated

 

Data sources are the Openprise objects that connect to your actual data for the purpose of pulling data into Openprise from your source systems.  Your data can be in cloud systems such as Marketo, Salesforce or Eloqua. Or your data can be stored in cloud drives such as Google Drive, Box, Dropbox, or even FTP servers.  We even support cloud databases such as Amazon Redshift or Amazon RDS MySQL databases.

To Create a Data Source

DS_01.png

  1. Go to Data - Data Sources
  2. Click on "Add Data Source" and select Standard Connector.  To learn more about Open Connector, please click HERE
  3. Data Source Name – required. This name will be used when naming the import data source for jobs.
  4. Data Source Administrators – required, only users with Administrator rights can use the Data Source.
  5. Source Technology and Data Format – Select the source system (can be a sandbox or production environment).
  6. Add Account Information – Add a valid user for your system.
  7. Directory or Entity – Folder where the file resides for Cloud drives or Entity for Cloud Systems or Cloud Databases.
    • If you are importing a static list from Marketo List Members, you will need to select the name of the list on the left and move it to the right using the Add button
    • list1.png
  8. Click NEXT

Depending on the source technology and data format selected, the following options may be presented:

  1. Automatically run assessment report – select this option to have Openprise run a data assessment report, and select the frequency (Weekly, Monthly) from the drop-down list.
  2. Email report summary to data source administrators – select this option to email the summary automatically.

DS_02.png

Data sources created from a file will provide some additional configuration options:

_5.png

  1.  Import one file at a time.  This option is helpful if you plan to process multiple files through a data source, such as in a list import process.
  2. Move processed files after importing denotes that Openprise will automatically create a sub-folder in your directory for processed files.  This option will automatically be checked if import one file at a time is selected.
  3. Time zone
  4. Text delimiter
  5. Import fields by name. This option imports field by column name instead of the default column position. Column Name (ex. Company) must be exact in every file imported to this data source.  
  6. Skip import if the file is missing fields
  7. Import additional fields.  This option allows the system to import additional fields if your goal is to solely keep the information of those additional fields rather than perform any transformations on them within the platform.  For example: You set up your Data Source with 10 fields and then you import a new file with 15 fields where 5 of those fields are new. This checkbox gives you the option to import and carry those 5 extra fields throughout the jobs so you can export them with the rest of the fields. You will not be able to see the extra 5 fields in the jobs/data source. 
  8. Truncate values that exceed a certain number of characters.

*NOTE: Data source importing only supports formats of mm/dd/yyyy or mm-dd-yyyy formats. If a date is imported as dd/mm/yy or dd-mm-yy, the dates would not convert properly. For example, 30-10-2020 would become 6-10-2022 because 30 is treated as 30 months which means 24 months (2 years) and 6 months and translates to adding 2 years to the year value resulting in 6-10-2022.

Once a data source has been created, data can be imported. See Advanced Topics below 

To Import a Data Source

To import a data source, click on the card of your data source and select Import Now.

DS_03.png

The Data Source menu options are described below:

  • Configuration – select to change the configuration of the data. This is useful if you want to change the attributes you are pulling in from your Salesforce, Marketo, etc. system. When changing the data source configuration, make sure you select the Parse option (or tab) before proceeding to the Map option. During loading of the Parse page, Openprise goes to your source system to get the latest data configuration (commonly called a schema), so if you skip the Parse step, you will not be able to view the most up-to-date data configuration.
  • View Data – use this option to view data belonging to the data source.
  • Import Now – Select this option to load any new data into the Data Source. It is important to note that creating a Data Source doesn't load the data automatically. You must import the data either by selecting this option, or creating a bot with a step that imports data into the data source.  With each Data Source import, Openprise gets any changed or new records from the source system. If there is no method for Openprise to determine if a record is new or has been changed since the last import, all data in the source system will be imported. Note: There is a limit for how many direct connections can be made at the same time to the same target system. Due to this limitation, it may take some time to import data as each import and export must be queued to accommodate the system's limitations.
  • Re-sync-Full Re-sync - This option is currently available for Salesforce and Marketo. This option will remove deleted records, add new records, add or remove attributes and update all data to time of re-sync. Jobs and/or Bots will continue to run when using this option.
  • Re-sync - Deleted record detection only - This option is currently available for Salesforce and Marketo. This option checks every record already imported in Openprise to see if it was deleted in the users CRM system. If a record has been deleted from the CRM system, it will be purged from the data source inside Openprise. Jobs and/or Bots will continue to run when using this option.
  • Download – select this option to download your data. After naming the data download file, the data is available under the Data Downloads tab. Large data sources will take some time to be ready for download.
  • Purge – Purge all data – Use this option to delete all data in your data source. This option, along with a subsequent data import, is useful for systems that delete records directly (as opposed to "marking them deleted"). For these systems (Marketo, Salesforce, etc.), Openprise cannot tell when a record has been deleted in the target system directly, so a periodic purge and re-import is recommended to sync the data between Openprise and the target system. When purging and re-importing, all jobs and/or bots will need to be paused to avoid errors based on data not being available.
  • Purge – Filter then purge – This option works in the same manner as "Purge all data", except you can first filter the records before purging to delete a portion of the data in your Openprise Data Source.
  • Delete – use this option to delete the data source AND the data in the data source. If the data source belongs to a bot, an error message will be displayed and the data source will not be deleted. However, if the data source is used by a job and is deleted, the job will be marked with errors (as seen in red text on the job screen).

Advanced Topics:

Filtering records on import

In some environments, there may be reasons to limit the records imported into Openprise. For example, if there are sensitive records in the source system that cannot be exported or if there are extraneous records that do not need to be in Openprise. In these cases, an import filter can be defined in the data source configuration as seen in the filter below.

GDrive_04.png

 

Some samples of the import filter are as follows:

 

For string fields
 LastName = 'foo'
  LastName = null
  Lastname LIKE 'appl%'
  City != 'San Francisco'
  BillingState IN ('California', 'New York')
  BillingState NOT IN ('California', 'New York') 

For multi-select picklist fields
 MSP1__c = 'AAA;BBB'
  MSP1__c includes ('AAA;BBB', 'CCC')

The first filter returns records where MSP1__c has both AAA and BBB selected.
The second filter returns records where MSP1__c either have AAA and BBB selected or just CCC.

For date fields
 CreatedDate  2005-10-08T01:02:03Z
  CreatedDate <1999-01-01 createddate="CreatedDate"> LAST_WEEK</1999-01-01>

For boolean fields
 BooleanField = TRUE
  BooleanField = FALSE

Using logical operations with multiple fields
 LastName = 'foo' OR FirstName != 'bar'
  Company IN ('openprise', 'opt') AND Country = 'USA'
  CreatedDate  2021-01-01T00:00:00Z OR (Company IN ('openprise', 'opt') AND Country = 'USA')

Using object relationship in filters
 Account.Name = 'foo'

If the data source is importing Salesforce Contact records, then the above filter will 
retrieve only contacts where the associated account's name is "foo".

 

Note that this feature is currently available for Salesforce related data sources only. It will be expanded to other source systems in future releases.

Notes:

  1. If you are connecting to a system that you are hosting in the cloud (for example, a MySQL database on the AWS cloud, an sFTP server, or AWS S3 bucket), please contact your Customer Success Manager to get IP addresses to whitelist. The whitelist IP addresses are needed to allow Openprise to connect to your servers.
  2. For additional help on connecting to Redshift, click HERE.
  3. For additional help on connecting to an Amazon S3 bucket, click HERE.

 

 

 

Related articles