If you don’t see data that you are expecting to see in one or more tables, there are a few steps you can take to diagnose the issue…

Verify that the file(s) containing the data in question was successfully posted to the SFTP location. Most SFTP software includes search functionality that allows you to search the relevant folder for the filename or a portion of the filename (e.g. filename contains ‘foo’)

Resolution: If the file was not posted to the SFTP location, attempt to re-post (or re-request) the data and check the Redshift table to see if it has loaded. Depending on the size of the file and other files in the processing queue, this could take anywhere from a few seconds to several minutes.

If the file was successfully posted to the SFTP location, download a copy of the file to your desktop and open the file in a text editor (e.g. Notepad) or Excel to check the following issues that could result in a load failure:

In this URL the presence of various special characters can cause the data trainer to take a long time to infer the data resident in that column:

Resolution: Fix the error(s) in the file, re-post the file to the SFTP location with a new name (e.g. if the original file was named some_data_20150101.csv rename the new file to something like some_data_20150101_2.csv) and check the target warehouse table to see if it has been successfully loaded.

If the file passes the above checks, please submit a support ticket by emailing support@openbridge.com so that a support agent can assist with the diagnosis and resolution of the load error. To facilitate the diagnostic efforts please be sure to include the following in the email:

The initial load file is the first file that is posted to a particular folder. This file is what generates and defines the properties for the respective warehouse table (field names, order, and formats). Subsequent files posted to a folder need to have the same specifications as the initial load file to be loaded successfully.

The Openbridge platform creates a unique id (ob_transaction_id) based on the hash for each row of data processed. If a row of data is subsequently processed with the same hash value, it will automatically be excluded from the load process for the table in question. However, if any field values differ, the hash will also be different and the data will be loaded. Let's look at an example...

Let's say on 12/11/15 you posted a file named 20151211_ad_performance.csv to a table named ad_performance:

The records loaded to the ad_performance table

You will see that the system fields 'ob_transaction_id' and ob_modified_date have been added to your table and represent the unique id for that record and the timestamp that the record was loaded to the table.

Then let's say the next day, 12/12/15 that you posted another file named 20151212_ad_performance.csv that included ad performance data for both 12/10/15 and 12/11/15:

The Openbridge platform will analyze this file and identify the 1st and 3rd records on this file as duplicate records because all field values are the same and exclude these records from the load process into the ad_performance table. 

However, let's say that your data changes slightly over time or reflects lifetime values for a particular metric and the file you posted on 12/12/15 looks like this...

In this case, the value in the impressions field for ADID on 12/10/15 changed from 120 to 121. As a result, the row of data will no longer have the same hash value as the row from the previous file and it will be loaded to the ad_performance table

As you can see, there are now two rows of data for ADID 0123 on 12/10/15 with different values for ob_transaction_id and ob_modified_date. Depending on how your reporting queries are structured this could result in overstating impressions for ADID 0123 on 12/10/15.

If your data changes over time, it is best practice to only load data for a particular date one time. If you are faced with this situation you have a couple of options...

If the number or order of fields in your file changes and it no longer matches the layout of the initial 'blueprint' file used to create the table, the new file will fail to load. If the change to the file layout delivered from your source is valid (i.e. not the result of an upstream processing error) you have a couple of options to resolve the issue.

If only the name of the column on the source file changes (e.g. click_percent  becomes click_pct ) but the underlying data type and order of fields stay the same, the file should be processed successfully. The field name in the resulting warehouse table will continue to reflect the original field name. If for some reason you need to change the name of the field in the new table, contact Openbridge Support (support@openbridge.com).

If only the naming convention of the source file changes (e.g. ad_performance_daily.csv  becomes ad_performance_yyyymmdd.csv ) but the underlying data type and order of fields stay the same, the file should be processed successfully.

The Openbridge pipeline is designed to automatically load delimited text files with one header row. If the files delivered by your source system cannot be modified to remove extraneous header (or footer) rows you will need to contact Openbridge Support (support@openbridge.com) to modify the table blueprint to ignore those rows. For example, let's say your source files looks like this...

In this example, there are 4 additional rows above the column headers and a footer row with summarized totals for the CLICKS and IMPRESSIONS fields. The initial load of this file will fail because the first 4 rows do not contain valid delimiters. You do not want to load either the additional header or footer rows as it will impact your ability to query these fields in the Redshift table. We will need to modify the 'blueprint' for these files to ignore the first 4 rows and the last (footer) row.

Once that change has been implemented, as long as the source files follow the same layout and structure as the sample file above, they will successfully load on an automated basis.

Pipeline supports the SFTP transfer method. SFTP is integrated into many graphical tools or can be done directly via the command line.

FTPES and FTP protocols are not supported. FTP is an insecure protocol that should only be used in limited cases or on networks you trust. 

SFTP is based on the SSH2 protocol, which encodes activity over a secure channel. Unlike FTP, SSH2 only uses a single TCP connection and sends multiple transfers, or "channels", over that single connection.

Openbridge currently supports public key and password authentication for both SFTP and SCP file transfer protocols. We do NOT support any shell access. Also, not all SFTP commands are accepted. 

If you need to use public-key authentication, please submit a support ticket to support@openbridge.com and we can set that up for you. 

Please be aware that the use of SSH keys does require setup, configuration, and validation. This is especially true for upstream systems like Adobe as they require a public key to be attached to each unique Openbridge account.  Normally it can take 24-48 hours to activate custom SSH keys for an account given the various requirements of each upstream system.  

Connection Details:

NOTE: If you need a  host with a static IP address, you can use the host name of static.pipeline.openbridge.io .

If your network blocks outbound connections and requires whitelisting the servers internal resources will connect with you can use these two IP address:

Openbridge does not support the use of FTP. We recognize that there are some systems that can only deliver data via FTP. For example, many of the Adobe export processes typically occur via FTP. However, it should be noted that the use of FTP offers no encryption for connection and data transport. Using the FTP protocol is regarded to be unsafe. It is therefore advisable to use SFTP connections to ensure that data is securely transferred.

If you need FTP access, please contact support.

Your corporate firewall may be blocking outbound connections on port 22 . If you are having connection difficulties, please make sure you can make outbound network connections for SFTP make sure outbound port:22 or port:443 is open. Either can be used to connect.

To help protect the integrity of the data sent to Openbridge, we do not allow the delivery of certain files. For example, you are not allowed to send a file called app.exe or my.sql. This reduces the potential for introducing unwanted or malicious software threats. The following is a sample of the blocked files:

If you attempt to send a file that matches a blocked file then the transfer will not be allowed.

Hidden files are not allowed on the server. Hidden files have a dot prefix . in the file name. For example, .file.txt or .foo would be considered hidden files and be blocked.

The following are examples of names using a dot prefix:

If you attempt to send a file that contains a . prefix the transfer will not be allowed.

Normally data delivered to the SFTP server will be routed to the target data warehouse. However, there is one exception. Any files placed into a /testing directory will be ignored. By default, each user has a /testing folder generated for them upon login. This is a safe place to upload files without the risk of the data flowing downstream to the data warehouse.

In most cases, file transfers occur without incident. However, there are situations where an error may arise. Troubleshooting an error can be a difficult and time-consuming endeavor, especially in cases where a system may be sending 100s or 1000s of files a day.

Openbridge does not have visibility into what should be sent from a source system. It only knows what was sent by the source system. For example, if there are 100 files in a source system and only 50 were sent, Openbridge is only aware of the 50 delivered files. We will not know that an additional 50 files were not delivered.

The source system should be tracking what was delivered and what was not delivered. We suggest that a manifest of files is maintained in the source system. This manifest would identify the files to be delivered and their state (success, failure, pending).

The manifest procedure allows the source system to recover from certain errors such as failure of the network, source/host system, or in the transfer process. In the event of an error, the source system would know to attempt redelivery for any file that had not received a successful 226  code from Openbridge.

Your client will normally be sent a response code of 226 to indicate a successful file transfer. However, there are other status codes that may be present. See below:

If you are stuck with an error condition, reach out to Openbridge Support (support@openbridge.com) for help.

Yes, Openbridge uses checksum (or hash) validation to ensure the integrity of the file transfer. Openbridge uses a 128-bit MD5 checksum, which is represented by a 32-character hexadecimal number. This only applies to files once they are in our possession. We suggest that source systems also calculate MD5 checksum in advance of file delivery to increase the confidence that the integrity of the file to be delivered is met. Depending on the tools employed by a source system, the checksum process might be built-in.

Employing file integrity checks will help you cross-check that the files delivered from the source system match what was delivered to Openbridge. While the use of MD5 is the default, Openbridge can support other checksum commands when sending data to us:

If you need assistance with any of these, please contact Openbridge Support.

Openbridge employs a dynamic "ban" list that prevents the banned user or host from logging in to the server. This will occur if our system detects 4 incorrect login attempts. The ban will last for approximately 30 minutes at which time you can attempt to log in again. If you continue to have difficulties please contact support.

Openbridge also employs a DNSBL (commonly known as a 'Blocklist"). This is a database that is queried in real time for the purpose of obtaining an opinion on the origin of incoming hosts. The role of a DNSBL is to assess whether a particular IP Address meets the acceptance policies of inbound connections. DNSBL is often used by email, web, and other network services for determining and rejecting addresses known to be sources of spam, phishing, and other unwanted behavior.

More information on DNS blacklists can be found here:(http://en.wikipedia.org/wiki/DNSBL)

Openbridge sets the maximum number of seconds a connection between our server and your client can exist after the client has successfully authenticated. This is typically 10 minutes or 600 seconds in length. If you are idle for longer than this time allotment the server will think you are finished and disconnect your client. If this occurs you will simply need to reconnect.