Project Overview

The Solar Retina is a software platform that provides real-time intelligence of total “behind-the-meter” solar PV (Photovoltaics) generation on the distribution system. Using crowdsourced solar generation data from actual PVs on the distribution grid, we provide superior distributed solar generation analysis

Challenge Overview

We are making few changes to Ruby on Rails application in this challenge as outlined below.

The application was built in a past assembly challenge, you can refer to it to understand what we built:

http://www.topcoder.com/challenge-details/30048722/?type=develop&noncache=true

Challenge Requirements

You will address the following in this challenge :

Aggregation Types

Right now we are importing data into location_data table as is, the data are time series data with 5 minutes interval.

In frontend when we try to retrieve the time series to render chart for large periods (i.e. 180 days) we notice a performance issues and too much delays in retrieving the data.

So we are introducing new tables to store aggregations of the data, so during import you will add the following steps after inserting the data from csv into location_data :

• Aggregate data into daily intervals :

  • This step should be executed right after completing the insert from csv for the given location_id.

  • For each new location id aggregate data from location_data for that location by executing this query :
    select (
        FROM_UNIXTIME(date_time,'%Y-%m-%d')) as date_time,
        sum(instantaneous_power) as w,
        sum(ramp_rate) as r
    from location_data  
    where location_id = {location_id}
    group by FROM_UNIXTIME(date_time,'%Y-%m-%d') ;

  • Insert the returned data into location_data_day table. The columns will be same as location_data table.

• Aggregate data into hourly intervals :

  • This is the second aggregation we are executing.

  • Query to execute is :
    SELECT
    UNIX_TIMESTAMP(FROM_UNIXTIME(date_time,'%Y-%m-%d %H')) as date_time,
    sum(instantaneous_power) as w,
    sum(ramp_rate) as r
    from location_data
    where location_id = {location_id}
    group by FROM_UNIXTIME(date_time,'%Y-%m-%d %H') ;

  • Insert data into location_data_hr table. Same fields are other tables.

• Aggregate data into 15 minutes interval :

  • This is the last aggregation type.

  • The query is little bit tricky :
    select

 quarter, sum(instantaneous_power), sum(ramp_rate)

from

(

 select

   location_data.*,

   concat(

     FROM_UNIXTIME(date_time,'%Y-%m-%d %H'),

     case

       when minute(FROM_UNIXTIME(date_time)) between  0 and 14 then ':00'

       when minute(FROM_UNIXTIME(date_time)) between 15 and 29 then ':15'

       when minute(FROM_UNIXTIME(date_time)) between 30 and 44 then ':30'

       when minute(FROM_UNIXTIME(date_time)) between 45 and 59 then ':45'

     end

   ) as quarter

 from location_data

 where location_id = {location_id}

) as quarters

group by quarter;

• Insert data into location_data_quarter table. same fields as others.

• Remove auditing fields from location_data_xxx tables, not needed.

Frontend Charts Aggregation

Based on the updates we are making in importer step to store data grouped into intervals, we will update the frontend JS code to pull data based on selected date interval :

• “Day” : use 5 minutes interval from location_data .

• “7 Days” : use 15 minutes interval from location_data_quarter

• “30 days” : use 1 hr interval from location_data_hr

• “180 days” : user 1 day interval from location_data_day

• “Other” : it will follow the constraints above :

  • <= day : 5 minutes interval data

  • > day and =< 7 days : 15 minutes interval data

  • > 7 days and =< 30 days : 1 hr interval data

  • > 30 days : 1 day interval data.

Importer Fix

• Importer should use activerecord-import library to insert batches into DB.

• Fix time used to calculate the date_time should start from 1/1/2014 EST.

• We are providing you with lib/importer_methods.rb, it has these updates but you need to make some changes to get the library working, i.e. updating Gemfile.

• Update application startup to run any pending or in-progress import task. We might got some crashes and this requires proceeding the current import.

Performance Chart Calculation Fix

Performance Index calculation shouldn't be greater than one in any case.  Since it is  (Sum of Energy Generation) / (Sum of Maximum Capacity).

Here is more details about the calculation https://drive.google.com/drive/u/0/folders/0BxGw3rNZWrkgVEVMdjFvQzRUczA/0By6n2kEXozMwfnh2RkdZS2F2Y1Q1V0xienZJbDFzWmtHWVVvUTAzWjRqSFBjRUNib2w3YzQ

Make the fix for this issue.

“Other” tab in Chart popup

For the "Other" date series, where the user selects date and time, it immediately calculates on change.  The client would like there to be an apply button in the div so that they can pick start date and time, then hit apply to have it calculate.

Zooming in chart

Implement zooming in chart http://www.flotcharts.org/flot/examples/navigate/index.html  

Playback Heat map

• Update play button to use latest date in db when start playing.

• Update options to be 1hr, 4hrs, 8hrs, and 12hrs.

• We will use 5 minutes aggregation (location_data) for this one.

Chart Tabs Unit of Measures

Add units to the labels on the three graph-selection buttons

• generation (W)

• performance index (p.u.)

• ramp rate (W/s)

Testing

You are provided with sample csv file and dump mysql, you can use it for testing.

Documents

The current solution is provided in challenge forums.

Deliverables

• Patch file of updated source code that implemented the requirements.

• readme file to reviewer.

Final Submission Guidelines

.