IT Analytics SEP report fails to load "An error has occurred during report processing"

Products

• IT Analytics

Issue/Introduction

Error "Cannot read the next data row for the dataset 'dataset name'" occurs when accessing an IT Analytics report

When accessing an IT Analytics report, the error "An error has occurred during report processing. Cannot read the next data row for the dataset 'dataset name'." occurs. Also, processing of cubes that relate to the report may also result in issues.

In IT Analytics reports:

An error has occurred during report processing. (rsProcessingAborted)
            Cannot read the next data row for the dataset 'dataset name'. (rsErrorReadingNextDataRow)
                         For more information about this error navigate to the report server on the local server machine, or enable remote errors.

In SQL when trying to process a cube or dimension:

The server sent an unrecognizable response.
"<invalid-character>, hexidecimal value 0x<hex-value>, is an invalid character. Line <line-#.>, position <position-#>. (System.Xml)

Cause

If invalid XML characters (i.e., certain non-alphanumeric characters) are present in data, the Microsoft SQL Server Analysis Server (SSAS) may not be able to process cubes and their dimensions. This is because XML reserves certain characters that are non-alphanumeric in nature for use as programming code. When these are seen in data, XML attempts to process them as escape codes for code, but as they are not actually code, it results in errors. This is therefore not a limitation or issue caused by the IT Analytics product or by SSAS, but by the contents of the user's data, however it was populated. This may occur also if data becomes corrupted (SQL server crash, bad import, etc.)

Additional information on this can be found on Microsoft's web sites:

http://technet.microsoft.com/en-us/library/microsoft.analysisservices.dataitem.invalidxmlcharacters.aspx
http://technet.microsoft.com/en-us/library/microsoft.analysisservices.invalidxmlcharacters.aspx
http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.dataitem.invalidxmlcharacters(v=sql.90).aspx

Resolution

IMPORTANT NOTES ABOUT USING THIS ARTICLE:

(1) This article is only intended to troubleshoot and resolve the exact errors as shown above. If the user sees a different variation of these errors, this article is most likely not applicable.

(2) For simplicity of reading, the affected characters that are the issue, which are invalid XML characters, are from here on referred to as "non-alphanumeric characters", as this is how they will appear to the user. This does not indicate and should not be confused with, however, that any non-alphanumeric character is an issue, but only refers to those that will affect XML, as described in the URL references in the Cause section, above.

(3) "Non-alphanumeric charactes" refers to any non-standard or invalid character found that normally shouldn't be used. For example, copyright symbols, trademark symbols, line graphics, or what appear to be oriental letters (corrupted data, in this case). All letters, numbers and standard characters such as hyphens, periods, + signs, etc., are acceptable.

(4) Symantec Technical Support is unable to assist the user in in-depth troubleshooting this issue based on the cause: their data is invalid and SSAS directly cannot process the data. ITA is thereby effected, but is not the cause of the issue in itself. The user must themselves find and resolve any bad data that contains non-alphanumeric characters. Please contact your DBA if you need assistance in doing this. 

To help the user get started in troubleshooting this, Symantec Technical Support has provided the following suggestions:
 
Preferred Solution: Correct the data directly.
 

1. Check to verify which reports see this issue note their report names. This generally only occurs for a single report, or a small number; all others work successfully.
2. Using the affected product's own reports (not ITA reports) or a SQL script, locate and view those that are similar to the ones desired to be used in IT Analytics. Do any columns and fields visibly contain non-alphanumeric characters? For example, if "cost centers" was appearing as the data set that has the issue in the error, review the report found by clicking on the Reports button > All Reports > Service and Asset Management > Organizational Types > Cost Center > List of Cost Centers by Location. Do any of their names include invalid characters?
   
   Cost Center Name
   Springfield©
   Lindon
   
   In the first cost center's name there is a non-alphanumeric character, a copyright symbol "©". It doesn't make sense that there would be a copyright symbol in a cost center's name and so this sticks out visually as being invalid. This character may or may not be a reserved XML character. If so, SSAS will not be able to process any cubes and dimensions that reference this worker's name. Based on this, we know that at the least, the cost center's Name field has non-alphanumeric characters.
   
   This can be inspected in SQL too, for example:
   
   USE Symantec_CMDB
   SELECT *
   FROM vCostCenter
    
3. If the product reports do indeed show such non-alphanumeric characters, change these at their source. For example, for ServiceDesk users, if these were imported from Active Directory and the issue exists there, change the user name there, then perform an update import into ServiceDesk to change the user names there. Once changed, then try re-processing the IT Analytics cubes in question to then enable the IT Analytics ServiceDesk reports that previously did not work to then work. Note: Depending on the scope of the amount of non-alphanumeric characters, this may be a long and tedious process to manually fix for the user.
    

Workaround: Change SSAS to ignore the non-alphanumeric characters.
 

1. Run Microsoft Visual Studio, preferably on the SQL Server, or at least on a workstation that has access to the SSAS.
2. Click on the File menu > Open > Analysis Services Database.
3. Type the SSAS's server name in the Server field.
4. Click on the dropdown field for Database and select the SSAS database to edit.
5. Click on the OK button.
6. In the Solution Explorer window, scroll down until Dimensions are displayed.
7. Double click on the dimension to edit. For example: "Computer". Note: The dimension to edit will generally be very close or the same name as appears in the error, the data set name referred to there. The user may need to experiment with which one is the correct one to edit, however.
8. In the Attributes window, click on the dimension's attribute to edit. For example, "Computer - Name".
9. In the Properties window, scroll down until the Source section is displayed.
10. Click on the "+" to expand the NameColumn and KeyNameColumn fields.
11. Change the "InvalidXmlCharacters" field from Preserve to either Replace (which replaces the non-alphanumeric characters with a "?") or Remove (which completely removes the non-alphanumeric characters). It is recommended to set this on all unicode and non-unicode text attributes as potentially any off these could include invalid XML characters.
12. Repeat steps 7 through 11 until all dimensions and affected attributes are changed.
13. Click on the File menu > Save All. This will prompt to save the changes.
     

Troubleshooting: Use SQL to help find the columns and fields that contain non-alphanumeric characters.

While it is beyond the scope of Symantec Technical Support to assist in resolving data issues such as non-alphanumeric characters found in the customer's data, the attached SQL script "Parse Tables and Columns for Non-Alphanumeric Characters.sql" can help the user in finding non-alphanumeric data. This script can be ran against any database that is suspected of having non-alphanumeric characters (Symantec_CMDB, ProcessManager, SEPM, etc.) that IT Analytics accesses for data for its reports.

NOTES: 

(1) This SQL script does not remove the data but only finds it for the user.
(2) Once the user determines which columns and fields contain non-alphanumeric data, it is up to the user to determine how best to resolve this. It is recommended to use the above solution and workarounds to help with this.
(3) Instructions on how to use the script are found inside of it. Please note that this script will take an extremely long time to run and it is not recommended to run against a production database, at least during production hours, as performance may drop. This is because this script will parse through every table, column and field value looking for non-alphanumeric characters. It does not change anything but only reports on what is found. For example, on a small test database, this took seven hours to run. It is therefore strongly recommended that the user is absolutely certain that this is the issue and that the above listed solution and workarounds do not work before even considering using this script.