|
MD_Point_SurrogateKey
|
|
<None>
|
numeric
|
|
|
MD_Point_ID
|
Unique identifier assigned to a hard point by a given control system. This will generally be the same as the device ID. But in case the system uses aliases or other additional identifiers beyond the device ID for hard points, we need this attribute to be distinct. If the "point" recorded here is a utility bill, the ID will be an invoice number and date. The value here may also be something like a utility meter ID.
This attribute, together with the control system name, forms the business key for the MD_Point dimension.
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_ControlSystem
|
In combination with the designation assigned by the control system named here, forms the business key for the MD_Point dimension. The control system is basically just the SCADA, control, or general information system out of which the data here is extracted. Normally actual point data - measurements, pulse counts, etc. - are not uploaded directly to the data warehouse, but rather are taken from some system of record, named here.
If the "point" recorded here is actually a utility bill, the control system will be the vendor.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_Name
|
If a hard point has a name or mnemonic, in addition to its unique identifier (MD_Point_ID), then that name or mnemonic is recorded here.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_ShortName
|
Control/energy information/SCADA systems will often have (in addition to a numeric or other ID) both a name and a short name/abbreviated name for a point.
|
<None>
|
nvarchar(16)
|
16
|
|
MD_Point_AlternateName
|
Sometimes it is useful to have another name or 'alias' available for a given point - not necessarily one given by the system itself, but rather one we (Carleton) give it. This name can be set up to allow us to line up with similar points in other systems.
For example, we may have an 'OK Time' point in one wind turbine's SCADA system, but an 'OTT' or 'Lifetime operational time' variable in another. Those two things might be comparable. Giving them an identical MD_Point_AlternateName would help line the two up in reports.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_Type
|
Control systems assign points to types frequently, and this field allows that information to come through here.
How points are typed all depends on the information system housing the information. One may, e.g., classify points using adjectives like logical, enumerated, pulse, latched, digital, and so on. Another system may use a classification system like: Channel, Command, Control, Data, Error, Parameter, Statistic, Status, Virtual, or Bill (e.g., a utility bill). It all depends, as noted, on the control/SCADA/information system in which this information is housed. Whatever vocabulary it uses will be reproduced here, unless the vocabulary is just plain stupid, and we need to clean it up and make it consistent with other systems.
If a point is virtual, this information may also be registered in MD_Point_IsVirtual (as a 'Yes' value there).
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_OutputType
|
In general, three values: Binary, Analog, Counter, or Unknown. The value here represents what the physical measurement device outputs.
Increasingly meters incorporate their own internal logic and smarts, and do not require external RTUs (remote telemetry units) or PLCs (programmable logic controllers) or associated field panels incorporating these functions. Hence the value here may often be "Unknown" (in the sense of not applicable), because the meter itself is converting voltages from sensors into intelligent signals that follow some protocol, such as Modbus.
There is another attribute, MD_Point_Protocol, for the protocol.
|
DW_CodeDescShort
|
varchar(16)
|
16
|
|
MD_Point_Protocol
|
Provides the name of the primary protocol through which point data has been transmitted to a field panel, control, or energy management system.
The value here does not necessarily tell us if the meter itself is providing pulses, analog signals, etc. (There is another attribute for that, MD_Point_OutputType.)
Version numbers should not be included here, but some effort to use a limited vocabulary should be made (e.g., "Modbus TCP" only, and not also "Modbus over TCP" and "TCP-based Modbus", and not "Modbus 2.0 TCP").
The reason for having this attribute around is so we can ask questions like, "How much Modbus TCP is used, as opposed to Modbus Serial?" or "How much proprietary Siemens is feeding into this system, as opposed to OPC?"
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_IsVirtual
|
Value will be 'Yes' if a given point is soft or virtual (that is, calculated or aggregated from "hard" points).
That is, the value will be 'Yes' if the control, information, or SCADA system from which we extract information about a given point THINKS that that the point is virtual, or labels it as such. Some systems have no such concept, and so all point information downloaded from them will be labeled here as 'No' (not virtual).
Sometimes it is hard to draw a line between a soft and a hard point, since the hard points may actually be pulse meters that go through counters, or field panels, and ultimately to a control or information system of some kind. As the information passes through each of these systems it may be modified in some way, masking the original values emitted by the actual measurement device.
Basically, we have a 'Yes' here if the control or information system from which point data was obtained calls the point virtual.
Indication of a point's virtual or soft status may also be available in MD_Point_Type.
|
DW_YesOrNoNOTNULL
|
char(3)
|
3
|
|
MD_Point_AggregatorlID
|
Pulse data must be counted and interpreted in order to be useful, either by a counter, field panel, or piece of software. Also, soft or virtual points must generally be implemented (aggregated, calculated) in software or at a field panel.
This field provides the unique identifier for the device (or software) that is doing the counting, calculation, or aggregation, in effect creating a soft or virtual point.
In reality there are often going to be several intermediate systems between a given hard point and the data it generates' eventual destination in a control or energy information system. The purpose of this attribute is to offer a place to add information, if any of those intermediate systems are particularly useful to know about. For example, if a Shark electrical metering device accepts inputs from other metering devices (e.g., steam and water meters), and then forwards their pulse counts onward, it may be helpful to list the ID for the Shark device here as the aggregation point for the other devices - if that information is available.
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_AggregatorName
|
Pulse data must be counted and interpreted in order to be useful, either by a counter, field panel, or piece of software. Also, soft or virtual points must generally be implemented (aggregated, calculated) in software or at a field panel.
This field provides the name or mnemonic for the device (or software) that is doing the counting, calculation, or aggregation, in effect creating a soft or virtual point.
In reality there are often going to be several intermediate systems between a given hard point and the data it generates' eventual destination in a control or energy information system. The purpose of this attribute is to offer a place to add information, if any of those intermediate systems are particularly useful to know about. For example, if a Shark electrical metering device accepts inputs from other metering devices (e.g., steam and water meters), and then forwards their pulse counts onward, it may be helpful to list the Shark device here as the aggregation point for the other devices - if that information is available.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_SystemID
|
If a point is associated with a more general system, such as a wind turbine, this attribute will indicate the name of that system, as recorded in a particular MD_Point_ControlSystem's database. This lets us group points by system, and also answer questions about systems - which may have multiple points associated with them.
Note that the value here may be an integer, alphanumeric string, or something similar uniquely identifying a system within a particular database or control system.
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_SystemName
|
If a point is associated with a more general system, such as a wind turbine, this attribute will indicate the name of that system, as recorded in a particular MD_Point_ControlSystem's database. This lets us group points by system, and also answer questions about systems - which may have multiple points associated with them.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_DeviceID
|
ID assigned by the control system to a given measurement device.
The value here is for the measurement device, not for, say, the system to which the device is attached (e.g., a wind turbine). The original device specifications may not be visible, though, in the control or information system from which point data is obtained here, so this field's default ('Unknown') may frequently appear. Also, if a point is virtual or "soft" this field may not be applicable, and so will again default to 'Unknown'.
The value here may be related to what's in MD_Point_ID, or not. MD_Point_ID contains a unique identifier in the control, information, or SCADA system. The actual ID of the device doing the measuring is different from the ID of the point, or it may not even be available.
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_DeviceName
|
Name of device taking measurements at a given point. Sometimes a device may be sold or OEM'd under different names. It is impossible to fully sort this sort of thing out. The value here is for the measurement device, not for, say, the system to which the device is attached (e.g., a wind turbine).
See also MD_Point_DeviceID, which has the unique identifier for the device, which may (like this field) be 'Unknown'. Often a given control, information, or SCADA system will allow operators to add a name for a measurement device, and if so, that name goes here.
The value here is for the measurement device, not for, say, the system to which the device is attached (e.g., a wind turbine). The original device specifications may not be visible, though, in the control or information system from which point data is obtained here, so this field's default ('Unknown') may frequently appear. Also, if a point is virtual or "soft" this field may not be applicable, and so will again default to 'Unknown'.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_DeviceModelNum
|
Model # of device taking measurements at a given point. For virtual points, which may aggregate multiple physical points, a value like 'Not Applicable' is most appropriate here.
The value here is for the measurement device, not for, say, the system to which the device is attached (e.g., a wind turbine). The original device specifications may not be visible, though, in the control or information system from which point data is obtained here, so this field's default ('Unknown') may frequently appear. Also, if a point is virtual or "soft" this field may not be applicable, and so will again default to 'Unknown'.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_DeviceVendor
|
Name of vendor whose device is giving us measurements for a given point. For virtual points, which may aggregate multiple physical points, a value like 'Not Applicable' is most appropriate here.
The value here is for the measurement device, not for, say, the system to which the device is attached (e.g., a wind turbine). The original device specifications may not be visible, though, in the control or information system from which point data is obtained here, so this field's default ('Unknown') may frequently appear. Also, if a point is virtual or "soft" this field may not be applicable, and so will again default to 'Unknown'.
The value here should correspond to the information tied to actual measurements recorded. Measurement tables all have foreign key relationships with MD_Vendor_SurrogateKey. This field is therefore, to some extent, redundant, but is provided here on the theory that it may be helpful to have vendor information together with information on the measurement device itself.
|
DW_CommonName
|
nvarchar(64)
|
64
|
|
MD_Point_DeviceProtocol
|
This should be a generic description of the protocol a given meter or other measurement device speaks, such as BACNet or something generic like 'Siemens Proprietary.'
The value here is for the measurement device, not for, say, the system to which the device is attached (e.g., a wind turbine). The original device specifications may not be visible, though, in the control or information system from which point data is obtained here, so this field's default ('Unknown') may frequently appear. Also, if a point is virtual or "soft" this field may not be applicable, and so will again default to 'Unknown'.
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_DeviceNetworkAddress
|
IP address (IPv4, IPv6) or other unique network address.
A device may be associated with multiple networks and have multiple addresses (per network, or overall). This field is intended only to record the primary address on the network that connects the device to its primary associated control or building automation system.
|
DW_CodeDescLong
|
varchar(64)
|
64
|
|
MD_Point_DeviceResolutionOrAccuracy
|
This attribute is going to be difficult to populate, since it is generally not going to be provided by the source system.
Still, if needed, and however incompletely it might be populated, it is here.
|
DW_CodeDesc
|
varchar(32)
|
32
|
|
MD_Point_AuditKey
|
|
DW_AuditKey
|
bigint
|
|
|
MD_Point_Timestamp
|
|
<None>
|
timestamp
|
|