Archer BI Connector

Often users want to expose Archer data to external business intelligence (BI) software for reporting. Tableau and Power BI are two popular engines that are often used for this purpose. The out-of-box recommendation for connecting Power BI and Tableau to Archer requires the user to obtain a session token, which requires the user to take several detailed technical steps, resulting in poor user experience.

The Archer BI Connector Tool & Utility was built in response to this challenge. This offering is a .NET web application that can be used to connect BI software to Archer without the need for the user to learn their session token, providing a seamless user experience. It is a small stand-alone application and can be installed on the Archer web server side-by-side with Archer for on premise environments.

Note: Archer SaaS clients can leverage this offering but cannot install the offering in the Archer SaaS environment. The offering must be installed on a client owned and managed server that can communicate with the Archer instance.

With the Archer BI Connector in place, when it comes time to retrieve Archer data in Power BI or Tableau, the user can point the BI software to the connector and no further information is needed.

The Archer BI Connector was built using ASP.NET and includes an XML configuration file that allows for the specification of authentication credentials, along with other necessary details.

On this page

Release history

Release Date	Release Version	Notes
April 2024	6.14 P2	Initial Release on Archer Platform Release 6.14 P2
May 2024	6.14 P2 v2	Bug fix: The tool was unable to process the encoded query, hence was failing to respond with pagination. Fix is applied to handle encoded parameterized query.
June 2024	6.14 P2 v3	Troubleshooting steps are added for the installer. Steps to set up report on Power BI Server added.
September 2024	2024.06	Update: The tool is now certified to work with Tableau Desktop and Tableau Server. Bug fixes: If password stored in the config file contains any special characters such as #,&,<,> then the tool was giving 500 internal server error. Now the tool can store and process these special characters in the password. The tab index of the config encryptor was not organized. The tab index is now fixed for the UI.
May 2025	2025.02	Recertification on Archer Platform release 2025.02. Bug Fix: Allow pagination for multiple API calls to fetch entire data set, due to Archer Content API only supporting a max of 1000 records per page.

Overview

Key features and benefits

The Archer BI Connector Tool & Utility enables organizations to:

Quickly connect Archer to Power BI and Tableau to display Archer data as visuals in the BI tools
Access to data is authorized through user's session token
Data is retrieved in real-time from the environment
Changes in metadata are automatically accounted for
Certified for Microsoft Power BI for both desktop and server
Certified for Tableau Desktop and Tableau Server

Considerations

This document is not intended to suggest optimum installations or configurations. It is assumed that the reader has both working knowledge of the product involved, and the ability to perform the tasks outlined in this document.

Note: If any user ID is used with super admin access in the configuration, the application will have super admin access in Archer.

Prerequisites

The following table lists the components and prerequisites for the Archer BI Connector Tool & Utility.

Components	Prerequisites
Archer Solution Area(s)	All
Archer Use Case(s)	This offering does not require any prerequisite use cases.
Archer Applications	This offering does not require any prerequisite applications.
Uses Custom Application	No
Requires On-Demand License	This offering does NOT require any Archer On-Demand Application (ODA) licenses.
Archer Requirements	Archer Platform release 2025.02 and later
Partner/Vendor Requirements	N/A
Supported Archer Environments	On-Premises Archer SaaS Note: Archer SaaS clients can leverage this offering but cannot install the offering in the Archer SaaS environment. The offering must be installed on a client owned and managed server that can communicate with the Archer instance.
Supporting Software	IIS 10 / .NET 4.7.2 / IIS 6 Management Compatibility should be enabled for installation

Installing Archer BI Connector Tool & Utility

For the utility to function successfully, the server responsible for hosting the utility must have the required access to communicate with Archer instance. The functional IDs being used in the XML configuration file must have sufficient access in the target instance.

Security considerations

The information in this publication is provided “as is”. Archer makes no representations or warranties of any kind with respect to the information in this publication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose. The client is solely responsible for ensuring that the installation of the application is performed in a secure manner. Archer recommends clients perform a full security evaluation prior to implementation.

Run through the installation Wizard

Unzip the .zip file to get the Archer BI Connector Tool & Utility setup file.

Task 1: Run the installer

Double click on Archer BI Connector Tool & Utility Set Up .msi file.
Click Next.
Read the License Agreement, select I Agree, and click Next.
Select Site, Application Pool, enter a Virtual Directory Name, and click Next.
Click Next to start the installation.
Once the installation is complete, click Close.
Verify the installation by opening IIS. The application appears under the selected Application Pool, as a default setting the connector will be installed as anonymous authentication.

Task 2: Update the configuration file.

Unzip the .zip file to get the ConfigEncryptor.exe and ConfigEncrytor.exe.Config file.

Double click ConfigEncryptor.exe and run the application in the same machine where the Archer BI Connector is installed.
Fill in the configuration details according to the Config Parameter Guide.
Click Encrypt. A dialog window opens with the encrypted config parameters.
Open the Web.Config file for Archer BI Connector. The Web.Config file can be found under wwwroot/<applicationName>/.
Replace the entire <appSetting> section with the encrypted string.

Configuration parameter details

An ASP.NET application uses an XML configuration file called Web.Config. The configuration file should contain the following configuration details:

<add key="connectorBaseUrl" value="http://<<Host Name>>/Archer_BI_Connector_Tool" />

</appSettings>

Parameter	Description
logRequest	Used for testing purposes. When this setting is “true”, requests to the Archer BI Connector will be written to the log file.
archerBaseUrl	The root URL for the Archer instance from which information will be pulled.
connectorBaseUrl	The root URL where the Archer BI connector lives. This is needed to dynamically create responses to requests Power BI will make of the connector, specifically the odata.context of the response.
cacheFilePath	The flat file that will be used to cache Archer ID values. When the solution is started, values will be looked up from the configured Archer instance and stored in the cache file. This enables us to correctly reference fields, modules, and other resources using IDs appropriate to the Archer instance.
cacheFileEncryptionPassphrase	If this is provided, the cache file will be encrypted using this as the passphrase, provided security at rest for the lookup information.
useSso	When the Archer BI Connector is configured for Windows Authentication, set use Sso to “true” to authenticate to Archer as the requesting user through a single sign on API request to the SOAP API. This should be true if the Archer environment is also set up as single sign on.
soapSsoVirtualDirectoryName	When using SSO, there must be a virtual directory on the Archer web server pointed at the /ws folder and configured for Windows Authentication. The name of that virtual directory should be stored as the value for this configuration setting.
useSslValidation	When set to true, custom validation is used for the SSL cert. This is necessary when using a self-signed cert to avoid the validation dialogs that the client will pop.
securityProtocolVersion	Specifies which version of SSL/TLS should be used. Options include: SystemDefault, Ssl3, Tls, Tls11, and Tls12. Tls12 is the default.
archerInstance	The name of the Archer instance from which information will be pulled.
archerAdminUser	When the Archer BI Connector is configured for Windows authentication in IIS, this user will be used to look up the requesting user and the associated security parameter.
archerAdminPassword	Password for the archerAdminUser.
archerReportingUser	If the Archer BI Connector is configured for Anonymous authentication in IIS, this user will be used to create an Archer session and request data from the Archer Content API.
archerReportingPassword	Password for the archerReportingUser.
useDomainForUserAuth	If the Archer BI Connector is configured for Windows authentication in IIS, this setting will determine whether the domain of the requesting user should be included in the filter when looking up the requesting user in Archer.
httpTimeoutMilliseconds	This parameter allows for an increase in the default HTTP timeout when making requests to Archer. The default is 30,000 milliseconds (30 seconds).
useDefaultCredentials	This property can be set to true or false. When true, the logged-in user’s credentials can be requested by IIS, if needed. For this application, the value can safely be set to false.

Troubleshooting steps for the BI Connector Installer

If the Installer for BI connector abruptly ends with error message "The installer was interupted before Archer BI Connector Tool & Utility could be installed. You need to restart the installer to try again", run the .msi using command prompt to generate log.

Open command prompt from the start menu.
Go to the folder where the installer is using command CD <location of the installer>.
Run the following command:

msiexec /i "Archer.BI.ConnectorTool_UtilitySetUp.msi" /l*v "install.log"
It will generate the install.log file in the same location.
Open the log file and look for the following error:

MSI (c) (CC:94) [09:09:38:068]: Note: 1: 2235 2: 3: ExtendedType 4: SELECT `Action`,`Type`,`Source`,`Target`, NULL, `ExtendedType` FROM `CustomAction` WHERE `Action` = 'WEBCA_SetTARGETSITE'

MSI (c) (CC:00) [09:09:38:074]: Invoking remote custom action. DLL: C:\Users\ADMINI~1.ALL\AppData\Local\Temp\2\MSIBDEF.tmp, Entrypoint: SetTARGETSITE

INFO : [06/26/2024 09:09:38:079] [SetTARGETSITE ]: Custom Action is starting...

INFO : [06/26/2024 09:09:38:080] [SetTARGETSITE ]: CoInitializeEx - COM initialization Apartment Threaded...

ERROR : [06/26/2024 09:09:38:082] [SetTARGETSITE ]: FAILED: -2147221164

ERROR : [06/26/2024 09:09:38:083] [SetTARGETSITE ]: Custom Action failed with code: '340'

INFO : [06/26/2024 09:09:38:084] [SetTARGETSITE ]: Custom Action completed with return code: '340'

CustomAction WEBCA_SetTARGETSITE returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)

Action ended 9:09:38: WEBCA_SetTARGETSITE. Return value 3.

If this error is encountered then IIS6 Metabase needs to be Installed.
Click Start, click Administrative Tools, and then click Server Manager.
Click Manage, click Add Roles and Features.
Click Next, then select Role-based or feature based installation then click Next.
Select the Server and click Next.
Select Role Web Server (IIS)>>Management Tools>>IIS 6 Management Compatibility>>IIS 6 Metabase Compatibility and click Next. Make sure both highlighted checkboxes are selected.
Click Next on the feature page.
Click Install.
Once the process is complete try to run the installer normally as usual.

Setting Up Archer BI Connector with SSO Authentication

As a default configuration, Archer BI Connector is installed in IIS with anonymous authentication. It is recommended to install BI connector in a single server not behind a load balancer into multiple web server.

1. Archer should be configured as SSO.

2. Change the authentication in the virtual app for BI Connector to windows authentication from anonymous authentication.

3. Create a virtual directory with a preferred name and point it to Archer WS folder and set this virtual directory authentication as windows authentication.

4. In the web.config file change useSso property to true and change soapSsoVirtualDirectoryName to the virtual directory created in the previous step.

Note: The windows identity passed should have a principal name matching the archer profile username. Tableau does not work with SSO enable it should be either anonymous or Basic authentication.

Setting Up Archer BI Connector with Basic Authentication

Change the authentication in the virtual app to basic authentication from anonymous authentication.

If basic authentication option is not visible, then follow the steps below:

Open Server Manager click on Add Roles and Features.
Choose Role-based or feature-based installation and click next.
Select the server and click next
In server Roles expand Web Server (IIS) and then expand Web Server.
Under Security check the box for Basic Authentication.
Click Next and Install.

Note: The windows identity passed should have a principal name matching the archer profile username and the password should also be matching the Archer user profile password.

Setting up Automation Script

There is a power shell script supplied with the package, which can be used for automation to get data from the BI connector in case there is a table which has more than 1000 records. This is not a mandatory component, users can choose their own automation to tackle the process. Follow the steps below to set up the out of the box automation script:

Get the FetchArcherData.ps1 file from the package and put in the desired location.
Open the file in editor to supply some information to set up the automation.
If you want the output of the script to be placed in the local folder keep the $remoteFileStorage variable at line 45 as $false for, FTP location change it to $true. It recommended to be kept at local for faster access.
If you have chosen it to be local, then supply the $ouputPath located at line as something can be accessed by both the script and your preferred BI Tool default is "C:\Temp\".
If you have chosen it to be remote, then supply the correct details in the following variables:

$ftpServer = "" #provide FTP server location $ftpUsername = "" #ftp log in credential $ftpPassword = "" #ftp log in password $remoteFilePath = "" #path inside ftp server
Next setup the tables in the $hashtable in line number 52 onwards that you want the script to fetch. There is not limit on how many tables it can fetch, but with increasing number of tables the run time will increase. To setup the table follow the syntax below:
Syntax: $hashtable["desired_file_name.json"]="https://<hostname>/Archer_BI_Connector_Tool/contentapi/<desired_valid_table_name>"

Example:for Power BI

$hashtable["Incidents.json"]="http://localhost/Archer_BI_Connector_Tool/contentapi/Incidents"

Example: for Tableau

$hashtable["Findings.json"]="http://localhost/Archer_BI_Connector_Tool/tableau/Findings"
The default page size in 500 records per page but it can be changed in line number 61 [int]$Top = 500. The max limit is 1000, similar to the contentapi.
Finally, if the BI Connector Tool is set up as SSO or anonymous, then there no need to change anything. If the BI Connector Tool is setup as Basic Authentication, then provide the credential for the connection at line number 37 and 39

$username = "<<credential for basic Auth>>"

$password = "<<credential for basic Auth>>"

then go to line 69 and remove -UseDefaultCredentials and add -Credential $credential
Once the changes are done save the file and close it.
Open Windows Task Scheduler and go to Task Scheduler Libray.
Click on create task in the Action pane on the right.
In the General Tab provide a task name and select "Run whether user is logged on or not" and check "Do not store password".
Go to Triggers tab and click new and set up the schedule as per you need. Do not forget to check the enable check box.
Go to Actions tab and select new, Action should be Start a program, Program/Script should be "powershell.exe" and arguments should be -File "<location of the script file in your local file system>\FetchArcherData.ps1".
In the conditions tab set up the conditions as per your need to make sure uninterrupted execution of the script.
Finally, in the Settings tab click the checkbox "Run task as soon as possible after a scheduled start is missed". Click ok and exit.

Connecting Archer BI Connector Tool & Utility using Power BI

Connect to Archer BI Connector using OData feed

Open Power BI desktop.
Click Get Data and select OData feed.
Enter the connection URL with your hostname and click OK.
Select a table from the list generated and select Load / Transform Data.
Create a report using the columns.

Connect to Archer BI Connector using Web

From data source select web, put the endpoint and set timeout value as per your need and click “OK”, OData queries can be passed in web connector.
Transform the data as per your need and click close and apply
Power BI Desktop will evaluate the data source after that the data source should Appear
Create a report using the columns.
In case the target table has more than 1000 records make multiple call to fetch the pages either with Advanced power query editor or make single calls and append the tables. To append queries, open Power Query Editor and select Append Queries as New under the home tab.
You can also use the power shell script included in the package and schedule it using Windows Scheduler. Details on how to set up the script can be found in the Setting up Automation script section. The source in this case for Power BI should be a JSON file and the JSON file is generated by the script.

Connect to Archer BI Connector using Power BI Server

Open Power BI Server
Upload saved Report By clicking upload button, and the report should appear in the view.
Open the Manage report by clicking the hamburger menu in the report tile
Check the connection
Create a refresh schedule
Refresh the report (report will refresh on a schedule too)

Connecting Archer BI Connector Tool & Utility using Tableau

Open Tableau Desktop or personal workbook in Tableau Server
Select OData connector from list of connectors to connect to server
Enter the connection URL with your hostname and select the authentication method based on how the connector is set up. Tableau supports anonymous and basic authentication.
Drag and drop the tables those are needed to build reports and build the relationship between the tables then go to sheets to build reports
Organize the reports build in dashboard and save the Tableau workbook or publish the workbook.
Collection type fields such as Multiselect Values List, Cross-reference or Related-Record are ~ separated. Split operation can be performed using `~` as separator.
To fetch data from a table which has more than 1000 records use the automation script provided or you can build you own which will produce a JSON file as output. Then in the data source select the JSON option in tableau desktop and load the data then perform your reporting. Note to use this option first build the report in Tableau Desktop and publish the same into your cloud instance of Tableau.
Collection type fields such as Multiselect Values List, Cross-reference or Related-Record are ~ separated. Split operation can be performed using `~` as separator.

Endpoints to connect to Archer BI Connector Tool & Utility

The endpoints can be found using the URL format mentioned below:

https://<<Hostname>>/<<virtual path name>>/

1. Endpoints for Tableau

To fetch all the available endpoints in Archer:
https://<<HostName>>/<<Virtual Path Name>>/tableau

To fetch the metadata for each of the Archer endpoints:
https://<<HostName>>/<<Virtual Path Name>>/tableau/$metadata
To fetch the data for each of the Archer tables:
https://<<HostName>>/<<Virtual Path Name>>/tableau/Incidents/<<desired table name with valid query>>. Click on Get Table Data for Tableau by Endpoint, in the prompt pass the table name along with qualified query: Incidents?$skip=100&$top=200

2. Endpoints for Power BI

To fetch all the available endpoints in Archer:
https://<<HostName>>/<<Virtual Path Name>>/contentapi
To fetch the metadata for each of the Archer endpoints:
https://<<HostName>>/<<Virtual Path Name>>/contentapi/$metadata/$metadata
To fetch the data for each of the Archer table: https://<<HostName>>/<<Virtual Path Name>>/contentapi/<<desired table name with valid query>>. Click on Get Metadata for Power BI By Endpoint, In the prompt pass a valid table name along with valid query: Incidents?$top=500

Certification environment

Date Tested: May 2025

Product Name	Version Information	Operating System
Archer	2025.02	Windows

ADDRESS	ABOUT ARCHER	CUSTOMERS
13200 Metcalf Ave	Events	Archer Academy
Suite 300	Resources	Archer Exchange
Overland Park, KS 66213	Services	Support
	About Us	Archer Community