How to handle Multiple Custom fields with Same Name but Different Case in Insights

How to handle multiple custom fields with the same name but different case in insights (ex. hello and Hello) ?

Description:

Sisense doesn't support multiple custom fields with same name but different casing. The cube build will fail in such case.

 

Steps to Reproduce:

  1. Create process that adds custom log field : Hello : test
  2. Run workflow
  3. Run build and make sure little table gets created
  4. Update process and change custom log file : hello : test
  5. Run workflow
  6. Let Sisense cube build

 

Workaround:

  • Identify the custom field that is causing the issue. The duplicate columns can be identified by using the below powershell script.

foreach ($f in Get-ChildItem "C:\ProgramData\Sisense\DataConnectors\JVMContainer\Connectors\UiFrost\state")

{

$duplicate = Get-Content $f.FullName | Group-Object | Where-Object { $_.Count -gt 1 } | Select -ExpandProperty Name

if ($duplicate)

{

write-output $f.FullName

foreach ($d in $duplicate)

{

write-output "Duplicate Column : $d"

}

}

}

  • Once the duplicate fields are identified, replace data in the Insights DB by running below SQL scripts.

 

-- For Robotlogs table --

DECLARE @processName nvarchar(200);

DECLARE @oldArgument nvarchar(200);

DECLARE @newArgument nvarchar(200);

 

-- Initialize the variable.

SET @processName = N'processName';

SET @oldArgument = N'initialValue';

SET @newArgument = N'newValue';

 

update RobotLogs

set rawMessage = replace(rawMessage COLLATE Latin1_General_CS_AS, @oldArgument, @newArgument)

from RobotLogs

where ProcessName = @processName and rawMessage like '%' + @oldArgument + '%' COLLATE Latin1_General_CS_AS

 

-- For Queues table --

DECLARE @queueName nvarchar(200);

DECLARE @oldArgument nvarchar(200);

DECLARE @newArgument nvarchar(200);

 

-- Initialize the variable.

SET @queueName = N'queueName';

SET @oldArgument = N'initialValue';

SET @newArgument = N'newValue';

 

update QueueItems

set SpecificData = replace(SpecificData COLLATE Latin1_General_CS_AS, @oldArgument, @newArgument)

from QueueItems

where QueueName = @queueName and SpecificData like '%' + @oldArgument + '%'     
 

  • Identify the UUID for the process that is causing the issue by looking in the datasets table (ex. select * from Datasets where TableName = 'Process-BlankProcess26_prep-1' order by 1 desc)
  • Delete incorrect column in connector state file using the guid returned from the datasets table (ex. C:\ProgramData\Sisense\DataConnectors\JVMContainer\Connectors\UiFrost\state\1ba2ca1b-4971-4a56-9ab1-f5f84eb52902_json_fields)
  • Delete the cube
  • Re-create the cube using the Insights Admin tool