Saturday, October 29, 2022

ODI 12C - Importing flat file to ODI studio

Usecase: Here, we will see how to import a flat .csv file in ODI studio.

High level steps:

  • Under Topology, choose physical architecture >> File Technology and create new data server and new physical schema with the file location.
  • Under Topology, choose logical architecture >> File Technology and create new logical schema.
  • Under Design >> model >> create new model or use existing model >>select Technology and logical schema 
  • Under model, create new datastore >> provide resource file name >> files format 
  • Over data store >> view data
If you want to have variable file name, then create a variable in a project and use it as refresh variable and use it in the model data store resource name as #procjectcode.variablename

Detailed Steps with screenshots:





















Tuesday, October 25, 2022

ODI 12C - Write file names from OS location to database

Usecase: Here we will use a procedure and jython script to take file names from OS location or mount location and write them to a database table. 

Step1: Create a database table to store the file names and other params

Create table poc_param_tbl (

SNO NUMBER,

FILE_NAME VARCHAR2(500),

PROCESSED_FLAG VARCHAR2(1),

INSERT_DT TIMESTAMP(6)

UPDATE_DT TIMESTAMP(6)

);

Step2: create database physical and logical architecture connection under Topology:










Step3: create a project, a variable and create a new procedure with 2 tasks - to trucate the table and to run the jython code.















Jython script:

import os
import datetime

sc=odiRef.getJDBCConnection("SRC")
cnt=0
filepath="#POC.LV_EPM_OBJ_STORAGE_SRC_FOL_PATH"
ct=datetime.datetime.now()
setFileNameQuery="insert into POC_param_tbl(sno, file_name,insert_dt,update_dt,processed_flag) values(?,?,?,?,?)"

for files in os.listdir(filepath):
   if files.endswith(".csv"):
      cnt =cnt+1
      sqls=sc.prepareStatement(setFileNameQuery)
sqls.setInt(1,cnt)
sqls.setString(2,files)
sqls.setTimestamp(3,ct)
sqls.setTimestamp(4,ct)
sqls.setString(5,"N")
executeStatement=sqls.executeUpdate()
sqls.close()

Notes:

1. Suppose any patching is done to the server where ODI studio installed, then we need to clear the cache and restart odi studio otherwise it will fail with "Script Engine not present error"

Path:

Cd /u01/oracle/mwh/odi/studio

./odi.sh -clean -initialize

2. Here, we have hardcoded the sourcr path in the project variable, that we can use as refresh variable and fetch from the database table.


Sunday, October 2, 2022

ODI - Why ODI has two J$ views with CDC and Journalizing

The J$D view is called the data view. It stores the details of changed data. Other view stores status of data consumption by subscribers.




Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...