Technical FAQs

Ask a Question

PME 7.2.2/PME 8.0: Missing Parameters in default measurement list for certain meters in Reports or Dashboards

Issue
Measurement parameters are either missing completely or partially from the default measurement list for certain meters when attempting to add measurements to a data series in dashboards or reports. In some instances, the parameters are found under custom measurements instead.



Product Line
Power Monitoring Expert 7.2.x, 8.0

Environment
StruxureWare Power Monitoring 7, Power Monitoring Expert 7.2, Power Monitoring Expert 8, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Cause
This behavior in PME is caused by the quantity ID's in the database for specifc meters (sourceID's) being set to custom measurement ID's instead of the correct factory measurement ID's.

Resolution

**Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.

In order to determine the sourceID of the device name, execute the following query to show a table containing ID and Name from dbo.Source:

use ION_Data
SELECT ID, Name FROM [ION_Data].[dbo].[Source] order by ID asc

The SQL script found below is required to be executed in order to resolve the issue. The sourceID of the meter device will be required to be defined below in the script at "SourceID=" as highlighted. The script will need to be executed each time for each source to correct the issue.

--
USE [ION_Data]

DECLARE @RecordCount INT
DECLARE @Index INT
DECLARE @CID BIGINT
DECLARE @FID BIGINT

DECLARE @MeasurementsToFix TABLE
(
    Num INT IDENTITY(1,1)
    , FactoryID BIGINT
    , CustomID BIGINT
)

SELECT @Index = 1

--get the list of custom measurement IDs and the corresponding correct factory measurement IDs
INSERT INTO @MeasurementsToFix
SELECT 
    DefaultLabelQuantity.QuantityID
    , Quantity.ID
FROM 
    DefaultLabelQuantity INNER JOIN Quantity ON
    DefaultLabelQuantity.Label = Quantity.Name
WHERE 
    Quantity.ID > 9999

-- Uncomment the following line for testing purposes
-- SELECT * FROM @MeasurementsToFix

-- assign quantity IDs from custom to factory for the affected source

SELECT @RecordCount = COUNT(Num) FROM @MeasurementsToFix

BEGIN TRY

    BEGIN TRAN

    WHILE (@Index <= @RecordCount)
        BEGIN
            -- assign quantity IDs from custom to factory for the affected source
            -- get the first set of custom and factory IDs from the measurements table
            SELECT @CID = CustomID FROM @MeasurementsToFix WHERE Num = @Index
            SELECT @FID = FactoryID FROM @MeasurementsToFix WHERE Num = @Index
            UPDATE DataLog2 SET QuantityID = @FID WHERE QuantityID = @CID and SourceID = <sourceID to correct>
            -- fix the entries in the channel table
            UPDATE Channel SET QuantityID = @FID WHERE QuantityID = @CID
            -- increment the index
            SELECT @Index = @Index + 1
        END

    IF @@TranCount > 0
    BEGIN
        COMMIT
    END    

END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK
    END
    
    SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

END CATCH

--
 
Was this helpful?
What can we do to improve the information ?