Advanced Analytics search is returning invalid results

Problem

Enterprise Vault (EV) Discovery Accelerator (DA) Analytics Searches do not show the expected results. SQL is unable to successfully create the full text indexes for the tables tblIntAnalysedItems_X and/or tblIntItemCustomAttributes_X, while the Discovery Accelerator (DA) Client shows the Automatic categorization as Completed.

Error Message

From a pop-up window in the DA Client's Analytics tab in a Case:

<!> Analytics

Full-text indexing of some collected items is in progress.  The advanced search and automatic categorization facilities may not work properly until this process has completed.  If the status remains unchanged after 15 minutes, you may need to rebuild the full-text indexes by following the instructions in this article:
http://www.veritas.com/docs/TECH160612

Cause

A myriad of reasons can cause SQL to unsuccessfully create full-text indexes (FTI). The most common culprit is anti-virus scanning of the SQL database files. 

If the following errors are seen:

- The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblIntConversationItems_X_ConversationId.

- Exception: Error ConversationAnalysis_Continuous_DB_Failure_Error

Go to SQL Management Studio (SSMS) | Databases | Expand the DA Customer database | Tables | Right-click the tblIntAnalysedItems_<CaseID> table | Full-Text Index Properties.
If the following are not set to zero, there is an issue with the FTI:
- Table Full-Text Pending Changes
- Table Full-Text Fail Count

Solution

Analytics FTI errors can usually be resolved by rebuilding the FTI. To rebuild the FTI for a DA Analytics-enabled Case, follow these steps:

To Rebuild the FTI for tblIntAnalysedItems_X:

1. Right-click the tblIntAnalysedItems_X table | Full-Text Index | Properties.

2. In the General tab, review the Table Full-Text Fail Count for the number of failures.

3. In the Columns tab:

3.1. Verify the primary index is selected; this index name starts with PK_ and will not have _RowID appended to the end of the name.

3.2. Note the selected items in Available Columns and the associated Language For Word Breaker and Type Column values. These will need to be re-selected/re-entered in the next steps. A screenshot of the current settings might be helpful.
Sample Columns page entries for tblIntAnalysedItems_X:

Available Columns    Language For Word Breaker    Type Column
Author    English    [blank]
ContentData    English    ContentDataType
Email BCC    English    [blank]
EmailCC    English    [blank]
EmailTo    English    [blank]
Extension    English    [blank]
FileName    English    [blank]
MsgClass    English    [blank]
OriginalLocation    English    [blank]
RetentionCategoryName    English    [blank]
Subject    English    ContentDataType

3.3. Close the FTI Properties window.

4. Right-click the tblIntAnalysedItems_X table | Full-Text Index | Delete Full-Text Index | Respond accordingly to the prompt to delete the FTI | Close the status window upon deletion completion.

5. Create a new FTI:

5.1. Refresh the database view in SSMS.

5.2. Right-click the tblIntAnalysedItems_X table | Full-Text Index | Define Full-Text Index.

5.3. Click Next at the FTI Wizard starting page.

5.4. Select the primary index as the Unique Index (does NOT have _RowID at the end of the Index name), then click Next.

5.5. Select the Available Column items, applicable Language For Word Breaker information and Type Column values for each selected Available Column entry as noted above. Click Next when all fields are correctly populated.

5.6. Leave all entries at their default values on the Select Change Tracking page and click Next.

5.7. Select the appropriate Fill-Text Catalog in the Select full-text catalog field. The _X value must match the _X value of the table name. For example, if the table name is tblIntAnalysedItems_5, the full-text catalog should be DataFullTextIndex_<DA_Customer_database_name>_5. Click Next.

5.8. Do not edit any further settings. Click Next through the remaining windows. Click Finish at the last window.

5.9. Monitor the Full-Text Indexing Wizard Progress window and click Close when successfully completed.

To Rebuild the FTI for tblIntItemCustomAttributes_X:

1. Right-click the tblIntItemCustomAttributes_X table | Full-Text Index | Properties.

2. In the General tab, review the Table Full-Text Fail Count for the number of failures.

3. In the Columns tab:

3.1. This table does not have a primary index and only has a unique index. Verify the unique index is selected; this index name starts with UQ_ and has _RowID appended to the end of the name, then click Next.

3.2. Note the selected items in Available Columns and the associated Language For Word Breaker and Type Column values. These will need to be re-selected/re-entered in the next steps. A screenshot of the current settings might be helpful.
Sample Columns page entries for tblIntItemCustomAttributes_X

Available Columns    Language For Word Breaker    Type Column
StringValue    English    [blank]

3.3. Close the FTI Properties window.

4. Right-click the tblIntItemCustomAttributes_X table | Full-Text Index | Delete Full-Text Index | Respond accordingly to the prompt to delete the FTI | Close the status window upon deletion completion.

5. Create a new FTI:

5.1. Refresh the database view in SSMS.

5.2. Right-click the tblIntItemCustomAttributes_X table | Full-Text Index | Define Full-Text Index.

5.3. Click Next at the FTI Wizard starting page.

5.4. Select the unique index as the Unique Index (has UQ_ at the beginning of the index name and _RowID at the end of the index name), then click Next.

5.5. Select the Available Column items, applicable Language For Word Breaker information and Type Column values for each selected Available Column entry as noted above. Click Next when all fields are correctly populated.

5.6. Leave all entries at their default values on the Select Change Tracking page and click Next.

5.7. Select the appropriate Fill-Text Catalog in the Select full-text catalog field. The _X value must match the _X value of the Table Name. For example, if the table name is tblIntItemCustomAttributes_5, the full-text catalog should be DataFullTextIndex_<DA_Customer_database_name>_5. Click Next.

5.8. Do not edit any further settings. Click Next through the remaining windows. Click Finish at the last window.

5.9. Monitor the Full-Text Indexing Wizard Progress window and click Close when successfully completed.