Overview: Unit System

The Unit System Editor provides a table of equivalencies that enables EnergyCAP to convert between various energy types, units and commodities. By relating and harmonizing utility billing data from diverse sources, and reporting on that data using user-selected energy 'Roll-Up units' such as MMBTUs, EnergyCAP is able to provide meaningful energy information across the enterprise.

WATCH VIDEO on the Unit System (Basic Session)

WATCH VIDEO on the Unit System (Advanced Session)

The Unit System Editor interface enables the user to define the components needed to store utility billing data in EnergyCAP Enterprise. These components may also be the building blocks for other functions. Components that may be created, defined and incorporated in the Unit System Editor include:

• Commodities: Goods, services, or entities that are being monitored by EnergyCAP Enterprise. Examples include electricity and natural gas, but can also include less traditional categories like wood or weather.

• Types: Defines a type of observation monitored for a commodity and provides the context for that data’s usage. Example types include use, percentage and demand, but may also include taxes and cost.

• Units: Define the units of measure for all commodities. You can also define conversions between units of like types (e.g., converting cubic feet to gallons) by supplying the equation to use for the conversion.

• Equivalencies: Define relationships between commodities, types, and units.

All numeric data in EnergyCAP Enterprise must be assigned a Commodity, Type, and Unit. These three categories combine to form a physical definition for any piece of numeric data. Categories are further defined and related to each other using additional qualifications and groupings including:

• Commodities: Common Usage Roll-Up, Global Usage Roll-Up
• Types: Rule, Class, Origin, and Applied
• Units: Unit Type
• Equivalencies: Default Unit, Class, Unit Type

Accessing the Unit System Editor

• From the Tools menu in any module or view, select Unit System. The Unit System Editor window appears.

Unit System Q&A

What is a Common Usage Roll-Up?
ANSWER: The Common Usage Roll-Up is the default unit of measure that will be used for a commodity on reports and PowerViews when different vendors use different units for that same commodity (example: some vendors report natural gas in MCF and others report it in DKTHM). In EnergyCAP, the user can determine and/or change the Common Usage Roll-Up

What is a Global Usage Roll-Up?
ANSWER: The Global Usage Roll-Up is the default unit of measure for reports combining unlike commodities. It is the 'highest' level of energy conversion in EnergyCAP. The default unit is MMBTUs. It is generally not advisable to change this default setting, although it is possible.

How do I change the Common or Global Usage Roll-up?
ANSWER: View the topic on Changing the Common Unit Roll-Up to see the process. Changing the Global Usage Roll-Up is identical to changing the Common Unit Roll-Up except that the commodity of ENERGY is selected and the Global Roll-Up checkbox is checked (Equivalencies window, General tab).

How do Unit System decisions affect EnergyCAP reports?
ANSWER: Changes to the Common Unit Roll-Up will be reflected on most reports using that commodity. Some bill-by-bill reports may show 'native use' units, while other reports that include multiple and diverse commodities may display the Global Roll-Up unit (usually MMBTUs). ! In order for these changes to be applied to historical/existing billing data, the SQL statement at the end of the process for changing the Common Unit Roll-Up MUST be executed. !

How do Unit System decisions affect EnergyCAP bill templates?
ANSWER: Changing the Unit System values will not directly affect bill entry templates. However, historical bills created with a template for a commodity which has been changed in the Unit System will need to have the cleanbill script run (see Changing the Common Unit Roll-Up topic).

How do I set up a new commodity?
ANSWER: See Working with Commodities.

How do I create an observation type?
ANSWER: See Working with Observation Types.

How do I create or modify Equivalencies?
ANSWER: See Working with Equivalencies.

What if different vendors report the same commodity in different units?
ANSWER: The Common Roll-Up Unit will be used for reports involving multiple units for that commodity.

How do changes to the Unit System affect my historical bills?
ANSWER: See Changing the Common Unit Roll-Up topic for information regarding historical bills. Because of the way that EnergyCAP stores billing data in multiple database tables, a special script process is required to ensure that Unit System changes are applied to historical bills!

How do I create new units of measure?
ANSWER: Open the Unit System Editor and click the Units tab. Then click the Plus button under the Commodities List Box to open the Create Unit window. Follow the prompts to complete setup of the new Unit. Then select the Unit from the Commodities List Box and click the Plus button underneath the Convert To: list button. The Create Conversion window will open. Select the desired unit to convert to, and supply the conversion formulas in the fields indicated. Then click OK to save the conversion.

The Unit System Editor provides an interface for managing commodities, observation types, units of measure, and equivalencies in the EnergyCAP application.

Default Commodities, Types, Units and Equivalencies cannot be deleted. The user may only modify their Display names, NOT their Codes which are fixed. 

User-created Commodities, Types, Units and Equivalencies can be deleted. The user may modify their Display names AND their Codes.

1. From the Tools menu in any module or view, select Unit System. The Unit System Editor window appears.

   • Select the Commodities tab to manage commodities. Commodities are the energy resources supplied by utilities (e.g. natural gas, electricity, coal, etc.).

   • Select the Types tab to manage observation types. Observation types are used to describe individual line items from the utility bill (e.g. billed demand, fee, tax, use, etc.)

   • Select the Units tab to manage unit of measures. Units refer to the unit of measure associated with the commodity (e.g., kWh, gallons, cu yd., etc.)

   • Select the Equivalencies tab to manage unit equivalencies. Equivalencies relate the various units of measure that share the same Class.

2. Exit the Unit System Editor by clicking Close.

*Cleanbill is a script which reprocesses a defined set of bill data, marking the set as 'dirty' and then repopulating relevant database tables based on the newest unit conversion settings. This process updates values for native usage units, common usage units, and rollup usage units.

The Unit System Editor Commodities tab lists commodities by their code and display. Lists may be sorted by column heading simply by clicking on the heading. To reverse the sort, click the column heading once more.

NOTE: Default commodities, types, unit, and equivalency displays provided by EnergyCAP can only be edited, not deleted. User-created commodities, types, units, or equivalencies can be edited AND deleted.

1. From the Tools menu in any module or view, select Unit System. The Unit System Editor window appears.

2. Select the Commodities tab to manage commodities.

• Add a commodity by clicking . The Create Commodity window appears.

1. In the Code field, enter the unique, self-defined identifier.

2. In the Display field, enter the name to appear throughout the system.

3. Click OK. The Create Commodity window closes.
   
   NOTE: After a Commodity has been added, it is necessary to relate the commodity to other commodities (see Working with Observation Types and Working with Equivalencies), in order to produce accurate EnergyCAP usage reports involvng multiple meters and commodities.

• Modify a commodity by selecting the appropriate commodity then clicking . The Commodity Properties window appears.

1. Change the code by clearing the Code field then entering the new code. Note that the code for default commodities cannot be edited. Only user-created commodity codes can be edited.

2. Change the display by clearing the Display field then entering the new display.

3. Click OK. The Commodity Properties window closes.

• Delete a commodity by selecting the appropriate commodity then clicking . 
  Note that default commodities cannot be deleted. 
  The Confirm window appears.

  • Click Yes to delete the commodity.

  • Click No to cancel the operation.

1. After making your selections, exit the Unit System Editor by clicking Close.

*Cleanbill is a script which reprocesses a defined set of bill data that is marked as 'dirty' and then repopulates relevant database tables based on the newest unit conversion settings. This process updates values for common usage units and global usage units.

The Unit System Editor Types tab lists unit types by their code and display. You can sort the lists by clicking a column heading. Sort the lists in reverse order by clicking the column heading once more.

For default Types, it is only possible to edit the Display name. For user-created Types, any of the fields can be edited.

1. From the Tools menu in any module or view, select Unit System. The Unit System Editor window appears.

2. Select the Types tab to manage observation types.

• Add a type by clicking . The Create Type window appears.

1. In the Code field, enter the unique, self-defined identifier.

2. In the Display field, enter the name to appear throughout the system.

3. Select the Class from the drop-down list. In EnergyCAP, the Class indicates what sort of observation is being made. It answers the question: "What is being tracked?" The number of Classes is limited and fixed.

4. Select the time of use from the drop-down list (applicable for time-of-use commodities).

5. Select the origin from the drop-down list. The number of Origins is limited and fixed.

6. Select the applied status from the drop-down list. There are only three statuses: Credit/Provided, Debit/Consumed and Ignore.

7. Click OK. The Create Type window closes.

• Modify a type by selecting the appropriate type then clicking . The Type Properties window appears. Note that default Type codes and other properties cannot be modified. Only the Display for a default Type can be changed. User-created Types are fully editable.

1. Change the Code by clearing the Code field then entering the new code.

2. Change the Display by clearing the Display field then entering the new display.

3. Change the Class by selecting the new class from the drop-down list. In EnergyCAP, the Class indicates what sort of observation is being made. It answers the question: "What is being tracked?" The number of Classes is limited and fixed.

4. Change the Time of Use (TOU) by selecting the new time from the drop-down list. TOU values apply primarily to certain measurements of electricity. Note that the number of TOU options in EnergyCAP is limited and fixed.

5. Change the Origin by selecting the new origin from the drop-down list. The Origin option answers the question: "How is this commodity type being provided?" Note that the number of Origin options in EnergyCAP is limited and fixed.

6. Change the Applied status by selecting the new applied status from the drop-down list. Note that the number of Applied status options in EnergyCAP is limited and fixed. Credit/Provided would refer to a production-related Unit Type. Debit/Consumed would refer to most traditional utility billing scenarios. Ignore would indicate that production or consumption does not relate to this unit Type.

7. Click OK. The Type Properties window closes.

• Delete a type by selecting the appropriate type then clicking . Note that default Types cannot be deleted. Only user-created Types can be deleted. The Confirm window appears.

  • Click Yes to delete the type.

  • Click No to cancel the operation.

3. After making your selections, exit the Unit System Editor by clicking Close.

*Cleanbill is a script which reprocesses a defined set of bill data, marking the set as 'dirty' and then repopulating relevant database tables based on the newest unit conversion settings. This process updates values for native usage units, common usage units, and rollup usage units.

The Unit System Editor Units tab lists units of measure by their code and display. You can sort the lists by clicking a column heading. Sort the lists in reverse order by clicking the column heading once more. Selecting a unit displays its associated conversion factors in the Convert To pane.

1. From the Tools menu in any module or view, select Unit System. The Unit System Editor window appears.

2. Select the Units tab to manage unit of measures.

• Add a unit of measure by clicking the  icon below the Units list. The Create Unit window appears.

  1. In the Code field, enter the unique, self-defined identifier.

  2. In the Display field, enter the name to appear throughout the system.

  3. Select the Unit Type from the drop-down list. Note that Unit Types for default units cannot be changed. The "Type" categorization answers the question: "What is the specific property of the Commodity that is being measured by the unit?" Only user-created Unit Types are fully editable.

  1. Click OK. The Create Unit window closes.

• Modify a unit of measure by selecting the applicable unit of measure then clicking the  icon below the Units list. The Unit Properties window appears.

  1. Change the code by clearing the Code field then entering the new code. Note that Codes for default units cannot be changed. Only user-created Unit Types are fully editable.

  2. Change the display by placing the cursor in the Display field then updating the display information using the computer keyboard.

  3. Change the unit type by selecting the new unit type from the drop-down list. Note that Unit Types for default units cannot be changed. The "Type" categorization answers the question: "What is the specific property of the Commodity that is being measured by the unit?" Only user-created Unit Types are fully editable.

  4. Click OK. The Unit Properties window closes.

• Delete a unit of measure by selecting the applicable unit of measure then clicking the  icon below the Units list. Note that only user-created Units can be deleted. 
  The Confirm window appears.

  • Click Yes to delete the unit of measure.

  • Click No to cancel the operation.

• Add a conversion factor to a unit of measure by selecting the applicable unit from the drop-down list and then, in the Convert To section, clicking . The Create Conversion window appears. Insert the conversion multiplier in the Multiply ... field. If necessary add the constant in the field provided. Then click OK to save the new conversion.

• Modify a conversion factor for a unit of measure by selecting the applicable unit of measure and conversion factor from the drop-down list and then, in the Convert to section, clicking . The Conversion Properties window appears. Update the conversion multiplier in the Multiply ... field. If necessary modify the constant in the field provided. Then click OK to save the modified conversion.

• Delete a conversion factor for a unit of measure by selecting the applicable unit of measure and conversion factor then in the Convert to field click . The Confirm window appears.

  • Click Yes to delete the conversion factor.

  • Click No to cancel the operation.

• After making your selections, exit the Unit System Editor by clicking Close.

*Cleanbill is a script which reprocesses a defined set of bill data, marking the set as 'dirty' and then repopulating relevant database tables based on the newest unit conversion settings. This process updates values for native usage units, common usage units, and rollup usage units.

The Unit System Editor Equivalencies tab lists equivalencies by commodity, class, unit type, and default value. You can sort the lists by clicking a column heading. Sort the lists in reverse order by clicking the column heading once more.

Please note that default commodities, types, units, and equivalencies cannont be deleted from the Unit System. However, you CAN edit or delete any commodities, types, units, or equivalencies you have created. Also, default DISPLAY names CAN be edited, but the default CODES cannot be edited.

1. From the Tools menu in any module or view, select Unit System. The Unit System Editor window appears.

2. Select the Equivalencies tab to manage unit equivalencies.

• Add an equivalency by clicking . The Create Equivalence window appears.

1. On the General tab, complete the following:

   1. Select a commodity from the Commodity drop-down list.

   2. Select a class from the Class drop-down list. See Working with Observation Types for more information.

   3. Select a unit type from the Unit Type drop-down list. See Working with Units for more information.

   4. If this is the default, select the Default check box.

   5. In the Types section, click the checkbox to select all types that apply to this equivalency.

   6. In the Units section, click the checkbox to select all units that apply to this equivalency.

   7. To assign a default unit, select the unit name, then click Make default button. An asterisk [*] appears next to the default unit.

2. On the Conversion tab, complete the following:

   1. Add a conversion factor to a unit of measure by selecting the applicable unit then in the Convert To section click . The Create Conversion window appears. Follow the instructions for Creating Conversion Factors

   2. Modify a conversion factor for a unit of measure by selecting the applicable unit of measure and conversion factor then in the Convert to section click . The Conversion Properties window appears. Follow the instructions for Modifying Conversion Factors

   3. Delete a conversion factor for a unit of measure by selecting the applicable unit of measure and conversion factor then in the Convert to field click . The Confirm window appears.

      • Click Yes to delete the conversion factor.

      • Click No to cancel the operation.

3. Click OK. The Create Equivalence window closes.

• Modify an equivalency by selecting the applicable equivalency then clicking . The Equivalence Properties window appears.

1. On the General tab, modify any of the following:

   1. Select a commodity from the Commodity drop-down list.

   2. Select a class from the Class drop-down list. See Working with Observation Types for more information.

   3. Select a unit type from the Unit Type drop-down list. See Working with Units for more information.

   4. Select or clear the Default check box.

   5. In the Types section, click the checkbox to select all types that apply to this equivalency.

   6. In the Units section, click the checkbox to select all units that apply to this equivalency.

   7. To assign a default unit, select the unit name, then click Make default button. An asterisk [*] appears next to the default unit.

2. On the Conversion tab, complete the following:

   1. Add a conversion factor to a unit of measure by selecting the applicable unit then in the Convert To section click . The Create Conversion window appears. Follow the instructions for Creating Conversion Factors.

   2. Modify a conversion factor for a unit of measure by selecting the applicable unit of measure and conversion factor then in the Convert to section click . The Conversion Properties window appears. Follow the instructions for Modifying Conversion Factors.

   3. Delete a conversion factor for a unit of measure by selecting the applicable unit of measure and conversion factor then in the Convert to field click . The Confirm window appears.

      • Click Yes to delete the conversion factor.

      • Click No to cancel the operation.

3. Click OK. The Equivalence Properties window closes.

• Delete an equivalency by selecting the appropriate equivalency then clicking . The Confirm window appears.

  • Click Yes to delete the equivalency

  • Click No to cancel the operation.

1. After making your selections, exit the Unit System Editor by clicking Close.

*Cleanbill is a script which reprocesses a defined set of bill data, marking the set as 'dirty' and then repopulating relevant database tables based on the newest unit conversion settings. This process updates values for native usage units, common usage units, and rollup usage units.

This example provides the process for changing the Common Unit Roll-Up  for Propane from the default of ‘lb’ to ‘GAL’. Common Unit Roll-Up is the generally reported unit of measure for all meters sharing the same commodity. Common Units are used by EnergyCAP whenever multiple vendors report commodity use in different units. Using the common unit for most reports in EnergyCAP ensures an 'apples-to-apples' comparison of use, cost, and unit cost when reporting on commodities across the enterprise. The exceptions to this rule would include cross-commodity reports that would combine diverse commodities using the Global Roll-Up Unit, and bill-by-bill reports which might report the 'native unit' determined by the bill entry template.

Changing the Common Unit Roll-Up

1. In EnergyCAP, select the Unit System… option from the Tools menu. 
   
    
2. Confirm the Common Usage Roll-up Unit for Propane (the default EnergyCAP setting is ‘lb’) by clicking the Commodities tab and scrolling to the Propane commodity. The Common Usage and Global Usage Roll-Up settings are indicated in adjacent columns in the Commodity table. 
   
   
3. Click on the Equivalencies tab and scroll down to see if an Equivalency exists for Propane with a Default Unit of “Gal”. 
   
   
   If not, click on the “+” button in the lower left corner of the window to add the new Commodity 'on the fly'. The Create Equivalence window will open.

Creating the 'New' Commodity (if not present)

1. On the General tab of the Create Equivalency window, select the Commodity, Class, and Unit Type from the available drop-down menus. Also, check the Common Role-Up checkbox to make this combination of Commodity and Class the Common Roll-Up. 
   
   
2. In the Relationships area, select Types and Units that can be made equivalent. Typically, the only Type selected is “Use” and most of the available Units are selected. 
   
   
3. Click on the desired Common Unit Roll-Up unit (“Gal” in this example). 
   
   
   This will activate the Make Default button.
4. Click the Make Default button. 
   
   
   NOTE: When the Unit has been made the Default, an asterisk (*) will appear to the right of it.The General tab should now look like the one below.
   
   
5. Click on the Conversions tab. Then click the “+” button to create a new equivalence.
   
   
6. Select Energy Usage Heat [MMBtu] in the Convert TO: drop-down menu. Enter 0.09160 for the multiplication factor (based on 91,600 Btu/Gal). 
   
   
7. Click OK to save this Conversion.
8. Click OK to save this Equivalence.The Equivalencies list will refresh in the Unit System Editor.

Verifying the new Common Roll-Up Unit

1. Click on the Commodities tab to confirm that the changes are reflected there--first in the Equivalencies tab ... 
   
   
   
   And then in the Commodities tab ...
   
   
2. Click Close to close the Unit System Editor.

IMPORTANT NOTE: Unit System changes will impact any bills entered AFTER the date of the change. 
! Existing historical bills in EnergyCAP will need to be updated to reflect the change. ALWAYS BACK UP YOUR DATABASE PRIOR TO MAKING UNIT SYSTEM CHANGES. ! 

Below is a sample SQL Script that could be run from either SQL Server Query Analyzer, or via the EnergyCAP Administrator Program to update the historical bills with the new roll-up information:

UPDATE BILL

SET bill.dirty = 1

WHERE billID in (SELECT billID from billaccountmeter

WHERE billaccountmeter.commodityID = 10)

GO

--

EXEC cleanbill

This topic provides a procedure for changing the EnergyCAP global unit from the default MMBtu to another unit (Giga-Joules or equivalent kWh ).

This is most helpful for non-US EnergyCAP users who  need to track Global Usage Roll-Up in units of Giga-Joules (GJ) or equivalent kWh (EKWH).  To complete the procedure successfully, the user will need:

• Conversion factors to convert from one Unit within a Type to another Unit within the same Type.

EXAMPLE: For the ‘Energy’ Type, to convert EKWH to J, multiply EKWH by 3600000 and then add a constant of 0. To convert J to EKWH, divide J by 3600000 and then add a constant of 0.

• Conversion factors to convert from each Unit/Type to the new Global Unit/Type.  Specifically, a conversion factor will be needed between each Common Usage Roll-Up Unit and the new Global Usage Roll-Up Unit for each commodity being tracked by the user.

EXAMPLE 1: To convert Electric, in kWh, to Energy, in GJ, values in kWh must be multiplied by a factor of 0.00360, adding a constant of 0.

EXAMPLE 2: To convert Natural Gas, in Therms, to Energy, in GJ, values in Therms must be multiplied by a factor of 0.105587, adding a constant of 0.

NOTE: If any bills exist in EnergyCAP prior to changing the Global Usage Roll-Up units, an SQL Script (see Running Cleanbill for Historical Bills, below) will need to be applied to the EnergyCAP database to update those bills.

To change the Global Unit in EnergyCAP, follow the procedure below. 
! It is recommended that a backup of the database be performed prior to making this global change. !

1. From the EnergyCAP main menu, click Tools> Unit System (Commodities tab). Note that the indicated default Global Usage Roll-Up unit is MMBtu.
   
2. Click on the Units tab and scroll through the list of available Units to be sure that the desired Global Usage Roll-Up unit currently exists in EnergyCAP. For example, GJ may be in the list, but EKWH is not. If the Global Usage Roll-Up unit DOES exist, skip to Step #8.
3. If the desired Global Usage Roll-Up Unit DOES NOT currently exist, click on the ‘+’ button to add it. The Create Unit window will open.
   
4. Enter the Code, Display and Unit Type information needed to create the Unit, then click the OK button to save the new  unit.
5. Select/highlight the new Global Usage Rollup unit from the Units tab and locate the Convert To pane in the top right corner of the Units tab display. Then click the associated ‘+’ button. The Create Conversion window will open.
   
6. To add a new conversion for the new Global Usage Rollup unit, select the conversion energy type from the dropdown list. Then input the correct conversion factor formula (use multiplier and/or add an optional constant).  You will need to know, or look up, what the conversion factors are.
7. Repeat the previous step to add conversions for all units of the same unit Type.
8. Click on the Equivalencies tab and scroll down to confirm that there is NOT a record for Commodity = Energy, Class = Usage, Unit Type = Energy, and Default Unit = GJ or EKWH.
   
   If there IS such a record, select it, open the Properties for it and make it the Common Roll-Up and Global Roll-Up by clicking on the appropriate checkboxes. Then skip to Step #14 below.
   
9. If a record does not exist, click on the ‘+’ button to Add a new Equivalence for either GJ or EKWH. Select the Commodity, Class, and Unit Type. Also, click the Common Roll-Up checkbox from the General tab to make this combination a Common Roll-Up.
   
10. Also, click the checkbox from the General tab to make this combination the Global Roll-Up.
11. In the Relationships area, select Types and Units that can be made equivalent. Typically, the only Type selected is Use,  and most of the available Units are selected.
    
12. Click on the desired Unit (either GJ or EKWH) to activate the ‘Make Default’ button active. Then click the Make Default button.Once you have clicked on the Make Default Button, an asterisk appears to the right of the selected Unit to indicate that it is the default:
    
13. Click the OK button to save this Equivalence. A message box will appear to warn you that changing the Global Default may break required equivalency conversions.
    
    Click the OK button to acknowledge the warning.
14. From the Equivalencies tab, edit the properties of all records with an asterisk in the ‘Common Usage Roll-Up’ column. For each record, create a new Conversion to go from the default unit to the new Global Usage Default Roll-Up Unit. The example below shows the conversion from Electric, in kWh, to Energy, in GJ.
    
    
    
    Once these Equivalence Conversions have been entered, any bills entered into EnergyCAP will have their consumption converted to the Global Roll-Up Unit equivalence.
15. Click on the Close button to close the Unit System Editor window.
16. To perform a quick check to be sure that all commodities that you track have an Equivalence Conversion to the new Global Roll-Up Unit, open the Unit System Editor by selecting Unit System... from the Tools menu. The Commodities tab will show which commodities have a Global Usage Roll-Up and it will indicate what the unit is.
    

Running Cleanbill for Historical Bills

If there are bills in EnergyCAP that were entered prior to the Global Roll-Up Unit change, the related bill tables will NOT be automatically updated to reflect the change. Instead, the SQL Script below will need to be run on the EnergyCAP database, either using SQL Query Analyzer or the EnergyCAP Administrator program.

UPDATE BILL

SET bill.dirty = 1

GO

--

EXEC cleanbill

Adding the Commodity

Adding a commodity involves changes to the Unit System in EnergyCAP. ! Great care must be taken when relating a new commodity to existing units of measure, in order to avoid errors that could potentially skew reporting on energy data! Make certain to devise and execute a quality process to verify unit system changes. Such a process could include running reports on bills using the new commodity, and comparing measurement data from multiple commodity types (and including the new commodity) in PowerView rollups for multiple places/accounts.

There are several reasons why it would be advantageous to add a new commodity. The most common is that the available commodity list does not adequately mirror the accounting structure of an organization. It may be desirable to separate out certain aspects of a commodity. For example, a "Trash" meter may not be adequate when separate reporting and/or accounting for Recycling fees is desired. Perhaps there is a need to associate more than one GL code with the Trash meter. To do this most efficiently, it may be appropriate to 'break down' a single commodity into a group of related commodities.

EXAMPLE: Your organization has six Trash meters. After setting up the new commodity, you use the Account Wizard to create six more Recycling meters, each linked to the same place and account as its counterpart Trash meter, but using the new commodity with a different GL code for your accounting purposes. 


During bill entry, the recyling charges are associated with the Recycling meters and the Trash charges are associated with the Trash meters.

Procedure:

1. Click Tools > Unit System. The Unit System Editor window will open.
   
   
2. Click the Commodities tab.
3. Click the Plus  icon to open the Create Commodity window.
4. Enter the new commodity Code and Display name.
5. Click OK to save the new Commodity

Defining the Commodity

Once the Commodity has been created, it must be associated with other Commodities, appropriate units of measure, and (where necessary) conversion factors to common units of measure used in EnergyCAP reports and PowerViews.

To define the new commodity:

1. Click Tools > Unit System.
2. Click on the Equivalencies tab.
3. Click the Plus button to open the Create Equivalence window.
4. Click the General tab and select the new commodity name from the Commodity drop-down list.
5. Select an appropriate Class from the drop-down list to define the new Commodity. Class should ordinarily be Usage unless this is a special-purpose entry.
6. Select an appropriate Unit Type from the drop-down list to define the new Commodity. Unit type will most often be energy, heat or volume. A Unit type of Counter can be used for information-only entries such as meter readings.
7. In the Relationships list box, click to check the Obervation Types that are valid for bill entry templates involving the selected Commodity.
8. In the Units list box, click to check the units of measure that are valid for the new Commodity. In some cases, it may be desirable to create a unique unit of measure to track the new commodity.
9. If multiple units of measure have been selected, it will be necessary to designate one as the default UOM. Click on the name of the unit to highlight it. Then click the Make Default button.

Setting up Conversions

Conversions are important when it is necessary to relate the commodity’s unit of measure from one Class to another Class (i.e. Therms of Natural Gas to CCF of Natural Gas is a translation of Class Heat to Class Volume.) Also, Conversions are useful to relate units of one commodity to another through the global rollup of commodities to Energy measured by MMBTU. There are other reasons to use Conversions, but these two are the most common. If reports are not turning out as expected, there is a chance the conversions have been set up incorrectly. Or it may be necessary to ADD a conversion in order for EnergyCAP to properly report on the new commodity’s data.

1. The Global Default is Energy-Usage-Heat in units of MMBTU (millions of BTU). If it is necessary for the new commodity’s usage to ‘roll-up’ to units of MMBTU when included in summary reports, a conversion factor must be added!
2. To enter the conversion factor, click the Conversions tab from the Create Equivalence window.
3. Then click the Plus button to add the new conversion. The Create Conversion window will open.
4. Select Energy-Usage-Heat from the Convert TO: drop-down list. Then input the multiplier to convert from the new commodity’s ‘native’ units of measure to MMBTU. 
   OPTIONAL: In the Add this constant: window, enter the value that should be added to the product of commodity and multiplier to complete the conversion.
5. Click OK to save and close.

The new commodity will be listed in the Template Manager (Accounting>Templates). Create the new template to match the invoice received from the vendor. If difficulties are encountered, it may be necessary to revisit the Unit System Editor and verify the units of measure and Classes associated with the commodity. Contact EnergyCAP, Inc. for additional assistance.

Validating Commodity Setup

To ensure that the Commodity has been set up correctly:

1. Run the SU11 Commodity List report to verify the presence of the new commodity.
2. Set up a new account/meter for the new commodity using the Account Wizard. Then input and save one bill for the account with the new commodity.
3. Use the BL01A report to verify the correct GL assignment for the new commodity meter.
4. From the Account Manager, click the Commodity tab for the Account with the new commodity and verify that the new commodity is displaying the correct charts and values. If applicable, select the Cost Center for the Account from the Navigation tree and then click the Summary tab. Verify that the fiscal year totals for the account are displaying correctly. Do the same for the Commodity and Monthly tabs and associated charts/values.
5. OPTIONAL: Locate the associated meter for the new commodity and view the METER Monthly and Bills tabs to ensure that the use/cost data is displaying as expected.
6. Using an AP interface? If so, export the bill(s) entered for the new commodity to make sure the GL assignments and/or interfaces are functioning properly with the new commodity.

Issue: When setting up a bill entry template, the desired Unit of Measure is not a choice in the drop-down list.

EXAMPLE 1: Creating HGAL Unit

In this example, it is necessary to create a new unit of measure for sewer usage--HGAL (hundreds of gallons)--because HGAL is the “native” unit of measure shown on the bill.

Solution: Create a new usage unit for sewer as follows:

1. Go to Tools – Unit System. The Units System Editor window will open. 
   
2. From the Unit System Editor, click the Units tab. Click the Unit Type heading to sort by Unit Type. Slide down to the “Volume” Unit Types because HGAL is a type of volume measurement. Does the unit of HGAL exist? 
   
3. In this case, the unit of HGAL does exist, it just hasn’t been “turned on” for Sewer. (Skip to step 5 in a situation like this)
4. If HGAL did NOT exist, you could quickly create it by (1) clicking on the + button to add a new unit, then (2) clicking on the + button under Convert To to add a conversion unit to allow EnergyCAP to convert this new unit of measure to COMMON volume units. Common units are shown in Unit System – Commodities tab. In this case, all Sewer usage is rolled up to Common units of MGAL (thousands of gallons) so you must add a conversion for HGAL to MGAL. (Multiply HGAL by 0.1 to get MGAL) 
   
5. After the required unit of measure has been created, you must turn it on for this commodity and this “class.” From the Unit System Editor, click the Equivalencies tab, find the commodity and select the class and unit type. In this case, we select usage and volume because we need to track usage in units of volume (HGAL) on the template. 
   
   
   
6. Click the Properties button, then click the HGAL checkbox to turn this unit of measure on. 
   
7. HGAL is now available for selection in the Template Manager.
   

EXAMPLE 2: Creating a new EKWH (Equivalent Kilowatt-Hours) Unit

1. Open the Unit System Editor (Tools> Unit System>Units tab) and verify that the desired unit does not exist.
   
2. click on the ‘+’ button below the list of Units to add it. The Create Unit window will open.
   
3. Enter the Code, Display and Unit Type information needed to create the Unit. Then click the OK button to save it.
4. Select/highlight the new unit from the Units tab and locate the Convert To pane in the top right corner of the Units tab display. Then click the associated ‘+’ button. The Create Conversion window will open.
   
5. To add a new conversion for the new unit, select the conversion unit from the dropdown list and input a conversion factor (multiplier) formula. Then click OK.
6. Repeat this process for each conversion within the same unit type. If necessary, use the Add this constant: option.
   
   
   NOTE: For a new common or global roll-up unit, it will be necessary to assign the Unit accordingly and set up equivalencies as well.

An obvious followup question is “Why aren’t all units of measure turned on for every commodity all the time?” If every possible option was available, drop-down selector lists would be unwieldy and unmanageable, increasing the opportunity for configuration errors. EnergyCAP just shows commonly used choices and allows you to configure those options that are uncommon. This is a quick a one-time configuration.

• 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