Installing ODI to Windows 7

If you receive a error message saying “this operating system is not supported” follow below operations.

Since Windows 7 is released as version 6.1, previous versions of ODI supports up to 6.0 depending on the version that you want to install. What you have to do to install ODI on Windows 7 platform is so simple.

  1. Find the file named, oraparam.ini in the ODI setup directory (default is \\InstallPath\ODI\setup\Windows\Disk1\install).
  2. Edit oraparam.ini and find the header [Certified Versions] or value named “CERTIFIED_VERSION_FAILURE_MESSAGE”.
  3. Add 6.1 next to 6.0 with comma without any space or other character. It should seem like that;

Windows=4.0,5.0,5.1,5.2,6.0,6.1

You can now run setup without asking version failure and follow on screen instructions.

ODI SendMail

Here is a brief description how you can handle ODI Sendmail command. This command is quite useful to send mails on errors or success in flow.
First of all ODISendMail can be used in ODI packages by drag and drop under in Toolbox. Here is a short explanation;

REFRESH CUBE : A simple procedure that starts the cube refreshment job.
P_CHECK_CUBES : A scenario that checks whether cube is refreshed or not. Succeeds when cube refresh process finishes successfully.
V_CUBE_MAIL_BODY : Refreshes a variable to be used in mail’s body section. Includes a select statement for descriptive usage.
OdiSendMail 25 : This is the pain point. Sends the prepared mail. Properties are below;
Continue reading

ODI Variables – General Know-How

  • Using a variable within a variable
    • If you want to use a ODI variable within a variable simply type variable name in refresh statement. Here is an exampls how you can do. Below SQL statement connects to MSSQL Server and returns if job (named “My_BI_Job”) succesfully finished or not in specific date . Variable #V_SYSDATE is a variable that is refreshed as SYSDATE from DUAL in Oracle database. If this doesn’t work try typing PROJECT_NAME after “#” sign. If you have multiples projects ODI confuses which project to get variable.

select count(1) from msdb.dbo.sysjobhistory
where step_id = 0
  and run_status = 1
  and job_id = (select job_id from msdb.dbo.sysjobs where name = ‘My_BI_Job’)
  and run_date = CAST(SUBSTRING(‘#V_SYSDATE’, 1,8) as integer)

Continue reading

Loading multiple files with ODI

If your source is file and you need to load multiple files into your database you need to do more than something. If you want to load your files using ODI interfaces you need to make some customization within ODI. There are some utilities or commands to load files into databases such as Sybase uses BCP, Oracle uses SQL Loader, MS SQL Server uses Bulk Insert. Some of them support multiple files, some of them support transformations. But there is a bit problem if name of the file is generated by source system, that means you don’t know the names of the files.

I want to explain a methodology loading multiple files into Oracle using external tables, which I think the best and easiest way.

First the environmental stuff and some prerequisites;

1. A Knowledge Module to load files using file name as a ODI Variable.
2. Some operating system folders for loading, rejected and log files.
3. A table named ETL_FILE_LOG to hold the file name in ODI Variable and to log in database.
4. 4 ODI Procedure getting file list, work with processed files, work with rejected files and rename files.
5. An ODI Interface to make transformations and loading.
6. 2 ODI Variables to manage file_name and file_id.
7. An ODI Package to manage everything.

Let’s start to do some work.

Continue reading