MASTER YOUR CASTLE with your new build or reno project
Enter competition

How do I add units to my custom measurements so that they display in Web Applications Reports?

FA209765

24/01/2018

Issue
An existing custom measurement does not have an associated unit.

Product Line
ION Enterprise 6.0 SP1
Power Monitoring Expert (PME) 7.0.1
Power Monitoring Expert 7.2.2
Power Monitoring Expert 8.0
Power Monitoring Expert 8.1

Environment
SQL Server Management Studio
Web Applications Reports

Cause
Custom measurements do not have a default unit.

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 this example, the custom measurement we will add a unit to is called Cstm Meas, and the unit will be testunit.
Note: The custom unit name will only be available in one language (e.g. English).

For ION Enterprise 6.0 SP1

1. Run this query to update the triple store:

USE ION_Network
DECLARE @measurement NVARCHAR(1000)
SET @measurement = N'Cstm%20Meas'
EXEC spTS_StandardRelationship_Add @measurement, N'unit', N'testunit', 'Q'


2. Check that your unit is written to the triple store correctly using this query:

USE ION_Network
SELECT DISTINCT
mm.[ID]
, mm.[StandardName]
, [LegacyID]
, [prefix]
, [unit]
, [phase]
, [quantity]
, [type]
, [format]
, [direction]
, [cumulative]
FROM
(SELECT [Subject], [SubjectNamespace], [Predicate], [Object] FROM dbo.vTS_Triples) AS [TS]
PIVOT
(
MAX([Object])
FOR [Predicate] IN
(
[LegacyID]
, [unit]
, [phase]
, [quantity]
, [type]
, [format]
, [prefix]
, [direction]
, [cumulative]
)
) AS [pvt]
INNER JOIN
dbo.MM_MeasurementMap mm ON (mm.FullName = [SubjectNamespace]+[Subject])
WHERE
mm.ID IN (10009) --> INSERT CUSTOM Measurement ID FROM MM_MeasurementMap table (not QuantityID)


3. Run the following query to update the CUL_DisplayText table:

USE ION_Network
INSERT INTO CUL_DisplayText (ResourceTypeID, ResourceID, Display) values (2, 'Unit=testunit', 'testunit')


Here is a before and after of how the units appear in an example Period over Period report:


For PME7.0.1 / PME7.2.2 / PME8.x

EXECUTE [ION_Network].[dbo].[spTS_StandardRelationship_Add] N'Cstm Meas', N'unit', N'testunit', 'Q'
 

If Cstm Meas is already associated with a unit, refer to FA279856 as how to assign a new unit to Cstm Meas.