Merge statement conflicts with constraint on the Organizations table

Products

• Information Centric Analytics

Issue/Introduction

After upgrading to 6.7, the Run User Loading Procedures and Run Loading Procedures job steps in both the nightly RiskFabric Processing job and the RiskFabric Intraday Processing job fail and log the following error message:

Error=The MERGE statement conflicted with the CHECK constraint "CC_Organizations_SubOrgName_No_Forbidden_Characters". The conflict occurred in database "RiskFabric", table "dbo.Organizations", column 'SubOrgName'.

Environment

Release : 6.7

Component : Integration Wizard

Cause

The Risk Fabric console contains controls to prevent the insertion of SQL and MDX reserved characters in organization and sub-organization names. The following characters are considered invalid:

,;':/\\*|@?\"+=[]<>"

As of version 6.6 MP2, there were no controls to prevent these characters from being included in organization names that are bulk imported through a data source query job. Beginning with version 6.7, CHECK constraints have been added to the dbo.Organizations table to prevent these characters from being inserted into that table.

Resolution

There are three options for working with this restriction:

1. Apply an import rule mapping formula to remove invalid characters
2. Modify the data source query to remove invalid characters using inline functions
3. Remove invalid characters from the source data

The recommended workaround is to apply an import rule mapping formula to remove the invalid characters at the time data are moved from staging tables to core tables in the RiskFabric relational database.

To do this, edit the integration's import rule mapping to use a formula for the OrganizationAbbreviation, OrganizationName, and OrganizationSubOrgName fields. That formula will call a built-in function that is included with 6.7 (dbo.fnReplaceSpecialCharacters). The function will parse these three columns on each imported record and replace any instances of special characters with a space (or an empty string to effectively concatenate the org name - your choice).

For the Organizations entity type in the Integration Wizard, there are only four fields:

1. 1. OrganizationAbbreviation (required)
   2. LastDetected (Optional)
   3. OrganizationName (Create and Associate Organizations)
   4. OrganizationSubOrgName (Create and Associate Organizations)

To apply a formula to the non-date fields, follow these steps:

1. Navigate in the console to Admin > Integration > Data Integrations
2. Expand the integration pack specific to the org data you're importing through a data source query
3. Right-click the import rule mapping for that integration pack and select Edit Import Rule Mapping
4. Under the Required Fields heading, locate the OrganizationAbbreviation entity column and select Formula from the dropdown menu in the Type column
5. Select Create New Formula from the dropdown menu in the Value column
   
   The Create Formula window opens
   
   
6. Enter a Display Name of your choice (for example, 'Replace Special Characters')
7. Enter a description of your choice
8. Enter the following in the Template field:

   dbo.fnReplaceSpecialCharacters({SourceColumn},' ')

   NOTE: If you wish to concatenate rather than use a space to separate the words in the string, simply remove the space between the two single quotation marks, like so:
   

   dbo.fnReplaceSpecialCharacters({SourceColumn},'')

   Alternatively, you could also replace special characters with an underscore:
   

   dbo.fnReplaceSpecialCharacters({SourceColumn},'_')

9. Click the Save button
   
   The Parameter Values for OrganizationAbbreviation Formula window opens
   
   
10. Select Source Column from the SourceColumnType dropdown menu and OrganizationAbbreviation from the Value dropdown menu
11. Click the Save button

Once the formula has been created, you need to apply it to the two remaining fields:

1. Under the Create and Associate Organizations heading, locate the OrganizationName entity column and select Formula from the dropdown menu in the Type column
2. Select the new formula (for example, 'Replace Special Characters') from the dropdown menu in the Value column
   
   The Parameter Values for OrganizationName Formula window opens
   
   
3. Select Source Column from the SourceColumnType dropdown menu and OrganizationName from the Value dropdown menu
4. Click the Save button
5. Repeat steps 1 through 4 for the OrganizationSubOrgName entity column
6. Click the Save button to save these changes to the import rule mapping

With these changes in place, the nightly job will apply the formula you've created and remove all special characters that violate CHECK constraints on these columns when merging records from the org staging table into the dbo.Organizations table.

Additional Information

Related knowledgebase article: Organization dashboards display no results