UiPath Orchestrator

The UiPath Orchestrator Guide

Enabling Table Grouping

For large deployments where the number of processes and queues in production approaches the per tenant limit, the number of tables created in the Insights data cube can impact performance. This occurs because Insights creates a custom table for each process or queue which has a custom field added (i.e. the Log Message activity or Custom Schemas for queue items).

To avoid any such performance degradation you must disable the creation of per processes and/or per queue tables in the cube and then enable table grouping. Note that these two settings are independent and can be configured in the manner that best suits your environment (e.g. you have a lot a queues but few processes) by changing the Boolean parameter(s) in either of Powershell or SQL methods detailed below.

Important!

This process does not delete any existing tables. If you have already created your Insights data cube with these settings enabled you must delete it and create a new cube.

  1. Using the UiPath.InsightsAdminTool, disable any tenants where Insights has already been enabled.
    For example: .\UiPath.InsightsAdminTool.exe disable -t tenantName -u admin -p Orch_Host_Password.

  2. Update the table grouping settings for each desired tenant using either the:
    a. Powershell script provided here, executing the script using the credentials of the targeted tenant.
    For example: .\UpdateInsightsTableCreationSettings.ps1 -server 'http://orchestrator' -tenancyName Default -user admin\. Where:

    • -server - your Orchestrator server.
    • -tenancyName - the targeted tenant.
    • -user - the username name of the administrative user for that tenant.

    b. SQL Method, running the following on your Orchestrator database:

DECLARE @TenantName nvarchar(64);
SET @TenantName = N'Default';
DECLARE @PerProcessTables nvarchar(5);
SET @PerProcessTables = N'False'
DECLARE @PerQueueTables nvarchar(5);
SET @PerQueueTables = N'False'
 
DECLARE @TenantId int;
SELECT @TenantId = Id FROM dbo.Tenants WHERE TenancyName = @TenantName;
 
IF (@TenantId is NULL)
BEGIN
  PRINT 'Could not find tenant ' + @TenantName
  RETURN
END
 
BEGIN TRAN
  IF EXISTS (SELECT * FROM dbo.Settings WHERE [Name] = 'Insights.PerProcessTables' AND TenantId = @TenantId)
  BEGIN
    UPDATE dbo.Settings
    SET [Value] = @PerProcessTables, LastModificationTime = GETDATE()
    WHERE [Name] = 'Insights.PerProcessTables' AND TenantId = @TenantId
  END
  ELSE
  BEGIN
    INSERT INTO dbo.Settings VALUES
    (@TenantId, NULL, 'Insights.PerProcessTables', @PerProcessTables, NULL, NULL, GETDATE(), NULL)
  END
 
  IF EXISTS (SELECT * FROM dbo.Settings WHERE [Name] = 'Insights.PerQueueTables' AND TenantId = @TenantId)
  BEGIN
    UPDATE dbo.Settings
    SET [Value] = @PerQueueTables, LastModificationTime = GETDATE()
    WHERE [Name] = 'Insights.PerQueueTables' AND TenantId = @TenantId
  END
  ELSE
  BEGIN
    INSERT INTO dbo.Settings VALUES
    (@TenantId, NULL, 'Insights.PerQueueTables', @PerQueueTables, NULL, NULL, GETDATE(), NULL)
  END
COMMIT TRAN

Where:

  • @TenantName - the name of the targeted tenant. In the above example, the Default tenant is used.
  • @PerProcessTables - whether process tables are created. The available options are True or False.
  • @PerQueueTables - whether queue tables are created. The available options are True or False.

Note:

The Powershell script is the recommended method for updating the table creation settings. The changes take effect immediately and no restart of Orchestrator is required.

If using the SQL method, note that these are cached settings and may take up to an hour to propagate. You must restart the Orchestrator web site on all deployed nodes if you want to force an immediate cache refresh.

  1. Re-enable each targeted tenant, selecting Y when prompted to Delete existing cube?.
  2. On the machine where Insights is installed, open an elevated command prompt.
  3. Navigate to the \Orchestrator\Tools directory. For example, cd C:\Program Files (x86)\UiPath\Orchestrator\Tools.
  4. Run the UiPath.InsightsAdminTool.exe utility with the necessary command parameters:
Command
Descripition

--groups

Mandatory.
The file path for the .json file containing the grouping definitions.

--debug

Enables debug messages in the UiPath.InsightsAdminTool output. By default, this is set to false.

--prompt

Prompts the user for each feature. By default, this is set to false.

--log-file

The file where UiPath.InsightsAdminTool logs are written.

--help

Displays the UiPath.InsightsAdminTool help screen with these commands.

--version

Displays the tool version.

For example: C:\UiPath\Orchestrator\Tools>UiPath.InsightsAdminTool.exe groupedTables --groups C:\groupings.json --log-file C:\customTables.log --prompt --debug

Note:

If you want to use table grouping and also have per process/queue tables on (e.g. Insights.perProcessTables set to True and Insights.perQueueTables set to False) your groups must include every (in this example) queue that you want in the grouped table.

Grouping Definition File

You must provide a .json file with your desired table groupings when executing the UiPath.InsightsAdminTool, using the following schema definition:

{
  "definitions": {},
  "$schema": "http://json-schema.org/draft-07/schema#",
  "$id": "http://example.com/root.json",
  "type": "object",
  "title": "The Root Schema",
  "required": [
    "groups"
  ],
  "properties": {
    "groups": {
      "$id": "#/properties/groups",
      "type": "array",
      "title": "The Groups Schema",
      "items": {
        "$id": "#/properties/groups/items",
        "type": "object",
        "title": "The Items Schema",
        "required": [
          "tenantId",
          "elasticubeNameTemplate",
          "groupName",
          "dataSource"
        ],
        "properties": {
          "tenantId": {
            "$id": "#/properties/groups/items/properties/tenantId",
            "type": "integer",
            "title": "The Tenantid Schema",
            "default": 0,
            "examples": [
              3
            ]
          },
          "elasticubeNameTemplate": {
            "$id": "#/properties/groups/items/properties/elasticubeNameTemplate",
            "type": "string",
            "title": "The Elasticubenametemplate Schema",
            "default": "",
            "examples": [
              "{TENANT}-Cube"
            ],
            "pattern": "^{TENANT}-(.*)$"
          },
          "groupName": {
            "$id": "#/properties/groups/items/properties/groupName",
            "type": "string",
            "title": "The Groupname Schema",
            "default": "",
            "examples": [
              "Group_1"
            ],
            "pattern": "^(.*)$"
          },
          "dataSource": {
            "$id": "#/properties/groups/items/properties/dataSource",
            "type": "string",
            "title": "The Datasource Schema",
            "default": "",
            "enum": [
              "Process",
              "Queue"
            ],
            "pattern": "^(.*)$"
          },
          "likeGrouping": {
            "$id": "#/properties/groups/items/properties/likeGrouping",
            "type": "object",
            "title": "The Likegrouping Schema",
            "required": [
              "likeClause"
            ],
            "properties": {
              "likeClause": {
                "$id": "#/properties/groups/items/properties/likeGrouping/properties/likeClause",
                "type": "string",
                "title": "The Likeclause Schema",
                "default": "",
                "examples": [
                  "%1",
                  "test",
                  "%"
                ],
                "pattern": "^(.*)$"
              },
              "excludeLikeClause": {
                "$id": "#/properties/groups/items/properties/likeGrouping/properties/excludeLikeClause",
                "type": "string",
                "title": "The Excludelikeclause Schema",
                "default": "",
                "examples": [
                  "%1",
                  "test",
                  "%"
                ],
                "pattern": "^(.*)$"
              }
            }
          },
          "inGrouping": {
            "$id": "#/properties/groups/items/properties/likeGrouping",
            "type": "object",
            "title": "The Ingrouping Schema",
            "required": [
              "elements"
            ],
            "properties": {
              "elements": {
                "$id": "#/properties/groups/items/properties/likeGrouping/properties/likeClause",
                "type": "array",
                "title": "The Elements Schema",
                "default": "",
                "pattern": "^(.*)$"
              }
            }
          }
        }
      }
    }
  }
}

Where:

  • groupName - The name that the table will have in the Insights cube.
  • tenantId - The Id of the tenant the grouping is applied to.
  • dataSource - Whether the data source is a Process or Queue.
  • Optional: elasticcubeNameTemplate - The name of the Insights cube, default {TENANT}-Cube. Specify this if the grouped tables should go to a different cube. For example, {TENANT}-DocCube. The tenant name will be substituted when the table is created, so if the Default tenant is being targeted, the table would be added to Default-DocCube.

For example:

{
  "groups": [
    {
      "groupName": "Group_1",
      "tenantId": 3,
      "dataSource": "Process",
      "likeGrouping": {
        "likeClause": "%1"
      }
    },
    {
      "groupName": "Group_3",
      "tenantId": 3,
      "dataSource": "Process",
      "likeGrouping": {
        "likeClause": "%3",
        "excludeLikeClause": "%Environment_1%"
      }
    },
    {
      "groupName": "Doc_1+2",
      "tenantId": 3,
      "elasticubeNameTemplate": "{TENANT}-DocCube",
      "dataSource": "Queue",
      "inGrouping": {
        "elements": ["Doc", "Doc2"]
      }
    },
    {
      "groupName": "TestQueues",
      "tenantId": 3,
      "elasticubeNameTemplate": "{TENANT}-Test-Cube",
      "dataSource": "Queue",
      "likeGrouping": {
        "likeClause": "%Test"
      }
    }
  ]
}

Updated about a month ago


Enabling Table Grouping


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.