Pacific Power Recent Data GET Endpoint

[solderq35]

Related Issue

• https://github.com/OSU-Sustainability-Office/automated-jobs/issues/42

Changes

SQL code (backend\dependencies\nodejs\models\pacific_power_data.js)

• Unix timestamp, pacific power meter ID for each pacific power meter, in the past 3 days. If there are multiple results per meter, get the most recent one. Sort final list of results by timestamp ascending (earliest first).
• You can test the SQL query in SQL workbench with some other arbitrary timestamp (like from March 3 or something) to confirm at least 1 timestamp will be different

JS Code (backend\dependencies\nodejs\models\pacific_power_data.js)

• See https://playcode.io/1804875 for quick testing

Testing

• Run backend locally (sam local start-api from backend directory)
• Visit http://localhost:3000/pprecent
• See earlier sections for other ways to test SQL and JS code

TODO

• Update PacificPower scraper in automated-jobs repo to handle uploading missing data when detected (based on pprecent endpoint), handle case of getting previous month's data
• Cloudwatch alerts for new meters (reference ppRecent)
  • Or move this to blacklisted meters data structure
  • May cause false alarms in rare case that a meter is down for more than 3 days and then up again
• 3 days is just a placeholder value, might raise this depending on performance (needs testing)
  • Just need some kind of upper limit to prevent infinite scaling as more data is added (will probably get better after pacific power meter blacklist is implemented)

[solderq35]

Misc Research

EDIT: I couldn't find any gaps (so far) in PacificPower meters data bigger than 1 day at a time of missed uploads (since the data seems to consistently show up on PacificPower, just up to 24 hours late for the latest upload)

Even in cases where the item number changed (replacement meter?), the gap was one day at most

Conclusion: 7 days is probably overkill, probably like 2 days timeframe is enough

• I guess in theory the regular missing data glitch could combine with the "replacement meter" scenario to form a bigger gap in the data, but that seems unlikely / can be solved later if it comes up

• Item #327, Meter #78163731

• Item #160, Meter #78163731
• To get this one to load properly when first navigating to it, try clicking "Previous" button, then navigating back via the calendar GUI
  • Might have future implications for fixing glitches where data is not shown / loaded initially on pacificpower site frontend

====

Code Changes

I tried extending the time window from 3 to 7 days as a test, and the performance didn't seem that much worse (maybe about 1.5 to 2x slower at worst, and still faster than the allbuildings API endpoint).

Local backend testing screenshots

• pprecent endpoint, 3 days time window (Duration: 445 ms)

• pprecent endpoint, 7 days time window (Duration: 642 ms)

• allbuildings endpoint (Duration: 940 ms)

TODO

• Probably won't do much more work in energy-dashboard repo for this, and move to the automated-jobs webscraper side
• https://github.com/OSU-Sustainability-Office/automated-jobs/issues/42#issuecomment-2031412455

[solderq35]

Marking this as ready for review. Think I will keep the 7 day timeframe because there were cases where the data was out of date for 2 days or more for one reason or another, and I have updated my missing data webscraper PR to check for up to 7 days prior via while loops

[solderq35]

Follow-up task (future PR): https://github.com/OSU-Sustainability-Office/automated-jobs/issues/51

[solderq35]

Updated to list data from past 7 days per meter. Duplicate values (same meter ID + time stamp as an existing value) are skipped

Also, upload with same meter ID + time stamp as an existing value now returns 400 error

[solderq35]

Fixed merge errors from exclusion energy dashboard PR, will fix merge errors for automated-jobs repo later