Extract, Transform, and Load (ETL)

Overview of the Historian Extract, Transform, and Load (ETL) Tools

Transferring data from one Historian server to another is typically performed by Proficy Historian collectors. These collectors provide a connected streaming data transfer mechanism (except the calculation and file transfer collectors). In a system where a steady network connection is not possible or not cost-effective, a periodic file-oriented data transfer is preferred. The Historian ETL tools consist of a comprehensive set of file-oriented data extraction, transfer, and loading tools.

Potential ways of using ETL tools:
  • Data transfer via radio or low bandwidth cellular connection
  • Data transfer where there is no connectivity (read and write using portable media)
  • Data transfer for periodic connectivity applications (for example, ships can transfer data when they arrive at a port)
  • Data migration from OSI PI Server to Proficy Historian
  • Data extraction to import into other applications
  • Data import from other applications
Components of Historian ETL:
  • Extract: Using this tool, you can extract time series data from Proficy Historian or PI Server. For Proficy Historian, you can also extract alarms and events data, perform scaling and absolute deadband compression.
  • Transform: Using this tool, you can transfer data from an onsite Historian server to the destination Historian server using a file-sharing application such as FTP, BITS, and so on.
  • Load: Using this tool, you can load data into Proficy Historian. This tool monitors a file directory, unzips the files, and processes them.
Depending on the use case, you can use these tools independently or together.
Limitations:
  • The ETL tools do not support array data.

Historian ETL Workflow

To transfer data using Historian ETL, you must perform the following steps.
Step Number Description Notes
1 Install Historian ETL. This step is required. You must install ETL on both the source and destination machines of the data transfer.
Note: If you want to upgrade ETL:
  1. Uninstall the existing version of ETL.
  2. Backup the configuration files, and delete them.
  3. Install the latest version of ETL.
2 Extract data from Proficy Historian or PI Server. This step is required. It involves extracting tag data and compressing it so that it can be transferred to the destination Historian server.
3 Transfer the data using BITS, FTP, or any other file-sharing application. This step is required. It involves setting up the file-sharing application that you want to use and then transferring the data to the machine on which the destination Historian server is installed.
4 Load the data into the destination Historian server. This step is optional. It involves extracting the .zip files transferred by the file-sharing application and then loading the data into the destination Historian server.
Note: Depending on the use case, you can use these tools independently or together.

About Prerequisites

Before you extract data, you must specify the Proficy Historian and/or PI Server tags and their properties whose data you want to extract. You can do this in one of the following methods:

Create a Tag Configuration File Manually

Procedure

Create an .xml file to include the tag properties for all the tags whose data you want to extract.
Tag properties for a tag named Pressure used in Proficy Historian:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Taglist>

<Tag Name="Pressure">

<LocalName>ValvePressure</LocalName>

<RemoteName>ValvePressure</RemoteName>

<Compression>1</Compression>

<DeadbandRange>2.5</DeadbandRange>

<DeadbandTimeout>2</DeadbandTimeout>

<RequireRescale>1</RequireRescale>

<HiEng>10</HiEng>

<LowEng>8</LowEng>

<HiScale>10</HiScale>

<LowScale>8</LowScale>

</Tag>

</Taglist>
Tag properties for a tag named Pressure used in PI Server:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Taglist>

<Tag Name="Pressure">

<LocalName>ValvePressure</LocalName>

<RemoteName>ValvePressure</RemoteName>

</Tag>

</Taglist>

What to do next

Extract the tag data.

Specify Tags Using a Template Spreadsheet

About this task

This topic describes how to specify these tags and their properties using a template spreadsheet, which is provided with the Historian ETL package. You will then export the data into an .xml file.

Procedure

  1. If you want to specify tags for Proficy Historian, access the ProficyHistTagConfigGenerateExcel.xlsx file located in the Historian ETL Extract folder. If you want to specify tags for PI Server, access the PIHistTagConfigGenerateExcel file located in the Historian ETL PI Extract folder.
  2. Optional: If you want to modify an existing tag configuration file, you can import the data from that .xml file by selecting Developer > Import.
    Tip: If the Developer tab is not available, right-click the menu bar of the spreadsheet, select Customize the Ribbon, and then select the Developer check box.
    Data from the .xml file is imported into the spreadsheet.
    Note: If an error occurs, stating that the data is not valid according to the schema, verify that:
    • You have provided values in all the red-colored columns.
    • There are no blank rows.
    • The values that you have entered are valid and of the same data type as defined for each property.
    For details, refer to the Tag Properties topic.
  3. For each tag, enter or modify values in the columns for the tag properties. A value is required in the red-colored columns.
  4. Save the file.
  5. Select Developer > Export.
    Tip: If the Developer tab is not available, right-select the menu bar of the spreadsheet, select Customize the Ribbon, and then select the Developer check box.
  6. Enter a name and location for the .xml file.
    The tag configuration file is created with the list of tags and their properties to be extracted.
    Note: If an error occurs, stating that the data is not valid according to the schema, verify that:
    • You have provided values in all the red-colored columns.
    • Values in the Name column are unique.
    • There are no blank rows.
    • The values that you have entered are valid and of the same data type as defined for each property.
    For details, refer to the Tag Properties topic.

What to do next

Extract the tag data.

Specify Tags Using a New Spreadsheet

About this task

This topic describes how to specify these tags using a new spreadsheet. You will then export the data into an .xml file.

Procedure

  1. Create a Microsoft Excel file.
  2. Enter column names matching the names of the tag properties.
  3. Optional: If you want to modify an existing tag configuration file, you can import the data from that .xml file by selecting Developer > Import.
    Tip: If the Developer tab is not available, right-select the menu bar of the spreadsheet, select Customize the Ribbon, and then select the Developer check box.
    Note: If a message appears, stating that the .xml file is not linked to schema, ignore the message.
  4. Import the schema:
    1. Select Developer > Source > XML Maps > Add.
      Tip: If the Developer tab is not available, right-select the menu bar of the spreadsheet, select Customize the Ribbon, and then select the Developer check box.
    2. If you want to specify tags for Proficy Historian, select the ProficyHistTagConfigSchema file located in the Historian ETL Extract folder. If you want to specify tags for PI Server, select the PIHistTagConfigSchema file located in the Historian ETL PI Extract folder.
  5. Map each column name with each entry under Taglist in the XML Source section. To do so, select each column name, and then double-click the corresponding property under Taglist.
    Each property under Taglist changes to bold formatting, indicating that it is mapped to the corresponding column.
  6. For each tag, enter or modify values in the columns.
  7. Select Design > Properties, select the Validate data against schema for import and export check box, and then select OK.
  8. Save the file.
  9. Select Developer > Export.
  10. Enter a name and location for the .xml file.
    The tag configuration file is created with the list of tags and their properties to be extracted.
    Note: If an error occurs, stating that the data is not valid according to the schema, verify that:
    • You have provided values in all the red-colored columns.
    • Values in the Name column are unique.
    • There are no blank rows.
    • The values that you have entered are valid and of the same data type as defined for each property.
    For details, refer to the Tag Properties topic.

What to do next

Extract the tag data.

Tag Properties

This topic provides a list of tag properties that you can define for each tag that you want to extract from Proficy Historian or PI Server.
Table 1. Tag properties for Proficy Historian Tags
Column Name Data Type Description
Name String Enter the name of the tag. A value is required and must be unique.
LocalName String Enter the local name of the tag. A value is required.
RemoteName String Enter the remote name of the tag. A value is required.
Compression Boolean
  • Enter 1 if you want to enable collector compression.
  • Enter 0 if you do not want to enable collector compression.
DeadbandRange Numeric Enter the collector deadband range (only absolute values, not in percentage).
DeadbandTimeout Numeric Enter the collector compression timeout for the tag.
RequireRescale Boolean
  • Enter 1 if you want to enable scaling, which converts an input data point to an engineering units value.
  • Enter 0 if you do not want to enable scaling.
HiEng Numeric Enter the upper limit in engineering units for the tag.
LowEng Numeric Enter the lower limit in engineering units for the tag.
HiScale Numeric Enter the upper limit for the tag value if scaling is enabled.
LowScale Numeric Enter the lower limit for the tag value if scaling is enabled.
Table 2. Tag Properties for PI Server Tags
Column Name Data Type Description
Name String Enter the name of the tag. A value is required and must be unique.
LocalName String Enter the local name of the tag. A value is required.
RemoteName String Enter the remote name of the tag. A value is required.

About Extracting Data

You can extract data from one of the following sources:
  • Proficy Historian: This is performed by the Historian ETL Extract tool as follows:
    1. Extracts data related to tags into text files, which are named in the following format: YYYYDDMMHHRR_<onsite Historian computer name>.txt. These files are stored in the following folder: <Historian ETL installation location>/Historian ETL Extract/HistFiles.
      Note: Data related to alarms and events is stored in .lax files. You can choose not to extract data related to alarms and events.
    2. After a specified number of files are extracted (by default, 6), the files are compressed into a .zip file, which is named in the following format: YYYYDDMMHHRR_<onsite Historian computer name>.zip. These files are stored in the following folder: <Historian ETL installation location>/Historian ETL Extract/ZipFiles.
    3. Deletes the text files in the <Historian ETL installation location>/Historian ETL Extract/HistFiles folder after they are compressed.

    Before you extract data using this tool, you must configure this tool.

  • PI Server: This is performed by the Historian ETL PI Extract tool as follows:
    1. Extracts data related to tags into text files, which are named in the following format: YYYYDDMMHHRR_<onsite Historian computer name>.txt. These files are stored in the following folder: <Historian ETL installation location>/Historian ETL PI Extract/HistFiles.
    2. After a specified number of files are extracted (by default, 6), the files are compressed into a .zip file, which is named in the following format: YYYYDDMMHHRR_<onsite Historian computer name>.zip. These files are stored in the following folder: <Historian ETL installation location>/Historian ETL PI Extract/ZipFiles.
    Note: Encryption is not supported by the Historian ETL PI Extract tool.

    Before you extract data using this tool, you must configure this tool.

Configure the Historian ETL Extract Settings

Before you begin

Complete the prerequisite steps.

About this task

This topic describes how to configure the Historian ETL Extract tool to modify the default folders to store the extracted data, to specify whether data related to alarms and events must be extracted, and so on.
Note: These settings are saved in the HistorianETLExtract.exe.config file.

Procedure

  1. Run the Historian ETL Extract Configuration file located in the Historian ETL Extract folder.
    Tip: You can also enter ETL Historian Extract in Windows Run.
    The Historian ETL Extract Configuration window appears, displaying the Basic Configuration section.
  2. If the configuration details are stored in a file, select Import Config to import the settings. Otherwise, skip to the next step.
  3. Provide values as specified in the following table.
    Field Description Default Value
    Historian Server Enter the host name or IP address of the onsite Historian machine. If you leave it blank, the local host name is considered. Blank
    Historian User Enter the ID of the user to connect to the Historian server on the onsite Historian machine. A value is required only if security is enabled for the Historian server. Blank
    Historian Password Enter the password of the user to connect to the Historian server on the onsite Historian machine. A value is required only if security is enabled for the Historian server. Blank
    Unit ID Enter the unit ID of the machine from which you want to transfer data. OSMName
    Run Interval Enter the interval, in seconds, at which the Historian ETL Extract tool will extract data. You must enter a value greater than or equal to 60. 300 (that is, a text file is created for data that is extracted in 300 seconds)
    Min # of Files to Compress Enter the number of files that must be compressed into a single .zip file. 6 (that is, a .zip file is created for every six text files)
    Alarms & Events Select False if you do not want to extract data related to alarms and events. True
  4. Select Files, and then provide values as specified in the following table.
    Field Description Default Value
    Historian Export Path Enter the path to the folder in which the text files containing the extracted data must be stored. <Installation folder of Historian ETL>/Historian ETL Extract/HistFiles
    Tag Configuration File Enter the path to the tag configuration file that you have created. <Installation folder of Historian ETL>/Historian ETL Extract/OSM_OSMName.xml
    Zip Export Path Enter the path to the folder in which the compressed files must be stored. <Installation folder of Historian ETL>/Historian ETL Extract/ZipFiles
    State File

    Enter the path to the file that the Historian ETL Extract tool will create to store the timestamp of the last successful export. This timestamp is used to identify the start time for next iteration of extraction. This ensures that there is no loss of data during extraction.

    For example, suppose the current time is 11am, and data has been extracted only till 9am. The state file contains the timestamp for 9am. Therefore, when data extraction is resumed, it is extracted from 9am.

    The state file is created after you apply the Historian ETL Extract settings. It is updated each time .zip files are transferred to the destination Historian server or when the Historian ETL Extract tool is stopped. For a sample state file, refer to Example of a State File.

    		 <Installation folder of Historian ETL>/Historian ETL Extract/State.xml
    Regen File Enter the path to the regeneration file. You can use this field to extract historical data.

    If the Generate Sample Regen File field is set to True, a sample regeneration file will be created after you apply the settings. You can modify this file as needed, and specify the path of the same file in the Regen File field. For a sample regeneration file, refer to Example of a Regeneration File.

    		 <installation folder of Historian ETL>\Regen.xml
  5. Select Advanced Configuration, and then provide values as specified in the following table.
    Field Description Default Value
    Log Level Select the log level to indicate the amount of information to be logged. The following options are available:
    • Info
    • Error
    • Debug
    		 Info
    Catch Up Interval Enter the catch up interval, in seconds, used to size files when catching up to the current time. 10
    Save Limit Enter the number of files to be exported after which the State.xml file must be updated. 20
    Catch Up Time Limit Enter the maximum time, in hours, to go back when catching up after a restart.

    You can use this field to extract historical data.

    		 168
    Delay Interval The duration, in seconds, by which the data retrieval time will be reduced. For example, if data will be retrieved for 10 minutes, and if you enter 60 in this field, data for the last 60 seconds will not be retrieved in that batch; it will be retrieved in the next batch. This will ensure the retrieval of any dynamic records that were updated in that duration. 60
    Sample Regen File Enter the path to the sample regeneration file that will be created if the Generate Sample Regen File field is set to True. You can modify this file as needed. <installation folder of Historian ETL>\Sample_Regen.xml
    Generate Sample Regen File Select True if you want to generate the sample regeneration file. You can then modify this file as needed. False
  6. Select Save.
    The changes to the settings are applied and saved in the HistorianETLExtract.exe.config file.

What to do next

Start the data extraction.

Configure Historian ETL PI Extract Settings

Before you begin

Complete the prerequisite steps.

About this task

This topic describes how to configure the Historian ETL PI Extract tool to modify the default folders to store the extracted data.
Note: These settings are saved in the PIHistorianETLExtract.exe.config file.

Procedure

  1. Run the Historian ETL PI Extract Configuration file located in the Historian ETL PI Extract folder.
    Tip: You can also enter ETL PI Extract in Windows Run.
    The Historian ETL PI Extract Configuration window appears, displaying the Basic Configuration section.
  2. If the configuration details are stored in a file, select Import Config to import the settings. Otherwise, skip to the next step.
  3. Provide values as specified in the following table.
    FieldDescriptionDefault Value
    Historian ServerEnter the host name or IP address of the PI Server machine.Blank
    Historian UserEnter the ID of the user to connect to the PI Server machine. A value is required only if security is enabled for the Historian server.Blank
    Historian PasswordEnter the password of the user to connect to the PI Server machine. A value is required only if security is enabled for the Historian server.Blank
    Unit IDEnter the unit ID of the machine from which you want to transfer data. OSMName
    Run IntervalEnter the interval, in seconds, at which the Historian ETL PI Extract tool will extract data. You must enter a value greater than or equal to 60. 150 (that is, a text file is created for data that is extracted in 150 seconds)
    Min # of Files to CompressEnter the number of files that must be compressed into a single .zip file.6 (that is, a .zip file is created for every six text files)
  4. Select Files, and then provide values as specified in the following table.
    FieldDescriptionDefault Value
    Historian Export PathEnter the path to the folder in which the text files containing the extracted data must be stored.<Installation folder of Historian ETL>/Historian ETL PI Extract/HistFiles
    Tag Configuration FileEnter the path to the tag configuration file that you have created.<Installation folder of Historian ETL>/Historian ETL PI Extract/OSM_OSMName.xml
    Zip Export PathEnter the path to the folder in which the compressed files must be stored.<Installation folder of Historian ETL>/Historian ETL PI Extract/ZipFiles
    State File

    Enter the path to the file that the Historian ETL PI Extract tool will create to store the timestamp of the last successful export. This timestamp is used to identify the start time for next iteration of extraction. This ensures that there is no loss of data during extraction.

    For example, suppose the current time is 11am, and data has been extracted only till 9am. The state file contains the timestamp for 9am. Therefore, when data extraction is resumed, it is extracted from 9am.

    The state file is created after you apply the settings. It is updated each time .zip files are transferred to the destination Historian server or when the Historian PI ETL Extract tool is stopped. For a sample state file, refer to Example of a State File.

    		<Installation folder of Historian ETL>/Historian ETL PI Extract/State.xml
  5. Select Advanced Configuration, and then provide values as specified in the following table.
    FieldDescriptionDefault Value
    Log LevelSelect the log level to indicate the amount of information to be logged. The following options are available:
    • Info
    • Error
    • Debug
    		Info
    Catch Up IntervalEnter the catch up interval, in seconds, used to size files when catching up to the current time.10
    Regen FileEnter the path to the regeneration file. You can use this field to extract historical data.

    If the Generate Sample Regen File field is set to True, a sample regeneration file will be created after you apply the settings. You can modify this file as needed, and specify the path of the same file in the Regen File field. For a sample regeneration file, refer to Example of a Regeneration File.

    		<installation folder of Historian ETL>\Regen.xml
    Save LimitEnter the number of files to be exported after which the State.xml file must be updated.20
    Catch Up Time LimitEnter the maximum time, in hours, to go back when catching up after a restart.

    You can use this field to extract historical data.

    		168
    Delay IntervalThe duration, in seconds, by which the data retrieval time will be reduced. For example, if data will be retrieved for 10 minutes, and if you enter 60 in this field, data for the last 60 seconds will not be retrieved in that batch; it will be retrieved in the next batch. This will ensure the retrieval of any dynamic records that were updated in that duration.60
    Sample Regen FileEnter the path to the sample regeneration file that will be created if the Generate Sample Regen File field is set to True. You can modify this file as needed.<installation folder of Historian ETL>\Sample_Regen.xml
    Generate Sample Regen FileSelect True if you want to generate the sample regeneration file. You can then modify this file as needed.False
  6. Select Save.
    The changes to the settings are applied and saved in the PIHistorianETLExtract.exe.config file.

What to do next

Start the data extraction.

Extract Historical Data

About this task

By default, the ETL tools extract current data, starting from the time you have configured the tags for data extraction. This topic describes how to extract historical data using the ETL tools.

Procedure

  1. Create a regeneration file, specifying the start time, end time, and interval for which you want to capture the historical data. For a sample regeneration file, refer to Example of a Regeneration File.
  2. If you want to extract data from Proficy Historian, run the Historian ETL Extract Configuration file located in the Historian ETL Extract folder. If you want to extract data from PI Historian, run the Historian ETL PI Extract Configuration file located in the Historian ETL PI Extract folder.
    Tip: You can also enter ETL Historian Extract or ETL PI Extract in Windows Run.
    The Historian ETL Extract Configuration or the Historian ETL PI Extract Configuration window appears, displaying the Basic Configuration section.
  3. Select Advanced Configuration, and then provide values as specified in the following table.
    Field Description Default Value
    Regen File Enter the path to the regeneration file that you have created. <installation folder of Historian ETL>\Regen.xml
    Catch Up Time Limit Enter the duration, in hours, for which you want to extract historical data. For example, if you want to extract data for the past one day, enter 24. 168
    Temp Regen File Enter the path to the temporary regeneration file. <installation folder of Historian ETL>\Temp_Regen.xml
    Generate Temp Regen File Select True. False
  4. As needed, provide values in the remaining fields for Proficy Historian or PI Historian.

What to do next

Start the data extraction.

Start the Data Extraction

Before you begin

Procedure

  1. To start data extraction from a Proficy Historian server:
    1. Run the Historian ETL Extract Configuration file located in the Historian ETL Extract folder.
      The Historian ETL Extract Configuration window appears.
    2. Select Start Service.
      Data extraction from the Proficy Historian server begins.
  2. To start data extraction from PI Server:
    1. Run the Historian ETL PI Extract Configuration file located in the Historian ETL PI Extract folder.
      The Historian ETL PI Extract Configuration window appears.
    2. Select Start Service.
      Data extraction from PI Server begins.
    Tip: If the tool does not start as expected, access the logs using Windows Event Viewer.

What to do next

Transfer data using BITS, FTP, or any other file-sharing application.

About Transferring Data Using Background Intelligent Transfer Service (BITS)

After you extract data from a Proficy Historian or a PI Server, you must transfer it to the destination machine. To do so, you can use BITS, FTP, or any other file-sharing application.

To transfer data using BITS, perform the following steps:
  1. Install the BITS IIS server extension.
  2. Configure the BITS settings.
  3. Transfer data to the destination machine.

Configure BITS

Before you begin

Install the BITS IIS server extension.

Procedure

  1. Using IIS Management Console, navigate to the default website node, select Add Virtual Directory, and create a virtual directory named MD_BITS.
  2. In the MD_BITS folder, create a folder named OSMUploads.
  3. Enable the BITS IIS server extension:
    1. Navigate to the virtual directory in IIS Manager.
    2. From the list of features in the virtual directory, double-click BITS Uploads.
    3. Select the Allow clients to upload files check box, and then select Apply.
  4. Change the port number of the default website in IIS. By default, the port number is 80.
    1. In IIS Manager, in the Connections section, under the computer name > Sites, select Default Web Site.
    2. In the Actions pane, under Edit Site, select Bindings.
    3. In the Site Bindings window, select http > Edit.
    4. In the Edit Site Bindings window, in the Port field, enter the new port number (for example, 6150), and then select OK.
    5. In the Site Bindings window, select Close.
    6. In the Actions section, under Manage Web Site, select Stop, and then select Start.

What to do next

Verify the data transfer settings.

Verify the Data Transfer Settings

This topic provides a list of tasks that you can perform to verify that the data transfer settings are correct.

  • From you onsite Historian machine, using Internet Explorer, verify that you can access a web page created on the destination machine.
  • Verify that the Historian ETL Load tool running on the destination machine is configured to watch the virtual folder in IIS for the incoming .zip files.

Transfer Data using BITS

Before you begin

Configure BITS.

About this task

You can transfer data using BITS by performing one of the following steps:

Procedure

  1. If you want to use the OSM_LBW_Transfer.vbs file to transfer files, perform the following steps:
    1. Verify that the OSM_LBW_Transfer.vbs file is configured to watch the folder in the destination machine in which the .zip files will be placed.
    2. Run the OSM_LBW_Transfer.vbs script by running the OSM_LBW_Transfer.cmd file.
      The script transfers files in the <Historian ETL installation location>/Historian ETL Extract/ZipFiles folder to the destination machine.
  2. If you want to use the OSMBitsDownload.vbs file to download files, perform the following steps.
    1. Access the OSMBitsDownload.vbs file, and verify that the path to the files and folders specified in the file is correct.
    2. Run the OSMBitsDownload.vbs script by running the following command: cscript OSMBitsDownload.vbs.
      The script uses the DownloadFilesToOSM.txt file to fetch the names of the files that must be transferred.
      Important:
      • Ensure that the DownloadFilesToOSM.txt file exists in the same location as the OSMBitsDownload.vbs file.
      • In the DownloadFilesToOSM.txt file, enter the file names that you want to download using the OSMBitsDownload.vbs script. If you want to transfer all zip files, enter *.zip.
      Tip: To access the jobs created by BITS while transferring data, access the BITSADMIN/LIST/ALLUSERS folder. If the tool does not start as expected, access the logs using Windows Event Viewer.

What to do next

Load the data into the destination Historian server.

About Transferring Data Using File Transfer Protocol (FTP)

After you extract data from a Proficy Historian or a PI Server, you must transfer it to the destination Historian server. To do so, you can use BITS, FTP, or any other file-sharing application.
Note: The minimum bandwidth required to transfer data using FTP is 2 KBps.
To transfer data using FTP, perform the following steps:
  1. Install an FTP server on the destination Historian server.
  2. Configure the FTP settings.
  3. Transfer data to the destination Historian server.

Configure FTP

Before you begin

Install FTP on the destination Historian server.

About this task

Procedure

  1. Access the FTPFileTransfer file located in the <Installation folder of Historian ETL>/Historian ETL Transform folder.
    The FTP File Transfer Tool window appears.
  2. If the FTP configuration details are saved in a file, select File > Load, and then select the configuration file that contains the details. Otherwise, skip to the next step.
  3. Provide values as specified in the following table.
    Tip: You can select File > New to create a new instance of the settings.
    FieldDescriptionDefault Value
    FTP Server AddressEnter the address of the FTP server.ftp://127.0.0.1/ftpserver
    FTP UserIDEnter the user ID to log in to the FTP server.Blank
    FTP PasswordEnter the password to log in to the FTP server.Blank
    Directory to MonitorProvide the folder from which files must be transferred.C:\
    File MaskEnter the file mask for the FTP server.*.zip
  4. Optional: Select Test Connection to test the FTP server connection.
  5. Select File > Save to save the configuration details, which you can reuse.
  6. Select File > Detail Log if you want a detailed logging, which will help you in troubleshooting.

Results

The FTP server settings are configured.

What to do next

Transfer data using FTP.

Transfer Data Using FTP

Before you begin

Configure FTP.

Procedure

  1. Access the FTPFileTransfer file located in the <Installation folder of Historian ETL>/Historian ETL Transform folder.
    The FTP File Transfer Tool window appears.
  2. Select Start.
    Data is transferred from the ZipFiles folder to the machine on which the destination Historian is installed.

What to do next

Load the data into the destination Historian server.

About Loading Data

After the data is transferred to the machine on which the destination Historian is installed, you must load it into the destination Historian server using the Historian ETL Load tool. This tool loads data as follows:
  1. Extracts the .zip files in the <Historian ETL installation location>/Historian ETL Load/ZipImportFiles folder and stores the text files in the <Historian ETL installation location>/Historian ETL Load/ImportFiles folder in the destination Historian server.
  2. Loads the data into the destination Historian server.
  3. Deletes the .zip files in the <Historian ETL installation location>/Historian ETL Load/ZipImportFiles folder, and imports the text files to the destination Historian server.
  4. Deletes the text files in the <Historian ETL installation location>/Historian ETL Load/ImportFiles folder after importing them to the destination Historian server.

Configure the Historian ETL Load tool

About this task

This topic describes how to configure the Historian ETL Load service to modify the default folders to store the extracted data, to specify whether data related to alarms and events must be transferred, and so on.
Note: These settings are saved in the HistorianETLLoad.exe.config file.

Procedure

  1. Run the Historian ETL Load Configuration file located in the Historian ETL Load folder.
    Tip: You can also enter ETL Historian Load in Windows Run.
    The Historian ETL Load Configuration window appears, displaying the Basic Configuration section.
  2. If the configuration details are stored in a file, select Import Config to import the settings. Otherwise, skip to the next step.
  3. Provide values as specified in the following table.
    FieldDescriptionDefault Value
    Historian ServerEnter the host name or IP address of the destination Historian server. If you leave this field blank, the local host name is considered.Blank
    Historian UserEnter the ID of the user to connect to the destination Historian server. A value is required only if security is enabled for the destination Historian server.Blank
    Historian PasswordEnter the password of the user to connect to the destination Historian server. A value is required only if security is enabled for the destination Historian server.Blank
    IH Status TagnameEnter the name of the tag that is used to check the state of the destination Historian server. This tag must exist in Historian.IHStatusTag
    Alarms & Events Select False if you do not want to load data related to alarms and events. True
    Add Tag Automatically Select True to create a tag automatically in the destination Proficy Historian server if the tag name that you entered does not exist in the server. False
  4. Select Files, and then provide values as specified in the following table.
    FieldDescriptionDefault Value
    Historian File PathEnter the path to the folder in which the text files containing the extracted data are stored.<Installation folder of Historian ETL>/Historian ETL Load/ImportFiles
    Zip File PathEnter the path to the folder in which the compressed files are available.<Installation folder of Historian ETL>/Historian ETL Load/ZipImportFiles
    Error File PathEnter the path to the folder in which file that could not be loaded or deleted must be stored.<Installation folder of Historian ETL>/Historian ETL Load/ErrorFiles
    Error File Life Time (days) Enter the amount of time, in days, error files must be stored before deletion. 3.5
  5. Select Advanced Configuration, and then provide values as specified in the following table.
    FieldDescriptionDefault Value
    Log Level Select the log level to indicate the amount of information to be logged. The following options are available:
    • INFO
    • ERROR
    • DEBUG
    		 INFO
    Write Batch SizeEnter the number of records to write to the Historian server at one time.
    Tip: A large batch size can produce faster tag value writes, but can lead to poor performance if multiple collectors share the same Historian server. A 1000 to 2000 batch size is ideal for a shared Historian server.
    		1000
    Wait Interval (seconds)Enter the amount to time, in seconds, to wait to check for new files.90
    Wait For ReplySelect False if you do not want the Historian ETL Load tool to wait for the acknowledgement from Historian that files are transferred.True
    Error On ReplaceSelect True if you want an error to be logged if duplicate data is transferred.False
    Max RetriesEnter the maximum number of times the Historian ETL Load tool must try to resend data in case of an error or failure.2
    Retry Timeout (seconds)Enter the amount of time, in seconds, to wait before trying to resend data.5
  6. Select Save.
    The changes to the settings are applied and saved in the HistorianETLLoad.exe.config file.

What to do next

Load data into the destination Historian server.

Load Data into the Destination Historian Server

Before you begin

  1. Configure the ETL Load tool.
  2. Verify that the Historian ETL Load tool running on the destination Historian server is configured to watch the IIS Virtual folder for the incoming .zip files.

Procedure

  1. Run the Historian ETL Load Configuration file located in the Historian ETL Load folder.
    The Historian ETL Load Configuration window appears.
  2. Select Start Service.
    Tip: If the tool does not start as expected, access the logs using Windows Event Viewer.
    The data is loaded in the destination Historian server.

Troubleshooting ETL Issues

Unable to Start the ETL Tools

Issue: When you try to start the Historian ETL Extract, Historian ETL PI Extract, or the Historian ETL Load service, an error occurs.

Diagnostics: If there is a limited bandwidth of the internet, the certificate publisher service times out after 30 seconds. To avoid this issue, disable the publisher service:
  1. Access the configuration file for the service that you want to start. For example, for the Historian ETL Extract service, access the HistorianETLExtract.exe.config file.
  2. Set the element generatePublisherEvidence to disabled.

Data File Format

This topic provides the format of the content in a text file that contains tag data extracted by the Historian ETL Extract tool. Each line in the text file contains the following parameters, separated by commas:
Parameter Description Valid Values
Time The time at which the data is captured. It is displayed in the following format: <epoch format>:<nanoseconds, human-readable>
For example, if the value is 1601890328:76000000, then:
  • 1601890328 indicates the time in the epoch format.
  • 76000000 indicates the time in nanoseconds, human-readable.
The value after the colon is greater than 0 only if the time resolution of the tag is milliseconds or microseconds.		 N/A
Tag name The name of the tag for which the data is captured. Any tag that exists in the destination Historian server
Note: If the AddTagAutomatically parameter is set to True, when you add a tag that does not exist in the Historian server, it will be created.
Data value The value of the data that is captured. N/A
Data type The data type for the value that is captured.
  • short
  • int
  • float
  • Double
  • Single Byte String
Quality The quality of the data that is captured.
  • GOOD
  • BAD
Note:
  • The BLOB data type is not supported.
  • Comments are not captured.
  • Data quality other than GOOD and BAD is not supported.

Example of a Regeneration File

The following lines of code represent the content of a regeneration file, which provides the start time, end time, and interval to export data. After all the requests are processed, the regeneration file is deleted.
Note: The regeneration file has three sections for setting the start time, end time, and interval. These multiple sections act as a backup. That is, if the values provided in the first section are not valid (for example, the start time is later than the end time, interval has a negative value), the values from the next section are considered.
<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:ArrayList id="ref-1" xmlns:a1="http://schemas.microsoft.com/clr/ns/System.Collections">
<_items href="#ref-2"/>
<_size>3</_size>
<_version>3</_version>
</a1:ArrayList>

<SOAP-ENC:Array id="ref-2" SOAP-ENC:arrayType="xsd:anyType[4]">
<item href="#ref-3"/>
<item href="#ref-4"/>
<item href="#ref-5"/>
</SOAP-ENC:Array>

<a3:RegenRequest id="ref-3" xmlns:a3="http://schemas.microsoft.com/clr/nsassem/ETLExtract/HistorianETLExtract%2C%20Version%3D1.9.0.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3Dnull">
<startTime>2020-07-02T09:39:37.3384336+05:30</startTime>
<endTime>2020-07-02T10:39:37.3384336+05:30</endTime>
<interval>3</interval>
</a3:RegenRequest>

<a3:RegenRequest id="ref-4" xmlns:a3="http://schemas.microsoft.com/clr/nsassem/ETLExtract/HistorianETLExtract%2C%20Version%3D1.9.0.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3Dnull">
<startTime>2020-07-02T09:39:37.3384336+05:30</startTime>
<endTime>2020-07-02T10:39:37.3384336+05:30</endTime>
<interval>4.5</interval>
</a3:RegenRequest>

<a3:RegenRequest id="ref-5" xmlns:a3="http://schemas.microsoft.com/clr/nsassem/ETLExtract/HistorianETLExtract%2C%20Version%3D1.9.0.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3Dnull">
<startTime>2020-07-02T09:39:37.3384336+05:30</startTime>
<endTime>2020-07-02T10:39:37.3384336+05:30</endTime>
<interval>14.5</interval>
</a3:RegenRequest>

</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Example of a State File

The following lines of code represent the content of a state file indicating that the last successful export occurred on June 6, 2010 at 5:00:04 PM in the UTC-4 time zone:
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

<SOAP-ENV:Body>

<a1:Persist id="ref-1" xmlns:a1="http://schemas.microsoft.com/clr/nsassem/LBExport/LBExport%2C%20Version%3D1.0.1.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3Dnull">

<lastExport>2010-06-07T17:00:04.2017462-04:00</lastExport>

</a1:Persist>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>