IT Analytics for ServiceDesk cubes fails to process

Products

• IT Analytics
• ServiceDesk

Issue/Introduction

All ServiceDesk cubes fail to process resulting in the error message generated below.

 

Error Messages 1
OLE DB error: OLE DB or ODBC error: The statement has been terminated.; 01000; String or binary data would be truncated.; 22001.
SQL queries 1
SELECT [UserDim].[UserID] AS [UserDimUserID0_0],[UserDim].[FirstName] AS [UserDimFirstName0_1],[UserDim].[LastName] AS [UserDimLastName0_2],[UserDim].[Address1] AS [UserDimAddress10_3],[UserDim].[Address2] AS [UserDimAddress20_4],[UserDim].[City] AS [UserDimCity0_5],[UserDim].[State] AS [UserDimState0_6],[UserDim].[Zip] AS [UserDimZip0_7],[UserDim].[OrganizationTitle] AS [UserDimOrganizationTitle0_8],[UserDim].[DisplayName] AS [UserDimDisplayName0_9],[UserDim].[VIP] AS [UserDimVIP0_10],[UserDim].[PrimaryEmail] AS [UserDimPrimaryEmail0_11],[UserDim].[Department] AS [UserDimDepartment0_12]
FROM
(

SELECT UserID, FirstName, LastName, Address1, Address2, City, State, Zip, OrganizationTitle, DisplayName, CASE WHEN VIP = 1 THEN 'Yes' ELSE 'No' END AS VIP,
PrimaryEmail, Department
FROM vITAnalytics_SD_UserDim
)
AS [UserDim]
 

Cause

The column size for one of the fields defined in the view vITAnalytics_SD_UserDim is smaller than the data that is attempted to be copied into it from the ProcessManager database. 

Resolution

The field in question is more than likely the DisplayName field as it is only configured to allow a max length of 50 characters. To very if the DisplayName field is the problem run the following query against your ProcessManager database

SELECT MAX(LEN(GroupName)) FROM [Group]

If the returned results is greater then 50 this will generate the truncation error. We will need to update the function fnITAnalytics_SD_UserDim & the view vITAnalytics_SD_UserDim

To update fnITAnalytics_SD_UserDim problem perform the following steps:

1. Expand Symantec_CMDB in SQL Manager.
2. Expand Programmability
3. Expand Functions
4. Right-click on Table-valued Functions & Choose Filter\Filter Settings
5. Type in _SD_ in the Value field for the Name Property and click OK
6. Expand Table-valued Functions
7. Right-click on dbo.fnITAnalytics_SD_UserDim & Choose Script Function As\ALTER To\New Query Editor Window
8. Find the line DisplayName nvarchar(50) NULL, & Modify it to DisplayName nvarchar(255) NULL,
9. Click the Execute Button.

To update vITAnalytics_SD_UserDim problem perform the following steps:

1. Expand Symantec_CMDB in SQL Manager.
2. Right-click on Views & Choose Filter\Filter Settings
3. Type in _SD_ in the Value field for the Name Property and click OK
4. Expand Views
5. Right-click on dbo.vITAnalytics_SD_UserDim & Choose Script Function As\ALTER To\New Query Editor Window.
6. Click the Execute Button.

If you don't feel comfortable performing these steps please contact Symantec Technical Support for assistance.