Issue
The user's application may require additional custom measurements to represent non-standard data.

Product Line
Power Monitoring Expert 8.x

Environment
Web Application Reports
Web Application Gadgets
SQL Server Management Studio

Cause
Metadata summarizes basic information about data, making finding and working with instances of data easier. Power Monitoring Expert (PME), is installed with a default set of measurements called Standard Measurements.
To expose the custom measurement to energy-related dashboard gadgets, you need to change the
measurement's metadata for the following three measurement characteristics:

1. Set ACCUMULATION_TYPE to CUMULATIVE (If the custom measurement represents cumulative data).
2. Set APPLICATION to CONSUMPTION and
3. Set UTILITY to a value that is not N/A. For example ELECTRICITY, GAS, etc.

To change the custom measurement's metadata, three IDs for each characteristic are needed.

1. The measurement's MetadataItemId;
2. The MetadataValueId for the measurement's current metadata; and
3. The MetadataValueId for the target metadata.

*Warning: Take backups of any files prior to modifying them. Ensure the backups are in a location that will not be overwritten (i.e. the Desktop).​


Resolution
The queries below can be found in the attached zipped file.
Step 1:
Using Query1, identify the Metadata currently assigned to your custom measurement. Replace with the custom measurement's name.

Query1

---

-- Show assigned metadata
SELECT *
FROM [ApplicationModules].[CommonDataModel].[TopicMetadataView]
WHERE [Description] LIKE '%%'

---

From the result pane record TopicId and MetadataItemId. (note: A negative TopicId indicates a custom measurement. Measurements supplied by the factory have a positive TopicId number. Measurement TopicId)


Step 2:
Using Query2, identify the current MetadataValueIds. Replace the with the number recorded in Step 1.

Query2

---

-- Show the Measurement's Metadata in a legible format
SELECT
B.[MetadataItemId]
, C.[DataSourceRepresentation] as MeasurementName
, A.[MetadataValueId]
, E.[Name] as MetadataName
, D.[Value] as MetadataValue
FROM [ApplicationModules].[Metadata].[MetadataItemValue] A
INNER JOIN [ApplicationModules].[Metadata].[MetadataItem] B
ON B.MetadataItemId = A.MetadataItemId
INNER JOIN [ApplicationModules].[DataSourceBus].[DataSourceTopic] C
ON C.TopicId = B.TargetId
INNER JOIN [ApplicationModules].[Metadata].[MetadataValue] D
ON D.MetadataValueId = A.MetadataValueId
INNER JOIN [ApplicationModules].[Metadata].[MetadataName] E
ON E.[MetadataNameId] = D.MetadataNameId
WHERE C.[TopicId] =
ORDER BY D.[MetadataNameId],A.[MetadataValueId]

---

From the result pane record three MetadataValueIds for CDM.MdName.ACCUMULATION_TYPE, CDM.MdName.APPLICATION, and CDM.MdName.UTILITY.


Step 3:
Using Query3, identify the Target MetadataValueId for ACCUMULATION_TYPE characteristic and find the MetadataValueId for the CUMULATIVE metadata.

Query3

---

-- Find the MetadataValueId for CUMULATIVE
SELECT a.[MetadataValueId], a.[Value]
FROM [ApplicationModules].[Metadata].[MetadataValue] a
INNER JOIN [ApplicationModules].[Metadata].[MetadataName] b
ON b.MetadataNameId = a.MetadataNameId
WHERE b.[Name] = 'CDM.MdName.ACCUMULATION_TYPE'
AND a.[Value] = 'CDM.MdValue.CUMULATIVE'

---

From the result pane record the target MetadataValueId for CUMULATIVE.


Step 4:
Using Query4, identify the Target MetadataValueId for APPLICATION characteristic and find the MetadataValueId for the CONSUMPTION metadata.

Query4

---

-- Find the MetadataValueId for CONSUMPTION
SELECT a.[MetadataValueId], a.[Value]
FROM [ApplicationModules].[Metadata].[MetadataValue] a
INNER JOIN [ApplicationModules].[Metadata].[MetadataName] b
ON b.[MetadataNameId] = a.[MetadataNameId]
WHERE b.[Name] = 'CDM.MdName.APPLICATION'
AND a.[Value] = 'CDM.MdValue.CONSUMPTION'

---

From the result pane record the target MetadataValueId for CONSUMPTION.


Step 5:
Using Query5, identify the Target MetadataValueId for UTILITY characteristic and find the MetadataValueId for the desired utility that best represents your custom measurement.

Query5

---

-- Find the MetadataValueId for desired UTILITY
SELECT a.[MetadataValueId], a.[Value]
FROM [ApplicationModules].[Metadata].[MetadataValue] a
INNER JOIN [ApplicationModules].[Metadata].[MetadataName] b
ON b.MetadataNameId = a.MetadataNameId
WHERE b.[Name] = 'CDM.MdName.UTILITY'
AND a.[Value] <> 'CDM.MdValue.N/A'
ORDER BY a.[Value]​

---

From the result pane record the target MetadataValueId for the desired UTILITY.


Step 6:
With the Measurement, MetadataItemId recorded in step 1, and target MetadataValueIds recorded in Steps 3,4, and 5, run Query6 to update the necessary metadata assignments for each measurement characteristic that will change (ACCUMULATION_TYPE, APPLICATION, and UTILITY).

Query6

---

-- Update Measurement metadata
UPDATE [ApplicationModules].[Metadata].[MetadataItemValue]
SET MetadataValueId =
WHERE MetadataItemId =
AND MetadataValueId =

---

Step 7:
Review a summary of the assigned metadata by re-running Query2 in Step 2.
Ensure:
1. CDM.MdName.ACCUMULATION_TYPE is set to CDM.MdValue.CUMULATIVE.
2. CDM.MdName.APPLICATION is set to CDM.MdValue.CONSUMPTION.
3. CDM.MdName.UTILITY is set to a value that is not CDM.MdValue.N/A.


Note: For PME9.0 please refer to PME 9.0 How to Expose Custom Measurements for Use in Web Applications  Application note, as procedure has changed compared to PME8.x version
Link:
https://ecobuilding.schneider-electric.com/search?documentUUID=881bba0d-bcd6-403a-a41d-c38a5fd2ed8d&title=PME%209.0%20How%20to%20Expose%20Custom%20Measurements%20for%20Use%20in%20Web%20Applications#tab/documents

The following video shows how metadata is defined for an energy-related custom measurement called CustomLabel1.

Released for:Schneider Electric Hong Kong