Data row read error loading reports
search cancel

Data row read error loading reports

book

Article ID: 156244

calendar_today

Updated On:

Products

IT Analytics IT Management Suite

Issue/Introduction

The following error is thrown when accessing an IT Analytics (ITA) report:

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 addition, the processing of the cube(s) on which the report is based also fail and log an error similar to the following:

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

Environment

ITMS 8.x

IT Analytics

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 throws an error. This is therefore not a limitation or issue caused by ITA product or SSAS, but by the contents of the data source due to the manner in which it was created and stored. This may also occur if data become corrupted (SQL server crash, bad import, etc.).

Resolution

To resolve this error, Broadcom recommends correcting the data in the data source system. To do this, follow this general procedure:

  1. Determine which reports return this error and note their report names. This generally only occurs for a single report, or a small number; all others work successfully.
  2. Using the data source system's built-in reporting (e.g., Symantec Endpoint Protection) or a SQL script, locate and view those reports that are similar to the reports used in ITA. Do any columns contain non-alphanumeric characters?

    For example, CostCenterAbbreviation is the name of a column in a data set generating this error. A SQL query in the source database returns the following values on this column:

    LIN
    LON
    SPR©
    TAC

    The copyright symbol (©) is not an appropriate character for a cost center and is easily identified as a non-alphanumeric character. This character may or may not be a reserved XML character. If it is, SSAS will not be able to process the cubes and dimensions that reference this cost center abbreviation. Based on this query, we know that the CostCenter field contains non-alphanumeric characters and warrants further investigation.
  3. If queries or source reports reveal the presence of non-alphanumeric characters, change these at their source. For example, for ServiceDesk users, if records were imported from Active Directory (AD) and the issue exists there, change the affected record(s) in AD and then perform an update import into ServiceDesk to change the user names there. Once changed, re-process the ITA cubes in question to enable the IT Analytics ServiceDesk reports that previously failed. Depending on the scope of the problem, you may need to identify and correct multiple characters spanning multiple columns.  

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

While it is beyond the scope of Broadcom Technical Support to assist in resolving data issues such as the existence of non-alphanumeric characters in source data, the attached SQL script Parse Tables and Columns for Non-Alphanumeric Characters.sql can assist with finding non-alphanumeric data. The script can be run against any source database queried by ITA suspected of containing non-alphanumeric characters (Symantec_CMDB, ProcessManager, SEPM, etc.).

NOTE:

  1. This script does not remove or replace data.
  2. Instructions for using the script are documented within the script itself.
  3. The script will parse every table, column, and row looking for non-alphanumeric characters; consequently, Broadcom recommends not running the script against a production database during production hours, as it will adversely affect database performance.
  4. Once you determine which records contain non-alphanumeric data, you must determine how best to resolve this (i.e., cleanse the data or modify the cube dimension(s) to ignore XML conversion failures).

Workaround

As an alternative to correcting the source data, you can configure SSAS to automatically convert non-alphanumeric characters. To do so, you must have a copy of Visual Studio with the Analysis Services project plug-in installed and access to the server hosting the IT Analytics cubes. Support for this method is not provided by Broadcom.

Additional Information

Additional information on XML parsing and invalid characters can be found in the following Microsoft technical articles:

InvalidXmlCharacters Enum
https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.invalidxmlcharacters?view=analysisservices-dotnet
NOTE: URL last validated June 5, 2023

DataItem.InvalidXmlCharacters Property
https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.dataitem.invalidxmlcharacters?view=analysisservices-dotnet
NOTE: URL last validated June 5, 2023

Attachments

Parse Tables and Columns for Non-Alphanumeric Characters.sql get_app
Powered by Wolken Software