Meter data import problem

[RDmitchell]

Describe the bug I think this might be outside of what SEED can import right now but wanted to make an issue for it so I don't lose track of this.

The City of Berkeley is trying to import a meter data file that only has meter data in it, no separate tab with matching property records. The meter data is in the format from the Portfolio Manager Custom Report Template, ie, no meter ID / meter number, just 2 columns, one for gas and one for electricity

I have tested this on dev1 (SHA: 156ac0b00), on the Berkeley org, where the Property IDs in the meter data exist in the Property Inventory, using the "Meter Data" tab

I then get this error message (which is what Berkeley is also getting).

I expect it isn't importing because there is no property tab (?)

Expected Behavior That the file would import --

Actual Behavior See description above

Instance Information Me: Instance: dev1 SHA: 156ac0b00

City of Berkeley Instance: demostaging SHA: 60243aded

File can be found here https://drive.google.com/drive/folders/1jG4Cpc56SoZAastNxQvbljzyVKhHLzQa?usp=sharing

[macintoshpie]

My first impression is that the headers of the file are wrong, here's an example of what I think it should look like exported as a data report from ESPM (Note the difference in the column "Electricity Use (kBtu)") -- a file with these headers is able to be imported.

Have they modified the original file to include the "(Grid)" part? Could you test importing it with that header?

[RDmitchell]

@RDmitchell to ask Ammon if he edited the headers

if not, we need to find out how he exported the data.

[RDmitchell]

@macintoshpie -- Ammon did not edit the headers, this is what comes directly from ESPM. I think San Francisco might have the same issue -- I am in the process of getting a file from them.

So maybe our understanding of what the fields names are from ESPM is incorrect.

I have an ESPM account and will do some experiments to see what the fields names are for different types of downloads.

[RDmitchell]

@macintoshpie -- There are many different meters that can be selected when generating a custom report template. Seems like we need to be able to import all of the possibilities ??

Here is what I see in ESPM in terms of meter selection when generating a Custom Report Template -- there isn't a straight "Electricity Use" without "(Grid)" in it.

I generated a custom report template for a test property "Office" Here is how I set up the report

Clicking on "Select Information & Metrics", I selected these fields under the "Energy Use by Fuel Source" section

Looking at a summary of the fields I selected (by clicking the "View Selection and Order" link in the upper right of the previous screenshot. The Electricity Use has "(Grid)" in the title

Here are the results -- notice that the Electricity has "(Grid)" attached to the title.

[RDmitchell]

@macintoshpie -- I now have two sample spreadsheets from two different cities, Berkeley and San Francisco, and the headers for the meter names are slightly different.

The Berkeley one is the same as my test case, ie, the Electricity grid name has "(Grid)" in the title.

However the San Francisco one has the Electricity grid without "(Grid)" in the title.

I will try to figure out under what circumstance that is generated.

[RDmitchell]

But it does seem like we need to be able to import all the different type of meter names that are available in ESPM.

Seems like it would be nice to get a definitive list from the ESPM folks ... ???

[RDmitchell]

The ESPM reporting is a bit complex, as there are multiple ways to download data.

Here is one set of options:

• Download your entire portfolio to Excel
  • this option generates this file The Meters tab The Meter Entries tab
• Create a custom download
  • this option has these selection criteria for generating the report
    
  • And results in this spreadsheet -- this is the Meters tab Here is the Meter Entries tab

[RDmitchell]

so it seems like the main difference is whether the meter data is coming from a Custom Report Template or the standard ESPM reports. I think we, unfortunately, should try to support both.

Robin

[RDmitchell]

It seems that the Berkeley file was generated with a Custom Report Template.

But I have yet to be able to generate the file headers in the San Francisco spreadsheet that don't have "(Grid)" in the Electricity Use column name.

I am following up with SF to see how they generated their file.

[macintoshpie]

@RDmitchell That's interesting, it looks like ESPM has actually changed the name of the column... See the screenshot I have here when originally implementing support for custom reports https://github.com/SEED-platform/seed/issues/2673#issuecomment-828616846

It looks like there's no longer a generic Electricity column, which means:

1. we need to grab it from this new one instead
2. we can actually specify the type of electricity now (previously it was Unknown)

Do you mind if open a new ticket with this description and close this one as a duplicate?

[RDmitchell]

@macintoshpie -- it seems like it might be possible to generate data 2 different ways, and have the field name either include "grid" or not include it ... I have a file from San Francisco that just has "Electricity" without "grid" in the field name.

See this file (in this issue folder) https://docs.google.com/spreadsheets/d/12NAisONMcokTl4BJ1qJ4ANlmU2e1b6u6/edit?usp=sharing&ouid=103887815394604824348&rtpof=true&sd=true

So SEED might have to be able to deal with both.

[macintoshpie]

Hmm, I wonder if they'd be able to regenerate their reports? And if they do regenerate it, if it would have the updated column name? @RDmitchell any idea if they could test that for us?

[RDmitchell]

@macintoshpie -- I just got a new file from SF, generated today, and it has "grid" in the title for the electricity column. So it does seem like ESPM might have changed the field name.

Here is a link to the new file from SF (it is in the folder for this issue) https://docs.google.com/spreadsheets/d/12oI1CsDGX603IQhkDm8T0giU5Nh2HMIc/edit?usp=sharing&ouid=103887815394604824348&rtpof=true&sd=true

But we might need to be able to import the old version, without the "grid" in the name? I think cities may want to import older meter data that they collected in previous years.

@nllong -- I wonder if we should get in touch with ESPM programmers and get the definitive scoop on this, rather than just guessing.

[macintoshpie]

@RDmitchell thanks for figuring that out. I guess that's y'alls call on whether we'd need to support both column names or if we can require users to reexport their data. Though SEED's support for this new file import is very new, within the last 6 months, so maybe there aren't too many folks that have those files lying around b/c SEED didn't support it previously? Also support for that meter type has never been available on the production server I believe.

If you do end up contacting ESPM, it might be helpful to know whether the old electricity use column was only grid data as well (it just wasn't specified in the header). That could simplify our parsing if we do end up supporting both column names.

[RDmitchell]

@nllong -- how do you want to proceed? Do you have a contact at ESPM that Ted or someone could talk to about this?

[nllong]

I think it would be great to talk to someone from ESPM. I have the contact for Caterina. Happy to make the introductions (not sure that I could articulate the issue well enough right now). Whom should I put them in touch with?

[RDmitchell]

I would say a programmer (Ted or ??) and maybe myself, just to lay out the context ...

[nllong]

How about I connect both of you. Stay tuned!

[macintoshpie]

Most recent update from ESPM

Yes – we recently changed the name of the metric to “Electricity Use (Grid) – Monthly” – to make it clear that it is only for grid electricity. It previously only included Grid electricity. ... If a data request has this column, the column header will be updated, and it should not cause a problem with your data request.

Also it sounds like from SEED users that we might not have to worry about supporting old versions of the file (does that sound right @RDmitchell ?)

[RDmitchell]

@macintoshpie -- I think we do not need to support older files with the old name. It sounds like the data can be recreated from ESPM when needed.

[macintoshpie]

Closing this issue b/c I've summarized the technical requirements in #3028