CALLING FUNCTION IN ODI

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SDBU schema as source
2. Reverse engineer EMP table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Create target table in Target Schema using below script

Create Table Tgt_function  (     Empno  Number ,     Ename  Varchar2(20),     Sal    Number ,     Deptno Number ,     Dname  Varchar2(20)  )

2. Reverse engineer into TARGET MODEL

STEP 3 : Create database function

1. Create Database Function

CREATE OR REPLACE  FUNCTION RR_GET_DNAME     (      pDEPTNO IN NUMBER)     RETURN VARCHAR2  IS     DNAME VARCHAR2(11);  BEGIN     SELECT DNAME INTO DNAME FROM DEPT WHERE DEPTNO = pDEPTNO;     RETURN(DNAME);  END;

STEP 4 : Creating Interface/Mapping

1. Create interface with the name of INT_CALL_FUNCTION
2. Click on mapping tab → Drag and drop source (EMP)and target (Tgt_function)in respective locations

3. Select DNAME column → In properties window → under implementation → Click on Edit → call the function as shown → click on ok
4. 
5. Flow tab
6. Select Target and select IKM SQL Control Append
7. Select FLOW_CONTROL = False

8. Click on SAVE
9. Click on RUN
10. Observe output
11. 
12. 

Database EVALUATE Functions

Database Functions

Developers can create requests by directly calling database functions from either Oracle BI Answers or RPD. Key uses for these functions include the ability to pass through expressions to get advanced calculations, as well as the ability to access custom written functions or procedures on the underlying database

1.       EVALUATE

2.       EVALUATE_ANALYTIC

3.       EVALUATE_AGGR

4.       EVALUATE_PREDICATE

Exercise 4:  Find first space position in Name column of customer

1.    Create a logical column in RPD with the name of Name_Space_Position

2.    Develop below formula à drag and drop into presentation layer

 

3.    Develop a report as shown and observe output

SAMPLE INTERVIEWS

Sample Interview 1:'

1. In your previous project, what were your sources?
1. we can tell 
1. oracle apps
2. siebel
3. Peoplesoft
4. Jdedwards
5. Mainframes ..etc
2. Have you ever worked with MySequel or Essbase sources?
1. you can tell yes or no
3. If you have 1 fact and 4 dimension, which table would you import first?
1. Anything we can import
2. If we are loading data using ETL then first we need to load dimension tables then fact tables 
4. If you have two fact, can you join fact to fact.
1. No .if we join fact to fact we wont get star schemas hence we can not define content logical level
5. In the logical layer, the logical join, what is the cardinality?
1. It tells how the tables are related ,based on this obiee understands fact and dimension tables 
6. How do you use the chronological key
1. In case time based hierarchies it will do calculations based on this key
2. example : month level 12 +1 =1 and day level 31+1=1
7. Cache management?
1. Please refer class room notes
2. 
   
8. Have you used IBot?
1. IBOTS and AGENTS are same ,please refer class room notes 
9. Have you ever installed and what environment?
1. you can tell yes or no .but if you say YES you need to aware of LINUX /windows server installation process
2. http://rritec.blogspot.in/2014/09/obia-111181-installation.html
10. Have you ever did configurations on a linux environment?
1. Yes or NO .if you say YES then need to answer steps of configuration
11. Say that you have a report that is running for long hour, let's say 20 minutes, how do you debug it?
1. we will take SQL query of the report and as for the query we will apply aggregat tables /cache/hints/partition/log level/index creation..etc
12. What kind of security do you use?
1. LDAP
2. External table
13. where you involved in code migration from one environment to another?
1. Yes .refer classroom notes deployment chapter
14. Say you developed a code, how do you migrate the code changes to production?
1. using Catalog manger for more info refer deployment chapter
15. Can you give me a brief background of your obiee experience.
1. Refer project video of mine
16. Can you explain to me about your previous project?
1. Refer project video of mine

ODI 12c Installation

1. Install Oracle 11.2.0.3/4

2. http://rritec.blogspot.in/2014/09/02-oracle-database-installation.html
3. JDK 7
4. Download ODI12 C
1. http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html
5. Extract as shown below

5. In Welcome Screen click on NEXT

6. Provide required  installation directory or leave default → Click on NEXT

7. Select Enterprise installation → Click on NEXT

8. Click on NEXT

9. Click on NEXT

10. Click on NEXT

11. Click on FINISH

DATA SETS

DataSets

Using UNION ,INTERSECT ,UNION ALL MINUS

STEP 1 : Creating and Reverse Engineering Source

1. Connect to Source schema (SCOTT)
2. Execute below scripts

create table EMP10 AS select * from emp where deptno=10 create table EMP20 AS select * from emp where deptno=20

3. We can use EMP table of SCOTT schema as source
4. Reverse engineer EMP10 and EMP20  table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Connect to Target Schema (TDBU)

create table EMP1020 AS select * from scott.emp where 1=2

2. Reverse engineer into TARGET MODEL

STEP 3 : Creating Interface/Mapping

1. Create interface with the name of m_Data_Sets
2. Click on mapping tab → Drag and drop source (EMP10)and target (EMP1020) in respective locations

3. Click on add/Remove Data Set → Click on Add new dataset
4. change name as EMP20 → select  operator as UNION → Click on Close

5. Drag and drop EMP20 source into source work area
6. 
7. Click on Flow tab
8. Select Target and select IKM SQL CONTROL APPEND
9. Select FLOW_CONTROL = False

10. Click on SAVE
11. Click on RUN
12. Observe output
13. 

Sorting in ODI 11G

Sorting in ODI 11G

1. It is equal to ORDER BY clause in SQL
2. it is available as a component in ODI 12 C

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SCOTT schema as Source

STEP 2 : Creating and Reverse Engineering Target

1. Duplicate EMP data store and rename as TGT_EMP_SORT
2. Drag and drop into Target Model

STEP 3 : Change KM

1. Duplicate the existing KM IKM SQL Control Append
2. Rename as IKM SQL CONTROL APPEND SORT
3. Open this KM → Select Details → Double Click on Insert New Rows → end of the code add below code

<%=odiRef.getOption("ORDER_BY")%>

4. Right Click on IKM SQL CONTROL APPEND → Select New Option

5. Provide name and type  as shown → save and close the KM

STEP 4 : Creating Interface/Mapping

1. Create interface with the name of m_SORT_IN_ODI11G
2. Click on mapping tab → Drag and drop source EMP  and target  TGT_EMP_SORT in respective locations

3. Click on Flow tab
4. Select Target and select IKM SQL Control Append Sort → Change options
1. ORDER_BY : ORDER BY DEPTNO ,SAL
2. FLOW_CONTROL : False
3. Truncate :True
4. Create Target Table :True

5. Click on SAVE
6. Click on RUN → Observe Code

7. Observe Target Table data