Configure the Microsoft Excel JAVA Agent
Excel Compatibility
The following chart shows the compatibility between Analytics Manager and Microsoft Excel versions.
|
Manager Version
|
Operating System
|
Excel Version
|
Language
|
|---|---|---|---|
|
9.4.x onwards
|
Windows 2019
Windows 2016
Ubuntu 20.04
Ubuntu 18.04
RHEL 8.6
RHEL 9.0
|
Excel 97-2003 (.xls)
Excel Workbook (.xlsx)
|
English (US)
|
Prerequisites
Ensure that you have the following setup on your system before running and configuring the Java Excel agent:
• jdk 11 should be installed.
• Maven.
• ThingWorx Analytics Extension <VERSION> .zip file should be downloaded from the PTC Software Download portal and fully extracted.
Build the Excel Agent
Complete the following steps to build an Excel JAVA agent :
1. Create a settings.xml file and save it at the following path ThingWorx-Analytics-Extensions/analytics-manager-tools/analysis_spreadsheet_agent directory.
Enter the following content :
<settings>
<localRepository>.\.m2</localRepository>
<servers>
</servers>
<mirrors>
</mirrors>
<profiles>
</profiles>
</settings>
<localRepository>.\.m2</localRepository>
<servers>
</servers>
<mirrors>
</mirrors>
<profiles>
</profiles>
</settings>
2. In the extracted ThingWorx-Analytics-Extensions folder, navigate to the following path : ThingWorx-Analytics-Extensions/analytics-manager-tools/analysis_spreadsheet_agent. The following image shows the directory structure inside the analysis_spreadsheet_agent folder:
3. Open the command prompt from the analysis_spreadsheet_agent directory and execute the following command mvn -s settings.xml clean package.
The output of the command would be an analysis_spreadsheet_agent-<version>.jar file located inside an analysis_spreadsheet_agent/target directory.
 | After successfully building the spreadsheet agent, ExcelAgentSupportedFormulas.html would be available in analysis_spreadsheet_agent directory. ExcelAgentSupportedFormulas.html file contains a list of MS-Excel supported and un supported function. |
Configure the Excel Agent
Follow the steps below to configure the Excel agent:
1. As a prerequisite, ThingWorx must be running with extensions imported.
2. In ThingWorx Composer , create a Thing (Connector) using a base thing template as TW.AnalysisServices.SDKConnectorTemplate.
3. To configure the Excel agent, open the config1.properties file in the installation directory of the agent. This XML file contains the configuration settings for the Excel agent. Add or edit the following values in the appSettings section using the correct XML syntax:
Property | Description |
|---|---|
URI | Specifies the URI of the ThingWorx server where the Analytics Manager framework is running. For example, ws://localhost:8080/Thingworx/WS. |
AppKey | Specifies the key used to communicate with ThingWorx without requiring any other authentication. You can use the default or create your own key in ThingWorx and specify it here. |
ScanRate | This property is applicable to simulation jobs only. Specifies the interval in milliseconds at which the agent checks for simulation property updates and triggers analysis jobs. Jobs are triggered only if property values have changed since the last scan. By default, this is set to 500. |
CallTimeout | Specifies the time in milliseconds that a server call waits before failing with a time-out error. Set this value according to the latency of the network. By default, this is set to 90000 milliseconds. |
ConnectionTimeout | A value used during agent startup to specify the maximum amount of time in milliseconds that the agent will wait when connecting to the ThingWorx server. If the initial connection attempt fails, the agent waits for two seconds before attempting to connect again. The agent repeats this process until the ConnectionTimeout number of milliseconds is reached. Then the startup will fail. By default, this is set to 20000 milliseconds. |
AgentID | Specifies a unique string that is used for the identification of an agent to the ThingWorx server. If the value is not specified, a unique ID is generated automatically and is reused until a different one is provided using this property. |
AppImplClass | Specifies the fully qualified name of the class that implements the application interface. This class contains the bridging code between the agent and the analysis provider. This property is required. |
ConnectorName | Specifies the name of the connector to which this agent must communicate. The default value TW.AnalysisServices.Excel.ExcelConnector is installed by the Analytics Manager extension on the ThingWorx serve. |
AgentWorkDir | Specifies the directory used by the agent to store model files, temporary files, result files, and data files. The default AgentFiles folder is created in the installation directory of the agent. The following subdirectories are created under this directory: • Models • TempFiles • Results • DataFiles |
AppExecutionThreadpoolSize | Specifies the number of jobs that a client can handle at a time. |
UseRedundantAgent | Used to monitor the primary agent for crashes. If the value is set to true, a secondary agent is launched at the startup. This secondary agent monitors the primary agent. If the primary agent crashes, it is restarted automatically. If the secondary agent fails, the primary agent restarts it. |
GatherClientHealthData | Set the value to true to monitor the health of the client. By default, this is set to false. |
ClientHealthDataInterval | Specifies the time interval in milliseconds after which the agent polls the system and its clients for health information. The default value is 60000. |
ClientInstanceInitCount | Specifies the number of client instances that start when the agent is started. The default value is 1. |
ClientInstanceMaxCount | Specifies the maximum number of client instances that this agent can start. Specify a value greater than 0 to provide a fixed maximum value for the number of clients. |
MaxMessageSize | Specifies the maximum messages transfer limit (in bytes) between the agent and the server per request. |
SupportDeployment | Set to true to deploy new docker-based or jar-based analysis agents. |
Run the Excel Agent
Use any one of the below methods to run the agent
1. Copy the files from target/analysis_spreadsheet_agent-<version>.jar and src/test/resources/config1.properties to an empty directory.
Execute the following command to run the agent.
java -cp analysis_spreadsheet_agent-<version>.jar com.ptc.analysis.client.impl.AnalysisSDKAgent mode=null[MS1] configFile=./config1.properties
2. Run the jar from the target directory by providing complete path of config1.properties in the command as follows:
java -cp analysis_spreadsheet_agent-<version>.jar com.ptc.analysis.client.impl.AnalysisSDKAgent mode=null[MS2] configFile=../src/test/resources/config1.properties
Follow the steps below to check the connected client count
1. From the left navigation pane, click Browse Icon and search for the Connector by its name.
2. Double click the connector name to view the details.
3. Click the Properties and Alerts tab to see the connected client count.
Create an analysis model once the connection has been established.
To disconnect the agent and exit, enter the letter q in the command-line interface.