Unit System Data and the Cleanbill Process

• UnitConversion
• Community
• Equivalence

The UnitConversion table contains the factors to convert between units of the same unit type (heat, volume, length, etc.).

The Community table lists all the valid commodity-noun-unit type-unit combinations, and identifies which are the common (for each commodity-noun-unit type grouping) and global (for usage only, across all commodities and unit types) unit rollup combinations (e.g. for natural gas-usage-heat, therm, dkthm, kbtu and mmbtu are valid units, but dkthm is the common unit; for natural gas-usage-volume, cf, ccf and mcf are valid units, but mcf is the common unit, ...).

The Equivalence table contains the factors to convert between unit types and units with the same commodity and noun (use, demand, etc.) - common rollups, and between unit types and units with different commodities and nouns - global rollup.

Specifically for CleanBill, the conversions are used in this way:

-- nativeUse, noun 26 = use, observation type NULL = normal use (not generation, transmission, etc.), credit 1 = positive value, credit 2 = negative value, credit 3 = informational value (not included in computation)

BodyLine.value * (abs(observationtype.credit - 2) * 2 - 1) * case when ((ObservationType.noun = 26) AND (ObservationType.creation IS NULL) AND (ObservationType.credit <> 3)) then 1 else null end AS nativeUse,

--nativeUnit

BodyLine.unitID * case when ((ObservationType.noun = 26) AND (ObservationType.creation IS NULL) AND (ObservationType.credit <> 3)) then 1 else null end AS nativeUnitID,

--commonUse

((((bodyline.value * U.multiplier) + U.offset) * e1.multiplier) + e1.offset) * (abs(observationtype.credit - 2) * 2 - 1) * case when ((ObservationType.noun = 26) AND (ObservationType.creation IS NULL) AND (ObservationType.credit <> 3)) then 1 else null end AS commonUse,

--commonUnit

C2.unitID * case when ((ObservationType.noun = 26) AND (ObservationType.creation IS NULL) AND (ObservationType.credit <> 3)) then 1 else null end AS commonUnitID,

--globaluse

((((((bodyline.value * U.multiplier) + U.offset) * e1.multiplier) + e1.offset) * e2.multiplier) + e2.offset) * (abs(observationtype.credit - 2) * 2 - 1) * case when ((ObservationType.noun = 26) AND (ObservationType.creation IS NULL) AND (ObservationType.credit <> 3)) then 1 else null end AS globalUse,

--globalUnit

C3.unitID * case when ((ObservationType.noun = 26) AND (ObservationType.creation IS NULL) AND (ObservationType.credit <> 3)) then 1 else null end AS globalUnitID,

--nativeDemand, noun 10 = actual demand, observation type NULL = normal use (not generation, transmission, etc.), credit 1 = positive value, credit 2 = negative value, credit 3 = informational value (not included in computation)

BodyLine.value * (abs(observationtype.credit - 2) * 2 - 1)* case when (ObservationType.noun = 10) AND (ObservationType.credit <> 3) then 1 else null end AS nativeDemand,

--nativeDemandUnit

BodyLine.unitID * case when (ObservationType.noun = 10) AND (ObservationType.credit <> 3) then 1 else null end AS nativeDemandUnitID,

--commonDemand

((((bodyline.value * U.multiplier) + U.offset) * e1.multiplier) + e1.offset) * (abs(observationtype.credit - 2) * 2 - 1)* case when (ObservationType.noun = 10) AND (ObservationType.credit <> 3) then 1 else null end AS commonDemand,

--commonDemandUnit

C2.unitID * case when (ObservationType.noun = 10) AND (ObservationType.credit <> 3) then 1 else null end AS commonDemandUnitID,

--nativeBillDemand

BodyLine.value * (abs(observationtype.credit - 2) * 2 - 1)* case when (ObservationType.noun = 2) AND (ObservationType.credit <> 3) then 1 else null end AS nativeBillDemand,

--nativeBillDemandUnit

BodyLine.unitID * case when (ObservationType.noun = 2) AND (ObservationType.credit <> 3) then 1 else null end AS nativeBillDemandUnitID,

--commonBillDemand

((((bodyline.value * U.multiplier) + U.offset) * e1.multiplier) + e1.offset) * (abs(observationtype.credit - 2) * 2 - 1)* case when (ObservationType.noun = 2) AND (ObservationType.credit <> 3) then 1 else null end AS commonBillDemand,

--commonBillDemandUnit

C2.unitID * case when (ObservationType.noun = 2) AND (ObservationType.credit <> 3) then 1 else null end AS commonBillDemandUnitID

Below is a sample portion of the Cleanbill script:

FROM
(bill
JOIN
bodyline ON bill.billid = bodyline.billid
LEFT JOIN
(meter
JOIN
logicaldevice on meter.logicaldeviceid = logicaldevice.logicaldeviceid) ON bodyline.meterid = meter.meterid
LEFT JOIN
unit ON bodyline.unitID = unit.unitid
LEFT JOIN
unittype ON unittype.unittypeid = unit.unittypeid
LEFT JOIN
observationtype ON bodyline.observationtypeid = observationtype.observationtypeid
LEFT JOIN
noun ON noun.nounid = observationtype.noun)
LEFT JOIN
community c1 ON c1.commodityid = bodyline.commodityid AND c1.nounid = noun.nounid AND c1.unittypeid = unittype.unittypeid
LEFT JOIN
community c2 ON c2.commodityid = bodyline.commodityid AND c2.nounid = noun.nounid AND c2.commondefault = 1
LEFT JOIN
community c3 ON c3.globaldefault = 1
LEFT JOIN
unitconversion U ON U.fromUnit = bodyline.unitid AND U.toUnit = c1.unitid
LEFT JOIN
equivalence e1 ON e1.fromCID = c1.communityID AND e1.toCID = c2.communityID
LEFT JOIN
equivalence e2 ON e2.fromCID = c2.communityID AND e2.toCID = c3.communityID