Dict-Physical

Table MF_ReadingByHourNoAggregations

Card of table MF_ReadingByHourNoAggregations

Name	MF_ReadingByHourNoAggregations
Dimensional Type	Fact
Comment	This dimension houses things like equipment orientation (also wind direction as a compass point, N, NW, etc.), operational status information, device condition or state identifiers (open vs. closed) - all things that can't be aggregated. The grain of this fact table is similar to that of MF_ReadingByHourAccumulating and MF_ReadingByHourSnapshot, except that it goes a level deeper, adding the value to the grain (that is, to the primary key). Each possible value is associated with a row in MD_UnaggregatableReading. The count of those readings (for each hour) is in MF_UnaggRead_Count. Things recorded here are not really continuous, numberic, aggregatable data even if they look that way. One example is wind direction. For things like wind direction, which can be measured numerically (in this case, in degrees), but can't be aggregated easily, at least not by most business intelligence software, one should aggregate the data into some convenient set of numeric values (e.g., 16 compass points 0, 22.5, 45, 67.5, 90, etc. degrees), then enter only the mode (most frequent value) here, in degrees. This is a kludge. Otherwise, though, we'd have to create dimensions for each measurement of this type - or a separate fact table. Such facts are only semi-aggregatable (ideally one uses the mode, not max, min, avg, etc.). This table will typically be used in conjunction with the MD_ThingMeasured and MD_UnitOfMeasurement dimensions. Similar materials should be joined, where possible, to identical units of measurement. If necessary, conversions should be done to achieve this. That way, if we want to aggregate measurements across, e.g., multiple locations, or multiple devices, we can be sure the units are compatible.

Check constraint name of the table MF_ReadingByHourNoAggregations

CKT_MF_READINGBYHOURNOAGGREGAT

List of outgoing references of the table MF_ReadingByHourNoAggregations

MeasurementToBuilding

MD_Building (Shortcut)

MD_Building_SurrogateKey

Relationship_93

MD_MeasurementQuality

MD_MQual_SurrogateKey

Relationship_94

MD_Room (Shortcut)

MD_Room_SurrogateKey

Relationship_95

MD_Time (Shortcut)

MD_Time_TimeInteger

Relationship_96

MD_Date (Shortcut)

MD_Date_DateInteger

Relationship_97

MD_UnitOfMeasurement

MD_Unit_SurrogateKey

Relationship_99

MD_ThingMeasured

MD_ThingM_SurrogateKey

Relationship_100

MD_Point

MD_Point_SurrogateKey

Relationship_104

MD_Vendor (Shortcut)

MD_Vend_SurrogateKey

Relationship_105

MD_Source

MD_Source_SurrogateKey

Relationship_239

MD_UnaggregatableReading

MD_UnaggRead_SurrogateKey

List of diagrams containing the table MF_ReadingByHourNoAggregations

StarsEnergyDiagram

List of columns of the table MF_ReadingByHourNoAggregations

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_UnaggRead_SurrogateKey

<None>

numeric

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_UnaggRead_Coverage

Number of seconds in a given hour that we have data coverage for. Will typically be 3600 (i.e., the whole hour). This is also the default.

<None>

int

MF_UnaggRead_Count

Count of measurements, if available, that lie behind a given value. Defaults to 1.

Rows in this table are linked to a reading, so the counts here should normally be sliced by reading (e.g., reading X occurred Y times during period Z). Without the reading text, the count is not very useful.

<None>

int

MF_UnaggRead_AuditKey

DW_AuditKey

bigint

MF_UnaggRead_Timestamp

<None>

timestamp

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

<None>

int

MD_UnaggRead_SurrogateKey

<None>

numeric

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_UnaggRead_Coverage

Number of seconds in a given hour that we have data coverage for. Will typically be 3600 (i.e., the whole hour). This is also the default.

<None>

int

MF_UnaggRead_Count

<None>

int

MF_UnaggRead_AuditKey

DW_AuditKey

bigint