Oracle: Server & Client communication

The transaction proceeds as follows:

• The client sends a request for data.
• Oracle Net Services packages the request and sends it to the TNS.
• TNS routes the packaged request to the server.
• Oracle Net Services on the server side unpackages the request and sends it to Oracle Database 10g.
• Oracle Database 10g processes the request and sends the requested data to Oracle Net Services.
• Oracle Net Services packages the data and sends it to TNS.
• TNS routes the data to the client.
• Oracle Net Services on the client side unpackages the data and sends it to the application.

Oracle Date Types

PL/SQL is a Programming Language with SQL commands. Since oracle is an RDBMS, we can not define our own programs by only using it. That’s why it supports a language called PL/SQL. We can compile the sql and non sql statements for performing an action that is related to either a data in the data base or not related to data base.

Oracle Data types :

The information in a database is maintained in the form of table, each table consists of rows and columns to store the data. A particular column in a table must contain similar data, which is of a particular type.

The following are different data types supported by ORACLE

1. CHAR This data type is used to store fixed length character of the specified length. Where the maximum size is 255 bytes for columns/rows.

Syntax: char (size)

Example : Result char(4)

2. VARCHAR2 This data type is used to store variable length characters.

Maximum it can take is 2000 bytes for columns/row.

Syntax: varchar2 (size)

Example : sname varchar2(15)

3. NUMBER this data type is used to store both numbers and numbers with decimal pointes. It can take maximum precision up to 38 digits after decimal.

Syntax: Number(value, precisions)

Example : Empno number(5) -> Pure Integers

Sal number(6,2) -> Numbers With Decimals

4. DATE This data type is used to store date and time in a table. The date data types stores year (including the century) . the month, the days, hours, minutes, seconds. The maximum size is 7 bytes for each row in a table.

Syntax: Date

Example : Doj Date

5. LONG This data type is used to store variable length character containing up to 2 GB of information.

Syntax: Long

Example : Remarks Long

Restriction of Long

There are some restrictions of long data type.

1) Only one column is defined as long for table.

2) Long columns con not be indexed.

3) Long columns can’t appear in integrity constraints.

4) Long columns can’t be used in SQL expressions.

5) Long columns cannot be referenced by the SQL function

Type of repositories

Informatica PowerCenter includes following type of repositories:

• Standalone Repository: A repository that functions individually and this is unrelated to any other repositories.

• Global Repository: This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.

• Local Repository: Local repository is within a domain and it’s not a global repository. Local repository can connect to a global repository using global shortcuts and can use objects in it’s shared folders.

• Versioned Repository: This can either be local or global repository but it allows version control for the repository. A versioned repository can store multiple copies, or versions of an object. This features allows to efficiently develop, test and deploy metadata in the production environment.

Workflow Manager

Overview:

In the Workflow Manager, we define a set of instructions called a workflow to execute mappings we build in the Designer. Generally, a workflow contains a session and any other task we may want to perform when you run a session. Tasks can include a session, email notification, or scheduling information. You connect each task with links in the workflow.

We can also create a worklet in the Workflow Manager. A worklet is an object that groups a set of tasks. A worklet is similar to a workflow, but without scheduling information. You can run a batch of worklets inside a workflow.

After We create a workflow, We run the workflow in the Workflow Manager and monitor it in the Workflow Monitor.

Workflow Manager Tools

To create a workflow, we first create tasks such as a session, which contains the mapping you build in the Designer. We can then connect tasks with conditional links to specify the order of execution for the tasks we created. The Workflow Manager consists of three tools to help we develop a workflow:

• Task Developer. Use the Task Developer to create tasks you want to run in the workflow.
• Workflow Designer. Use the Workflow Designer to create a workflow by connecting tasks with links. We can also create tasks in the Workflow Designer as we develop the workflow.
• Worklet Designer. Use the Worklet Designer to create a worklet.

Workflow Tasks

We can create the following types of tasks in the Workflow Manager:

• Assignment. Assigns a value to a workflow variable.
• Command. Specifies a shell command to run during the workflow.
• Control. Stops or aborts the workflow.
• Decision. Specifies a condition to evaluate.
• Email. Sends email during the workflow.
• Event-Raise. Notifies the Event-Wait task that an event has occurred.
• Event-Wait. Waits for an event to occur before executing the next task.
• Session. Runs a mapping you create in the Designer.
• Timer. Waits for a timed event to trigger.

Transformations Lists and Overview

Transformations Overview:
A transformation is a repository object that can be generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. 

Transformations in a mapping represent the operations the Integration Service performs on the data. Data passes through transformation ports that you link in a mapping or mapplet.

 Transformations can be active or passive. Transformations can be connected to the data flow, or they can be unconnected.

Active: A transformation that can Change the number of rows that pass through the transformation

Passive: A transformation does not change the number of rows that pass through the transformation

Note: Transformation may be unconnected like Store Procedure, Un-Connected Lookup.

List of Transformation and its Descriptions:

SourceQualifier:

Source Qualifier is a Active/Connected transformation. It Represents the rows that the Integration Service reads from a relational or flat file source when it runs a session.

Aggregator:

Aggregator is an Active/Connected transformation. It Performs aggregate calculations like Sum, Max, Min, Avg, Count,..etc.

Application Source Qualifier:

Application Source Qualifier is Active/Connected transformation. Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a session.

Expression:

Expression is Passive/Connected transformation. It Calculates a value in a single row.

Filter:

Filter is Active/Connected transformation. It Filters data.

Joiner:

Joiner is a Active/Connected transformation. It Joins data from different databases or flat file systems.

Lookup:

Lookup is a Active or Passive/Connected or Unconnected transformation. It Look up and return data from a flat file, relational table, view, or synonym.

Normalizer:

Normalizer is a Active/Connected transformation. It is used as Source qualifier for COBOL sources. Can also use in the pipeline to normalize data from relational or flat file sources.

Rank:

Rank is a Active/Connected transformation. It limits records to a top or bottom range.

Router:

Router is a Active/Connected transformation. It Routes data into multiple transformations based on group conditions.

SequenceGenerator:

Sequence Generator is a Passive/Connected transformation. It Generates primary keys.

Sorter:
Sorter is a Active/Connected transformation. It Sorts data based on a sort key.

SQL:

SQL is a Active or Passive/Connected transformation. It Executes SQL queries against a database.

StoredProcedure:

Stored Procedure is a Passive/Connected or Unconnected transformation. It Calls a stored procedure.

Transaction Control:

Transaction Control is a Active/Connected transformation. It defines commit and rollback transactions.

Union:
Union is a Active/Connected transformation. It Merges data from different databases or flat file systems.

UpdateStrategy:

Update Strategy is a Active/Connected transformation. It Determines whether to insert, delete, update, or reject rows.

XML:
XML Generator is a Active/Connected transformation. It Reads data from one or more input ports and outputs XML through a single output port.

XML Parser is a Active/Connected transformation. It Reads XML from one input port and outputs data to one or more output ports.

XML Source Qualifier is a Active/Connected transformation. It Represents the rows that the Integration Service reads from an XML source when it runs a session.

PowerCenter Client

The PowerCenter Client application consists of the tools to manage the repository and to design mappings, mapplets, and sessions to load the data. The PowerCenter Client application has the following tools:

1. Designer. Use the Designer to create mappings that contain transformation instructions for the Integration Service.
2. Mapping Architect for Visio. Use the Mapping Architect for Visio to create mapping templates that generate multiple mappings.
3. Repository Manager. Use the Repository Manager to assign permissions to users and groups and manage folders.
4. Workflow Manager. Use the Workflow Manager to create, schedule, and run workflows. A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data.
5. Workflow Monitor. Use the Workflow Monitor to monitor scheduled and running workflows for each Integration Service.

Informatica Transformations 3

Following are the list of Transformations available in Informatica:

Aggregator Transformation Application Source Qualifier Transformation Custom Transformation Data Masking Transformation Expression Transformation External Procedure Transformation Filter Transformation HTTP Transformation Input Transformation Java Transformation Joiner Transformation Lookup Transformation Normalizer Transformation Output Transformation Rank Transformation Reusable Transformation Router Transformation Sequence Generator Transformation Sorter Transformation Source Qualifier Transformation SQL Transformation Stored Procedure Transformation Transaction Control Transaction Union Transformation Unstructured Data Transformation Update Strategy Transformation XML Generator Transformation XML Parser Transformation XML Source Qualifier Transformation Advanced External Procedure Transformation External Transformation

In the following pages, we will explain all the above Informatica Transformations and their significances in the ETL process in detail. 

Aggregator Transformation

Aggregator transformation performs aggregate funtions like average, sum, count etc. on multiple rows or groups. The Integration Service performs these calculations as it reads and stores data group and row data in an aggregate cache. It is an Active & Connected transformation.

Difference b/w Aggregator and Expression Transformation? Expression transformation permits you to perform calculations row by row basis only. In Aggregator you can perform calculations on groups.

Aggregator transformation has following ports State, State_Count, Previous_State and State_Counter.

Components: Aggregate Cache, Aggregate Expression, Group by port, Sorted input.

Aggregate Expressions: are allowed only in aggregate transformations. can include conditional clauses and non-aggregate functions. can also include one aggregate function nested into another aggregate function.

Aggregate Functions: AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN, PERCENTILE, STDDEV, SUM, VARIANCE

Application Source Qualifier Transformation Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a session.It is an Active & Connected transformation.

Custom Transformation

It works with procedures you create outside the designer interface to extend PowerCenter functionality. calls a procedure from a shared library or DLL. It is active/passive & connected type.

You can use CT to create T. that require multiple input groups and multiple output groups.

Custom transformation allows you to develop the transformation logic in a procedure. Some of the PowerCenter transformations are built using the Custom transformation. Rules that apply to Custom transformations, such as blocking rules, also apply to transformations built using Custom transformations. PowerCenter provides two sets of functions called generated and API functions. The Integration Service uses generated functions to interface with the procedure. When you create a Custom transformation and generate the source code files, the Designer includes the generated functions in the files. Use the API functions in the procedure code to develop the transformation logic.

Difference between Custom and External Procedure Transformation? In Custom T, input and output functions occur separately.The Integration Service passes the input data to the procedure using an input function. The output function is a separate function that you must enter in the procedure code to pass output data to the Integration Service. In contrast, in the External Procedure transformation, an external procedure function does both input and output, and its parameters consist of all the ports of the transformation.

Data Masking Transformation

Passive & Connected. It is used to change sensitive production data to realistic test data for non production environments. It creates masked data for development, testing, training and data mining. Data relationship and referential integrity are maintained in the masked data. For example: It returns masked value that has a realistic format for SSN, Credit card number, birthdate, phone number, etc. But is not a valid value. Masking types: Key Masking, Random Masking, Expression Masking, Special Mask format. Default is no masking.

Expression Transformation

Passive & Connected. are used to perform non-aggregate functions, i.e to calculate values in a single row. Example: to calculate discount of each product or to concatenate first and last names or to convert date to a string field.

You can create an Expression transformation in the Transformation Developer or the Mapping Designer. Components: Transformation, Ports, Properties, Metadata Extensions.

External Procedure

Passive & Connected or Unconnected. It works with procedures you create outside of the Designer interface to extend PowerCenter functionality. You can create complex functions within a DLL or in the COM layer of windows and bind it to external procedure transformation. To get this kind of extensibility, use the Transformation Exchange (TX) dynamic invocation interface built into PowerCenter. You must be an experienced programmer to use TX and use multi-threaded code in external procedures.

Filter Transformation

Active & Connected. It allows rows that meet the specified filter condition and removes the rows that do not meet the condition. For example, to find all the employees who are working in NewYork or to find out all the faculty member teaching Chemistry in a state. The input ports for the filter must come from a single transformation. You cannot concatenate ports from more than one transformation into the Filter transformation. Components: Transformation, Ports, Properties, Metadata Extensions.

HTTP Transformation

Passive & Connected. It allows you to connect to an HTTP server to use its services and applications. With an HTTP transformation, the Integration Service connects to the HTTP server, and issues a request to retrieves data or posts data to the target or downstream transformation in the mapping. Authentication types: Basic, Digest and NTLM. Examples: GET, POST and SIMPLE POST.

Java Transformation

Active or Passive & Connected. It provides a simple native programming interface to define transformation functionality with the Java programming language. You can use the Java transformation to quickly define simple or moderately complex transformation functionality without advanced knowledge of the Java programming language or an external Java development environment.

Joiner Transformation

Active & Connected. It is used to join data from two related heterogeneous sources residing in different locations or to join data from the same source. In order to join two sources, there must be at least one or more pairs of matching column between the sources and a must to specify one source as master and the other as detail. For example: to join a flat file and a relational source or to join two flat files or to join a relational source and a XML source.
The Joiner transformation supports the following types of joins:

• Normal

Normal join discards all the rows of data from the master and detail source that do not match, based on the condition.

• Master Outer

Master outer join discards all the unmatched rows from the master source and keeps all the rows from the detail source and the matching rows from the master source.

• Detail Outer

Detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.

• Full Outer

Full outer join keeps all rows of data from both the master and detail sources.

Limitations on the pipelines you connect to the Joiner transformation:
*You cannot use a Joiner transformation when either input pipeline contains an Update Strategy transformation.
*You cannot use a Joiner transformation if you connect a Sequence Generator transformation directly before the Joiner transformation.

Lookup Transformation

Passive & Connected or UnConnected. It is used to look up data in a flat file, relational table, view, or synonym. It compares lookup transformation ports (input ports) to the source column values based on the lookup condition. Later returned values can be passed to other transformations. You can create a lookup definition from a source qualifier and can also use multiple Lookup transformations in a mapping.

You can perform the following tasks with a Lookup transformation:
*Get a related value. Retrieve a value from the lookup table based on a value in the source. For example, the source has an employee ID. Retrieve the employee name from the lookup table.
*Perform a calculation. Retrieve a value from a lookup table and use it in a calculation. For example, retrieve a sales tax percentage, calculate a tax, and return the tax to a target.
*Update slowly changing dimension tables. Determine whether rows exist in a target.

Lookup Components: Lookup source, Ports, Properties, Condition.
Types of Lookup:
1) Relational or flat file lookup.
2) Pipeline lookup.
3) Cached or uncached lookup.
4) connected or unconnected lookup.