Subscribe

UiPath Insights

The UiPath Insights 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:
CommandDescripition
--groupsMandatory.
The file path for the .json file containing the grouping definitions.
--debugEnables debug messages in the UiPath.InsightsAdminTool output. By default, this is set to false.
--promptPrompts the user for each feature. By default, this is set to false.
--log-fileThe file where UiPath.InsightsAdminTool logs are written.
--helpDisplays the UiPath.InsightsAdminTool help screen with these commands.
--versionDisplays 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. A maximum of 75 groups may be created.
  • 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"
      }
    }
  ]
}

🚧

Important

  1. The grouping definition file does not support more than one "Like" clause.
  2. Insights cannot support tables with more than 40 columns, so you should not make groups where the number of custom fields per table will be greater than this number.
  3. When a new process or queue is added in Orchestrator and executed, if it matches an existing group name, the process or queue will be added to the grouping and will automatically be included in Insights.
  4. If a process or queue matches multiple groupings, it will be included in every grouping that it matches.
  5. When a new grouping file is uploaded, it deletes the previous groupings.

Updated 2 years 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.