As a service administrator, you can use the NetSuite Analytics Warehouse extract service to acquire data from a secure FTP source (SFTP) and use it to create data augmentations.

1. Check the port number for your secure FTP database and create a service request to get the port opened.

   Note:

   You must provide the IP address of the SFTP server, which should be a public IP and can’t be hostname and a fully qualified domain name (FQDN) or a class A private IP.
2. In NetSuite Analytics Warehouse, create the SFTP data connection using these instructions:
   1. In NetSuite Analytics Warehouse Console, click Data Configuration under Application Administration.
   2. On the Data Configuration page, click Manage Connections.
   3. On the Manage Connections page, click Create and then click Connection.
   4. In Create Connection, select Data Extraction in Usage Type, and then select SFTP as the connection type.
      
      

      

      
      
   5. In the dialog for the SFTP connection, select Standard in Connectivity Type, enter an email address to receive notifications in Notification Email, and provide applicable values in Remote Host, User Name, Private Key, Remote Host Extract Files Directory, File Type, CSV Delimiter, CSV Date Format, and CSV Timestamp Format. In Lock Metadata, specify whether you want to turn off the metadata extracts after first refresh if metadata isn't going to change. This option is useful if the flag to derive metadata from data files using the metadata utility is turned on in your source. In Remote Host, ensure that you specify an SFTP Server that supports FIPS Compliant key exchange algorithms.
      Ensure the following:

      • The table name and file name in your SFTP source needs to be the same.
      • The private key you provide is in the valid OpenSSH format and the minimum number of bits in the key should be 2048.
      • Date format for the data files must match the date format in your SFTP source; for example, if you've used MM/dd/yyyy (01/23/1998) in your source, then you must specify the same format in CSV Date Format. See About Date and Timestamp Formatting for CSV File-based Extractors.
      • Timestamp format for the data files must match the timestamp format in your SFTP source; for example, if you've used MM/dd/yyyy hh:mm:ss (01/23/1998 12:00:20) in your source, then you must specify the same format in CSV Timestamp Format.

      
      

      

      
      
   6. Verify that the Refresh Metadata toggle is enabled to ensure that the metadata is refreshed when you save the connection.
      You can later refresh the metadata from the Actions menu on the Manage Connections page, if required.

      Note:

      You can’t create augmentations for SFTP unless you perform a metadata extract.
   7. Click Save.
3. In your SFTP source:
   1. Create the folder structure in the Bucket using these guidelines:
      

      • The base folder in the bucket must match with the details provided in the connection.
      • Inside the base folder, ensure to place each file in its own folder.
      • Ensure that the Prefix of Data_Store_Name (same as Folder name) and Files in the target folder match exactly.

      See the "To create a folder or subfolder" section in Using the Console.

   2. Inside the base folder, create the metadata file for the Data Store List. This file lists the supported data stores. Each data store is a folder that has the actual file used in data augmentation, for example, ASSETS. Ensure that the file name and folder name match and there aren’t any special characters (including space) in the datastore, folder or file names.
      
   3. Create the metadata file for each data file under the data store folder using these guidelines:

      The META_DATASTORES.csv must have these columns:

      • DATA_STORE_NAME - A mandatory column to identify the data store name.
      • DATA_STORE_LABEL - A non-mandatory column that identifies the description of the data store.

      Each folder must have:

      • A data file that has the actual data that gets loaded into NetSuite Analytics Warehouse. This file must have a prefix with the DATA STORE NAME.
      • A metadata file for the list of columns contains all the column information on the data. This file must have a Prefix with META_DATASTORES_<DATA_STORE_NAME>_COL.
        • For the columns in this metadata, ensure the following:
        • If column name is ABC, then metadata can be ABC or “ABC” - the double quotes are ignored.
        • If column name is “ABC”, then metadata must be “”ABC”” – the first double quotes are ignored.

      Example

      In the image, the folder name is ACTIVITY_TYPES. Hence, the data store name is ACTIVITY_TYPES. You can confirm this from the META_DATASTORES.csv file. In this example, the file is named ACTIVITY_TYPES.xlsx or ACTIVITY_TYPES.csv. The metadata file must be META_DATASTORES_ACTIVITY_TYPES_COL.csv.

      The META_DATASTORES_ACTIVITY_TYPES_COL.csv has these columns:

      • DATA_STORE_NAME - This is a mandatory column.
      • COLUMN_NAME - This is a mandatory column.
      • COLUMN_LABEL - This is a non-mandatory column.
      • DATA_TYPE – This is a mandatory column.
      • WIDTH – This column identifies the string length.
      • PRECISION - This column value must be Numeric data type.
      • SCALE - This column value must be Numeric data type.
      • KEY_SEQUENCE - This is a mandatory column that identifies the Primary Key definition. If you’re using the composite primary key, then use column order numbers as values.
4. On the Manage Connections page, select Actions for the SFTP connection and then select Test Connection.
5. To delete a connection, on the Manage Connections page, click Actions for the applicable connection and select Delete.

   Note:

   Ensure that you delete the functional areas, data augmentations, and custom data configurations related to the data connection before deleting it. You can't update or load data from deleted data connections to the warehouse.
6. After the connections are successfully established, navigate to the Data Augmentation tile on the Data Configuration page, select the connection in Data Source, and create a data augmentation using the SFTP data. Select the applicable secure FTP source tables. See Augment Your Data.