Sustainability & Risk / Energy / Background Data - Facilities

Energy Application On-Ramp: Loading Bills Using Archibus Connectors

The Archibus Energy application processes data from your energy bills to provide summarized energy consumption data across your portfolio. To perform this analysis, data from your energy bills needs to be loaded into the application. This can be done manually using the Enter Bills task, or you can use the Archibus Connectors to automatically load this data. You can configure the Connectors so that bills are loaded automatically on a scheduled basis. This use of the Connectors to load your data is referred to as the on-ramp.

Connectors are developed from specifications provided by the vendor that tell you how to interpret the bill data in the electronic file, or in the absence of formal specifications, by studying the elements of example bills. You might have several vendors with a number of different bill types, and each of these would typically have different connectors. For example, if you have sites in multiple states, or even in different parts of the same state, you would need multiple connectors. In addition, bill specifications can change over time, so previous versions of the connector need to be accessible in case historical data needs to be loaded.

Consequently, a method is needed to correlate the connectors with their respective electronic bills. The Define Bill Connector Associations task stores data in the Bill Connectors table (bill_connector.) This table provides the association between the electronic bill and its connector, as well as the ability to track multiple versions of a connector.

Exempting bills from data checking

The format required for entering bill data depends on whether or not you want to run the weather model analysis. If you want to generate the Usage with Weather Model report, your bill data must meet the strict guidelines for entering bills for complete months with no gaps in time periods. However, you can exempt bill types from this validation, so that you can approve and archive bills that do not meet this guideline. See Configuring Bill Processing.

Exempting bills from this strict data entry requirements simplifies loading data using the Archibus Connectors. To more easily get started, you might exempt bills from the checking that bills are for complete months and have no gaps in Billing Periods. This will cause less validation errors. You can then use this bill data to get all reports excluding the Usage with Weather Model. If the Usage with Weather Model report is needed, you can use the Bill Processing Exceptions report to generate records in the format that the weather model can handle. See Options for entering bills when using the weather model analysis

Preparing the Data Source File

Starting the process of automatically loading utility billing information can either be manually performed from views built for the purpose or, you can employ the connector configuration to run the connector automatically on a scheduled basis. To use the Connectors, the bill data source file can be either an Excel spreadsheet, or an EDI file.

When using either format:

Automatic unit conversion and rollup: A key feature of the Connector used for energy bills is that it automatically performs the units' conversion calculations necessary for aggregating data as a standard part of the processing. Those calculation elements are available to you to incorporate into your connector.

Ability to bypass the approval process. In addition, an input parameter can be set to indicate that the bill should automatically be approved when the connector successfully completes. This will cause the data to move from the bill and bill_lines table into the bill_archive and bill_line_archive tables. The application draws data for analysis and reports from these archive tables. This feature is useful when the bills have already been approved prior to entry into the Archibus application, and you want to bypass the approval step in Web Central. For example, this would occur if Facilities or an Energy Manager receive the bills after they have been processed and paid by the Finance office.

Note: If you are performing proration or aggregation, or if you have configured the application to allow some non-periodic bills through, then you should not automatically approve and archive all bills on import. Those bills that fit the pre-existing constraints can be imported unchanged, but special cases should either stop in the account supervisor's queue for processing and approval, or else include additional processing via their bill connectors.

When using an Excel spreadsheet

The utility company might provide the data in this form, or you might have a process in place for manually entering the data into a spreadsheet. Archibus provides a spreadsheet and corresponding example connector. If you already have a spreadsheet, you have two choices.

The application assumes that each spreadsheet file will contain only one bill with its associated bill lines.

The following shows the process flow for loading the spreadsheet:

When using an EDI file

You can also use an Electronic Data Interchange (EDI) file to load your energy bill data into Archibus. EDI is an industry standard used by utility companies to transmit electric bills. Typically the consumer negotiates a method of transfer (ftp or network), and the utility provides a description of the format of the file that they will be providing.

Each utility Vendor that provides an EDI interface also provides a definition of the information contained in the file that is electronically transmitted. The Bill Connector definition and Load Bills workflow for EDI are very similar to Excel (see above) with the exception that EDI files tend to be more difficult for humans to read, and their data structure requires a unique Archibus Connector Type.

The following shows the process for the EDI workflow.

Defining Bill Connector Associations

The business process owner associates a connector with a bill type and vendor. This enables the system to associate the right connector with the right electronic bill file.

To define bill connector associations:

  1. Select the Energy / Background Data - Utilities / Define Bill Connector Associations task.
  2. In the Select Bill Connector pane, click Add New.
  3. In the Define Bill Connector pane, enter the following:

    Bill Type: Select the bill type you are associating with this connector. You select from the types that your business process owner has defined. See Defining Bill Types.

    Bill Connector ID. The name of the connector used to process bills from this vendor account and bill type. When you select a Bill Connector ID, the connector’s connection string is copied to the Default Path and File Name field.

    Vendor Code / Vendor Account. Select the energy vendor and the account this bill is for. The Vendor Account Code select value is filtered by the Bill Type and Vendor you selected.

    Bill Line Connector ID. The name of the connector used to process bill lines from this vendor account and bill type. For example, enter "Energy-XLS-Import-Bill-Lines”.

    Connector Version. Connectors change over time, so keeping former versions enables you to process old versions of the data source file. This is useful when you have a need to import historical data. The Connector Version field is used only as a reference for filtering, so that you can locate Connectors by their version.

    Date Effective Start / Date Effective End. The date range for which this specification is effective. Bill files generated between the start and end date can be processed with this connector.

    Approve and Archive? If Yes, the system automatically auto-archives the bill when importing. The application draws data for analysis and reports from these archive tables. Auto-archiving is useful when you want to bypass the approval step in Web Central when the bills have already been approved prior to entry into the Archibus application.

    Bill File Type.Select either XLSX or EDI, depending on which format the energy bill data that you are loading uses.

    Default Path and File Name.Enter the path to the data source file for the selected Bill Connector ID. When you select a Connector ID, the Connector's default path and file type is copied to this field. For example, /#Attribute%//@webAppPath%/projects/hq/datafeeds/risk/energy/bills/abrisk-energy-bill-sample.xlsx

  4. Click Save.

Running the energy bill Connectors

System Administrators can schedule the Connector to run on a regular basis using the Schedule tab of the System / System Administrator / Configure Connectors task. See the Configure Connectors Help topic in the System Management Help.

System Administrators can also run the Connector at any time from the Properties tab of the Configure Connectors task, or using the Execute Connectors task.

Working from the Energy application, you can run the Connectors using the Load Bills task. This task is available for the Cost Administrator or the Accounting Supervisor. See Loading Bills.

How the system processes data

A key feature of the automated bill loading process is that it converts the user’s data entry values to the roll-up unit (common units) and completes the rollup.

The bill connector definitions used for loading data must map the bill.status field to a constant value of “Imported.” Then, when the processImporedBills workflow rule runs, it processes all bills with a Status of 'Imported.' The workflow rule performs the calculations by calling the following existing workflow rules for all the Imported Bills:

Approve and Archive? field

When defining a bill connector association, you set the Approve and Archive field to either Yes or No.