Problem
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.
Error
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
Solution
(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.
Preferred Solution: Correct the data directly.
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.
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
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.
Run Microsoft Visual Studio, preferably on the SQL Server, or at least
on a workstation that has access to the SSAS. Click on the File menu >
Open > Analysis Services Database. Type the SSAS's server name in the
Server field. Click on the dropdown field for Database and select the
SSAS database to edit. Click on the OK button. In the Solution
Explorer window, scroll down until Dimensions are displayed. 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. In the Attributes window, click on the dimension's attribute
to edit. For example, "Computer - Name". In the Properties window,
scroll down until the Source section is displayed. Click on the "+" to
expand the NameColumn field. 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. Repeat steps 7 through 11 until
all dimensions and affected attributes are changed. 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.
