Oracle External Table with strong data type & datetime mask

Those who are familiar with Oracle Data Warehousing and ETL must be familiar with Oracle External Table. It is actually nothing but showing the file content in Oracle table by SQL Loader parsing.

I have seen some developers who always use external table without any strong data type i.e. they keep data types of all columns as varchar/varchar2. So, though the data should be datetime field or number field they put the data type of that field as varchar2 and do the type casting in stored procedures or views. This is certainly a bad practice due to the following reasons -

1. Explicit type casting is slower than the parsing time type imposing.
   
   
2. If data quality is not good it will be horrible to use varchar2 as all columns types. In case of using strong data type column, the rows in the files that do not meet the type constraints will go to the bad record and you will find the reason in log file why these records are discarded. While in case of weak type column, you will get lost during type casting. Because, these type miss-match records are loaded in the external table not in the bad file and during type casting in procedures or views you will get exception and you cannot identify which column and row in the file causes this problem.
   

Using strong data in external table is not at all a difficult task. You can easily do it by mentioning the type in external table definition. Your file access parameter can be same; you just need to change the column type in the external table. But there is one exception and that is the date type column.

For date type column, you need to change the access parameter also. It is not at all complex rather very easy. Just instead of using char as the field type in file access parameter use date and mention the date mask. As an example, to load date type data in date type column of external table we can use -

EVENT_TIME date 'YYYYMMDDHH24MISS'
instead of
EVENT_TIME char
where the sample data in this field is - 20110522180652

You see in the example date mask takes same pattern mask as the to_date() function. For more detail you can visit download.oracle.com and forums.oracle.com.

Task scheduler in Business Object XI

The purpose of task schedule in BO (or any other BI tool) is very much significant. By scheduling and viewing instances (An instance is a version of the object or report that contains data from the time that the object was run), you can ensure that the objects have the most up-to-date information available for viewing, printing, and distributing. Its purpose can be -

1. regular (or periodic) report refreshing so that the user will not face any delay when he sees the report or dash board.
2. checking the data integrity of data mart on regular basis without running the sql manually.
3. sending email attaching the report on regular basis.
   

Here are the steps:-

• Configure your job server from -
  administration launch pad > central management console > servers> respected server (e.g. Web_IntelligenceJobServer).
  Select proper destination. As an example, if you want to use email, you have to configure mail template and SMTP account configuration.
  
• Go to info view and find your report and click on the schedule hyperlink. Then schedule time (daily, monthly, ...), destination (default location, inbox or email group configured from job server).
• You have to make sure that you user proper OS authentication configure. You can check it from -
  Performance Management > Set up > Parameters > Scheduler Parameters > Scheduler Parameter.
• You can also troubleshoot your scheduler from -
  Performance Management > Set up > Tools >Check & Cleanup > Scheduled Program

For more detail, visit the http://www.sdn.sap.com and download the guide book.