Forecasting Electricity Demand
ZizhuoXu 11974209
1. ER diagram design
I have reused the ER diagram from the teacher provided, I have created my local table LOCAL_RM16, with all my forecast value with future 14 days.
2. Solution design
I used top down design method to construct my solutions.
2.1 Procedure RM16_forecast
Before update the table of LOCAL_RM16, I will do the truncation every time, and write the message to the log.
And using a for loop to loop over 14 days (defined as global variable in the package)
For each day forecasting, I have created a procedure to forecast one day only, and then after each day forecast.
2.2 Procedure FORCAST_ONE_DAY
This will forecast the required value on one day only, the date will be input as parameter in the procedure. for each day, the date will be checked if it is holiday or not, if no, then it will use normal day consumption to forecast the value, and if it is no, then it will check the date in the pass has the historical data or not, if no, then we will use Sunday consumption to forecast the value, if it has historical data, then we will use the holiday consumption.
After that, the XML file of the total TNI volume will be calculated and output to XML file stored in the directory (created before as /exports/orcloz/)
Email will be sent with xml attachment file to the email address in the VALUE filed of the parameter table created in my local user schema.
2.3 Procedure FORCAST_NORMAL_DAY_CONSUMPTION
This will insert to the LOCAL_RM16 table with the forecast value, on which the date is not holiday. This will based on the previous day value to and average out the value.
2.4 Procedure FORCAST_SUNDAY_CONSUMPTION
This will insert to the LOCAL_RM16 table with the forecast value, on which the day is Sunday. This will based on the previous Sunday value to and average out the value.
2.5 Procedure FORCAST_HOLIDAY_CONSUMPTION
This will insert to the LOCAL_RM16 table with the forecast value, on which the day is holiday. This will based on the previous holiday value to and average out the value.
3. Functions
A few functions are created to help checking the date and data.
3.1 Function HAS_HISTORICAL_DATA
This function will check if the input date has the historical data in the previous data. If yes, return the row count, if no, return 0;
3.2 Function IS_HOLIDAY
This function will check if the input date is the holiday or not. Return at least 1 if it is. Return 0 otherwise.
4. Package PKG_2017Main
Package is created to hold all function, procedures and all global variables as well.
5. SEND EMAIL procedure
The code design is Modularized, all of the functions and procedures are in the package, and it is broken down into manageable modules.