tajcorner

Monday, November 2, 2009

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.

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

Wednesday, October 7, 2009

Investigating the Data Entry Time using Flashback Query

On 5^th October 2009, at 7:00AM, we have been reported of a problem which is pertaining to the Forecast for HAL. After through analysis, we found that this has occurred because there was double entry HAL records in the EXPECTED_RATIO. Our next concern was at what time this double entry was done.

Was it done by the Planning Engineers when they did the entry on 4^th October in the EXPECTED_RATIO table for the forecast of 5^th October or Was it done by the Engineers during the night shift as the problem started after the successful completion of the 1:00AM reading.

To Find out this, we are using the Flashback Query Feature of Oracle.

What is Flashback Query :

Flashback Query provides a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database.

Or in Simple Terms

Table data can be queried as it existed at a point in time.

We understood from the users that they were experiencing the problem since 2:00 AM. So, we gave the following Query on the Live Database on 7^th October at 14:00hrs.

SQL> SELECT * FROM EXPECTED_RATIO AS OF TIMESTAMP TO_TIMESTAMP('05-OCT-2009 01:15:00','DD-MON-YYYY HH24:MI:SS') WHERE RATIO_DATE>'04-OCT-09'

RATIO_DAT RATIO_TIME AREA_ID RATIO_PERCENTAGE RATIO_SEQ

--------- ---------- ---------- ---------------- ----------

05-OCT-09 00:00 HAL 1.01 1

05-OCT-09 00:00 QSM 1.008 1

05-OCT-09 00:00 RUH .986 1

05-OCT-09 01:00 HAL 1.01948052 1

05-OCT-09 01:00 QSM .99559859 1

05-OCT-09 01:00 RUH 1.00820853 1

05-OCT-09 02:00 HAL .99044586 1

05-OCT-09 02:00 QSM .99734748 1

05-OCT-09 02:00 RUH .99171547 1

05-OCT-09 03:00 HAL .97749196 1

05-OCT-09 03:00 QSM .96631206 1

05-OCT-09 03:00 RUH .97580297 1

05-OCT-09 04:00 HAL .97039474 1

05-OCT-09 04:00 QSM .96422018 1

05-OCT-09 04:00 RUH .97830258 1

05-OCT-09 05:00 HAL .99322034 1

05-OCT-09 05:00 QSM .97240723 1

05-OCT-09 05:00 RUH .96394086 1

05-OCT-09 06:00 HAL .86006826 1

05-OCT-09 06:00 QSM .94129159 1

05-OCT-09 06:00 RUH .93817499 1

05-OCT-09 07:00 HAL .97619048 1

05-OCT-09 07:00 QSM .98960499 1

05-OCT-09 07:00 RUH 1.01351351 1

05-OCT-09 08:00 HAL 1.07723577 1

05-OCT-09 08:00 QSM 1.11239496 1

05-OCT-09 08:00 RUH 1.07209877 1

05-OCT-09 09:00 HAL 1.05660377 1

05-OCT-09 09:00 QSM 1.05288008 1

05-OCT-09 09:00 RUH 1.05419929 1

05-OCT-09 10:00 HAL 1.05714286 1

05-OCT-09 10:00 QSM 1.06457399 1

05-OCT-09 10:00 RUH 1.05913195 1

05-OCT-09 11:00 HAL 1.01013514 1

05-OCT-09 11:00 QSM 1.01179444 1

05-OCT-09 11:00 RUH 1.03492849 1

05-OCT-09 12:00 HAL 1.01003344 1

05-OCT-09 12:00 QSM .97002498 1

05-OCT-09 12:00 RUH .98937018 1

05-OCT-09 13:00 HAL .99668874 1

05-OCT-09 13:00 QSM .98798283 1

05-OCT-09 13:00 RUH 1.00993822 1

05-OCT-09 14:00 HAL 1.05980066 1

05-OCT-09 14:00 QSM 1.04604692 1

05-OCT-09 14:00 RUH 1.01023936 1

05-OCT-09 15:00 HAL 1.02821317 1

05-OCT-09 15:00 QSM 1.02574751 1

05-OCT-09 15:00 RUH 1.01171515 1

05-OCT-09 16:00 HAL .96036585 1

05-OCT-09 16:00 QSM .98218623 1

05-OCT-09 16:00 RUH .97215717 1

05-OCT-09 17:00 HAL .95555556 1

05-OCT-09 17:00 QSM .98598516 1

05-OCT-09 17:00 RUH .9760439 1

05-OCT-09 18:00 HAL .96345515 1

05-OCT-09 18:00 QSM .97073579 1

05-OCT-09 18:00 RUH 1.0076786 1

05-OCT-09 19:00 HAL 1.14137931 1

05-OCT-09 19:00 QSM 1.00861326 1

05-OCT-09 19:00 RUH 1.02054701 1

05-OCT-09 20:00 HAL .96374622 1

05-OCT-09 20:00 QSM .9675491 1

05-OCT-09 20:00 RUH .96773333 1

05-OCT-09 21:00 HAL .98119122 1

05-OCT-09 21:00 QSM .9788173 1

05-OCT-09 21:00 RUH .97451088 1

05-OCT-09 22:00 HAL .99361022 1

05-OCT-09 22:00 QSM .98016231 1

05-OCT-09 22:00 RUH .99773788 1

05-OCT-09 23:00 HAL .96784566 1

05-OCT-09 23:00 QSM .97424103 1

05-OCT-09 23:00 RUH .97775259 1

05-OCT-09 24:00 HAL 1.00996678 1

05-OCT-09 24:00 QSM 1.00849858 1

05-OCT-09 24:00 RUH .98637681 1

75 rows selected.

SQL> SELECT * FROM EXPECTED_RATIO AS OF TIMESTAMP TO_TIMESTAMP('05-OCT-2009 02:15:00','DD-MON-YYYY HH24:MI:SS') WHERE RATIO_DATE>'04-OCT-09'

RATIO_DAT RATIO_TIME AREA_ID RATIO_PERCENTAGE RATIO_SEQ

--------- ---------- ---------- ---------------- ----------

05-OCT-09 00:00 HAL 1.01 1

05-OCT-09 00:00 QSM 1.008 1

05-OCT-09 00:00 RUH .986 1

05-OCT-09 01:00 HAL 1.01948052 1

05-OCT-09 01:00 QSM .99559859 1

05-OCT-09 01:00 RUH 1.00820853 1

05-OCT-09 02:00 HAL .99044586 1

05-OCT-09 02:00 QSM .99734748 1

05-OCT-09 02:00 RUH .99171547 1

05-OCT-09 03:00 HAL .97749196 1

05-OCT-09 03:00 QSM .96631206 1

05-OCT-09 03:00 RUH .97580297 1

05-OCT-09 04:00 HAL .97039474 1

05-OCT-09 04:00 QSM .96422018 1

05-OCT-09 04:00 RUH .97830258 1

05-OCT-09 05:00 HAL .99322034 1

05-OCT-09 05:00 QSM .97240723 1

05-OCT-09 05:00 RUH .96394086 1

05-OCT-09 06:00 HAL .86006826 1

05-OCT-09 06:00 QSM .94129159 1

05-OCT-09 06:00 RUH .93817499 1

05-OCT-09 07:00 HAL .97619048 1

05-OCT-09 07:00 QSM .98960499 1

05-OCT-09 07:00 RUH 1.01351351 1

05-OCT-09 08:00 HAL 1.07723577 1

05-OCT-09 08:00 QSM 1.11239496 1

05-OCT-09 08:00 RUH 1.07209877 1

05-OCT-09 09:00 HAL 1.05660377 1

05-OCT-09 09:00 QSM 1.05288008 1

05-OCT-09 09:00 RUH 1.05419929 1

05-OCT-09 10:00 HAL 1.05714286 1

05-OCT-09 10:00 QSM 1.06457399 1

05-OCT-09 10:00 RUH 1.05913195 1

05-OCT-09 11:00 HAL 1.01013514 1

05-OCT-09 11:00 QSM 1.01179444 1

05-OCT-09 11:00 RUH 1.03492849 1

05-OCT-09 12:00 HAL 1.01003344 1

05-OCT-09 12:00 QSM .97002498 1

05-OCT-09 12:00 RUH .98937018 1

05-OCT-09 13:00 HAL .99668874 1

05-OCT-09 13:00 QSM .98798283 1

05-OCT-09 13:00 RUH 1.00993822 1

05-OCT-09 14:00 HAL 1.05980066 1

05-OCT-09 14:00 QSM 1.04604692 1

05-OCT-09 14:00 RUH 1.01023936 1

05-OCT-09 15:00 HAL 1.02821317 1

05-OCT-09 15:00 QSM 1.02574751 1

05-OCT-09 15:00 RUH 1.01171515 1

05-OCT-09 16:00 HAL .96036585 1

05-OCT-09 16:00 QSM .98218623 1

05-OCT-09 16:00 RUH .97215717 1

05-OCT-09 17:00 HAL .95555556 1

05-OCT-09 17:00 QSM .98598516 1

05-OCT-09 17:00 RUH .9760439 1

05-OCT-09 18:00 HAL .96345515 1

05-OCT-09 18:00 QSM .97073579 1

05-OCT-09 18:00 RUH 1.0076786 1

05-OCT-09 19:00 HAL 1.14137931 1

05-OCT-09 19:00 QSM 1.00861326 1

05-OCT-09 19:00 RUH 1.02054701 1

05-OCT-09 20:00 HAL .96374622 1

05-OCT-09 20:00 QSM .9675491 1

05-OCT-09 20:00 RUH .96773333 1

05-OCT-09 21:00 HAL .98119122 1

05-OCT-09 21:00 QSM .9788173 1

05-OCT-09 21:00 RUH .97451088 1

05-OCT-09 22:00 HAL .99361022 1

05-OCT-09 22:00 QSM .98016231 1

05-OCT-09 22:00 RUH .99773788 1

05-OCT-09 23:00 HAL .96784566 1

05-OCT-09 23:00 QSM .97424103 1

05-OCT-09 23:00 RUH .97775259 1

05-OCT-09 24:00 HAL 1.00996678 1

05-OCT-09 24:00 QSM 1.00849858 1

05-OCT-09 24:00 RUH .98637681 1

05-OCT-09 01:00 HAL 0 202975

05-OCT-09 02:00 HAL 0 202976

05-OCT-09 03:00 HAL 0 202977

05-OCT-09 04:00 HAL 0 202978

05-OCT-09 05:00 HAL 0 202979

05-OCT-09 06:00 HAL 0 202980

05-OCT-09 07:00 HAL 0 202981

05-OCT-09 08:00 HAL 0 202982

05-OCT-09 09:00 HAL 0 202983

05-OCT-09 10:00 HAL 0 202984

05-OCT-09 11:00 HAL 0 202985

05-OCT-09 12:00 HAL 0 202986

05-OCT-09 13:00 HAL 0 202987

05-OCT-09 14:00 HAL 0 202988

05-OCT-09 15:00 HAL 0 202989

05-OCT-09 16:00 HAL 0 202990

05-OCT-09 17:00 HAL 0 202991

05-OCT-09 18:00 HAL 0 202992

05-OCT-09 19:00 HAL 0 202993

05-OCT-09 20:00 HAL 0 202994

05-OCT-09 21:00 HAL 0 202995

05-OCT-09 22:00 HAL 0 202996

05-OCT-09 23:00 HAL 0 202997

05-OCT-09 24:00 HAL 0 202998

99 rows selected.

As can be seen from above, the changes did happen during the Night Shift after 1:00 AM.

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)

tajcorner

Monday, November 2, 2009

Wednesday, October 7, 2009

Investigating the Data Entry Time using Flashback Query

Solution to Hail Forecast Issue - (Oracle Application Specific)

My Favourites

Search This Blog

TAJSEC

Blog Archive

About Me