Dict-Physical

Table MF_ReadingByHourSnapshot

Card of table MF_ReadingByHourSnapshot

Name	MF_ReadingByHourSnapshot
Dimensional Type	Fact
Comment	This fact table holds aggregated measurement information for water, electricity, steam, natural gas, etc. usage, i.e., measurements taken over a period of time (SPECIFICALLY AN HOUR) by a given control/measurement system and aggregated (e.g., averaged, given as mix, max, etc.). Since the data here is already averaged, this table is only semi-aggregatable. That is, one can apply AVG, MIN, MAX, etc. functions to the values here, but not SUM. This table functions, therefore, like a periodic (hourly) snapshot. An analogue in commercial data warehousing would be inventory levels in a warehouse. You can't sum these, either. You take their averages over time mostly instead. Everything here is allocated down, or aggregated up, to the hourly level, so that everything is comparable, at least along the time dimension(s). Hours - the basic unit of temporal analysis here - must begin at minute 0 and second 0 and end at minute 59, second 59. Hence there is no need to store end dates/times, since all end dates will be the same as start dates, and all end times will equal the start time + 59 minutes + 59 seconds. If the resolution of the original measurement is below the second, then rounding will occur (e.g., 11:59:59.501 [11:59 and 59 seconds and 501 milliseconds] will be rounded to 12:00, if this is possible) . Links to MD_Time are only to rows where MD_Time_Second = 0. All measurement information must be representable as a floating point number (long float). Most nominal variables cannot be represented here (and in general, require special treatment in data warehouses anyway). Even nominal yes/no variables, which could be represented as integers (e.g., 1 = Yes, 0 = No), can't be used there. They should be placed instead in MF_ReadingByHourNoAggregation. Wind direction would be a typical example of this, and also operational status variables (equipment state or condition numbers). This fact table is unlike MF_ReadingByHourNoAggregation in the sense that MF_ReadingByHourNoAggregation records rates or measurements that can't be aggregated (easily), like status integers. It also differs from MF_ReadingByHourAccumulating in that values in this latter table should NOT be aggregated using SUM(), as noted above. Date and time values here are set according to local time (US Central Time).

Check constraint name of the table MF_ReadingByHourSnapshot

CKT_MF_READINGBYHOURSNAPSHOT

List of outgoing references of the table MF_ReadingByHourSnapshot

Relationship_51

MD_MeasurementQuality

MD_MQual_SurrogateKey

Relationship_107

MD_Date (Shortcut)

MD_Date_DateInteger

Relationship_108

MD_Building (Shortcut)

MD_Building_SurrogateKey

Relationship_109

MD_Room (Shortcut)

MD_Room_SurrogateKey

Relationship_110

MD_Point

MD_Point_SurrogateKey

Relationship_111

MD_UnitOfMeasurement

MD_Unit_SurrogateKey

Relationship_112

MD_ThingMeasured

MD_ThingM_SurrogateKey

Relationship_113

MD_Source

MD_Source_SurrogateKey

Relationship_114

MD_Vendor (Shortcut)

MD_Vend_SurrogateKey

Relationship_199

MD_Time (Shortcut)

MD_Time_TimeInteger

List of diagrams containing the table MF_ReadingByHourSnapshot

StarsEnergyDiagram

List of columns of the table MF_ReadingByHourSnapshot

MD_Point_SurrogateKey

<None>

numeric

MD_Date_DateInteger

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

MD_Time_TimeInteger

Integer representing the time of day in the format HHMMSS (e.g., 23:01:01 at one minute one second after eleven o'clock in the evening). Not typically used for reporting.

Unlike MD_Date_Dateinteger (in MD_Date), this integer is not really human-readable, because it's not set up for base-10. Rather, it's calculated using the formula: (hour << 16) Or (min << 8) Or (sec), where the Or's are logical, and the << operator is a binary left shift.

<None>

int

MD_Unit_SurrogateKey

<None>

numeric

MD_ThingM_SurrogateKey

<None>

numeric

MD_Source_SurrogateKey

<None>

numeric

MD_Vend_SurrogateKey

Foreign key linking to a row in MD_Vendor, providing information on the vendor whose device was used to take a given measurement.

<None>

numeric

MD_Room_SurrogateKey

<None>

numeric

MD_Building_SurrogateKey

<None>

numeric

MD_MQual_SurrogateKey

Many systems associate quality ratings with measurements, and this foreign key allows that relationship to be implemented here. Supported qualities include: Good, Bad, Uncertain.

<None>

numeric

MF_ReadHourSnap_RealGrain

The real or original temporal granularity of measurement, before it was refactored (allocated/pro-rated) for this table.

For example, a utility bill may be issued for a billing period of 30 days (month). But for the purposes of this table, we may allocated that total bill down to a lower level, so as to be compatible with other readings.

This attribute gives the (approximate) original measurement time-grain.

DW_TimeUnit

varchar(16)

MF_ReadHourSnap_AvgValue

This is the basic 'value' field for MF_ReadingsByHourSnapshot.

Values in this field are averaged (or split up, resulting from downwards allocation of values collected at a grain larger/higher than per hour).

<None>

double precision

MF_ReadHourSnap_MaxValue

If a measurement gets taken over a period of time, the measurement device may (in addition to an average or cumulative value) provide a max/min value. The max is provided here, if available. If it is not available, then this value defaults to MD_ReadHourSnap_AvgValue.

<None>

double precision

MF_ReadHourSnap_MinValue

If a measurement gets taken over a period of time, the measurement device may (in addition to an average or cumulative value) provide a max/min value. The min is provided here, if available. If it is not available, then this value defaults to MD_ReadHourSnap_AvgValue.

<None>

double precision

MF_ReadHourSnap_Variance

Some systems provide cumulative readings by aggregating individual readings at a lower level by taking their average (or min, max, etc.). In such cases, the system may also provide a reliability value in the form of a variance standard deviation, which measures, statistically, the amount of jitter in the data. Standard deviations, if that's all that are available, will be squared and thereby converted to variances, so they can be aggregated (summed) then divided by the number of samples and converted back to a standard deviation by taking their square root.

This value may not be available, and if so will default to zero.

See also the foreign key MD_MQual_SurrogateKey.

<None>

double precision

MF_ReadHourSnap_Coverage

Number of seconds covered for a given data point. Since the grain of this table is per hour, and the unit here is seconds, this defaults to 3600.

<None>

int

MF_ReadHourSnap_InputBy

DW_CommonName

nvarchar(64)

MF_ReadHourSnap_AuditKey

DW_AuditKey

bigint