OBIEE ETL

1.1            Introduction

We have 5 Jobs which are scheduled in Oracle scheduler.

1)      DAILY_ETL_PROCESS1:- This Job runs daily 6 am morning and it calls PROC_BTCH_RUN () main wrapper procedure and it calls proc_bulk_data () procedure which contains 41 procedures.

2)    DAILY_ETL_PROCESS2 :- This Job runs daily 9:30 am morning and it calls   PROC_RUN_BHV_CPY() procedure. This procedure also runs at 6 am for NCDEX and at 9:30 it populates other exchange’s data like MCX, ICEX etc.

3)      DAILY_SPOT_ETL_PROCESS3:- This Job runs 7 am morning only for TUE, WED, THU, FRI, SAT and it calls PROC_BTCH_RUN_2 () procedure which contains PROCEDURE_SPOTPRCE_FACT ().

4)      DAILY_SPOT_ETL_PROCESS4:- This Job runs 6 pm evening only for SAT and it calls PROC_BTCH_RUN_2 () procedure which contains PROCEDURE_SPOTPRCE_FACT ().

5)      DAILY_ETL_PROCESS5:- It runs daily 8 am and it calls BATCH_RUN_BO () procedure and this procedure calls PROC_BO_REP () procedure which contains 15 procedures.

If you want to see how many Jobs are scheduled and their details in OBIEE then run the below query in STG schema.

SELECT job_name, repeat_interval, start_date, next_run_date, last_run_duration, last_start_date, schedule_limit, comments, JOB_ACTION  FROM all_scheduler_jobs;

1.2            Scheduled Batch Run

1. We have scheduled Daily batch Process at 6:00 AM, 7 AM, 9:30 AM, 8 AM, 6 PM .We can check whether daily batch process run successfully or not, by using following SQL script in STG schema.

SELECT inserted_at, tmx_inserted_time, is_this_etl_a_success_flag

FROM tmx_triangulation WHERE INSERTED_AT = batch_date AND

IS_THIS_ETL_A_SUCCESS_FLAG = 'N';

Here batch_date=previous working day or the day you want to run the procedure.

If the upper query returns any record then do the following steps.

Just go to STG schema and open the Views and click on Triangulation.

In Filter location put B_DT=’Batch Date’ and press enter.

It will show all the procedure’s name including running and failed if any for the given’ Batch Date’.

If any etl_success_flag =’N’ then that process was failed.

After solving that process we have to run the failed procedure and procedure which run after failed process according to their order in wrapper procedure.

Till 10-JAN-2012 total count of running procedure is 83.

                                                   

In below you can find the print screen of Triangulation view.

2. By using ERROR_LOG table, we check is there any error occurred during batch

Execution or not.

Depending on error found take the necessary action.

Select batch_date, etl_name, erro_no, description, record_number from

Error_log where batch_date = batch_date;

. 

Here batch_date=previous working day or the day you want to run the procedure.

1.3            Main Screen For Batch Process

• If Scheduled batch process will not execute successfully then following steps are required to re-run batch process. Here I am giving an example of ‘P_POPULATE_MCX_PRCE_VIEW ’. Its inputs are start date and end date. User can select process and right click on above process there is an option ‘RUN’.

Same process should be followed for remaining Procedures.

  

After selecting RUN option we will get following screen.

In place of ‘XSTART_DATE => XSTART_DATE’ put start date and replace ‘XEND_DATE => XEND_DATE’ with End Date.

Start date and end date will be last working day for which we will run the procedure.  Example is given below.

               

After click on OK Button process will execute. Process will exit after completion. Check in view STG.Triangulation to see Log of what has been done.  If there is an error checks in table STG.Error_Log and contact TCS OBIEE Team.

Important Note: - After solving the error just run the error procedure first and then the remaining

                                Procedures.                 

                             But don’t run Bhavcopy procedure, how to run it, describe below.

1.4            Main Screen For Other Exchange Bhavcopy

• If Scheduled batch process will not execute successfully then following steps are required to re-run batch process. This process is in OBIEE STG schema with name ‘PROC_RUN_BHV_CPY ‘.User can select process and right click on above process there is an option ‘RUN’. This process will select last working day and will execute other exchange process for that day. But here we have to change time if we run Bhavcopy for NCDEX after 6 am and for other exchange also.

The time should be hour when you will run this procedure.

Like if anyone run this procedure at 10:20 am for NCDEX then we have to change the “If” condition where exch_id_flag=1 and the time will be 10;

And comment the else if part.

                 

After changing the code compile that procedure.

Then right click on that Procedure and you will get run option.

After click on OK Button process will execute. Process will exit after completion. Check in table STG.Tmx_Triangulation to see Log of what has been done.  If there is an error checks in table STG.Error_Log and contact TCS OBIEE Team.

After populating Bhavcopy data revert back the code.

If Bhavcopy fails for other exchange also then follow the same process for NCDEX except the time change.

Here we have to change the “else if” part like below.

After changing the code compile that procedure.

Then right click on that Procedure and you will get run option.

After click on OK Button process will execute. Process will exit after completion. Check in table STG.Tmx_Triangulation to see Log of what has been done.  If there is an error checks in table STG.Error_Log and contact TCS OBIEE Team.

1.5            Conclusion

After running all the procedure go to STG schema-> view and open Triangulation view.

After putting B_DT=’Batch Date’ you can see all the procedure’s name for that batch_date.

Now check ETL_SUCCESS_FLAG’s value, if any ‘N’ is there then repeat from 1.2 to 1.3.