Power Monitoring Expert - An error message appears when opening the Logical Device Type Editor or attempting to select measurements in Reports

Issue

Attempting to launch the Logical Device Type Editor fails with an error saying that a UNIQUE constraint on table @MeasurementUnits is being violated. When attempting to select a measurement while configuring a report in the Reports web application, an error in the System Log appears similar to the following:

Source = ReportView_MeasurementHierarchy
Target = /reporter/Default.aspx
Category = Reporting View
Description =
Violation of UNIQUE KEY constraint 'UQ__#B7D7C19__D64A4126724005BB'. Cannot insert duplicate key in object 'dbo.@MeasurementUnits'. The duplicate key value is (http://rddl.xmlinside.net/PowerMeasurementconfiguration/measurement/1/#Average Voltage Line-to-Line High)

Product Line

Power Monitoring Expert 8.x

Power Monitoring Expert 9.0

Power Monitoring Expert 2020

Environment

Logical Device Type Editor

Web Apps Reports

Cause

The root cause of both problems is the same - duplicate metadata for measurement units in the ION_Network database.

Resolution
To work around this issue, it is necessary to modify a stored procedure called spDAL_GetMeasurementHierarchyNodes in the ION_Network database. Change the existing stored procedure as follows:

INSERT INTO
@MeasurementUnits
SELECT
(SubjectNamespace + Subject) -- combine the namespace and the subject to form the full name
, Object
FROM
vTS_Triples
WHERE
Predicate = 'unit'

to be (changes are underlined) - put the 'DISTINCT' key word after SELECT:

INSERT INTO
@MeasurementUnits
SELECT
DISTINCT (SubjectNamespace + Subject) -- combine the namespace and the subject to form the full name
, Object
FROM
vTS_Triples
WHERE
Predicate = 'unit'

Released for: Schneider Electric Canada