Expression Transformation

Expression Transformation Overview:

Transformation type:

Passive

Connected

Use the Expression transformation to calculate values in a single row. For example, you might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers. You can also use the Expression transformation to test conditional statements before you pass the results to a target or other transformations.

Use the Expression transformation to perform non-aggregate calculations. To perform calculations involving multiple rows, such as sums or averages, use the Aggregator transformation.

The naming convention for an Expression transformation is EXP_Transformation Name.

You can enter multiple expressions in a single Expression transformation by creating an expression for each output port.

Note: After you make the transformation reusable, you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation.

• Passive and connected transformation.

Use the Expression transformation to calculate values in a single row before we write to the target. For example, we might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers.

Use the Expression transformation to perform any non-aggregate calculations.

Example: Addition, Subtraction, Multiplication, Division, Concat, Uppercase conversion, lowercase conversion etc.

We can also use the Expression transformation to test conditional statements before we output the results to target tables or other transformations. Example: IF, Then, Decode

There are 3 types of ports in Expression Transformation:

• Input
• Output
• Variable: Used to store any temporary calculation.

Calculating Values:

To use the Expression transformation to calculate values for a single row, we must include the following ports:

• Input or input/output ports for each value used in the calculation: For example: To calculate Total Salary, we need salary and commission.
•  Output port for the expression: We enter one expression for each output port. The return value for the output port needs to match the return value of the expression.

We can enter multiple expressions in a single Expression transformation. We can create any number of output ports in the transformation.

Example: Calculating Total Salary of an Employee

• Import the source table EMP in Shared folder. If it is already there, then don’t  import.
• In shared folder, create the target table Emp_Total_SAL. Keep all ports as in EMP table except Sal and Comm in target table. Add Total_SAL port to store the calculation.
• Create the necessary shortcuts in the folder.

Creating Mapping:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping -> Create -> Give mapping name. Ex: m_totalsal
4. Drag EMP from source in mapping.
5. Click Transformation -> Create -> Select Expression from list. Give name and click Create. Now click done.
6. Link ports from SQ_EMP to Expression Transformation.
7. Edit Expression Transformation. As we do not want Sal and Comm in target, remove check from output port for both columns.
8. Now create a new port out_Total_SAL. Make it as output port only.
9. Click the small button that appears in the Expression section of the dialog box and enter the expression in the Expression Editor.
10. Enter expression SAL + COMM. You can select SAL and COMM from Ports tab in expression editor.
11. Check the expression syntax by clicking Validate.
12. Click OK -> Click Apply -> Click OK.
13. Now connect the ports from Expression to target table.
14. Click Mapping -> Validate
15. Repository -> Save

Create Session and Workflow as described earlier. Run the workflow and see the data in target table.

As COMM is null, Total_SAL will be null in most cases. Now open your mapping and expression transformation. Select COMM port, In Default Value give 0. Now apply changes. Validate Mapping and Save.

Refresh the session and validate workflow again. Run the workflow and see the result again.

Now use ERROR in Default value of COMM to skip rows where COMM is null.

Syntax: ERROR(‘Any message here’)

Similarly, we can use ABORT function to abort the session if COMM is null.

Syntax: ABORT(‘Any message here’)

Make sure to double click the session after doing any changes in mapping. It will prompt that mapping has changed. Click OK to refresh the mapping. Run workflow after validating and saving the workflow.

Performance Tuning :

Expression transformation is used to perform simple calculations and also to do Source lookups.

1. Use operators instead of functions.
2. Minimize the usage of string functions.

If we use a complex expression multiple times in the expression transformer, then Make that expression as a variable. Then we need to use only this variable for all computations.

QUESTIONS:

1. What is an expression transformation?

An expression transformation is used to calculate values in a single row.

Example: salary+1000

2. How to generate sequence numbers using expression transformation?

Create a variable port in expression transformation and increment it by one for every row. Assign this variable port to an output port.

3.  How to duplicates from expression transformation without using sorter before that

1) we can distinguish between unique and duplicate records in Informatica by using forward reference technique ,in the sense, we can store the previous rec key value in Variable_Port and can be compared with the previous rec key value , but Data has to be come in sorted order on that key column , for that we should use sorter transformation.

once if you find the duplicate rec by comparing , you can flag that rec as duplicate and you can divert it to other target.

2) Without using sorter

in order to collect only unique records for flat file , just use aggregator before exp transformation, and check group by on the key column where you are expecting duplicate records. so you will get only unique records and duplicate records will be eliminated.

4. Consider the following employees data as source?

Employee_id, Salary

-------------------

10,          1000

20,          2000

30,          3000

40,          5000

5. Design a mapping to load the cumulative sum of salaries of employees into target table?
The target table data should look like as

Employee_id, Salary, Cumulative_sum

-----------------------------------

10,          1000,      1000

20,          2000,      3000

30,          3000,      6000

40,          5000,      11000

6. Design a mapping to get the previous row salary for the current row. If there is no previous row exists for the current row, then the previous row salary should be displayed as null.
The output should look like as

Employee_id, Salary, Pre_row_salary

-----------------------------------

10,          1000,      Null

20,          2000,      1000

30,          3000,      2000

40,          5000,      3000

7. Consider the following employees table as source

Department_no, Employee_name

----------------------------

20,               R

10,               A

10,               D

20,               P

10,               B

10,               C

20,               Q

20,               S

8. Design a mapping to load a target table with the following values from the above source?

Department_no, Employee_list

----------------------------

10,            A

10,            A,B

10,            A,B,C

10,            A,B,C,D

20,            A,B,C,D,P

20,            A,B,C,D,P,Q

20,            A,B,C,D,P,Q,R

20,            A,B,C,D,P,Q,R,S

9. Design a mapping to load a target table with the following values from the above source?

Department_no, Employee_list

----------------------------

10,            A

10,            A,B

10,            A,B,C

10,            A,B,C,D

20,            P

20,            P,Q

20,            P,Q,R

20,            P,Q,R,S

ADD REMOTE USER

1. Go to RUN
2. Type lusrmgr.msc

3. 
4. Select Users → Right click in the work area → click on new user → Provide below information → click on create
5. 
6. Double click on user RRITRC → click on Member of → click on add →
7. type Remote Desktop Users → Click on OK
8. 
9. Again ok
10. 

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