Appliance

SQL (iSheets) connector

HighQ Appliance integrates external applications into HighQ Applications. The integration allows contents from external applications to be imported into HighQ Applications. The HighQ appliance extracts data from an external application database by using custom queries and then maps the obtained data to HighQ Application resources. The query and mapping process is configured through the Administration interface of HighQ Appliance.

Appliance components

Configuring the SQL (iSheets) Connector

The SQL (iSheets) connector is part of the HighQ Appliance. It allows you to push data directly from existing sources, such as SQL database tables in SQL server or XML/RSS feeds, into iSheets in Thomson Reuters HighQ.
This data can then be formatted and manipulated using the built-in iSheet module functionality. Queries can be written and then used to map data from the source database to fields in an iSheet and synchronised on a one-off or recurring, scheduled basis.
First, add one or more destination iSheets in HighQ:
  • Create the iSheet template
  • Add system-level iSheet and columns
  • Create iSheet on a site from the template
Then configure the connector in Appliance:
  • Configure Admin connector
  • Test
  • Create Action
  • Create a query
  • Create Action mapping
  • Schedule
or
  • Create Module
  • Driver configuration
  • Admin configuration

A. Configure your instance

To map external database fields to iSheet fields, the iSheet template needs to exist in advance. Below are the details to create a new template:
  1. Create template
  2. Add system iSheets to the template
  3. Import iSheet from the template  

A.1. Create template

To create a new iSheet template, open the HighQ System Admin interface. Click
System Admin
>
iSheet
>
Create template
.
Add the title, status and template parameters.

Filter with Template parameters

Template parameters are used to create filters on the group of iSheets linked with this template, you can add as many parameters as you need.
The below example shows two filters: ClientCode and MatterCode:

A.2. Add system iSheets to the template

Create a system-level iSheet in the newly created template. iSheet creation is the same as creating a normal iSheet.
iSheets in the template are automatically created on your site when an iSheet template is added to your site.
After the iSheet is created, you can add columns to the iSheet.

A.3. Import iSheet from the template

You can now import the newly created template to any site, this creates new sheets on the site, defined by the template.
Open the
Admin
 menu of the site and under
Modules
 select
iSheet
. Click
Add
 and choose
Template
.
At this point, select
Import/Synchronise Data
 and set
Linked iSheet
 to
Yes
 if you want to import data via the iSheet/SQL connector.
You can also specify filter values based on the parameters set when you created the template. For example, if you need to synchronise data for a specific client (or clients), you can provide a client code (or codes separated by a comma) in the
ClientCode
 text box.
Every time you add an iSheet template to a site you can provide different values.
Now you have set up the templates and they are available in HighQ, you can map them to application data with HighQ Appliance.

B. Admin configuration

Open Appliance and configure drivers and log file settings in the Admin configuration screen:
External driver configuration
Select
Access method
 from the
Shared XML
 menu, then enter the parameters required for synchronisation.
Internal driver configuration
Enter the details of your HighQ instance. Data will be synced to this instance.
General settings
Enter settings related to the log file, such as log file size, name, log level, etc.

B.1 Admin configuration settings and limits

The settings include descriptions of the 'database server', this is the source server where you store your database. These details are needed If you want to migrate records to an iSheet.
  • Database type
    Specifies the type of database being used
  • Authentication type
    Specifies how users authenticate with the database (e.g., SQL server authentication or Windows authentication)
  • Available configuration options depend on the selected authentication type.
  • Database server IP address
    The IP address of the database server
  • Database server port
    Port for the database server
  • Database name
    Name of the database in the Database server
  • Database user name
    The username required to connect to the Database server
  • Database password
    The password required to connect to the Database server
  • Internal database
    Select to use an internal database
  • Proxy configuration
    External proxy configuration
  • Collaborate application URL
    URL for the Collaborate Application
  • Collaborate REST API URL
    API version used in collaborate API URL (as "api/{version}/")
  • Collaborate authentication key *
    The authentication key/token used to authenticate API requests to the Collaborate application.
  • Collaborate encryption key *
    Encryption key which is used to connect to the Collaborate application
  • Collaborate authentication type
    Authentication mechanism used by the Collaborate application (default value is "Basic")  
  • iSheet API call delay
    Specifies a delay between API calls made to iSheet (default value is 500 ms - please contact the professional services team for advice before you change this value)
  • Collaborate iSheet delete records threshold limit
    Threshold limit for deleting records within iSheet (default value is 5%, maximum value is 100%)
    • Deleting records in bulk:
       You should change this value for planned bulk deletes. For example, if your iSheet contains 10,000 records and you want to delete 1,000 records set this value to 10%.
  • Proxy configuration
    External proxy configuration
  • Custom authorization
    Custom authorization settings or mechanisms used within the application/system
  • iSheet progressive key API call delay
    The delay between progressive key API calls (default value is 500 ms - please contact the professional services team for advice before you change this value)
  • iSheet progressive key API max no. of calls
    Maximum number of progressive key API calls to get status (default value is 100 - please contact the professional services team for advice before you change this value)
  • File name
    Name of the log file
  • Maximum log size (MB)
    Maximum size for log files, in megabytes (default size is 200 MB)  
  • Log level
    Specifies the level of detail stored in the log file (e.g., "DEBUG", "INFO", "ERROR").
  • Delete historical purge data
    Delete historical purge data older than a specific period; reports for data older than the specified period are not generated
When you have finished the configuration click
Save & Test heartbeat
 to check the connection works.
A message confirms the test is successful.

C. Sync data from an SQL server

If your connection is successful, create ‘Actions’ in Appliance to fetch data from the external database.

C.1 Create Action

Each Action sets SQL queries and filters that fetch and sync information from the external database.
You may create one or more Actions as required. An Action requires an action name, type, and status.
Action settings
Open the
iSheet/SQL connector
, then
Action Scheduler
 and click
Settings
 to create an Action.
Select your iSheet template from the drop-down menu. A list of iSheets created with that template are shown on the left of the screen.
You can add a custom SQL query in the
External driver settings
 column for each iSheet. The query is used to fetch information from the external database and synchronise with the iSheet.

C.2 Creating a query

Enter a query in the text box under
External driver settings
:
Step 1:
 Include @FILTER-RULE@ in the ‘where’ condition of your query. For example, select c.clientcode,c.clientname from clients as c where @FILTER-RULE@
Step 2:
 Click
Add
 to add a filter rule
  • To compare numeric values use the following:
numerictype-columnname-in-query = @template parametername@
For example: 
c.clientcode = @ClientCode@
  • To compare string/varchar values use the following: 
varchartype-columnname-in-query = '@template parametername@'
  • To compare table column values against multiple values: 
numerictype-columnname-in-query in (@template parametername@) 
varchartype-columnname-in-query in ('@template parametername@')
  • Similarly, you can use other SQL relations, such as "like", "not in", "!=" etc.
Step 3:
 After typing your SQL query and applying filters to it, please validate the query with the "Validate" link and check the query result.
After you add the SQL query, select
Validate
 to validate the query. If successful, a preview of the first five rows is shown, with a summary of the iSheet location and the total number of records found.
Template selection is only applicable if the template iSheet is linked with at least one site and synchronisation is enabled for the site.

C.3 Filter for parameters

If you added a Parameter when you created the iSheet template and when you imported the iSheet template to a site, you can now link this to the SQL query with a filter.
A filter can refine the imported data by including or excluding elements based on specific criteria.
If you do not need filters, you can move to Action mapping.
To add a filter click
Add
.
Below are examples of one SQL query without filters and another SQL query with filters. Filters can be set to link to Parameters that were set at the iSheet template level.
  • Query without filters
    - Below is a normal query without any filter applied: 
  • Query with filters
    - Below is a query with a filter. If you need to apply filters, you must input @FILTER- RULE@ in the 'WHERE' clause of the query: 
The screenshot below shows a filter added to the query that is mapped to a parameter. Click
Add
 then type in the syntax to map the filter to a Parameter:
In this example,
Client_Code
 is a column in the table of the source database being queried. Additionally, Client_Code is also the name of a Parameter added to the iSheet template; note that the Parameter name must start and end with the '@' character.
You can use relational operations in the filter such as IN, NOT IN, LIKE, NOT LIKE, EQUALS, NOT EQUALS, etc.
After you add the SQL query, select
Validate
 to validate the query. If successful, a preview of the first five rows is shown, with a summary that shows iSheet query details and the total number of records found for each iSheet on each site.

C.4 Action mapping

Action mapping is used to map a database column to an iSheet column. For each column in a database, you must select the target column in the iSheet. For example, the first 'name' column of user details syncs to the prinary_name column in the iSheet.
Each mapping has an additional field 'external-ID' which is used to uniquely identify each record for insert, update, and delete operations. You must ensure that the query column mapped with 'external-ID' is unique for the query (it is a mandatory field).
In the mapping example below,
externalId
 is set to the
guid
 field in the SQL query:

C.5 Supported fields

You are only able to select basic field types for the mapping. In most cases, these fields/columns types cover all data you need to sync.
These fields are:
  • Single line text
  • Multiple line text
  • Choice
  • Number
  • Date and time

C.6 Scheduling Action

When you have created the query and mapping of the Action you can run a sync to populate initial data in the target iSheet. Run a
Force Sync
 to run the sync immediately.
The scheduling action allows you to choose when an action, such as a sync, is run on a repeating schedule, such as on a daily, hourly or custom basis.

C.7 Monitor action schedule

You can monitor the scheduled start date, time and duration of a scheduled action.
Open the Module, then select
Monitor
:
The
Scheduler Report
 shows you every action that is set up in the connector; it is possible to filter the report by date range.
Open the Module, then select
Scheduler Report
:
Reports generated show total records in the sync, success records, and failed records. You can also download the report in Excel format.

D. Sync data from XML or RSS

The SQL connector also supports syncing content from XML/RSS feeds. This can be used when the source application has an XML/RSS feed that can be used to sync content on a regular basis.
To sync data with XML, please follow the steps described below:
  1. Create module
  2. Driver configuration
  3. Admin configuration

D.1. Create module

Create a new module as described in the HighQ Appliance documentation.

D.2. Driver configuration

On the driver configuration page, two options are provided in the
External driver
 drop-down list:
  • Database version 1.0
  • RSS/XML Driver version 1.0
And one option in the
Internal drive
r drop-down list:
  • iSheet version 1.0
Choose the appropriate configuration for your source feed.

Synchronisation messages

When the synchronization process is initiated by the schedule, certain actions generate a message. Status messages are listed below, followed by a description:
  1. Please remove '>' OR '<' from [column name]
    Characters ‘>’ and ‘<’ are not allowed in iSheet data. Rows with such kind of data will fail.
  2. More than one choice value has been detected. You can only have one value in [column name] field
    Column value does not allow multiple selections in iSheet data. Rows with this kind of data will fail
  3. The value in [column name] field is not one of the available options
    It is for a choice type of column. Column value is not in available options. Rows with this kind of data will fail
  4. There are multiple invalid values in [column name] field
    It is for choice type of column with multiple selections. Given value have more than one invalid value which are not in choice list
  5. We detected an invalid date format for [column name]. Dates should be in the following format YYYY-MM-DD HH:MM:SS
    It is for date type of field. Invalid date format is given for the specified column. Rows with this kind of data will fail.
  6. [Column name] is required
    Value of column is null or blank. Given column is mandatory in the iSheet. Rows with this kind of data will fail.
  7. Length of [column name] must be less than or equal to {1}
    Length of column value must be less than or equal to given max limit. Rows with this kind of data will fail.
  8. Invalid HTML detected in [column name]
    HTML of column value is not properly formed. Rows with this kind of data will fail.
  9. Invalid Number for [column name]
    No given or the number type of column is invalid. Rows with this kind of data will fail.
  10. Maximum 10 digit is allowed before decimal places in [column name]
    Number type column maximum limit is 10 digits. More than 10 digits are not allowed. Rows with this kind of data will fail.
  11. Decimal is not allowed for [column name]
    If the column property decimal places is not defined, then decimal places are not allowed. Only integer-type values are allowed. Rows with this kind of data will fail.
  12. Invalid decimal Places for [column name], Maximum decimal places is [GIVEN NUMBER]
    Decimal places are more than defined in column property. Rows with this kind of data will fail.
  13. Value of [column name] must be greater than or equal to [GIVEN NUMBER]
    The value of the column is less than the column's minimum allowed value. Rows with this kind of data will fail.
  14. Value of [column name] must be less than or equal to [GIVEN NUMBER]
    The value of the column is greater than the column's maximum allowed value. Rows with this kind of data will fail.
  15. Value of [column name] must be greater than or equal to [GIVEN NUMBER1]. And must be less than or equal to [GIVEN NUMBER2]
    The value of the column is greater than the column's maximum allowed value and is less than the column's minimum allowed value. Rows with this kind of data will fail.
  16. Value of [column name] is invalid. Format must be [FORMAT]
    The format of the value in a date column is incorrect. Rows with this kind of data will fail.
  17. For [column name] Display Name is inserted without URL
    URL is not given in a hyperlink column. Rows with this kind of data will fail.
  18. Invalid url in [column name]. Please insert correct URL
    URL is not valid in a hyperlink column. Rows with this kind of data will fail.
  19. Please provide Siteid or Sitename and Sheetname or Sheetid
    siteID or sheetID not provided in request xml. If this message appears the operation is not performed.
  20. Please provide Siteid or Sitename
    The siteID is not provided in the request xml
  21. Please provide Sheetid or Sheetname
    The sheetID is not provided in the request xml
  22. Successfully Inserted
    Item successfully inserted in the iSheet
  23. Successfully updated
    Item successfully updated in the iSheet
  24. Successfully deleted
    Item successfully deleted from the iSheet
  25. ID is not available in Database
    Item not available in the iSheet

API reference (API calls and column types)

This API call is used to create iSheet records.
The request XML requires siteid and sheetid parameters to determine the iSheet to which records will be added.
To add the records to the correct columns a column and value mapping is provided, which is associated with a sequence parameter.
The headColumn defines the names of the columns to which the content will be added and row data defines the values for those columns.
Name
Type
Description
siteid
int
The id of the site that contains the iSheet
sheetid
int
The iSheet where the records are to be added
headcolumn
string
The name of the columns to which a value is to be added
sequence
int
A number representing the sequence of the column in the iSheet
ItemID (optional)
int
This parameter is to be used in case of file metadata iSheet, and is the id of the row which is to be updated.
(Please note that the id can be obtained by inspecting the HTML of the page.)
ExternalID (optional)
int
Item ID of the item from the external system. This saves the external item id of the item in the system, this should be used later to execute the delete operation.

Default values for the iSheet connector

Property / Configuration
Description
Default Value
Delete historical purge data
To delete historical purge data older than specific period, reports for older data than specified period will not be generated
One month older
iSheet progressive key API max no. of calls
Maximum number of API calls allowed to the progressive key service within iSheet.
100
iSheet progressive key API call delay
Delay between API calls to the progressive key service within iSheet.
5000 milliseconds
iSheet API call delay
Specifies a delay between API calls made to iSheet
500 milliseconds
isheet synchronizeisheetinternaldriver maxrecord
Maximum limit of records that should be inserted in isheet in single schedule
250
isheet synchronizeisheetinternaldriver delete maxrecord
Maximum limit of records that should be deleted in isheet in single api call
100
iSheet purge records repository location
Location to save iSheet purge records into xml format
D:/iSheetReport/

Connector performance specifications

This section describes the performance limits in which the connector operates effectively. Understanding these limits helps ensure optimal performance and prevents system overloads. The following table outlines the maximum thresholds for various parameters, including the number of rows processed, cell size, number of columns, processing time, and Tomcat memory settings.

Performance thresholds

Rows processed
Columns per row
Cell contents
Time to process
Tomcat memory setting
1000
10
512 VChar
9.5 min
4 GB
1000
10
512 VChar
8 min
10 GB
1000
10
512 VChar
6.5 min
16 GB
1000
20
512 VChar
11 min
4 GB
1000
20
512 VChar
9 min
10 GB
1000
20
512 VChar
8 min
16 GB
1000
40
512 VChar
14 min
4 GB
1000
40
512 VChar
13 min
10 GB
1000
40
512 VChar
11.5 min
16 GB
175000
20
512 VChar
60 hours
4 GB
250000
10
512 VChar
68 hours
4 GB
500000
40
512 VChar
180 hours
16 GB

Key Definitions

  • Rows Processed -
     indicates the maximum number of data rows the connector can handle efficiently
  • Columns per row -
     represents the maximum number of columns the connector can manage in a single processing task
  • Cell content -
     denotes the maximum length of data each cell can contain
  • Time to Process -
     the estimated maximum time required to complete the data processing within the specified limits
  • Tomcat Memory setting -
     specifies the maximum amount of memory allocated to Tomcat for processing tasks

FAQs

How does the sync work?
When the sync runs, this actions a delta call to the target DB and tries to create a 1:1 relationship with the actioned fields. The target DB returns information based on existing/deleted/new rows of information.
  • If there are existing rows in the target DB and HighQ the sync only syncs rows that have been updated in the target DB. All pre-existing unchanged rows remain as they are.
  • If there are deleted rows in the target DB, HighQ tries to replicate this and then tries to delete rows in the iSheet.  This works as long as the Appliance delete record limit is set to the same or higher than the quantity of deleted records in the target DB. However, the limit is usually set to a low number to mitigate accidental deletions in the target DB and HighQ doesn’t then try to replicate this by mistake. Please contact HighQ before making changes.
  • If there are new rows found in the target DB (based on querying the unique GUID – External ID number) HighQ then tries to add these to the iSheet based on the synced schedule.
Can I use the iSheet sync tool to manage a WIP?
You can use the iSheet sync tool for this use case and it is a great way to allow your clients to keep up to date with the current progress of the matter/project. Clients generally won’t have access to your target DB and potentially your DMS either, thus using the iSheet tool allows client interactions to take place on a secure/audited platform.
Can I sync a partial amount of data from the target DB and also allow manual entry on an iSheet?
This can be done and is a great way for your clients/team to see your live current data in an iSheet. This also allows your clients/team to add data in the iSheet if or when it is needed.
Can we sync the iSheet data back to our target DB?
Out of the box, the iSheet connector does not allow for bi-directional syncing. Sync is only one way: from your target DB to HighQ.
However, you can export an XML feed from the iSheet and parse this back into your target DB, however this currently isn’t supported by HighQ.
Can we name the module name to a matter/project name?
HighQ recommends that you set the module name (in the iSheet module) to a matter/project name and also include a description so the module is easily identifiable.
Can we use the same iSheet template for multiple actions?
You can only use an iSheet template for one action in the Action scheduler. Keep in mind that you need to map the DB columns to their respective columns in the
Settings
 >
Mapping
.
Does HighQ support UDFs?
Currently, HighQ doesn’t support the use of UDFs and we recommend that you use specific queries as this speeds troubleshooting and also allows support teams to understand the logic used in the query.
Will the status of the module affect the sync?
The iSheet module (in the Appliance) needs to be set to Active to sync from your target DB to HighQ.
Is there anything else that we need to set up?
If you know what data you want to replicate from your target DB, your DBA (DB Expert/Admin) must write a standard DB query, add it to the iSheet Connector in the Appliance Server, and validate the query. You then need to map the fields from your target DB to the iSheet columns and finally set the frequency of the sync.
Should I use a staging table to pull data from?
HighQ recommends that you use a staging table to pull from rather than a live production table. There are a few reasons for this:
  • Firstly this allows for optimal performance as it won’t be on an active SQL server that might run other operations at the same time.
  • The second reason is risk mitigation; having access to a staging table makes more sense as then it cannot affect the 'single source of truth' of the live production table.
Can we use two filter rules on the iSheet Connector and apply them to different parts of the query in the Action?
You can use any number of filters depending on your requirements and the query.
Can I use an iSheet Template in more than more Action in the same module (in Internal driver settings)?
No
, if a template is used in one Action, it cannot be used in another Action in the same Module.
Why are the drivers frozen and cannot be updated?
As described in the prompt, once you schedule a sync, the driver configuration cannot be changed.
Does the SQL connector work with iSheet Views?
No
, do not use Views.
Are task/file/folder metadata iSheets supported in the SQL Connector?
We
do not
 support task/file/folder metadata iSheets with the SQL Connector.
How do I check if I am using the latest version of the Appliance
Log in to the Appliance application. On the Home page select
Version
 from the left panel:
Is the iSheet Connector compatible with Collaborate's lookup functions?
No
, it is not compatible.
What does the ‘Length of Internal Table’ refer to in internal driver settings?
In version 3.6.5, we introduced an enhancement that allows for the creation of an internal table with a customizable fixed column length. Previously, it was set to a fixed length of NVARCHAR (MAX), without the ability to configure it.
  • The Appliance DB contains data synchronized from your external DB.
  • To store this data in the Appliance DB, the length is defined at the code level because there is no way to be uncertain about the maximum length of your data. 
    Note: Maximum length refers to the number of characters stored in a single cell or field (i.e. the intersection of a row and a column).
  • The default setting is NVARCHAR(512). This implies that all data is stored with a maximum length of 512 characters in the Appliance DB.
  • However, if you are confident that the length of the data will not exceed 128 characters, you can set this to NVARCHAR(128).
  • Consequently, you can configure the Length of the internal table to align with the length of the external data. 
    Note: The lower the value of the ‘Length of the internal table’, the better the performance of the iSheet connector.
Is the ‘template parameter’ a unique feature for the SQL connector? Or can it be used in conjunction with an API?
The Template Parameter is a feature for the SQL Connectors. It is used to establish filters on groups of iSheets linked with a template, allowing you to include as many parameters as necessary. This functionality is also achievable via API, as the Appliance synchronizes data through API calls.
What does clearing the cache do? And should we do this occasionally?
No
, Application Cache files are stored in RAM, rather than in the HDD or Appliance DB. If cleared, they will automatically be recreated, rendering the act of clearing them meaningless.
Is the 'template parameter' a unique feature for the SQL connector? Or can it be used in conjunction with an API?
The Template Parameter is a feature for the SQL Connector. It is used to establish filters on groups of iSheets linked with a template, allowing you to include as many parameters as necessary. This function is also possible via API, as the Appliance synchronizes data through API calls.
How can we redirect all our current jobs to the new database while ensuring they mirror the production environment? Would updating the external driver settings in the Admin configuration for that module suffice?
Updating or modifying External Database information is straightforward. Navigate to
Admin Configuration
 >
External Driver Settings
 to make adjustments.
Is there any way to pause jobs that are running?
Active Sync or Action Schedulers cannot be paused. Furthermore, we strongly advise against terminating them.
Note: active status indicates ongoing data synchronization. Terminating them may result in data loss, and upon termination, the scheduler re-syncs from the beginning - there is no resume functionality.
My HighQ appliance server is reaching capacity. The iSheetReport folder currently holds XX GB of XML files. Can these files be removed?
Note: It is important to maintain consistency in the duration for both historical purge data and deleting XMLs. For instance, if you delete historical purge data that is 6 months old, you should also delete XML reports that are 6 months old.
Step 1: Deleting Historical Purge Data
  • Navigate to the "Delete historical purge data" property in Admin Configuration.
  • Use this feature to delete historical purge data from the Appliance DB that is older than a specific period.
  • Note that reports for data older than the specified period will not be generated.
Step 2: Managing/Manually Deleting XML Reports
  • You can delete older XML files from the ..\iSheetReport location if you no longer require access to the corresponding Scheduler Reports.
  • Note: These XML files play a crucial role in previewing and downloading Scheduler Reports. Deleting older XML files results in the corresponding Scheduler Reports becoming inaccessible for preview or download.
Is there any way to prevent deletion of an iSheet record, but still allow edits?
It is not possible to prevent the deletion of an iSheet record while still allowing edits.

This article applies to:

  • Product: HighQ
  • Subject: Data Connection, iSheets