EFC Internal Documentation

Expand an accordion question or click on a tab below for information.

  • List of Definitions
    1. “Base charges” is the amount a customer is required to pay each billing period, regardless of water consumption. Base charges often vary by meter size.
    2. “Billing period” is the length of time a bill covers – available options are monthly, bimonthly, quarterly, semi-annually and annually.
    3. “Billing unit” is the unit the utility bills customers in. Available billing units for drinking water and wastewater are thousands of gallons or hundreds of cubic feet and for stormwater are square feet or units.
    4. “Bill overrides” TBA
    5. “Calculate bills” shows the bill at different volumetric consumption points for a particular rate.
    6. “Confirmed at” refers to the date when rate services were confirmed. When entered, rates are not automatically approved. New rates are unapproved until EFC staff with appropriate permissions.
    7. “Census area” is a unique identifier for a physical location associated with a rate structure. The centroid of census areas is input into supplemental data and permits automatic updating of data from the US Census.
    8. “Clone rate” duplicates an existing rate and its base and volumetric charges. Rate details change much less frequently than do base and volumetric charges. When a rate detail already exists but base and/or volumetric charges have changed, clone rate is quicker than entering a completely new rate detail. After cloning the user should adjust the new rate’s effective date, and then modify base and volumetric charges.
    9. “Dashboard” refers to the rates and dials displayed for a specific state. Dashboard URLs are https://dashboards.efc.sog.unc.edu/ca (where you replace ca with the appropriate state abbreviation). You can view dashboards without being logged in.
    10. “Database” refers to the database where utilities, rate structures and rates are entered. The database URL is https://dashboards.efc.sog.unc.edu . You should have a login to this system, as shown in Figure 1.

    11. Click File

      Figure 1. Database login screen.

    12. “Edit details” lets a user modify rate details such as effective date, billing unit and billing period.
    13. “Effective date” is the date a rate service took effect. Effective dates often begin the year or fiscal year after new rates are adopted by a utility.
    14. “Inside or outside of service area” refers to rates that differ inside of a utility’s service area or outside. Outside rates are often higher than inside rates, this can happen when customer’s inside a municipality’s city limits pay city taxes and those outside of city limits do not pay city taxes. When separate rates are charged inside of city limits and outside of city limits, they are entered as separate rows in the rate services table.
    15. “New rate service” creates a new rate service. To initially create a rate, a user must define the rate’s metadata (described in rate services below). Once a new rate is created it is associated with blank base and volumetric charges. The user must edit details and input base and volumetric charges to resolve QA violations.
    16. “Production” is the live system where most work will occur. While working on a dashboard, you will generate a new dashboard URL. You can go to the temporary URL by navigating to the dashboard screen and clicking “view” next to the dashboard in progress, to the far right in Figure 2.

    17. Click File

      Figure 2. Dashboard generation screen in the application.

    18. “QA violations” are violations indicated a rate has not been entered completely or correctly matching its volumetric rate structure. QA violations include:
      1. Incomplete Season
      2. Violates Decreasing Blocks
      3. Violates Has Blocks
      4. Violates Increasing Blocks
      5. Violates Increasing / Decreasing Blocks
      6. Violates Multiple Blocks
      7. Violates Anything
    19. “Rate approved” is a status indicating that EFC has reviewed a rate and that the data quality is sufficient to commit it to the database.
    20. “Rate manually reviewed” is a status indicating that EFC has reviewed a rate against the rate sheet submitted and has confirmed or updated data.
    21. “Rate details” are the specific base and volumetric charges associated with a rate service.
    22. “Rate services” are the metadata of rates. They capture information like service classes, service types, effective date, billing unit, billing period and volumetric structure. As EFC updates dashboards for different states, most rate service information will remain stable. To edit rate services, go to the Rate Services table and click the pencil to the far right.
    23. “Rate structures” are a name assigned to a unit of a utility with unique rate structures. Beaufort County, NC is considered one utility. It has separate rate structures for each of seven Water Districts because it charges different rates in each district. Rate structures are associated with one or more census places and can be associated with zero or more public water system ids.
    24. “Service classes” include residential, commercial, industrial and multi-family. A rate service can have one or more service classes assigned.
    25. “Service type” describes the type of service being billed for – water, sewer or stormwater. A rate service can have only one service type.
    26. “Staging” is a test system for reviewing coding changes to the database or dashboard displays. 
    27. “Supplemental data” includes data from the US Census, the Safe Drinking Water Information System and other fields provided by states for upload into the system. Some supplemental data is derived from APIs using connections defined in the menu Data > Reference Data. Other data can be uploaded using a .csv file, the upload occurs in the dashboard menu “Upload CSD data CSV” link.
    28. “Utilities” are the provider of water and sewer service. The utility includes fields for address, primary contact, phone and email.
    29. “Volumetric charges” are charges per unit volume of water or sewer service. Volumetric charges may be applied in addition to base charges.
    30. “Volumetric structure” are the names assigned to different types of volumetric structures such as uniform rates, increasing block, decreasing block. See General Dashboards Training for more information.
  • Utility Roles

    Building separate document to combine SCS documentation from GitHub with Client Documentation: Microsoft Teams link and Knowledge Base link.

  • Reporting Roles

    Reporting - The reporting role is used for querying data. There are two reporting users. User efc_ro supports Tableau connections to data and can only connect to views. User efc_query supports query connections and can connect to both views and tables.

  • Internal roles follow. Each successive role has the functions listed above, with more added.

  • Management
    1. Access to all utilities
      1. All internal users can see all utilities
      2. Utilities are shown on the home screen and can be accessed through the Data > Water/Sewer Data menu

    2. See internal EFC data
      1. All internal users can see Rate structures, rate services and one time fees data.
      2. Rate structures and rate services can be accessed through the Data > Water/Sewer Data menu or from shortcuts on other screens
      3. One-time fees are not tracked for all states. Where they have been tracked, they can be accessed through the Data > Water/Sewer Data menu
  • Data Entry
    1. Rate Edit
      1. Data entry users can edit rate structures and associate rate structures to census areas and SDWIS water systems in the database
      2. Rate structures can be accessed through the Data > Water/Sewer Data menu or from shortcuts on other screens
      3. Data entry users can add new rate services, edit rate services, edit rate details, and clone rates
      4. Data entry users can enter bill overrides
      5. Rate edit can be accessed through the Data > Water/Sewer Data menu or from shortcuts on other screens

    2. Rate Approve
      1. Data entry users can approve rates, on the rate edit screen
      2. Rate approval means that QA violations have been resolved, effective date and rates are consistent with the data supplied to EFC

    3. Utility Edit
      1. Data entry users can enter new utilities
      2. Data entry users can edit utility data, including contact information
      3. Utilities can be accessed through the Data > Water/Sewer Data menu or from shortcuts on other screens

    4. Utility Finance Edit
      1. Data entry users can enter a new year of utility finances
      2. Data entry users can edit existing utility finance data
      3. Utility finances can be accessed through the utilities screen. To add a new year of data, click the arrow. To edit data for an existing year, click the year and edit.
  • EFC Rate Editor
    1. Rate Delete
      1. EFC Rate editor users can delete rates
      2. Deleted rates are written to the table deleted_rate and data can be accessed through queries or Tableau
      3. Rate deletion can be accessed on the rate edit screen

    2. Reference Data Management
      1. Reference data is a broad permission. EFC rate editor users have menus that other users do not have. These are Data > Reference data and Dashboards.
      2. Reference data screens are:

        • Census – add Census areas. Census data can be bulk updated from the Census API by selecting County, County Subdivision, or Place and then clicking the button Load from Census API.
        • LGC – the Local Government Commission handles financials for North Carolina utilities and this screen connects utilities with LGC units.
        • SDWIS – drinking water compliance and supporting data is in the EPA SDWIS database. This screen connects with the SDWIS API, and you can add new public water system id’s to append SDWIS data to this list.
        • Geographic Regions – this screen shows state and county outlines used on dashboard maps.

      3. The dashboard screen is where you associate rate structures to state and supplemental data and build a dashboard.
      4. The Possible Rate Structures column contains important functions:
        • Select a state and associate all rate structures in the state to a dashboard.
        • If any rate structures are missed or need to be removed, that can be done on the Rate Structures screen.
        • Upload CSD Data CSV – supplemental data in the dashboard database can be replaced by a .csv file meeting the description in the modal.

      5. For more narrative on reference data and creating a dashboard, see Guide to generate dashboards.
  • EFC Admin
    1. User profiles see all
      1. EFC Admin users can see user profiles on the menu Admin > User profiles

    2. User profile management
      1. EFC Admin users can create new user profiles.
      2. EFC Admin users can edit and deactivate users. When editing, assign users a specific role such as “data entry”.
      3. EFC Admin users can send a password reset link to users. Some users may be established through CROMERR. If users reset their passwords via such a link, they will be switched to Database documentation.

    3. Background job debug
      1. EFC Admin users can run a background job debug. This is a script to run if building a dashboard hangs.