Solution to Hail Forecast Issue - (Oracle Application Specific)

 

 

---

From: Tajammul A Siddique
Sent: Wednesday, October 07, 2009 10:50 AM
To: Mohammed Abdurahman Al-Quayed; Abdullah A. Bn mousa
Cc: Waleed Mohammad Asiri
Subject: Solution to Hail Forecast Issue - (Oracle Application Specific)
Importance: High

 

 

HAIL FORECAST SHOWING ZERO ON THE HOURLY GRAPH

 

 

Issue : This issue was encountered on 5th October 2009. It was reported at 7:30AM, then after saving the hourly entry, the Forecast Graph was not showing the forecast value for Hail.

 

 

Analysis :

 

a) The forms involved are

 

1.OPR0370.FMB (LogSheet Form)

 

b) The tables involved for this function are

 

a) Expected_Ratios

b) AREA_EXP_TEMPS

c) HOURLY_LOG_AREAS

 

 

 

 

 

b) We have studied the functionality of the form and found that everytime, the SAVE button is clicked on the Logsheet form, it will generate the forecast for the remaining hours on the Excel Graph.

 

c) We tried to add values to the remaining hours from zero to numeric value, but, still when we save the reading for the next hour, again the values are returned to zero for the forecast hours.

 

d) After the code Walkthrough, i found that apart from the in-built program units, it is also using two external stored procedures during the save operation.

 

1. COPY_24(:date)

This procedure runs only at 24:00 and copies the data by using Merge from the previous date to the next date.

 

2. TRIG_CALC_EXPECTED

This trigger is the one, which fires and populates/modifies the data in the AREA_EXP_TEMPS table based on the ratios provided in the EXPECTED_RATIOS table for that day, that hour and that Area

 

 

 

How does it work :

 

When the user clicks on SAVE, it will insert the ACTUAL LOAD (area-wise) data into the IMPORT_VALUES column of the HOURLY_LOG_AREAS for that particular date and hour and, this fires a trigger which will multiply the value in the IMPORT_VALUES column with the RATIO_PERCENTAGE column of the EXPECTED_RATIO table and the result is stored in the AREA_EXP_TEMPS table.

 

When the user click on the Graph button, it will gather the values of the Actual load from the Hourly_Log_Areas.Import_values column as the actual load and the data for expected forecast for the remaining hours from EXPECTED.AREA_EXP_TEMPS based on the AREA_ID

 

 

 

What was the Error :

 

The error was found in the EXPECTED_RATIO table. This table should contain only 24 entries for each day for each AREA i.e RUH, QSM and HAL.

 

When we run the query below, we found the following issue

 

select * from expected_ratio where ratio_date>'04-oct-2009' and area_id='HAL'

 

RATIO_DATE	RATIO_TIME	AREA_ID	RATIO_PERCENTAGE	RATIO_SEQ
10/05/2009 00:00:00	00:00	HAL	1.01	1
10/05/2009 00:00:00	01:00	HAL	1.01	1
10/05/2009 00:00:00	02:00	HAL	0.99	1
10/05/2009 00:00:00	03:00	HAL	0.97	1
10/05/2009 00:00:00	04:00	HAL	0.96	1
10/05/2009 00:00:00	05:00	HAL	1.00	1
10/05/2009 00:00:00	06:00	HAL	0.86	1
10/05/2009 00:00:00	07:00	HAL	0.98	1
10/05/2009 00:00:00	08:00	HAL	1.05	1
10/05/2009 00:00:00	09:00	HAL	1.05	1
10/05/2009 00:00:00	10:00	HAL	1.07	1
10/05/2009 00:00:00	11:00	HAL	1.03	1
10/05/2009 00:00:00	12:00	HAL	1.03	1
10/05/2009 00:00:00	13:00	HAL	1.00	1
10/05/2009 00:00:00	14:00	HAL	1.06	1
10/05/2009 00:00:00	15:00	HAL	1.03	1
10/05/2009 00:00:00	16:00	HAL	0.96	1
10/05/2009 00:00:00	17:00	HAL	0.96	1
10/05/2009 00:00:00	18:00	HAL	0.96	1
10/05/2009 00:00:00	19:00	HAL	1.14	1
10/05/2009 00:00:00	20:00	HAL	0.96	1
10/05/2009 00:00:00	21:00	HAL	0.98	1
10/05/2009 00:00:00	22:00	HAL	0.99	1
10/05/2009 00:00:00	23:00	HAL	0.97	1
10/05/2009 00:00:00	24:00	HAL	1.01	1
10/05/2009 01:39:49	01:00	HAL	0.00	202,975
10/05/2009 01:39:49	02:00	HAL	0.00	202,976
10/05/2009 01:39:49	03:00	HAL	0.00	202,977
10/05/2009 01:39:49	04:00	HAL	0.00	202,978
10/05/2009 01:39:49	05:00	HAL	0.00	202,979
10/05/2009 01:39:49	06:00	HAL	0.00	202,980
10/05/2009 01:39:49	07:00	HAL	0.00	202,981
10/05/2009 01:39:49	08:00	HAL	0.00	202,982
10/05/2009 01:39:49	09:00	HAL	0.00	202,983
10/05/2009 01:39:49	10:00	HAL	0.00	202,984
10/05/2009 01:39:49	11:00	HAL	0.00	202,985
10/05/2009 01:39:49	12:00	HAL	0.00	202,986
10/05/2009 01:39:49	13:00	HAL	0.00	202,987
10/05/2009 01:39:49	14:00	HAL	0.00	202,988
10/05/2009 01:39:49	15:00	HAL	0.00	202,989
10/05/2009 01:39:49	16:00	HAL	0.00	202,990
10/05/2009 01:39:49	17:00	HAL	0.00	202,991
10/05/2009 01:39:49	18:00	HAL	0.00	202,992
10/05/2009 01:39:49	19:00	HAL	0.00	202,993
10/05/2009 01:39:49	20:00	HAL	0.00	202,994
10/05/2009 01:39:49	21:00	HAL	0.00	202,995
10/05/2009 01:39:49	22:00	HAL	0.00	202,996
10/05/2009 01:39:49	23:00	HAL	0.00	202,997
10/05/2009 01:39:49	24:00	HAL	0.00	202,998

 

 

 

As can be seen from above, there is a duplicate recording for HAL area for 5-Oct-09 and the second values were found to be zero, therefore the actual is multiplying with zero value and the resulting forecast was showing as zero.

 

 

How we fixed it :

 

We need to delete this duplicate entries from the above table, by doing the following steps

 

SQL>create table temp_expected_ratio as select * from expected_ratio where ratio_date='05-oct-09' amd area_id='HAL'

 

SQL>Delete  from expected_ratio where ratio_date>'04-oct-2009' and area_id='HAL'

 

SQL>INSERT INTO EXPECTED_RATIO SELECT * FROM TEMP_EXPECTED_RATIO

 

SQL>COMMIT;

 

 

The above four statements will copy the entries from EXPECTED_RATIO for '5-oct-09' for Area_id of HAL and it will ignore the duplicate entries as we are specific about the date, without time format.

 

Then we delete the full data for 05-oct-09 from the EXPECTED_RATIO table for HAL and re-insert again the correct and unique data. Finally, perform a commit to save the changes.

 

 

Conclusion :

The generation of forecast is based on the EXPECTED_RATIO table, deriving the values from Hourly_Log_area and storing the result in the AREA_EXP_TEMPS table. There should be any problem with the ratios as long as there are no duplicates in any of the above tables. (WallAllahu Aalam)

 

 

 

 

 

 

 

 

 

 

Disclaimer: This message and its attachment, if any, are confidential and may contain legally privileged information. If you are not the intended recipient, please contact the sender immediately and delete this message and its attachment, if any, from your system. You should not copy this message or disclose its contents to any other person or use it for any purpose. Statements and opinions expressed in this e-mail are those of the sender, and do not necessarily reflect those of Saudi Electricity Company (SEC). SEC accepts no liability for damage caused by any virus transmitted by this email.

هذه الرسالة و مرفقاتها (إن وجدت) تمثل وثيقة سرية قد تحتوي على معلومات تتمتع بحماية وحصانة قانونية. إذا لم تكن الشخص المعني بهذه الرسالة يجب عليك تنبيه المُرسل بخطأ وصولها إليك، و حذف الرسالة و مرفقاتها (إن وجدت) من الحاسب الآلي الخاص بك. ولا يجوز لك نسخ هذه الرسالة أو مرفقاتها (إن وجدت) أو أي جزئ منها، أو البوح بمحتوياتها لأي شخص أو استعمالها لأي غرض. علماً بأن الإفادات و الآراء التي تحويها هذه الرسالة تعبر فقط عن رأي المُرسل و ليس بالضرورة رأي الشركة السعودية للكهرباء، ولا تتحمل الشركة السعودية للكهرباء أي مسئولية عن الأضرار الناتجة عن أي فيروسات قد يحملها هذا البريد.