Designer Tool in Informatica

 

What is the Informatica Designer?

The Informatica Designer is a client tool used to create instructions for the Integration Service. The instructions consist of source and target definitions, along with transformation objects used to transform the data.

 

 

Who uses the Informatica Designer

Informatica Designer is used by system administrators and developers, alike.

Designer Terminology and Icons

Source Analyzer – Used create, import, and view source objects.

Target Designer – Used to create, import, and view target objects. The target  designer can also be used to create tables in the database.

Transformation Developer – Used to create, edit and validate reusable transformations.

Mapplet Designer – Used to create, edit, and validate reusable mapplets.

Mapping Designer – Used to create, edit, and validate mappings the Integration Service will use to extract, transform and load the data.

Workspace Window – Used to view or edit objects. Only one workspace can be open at a time.

Navigator Window – Used to view objects in repositories. Navigator objects are dragged into the workspace.

 

Designer Terminology and Icons Client Tools:

Designer Primary key – Unique key used to maintain referential integrity and insert updates and deletes.

Port – A link that connects source, transformation, and target data together.

Transformation Types– Source Qualifier – generates and queries the sources tables for data extraction.

Expression – applies logic that transforms data. These transformations are flexible in their abilities.

Sequence Generator – generates a nd assigns unique key values.

Aggregator – performs aggregate functions such as average, sum, maximum, minimum, etc…

Lookup – performs queries on data outside of the source qualifier.

Filter – filters data that meet specific criteria.

Router – directs data that meet group conditions to different transformation or targets.

Update Strategy – contains logic that determines how inserted source data is applied to the target tables.

Mapplet Input – Gateway objects for ports in and out of mapplets.

Mapplet Output – Gateway objects for ports in and out of mapplets.

 

Functions of Informatica Designer

 

 1.To view and import sources

 2.To view and import targets

 3.To create mapplets

 4.To write or generate source extract query

 5.To create mappings

 6.To transform the data

 7.To cleanse the data

 8.To pass mapping parameters

 

To view and import sources:

 

1. To view a source object, enter the Source Analyzer tool.

2. Drag and drop the source table from the Sources folder into the “Source   Analyzer” workspace.

3. Double-click on the source to view source properties. Client Tools: Designer

4. Click on the “Columns” tab to view source columns and definitions.

5. To import object from source, enter the Source Analyzer tool.

6. Click on the “Sources” tab. Choose the correct import type.

7. To “Import from Database...”, enter the ODBC data source, username, and   password.

8. Highlight tables and click “OK.”

 

To view and import targets:

 

1. To view a target object, enter the Target Designer tool.

2. Drag and drop the target table from the Targets folder into the “Target       Designer” workspace.

3. Double-click on the target to view target properties.

4. Click on the “Columns” tab to view target columns and definitions.

5. To import object from target, enter the Target Designer tool.

6. Click on the “Targets” tab. Choose the correct import type.

7. To “Import from Database...”, enter the ODBC data source, username, and password.

8. Highlight tables and click “OK.”

 

To create mapplets:

1. Enter the Mapplet Designer tool.

2. Click on the “Mapplet” tab, and select “Create…” from the drop down menu.

3. Name your mapplet with prefix “mplt_.”

4. Drag and drop your source tables from the Sources folder in the navigator. The sources will appear green in your Mapplet Designer.

5. Add an Mapplet Output transformation by clicking on the Output icon and then clicking in the Mapplet Designer workspace.

6. Connect source qualifier ports to output transformation.

 

 To write or generate source extract query:

1.Double-click on the source qualifier box (yellow) in the Mapplet Designer workspace.

2.Click on the “Properties” tab.

3. Click on the icon in the row labeled “Sql Query.”

4. Click “Generate SQL” to view the SQL that will be executed at run time. Click “OK.”

 

To write or generate source extract query:

 

 

 

To create mappings:

 

1. Enter the Mapping Designer tool.

2. Click on the “Mapping” tab, and select “Create…” from the drop down menu. Name your mapping.

3. Drag and drop your mapplet from the Mapplet folder in the navigator. It will appear light blue in your Mapping Designer workspace.

4. Add a non-reuseable expression transformation to the mapping by clicking on the icon and then clicking in the Mapping Designer workspace.

5. Connect the ports from the mapplet to the expression transformation

6.Add a non-reuseable filter transformation to the mapping by clicking on the icon and then clicking in the Mapping Designer workspace

7.Connect the ports from the expression transformation to the filter transformation.

8. Drag and drop your target table from the Target folder into the Mapping Designer workspace.

9.Connect your filter transformation ports to your target table.

 

To transform the data:

1. In the mapping designer workspace, double-click on the expression transformation object. Select the “Ports” tab.

2.Click the port you would like to transform. Click on the icon in the upper right-hand corner.

3.Click on the icon to paste the port. Rename the new port with “OUT_” as a prefix.

4.Uncheck “O” for the original port, and uncheck “I” for the new port.

5. Click on the icon in the “Expression” column.

6. Highlight and delete the contents in the “Formula:” box.

7. Under the “Functions” tab, double-click on the function you want to apply to the port.

8. Under the “Ports” tab, double-click on the input port you want to transform. Click “OK.”

9. The new output port will appear as a new row of the transformation. Reconnect this new port to its associated port in the next transformation.

 

 

To cleanse the data:

1.   In the Mapping Designer tool, double-click on the filter transformation object

2.   Select the “Properties” tab.

3.Click on the icon to open the formula box.

4.In the “Formula:” box, enter the function and/or filtering condition on a port you would like to apply to your mapping. Click “OK.”

5.Client Tools: Designer Your filtering conditions do not have to include a function or a true or false statement. Ex: “STATE = ‘CA’” is a valid filter condition.

 

To pass mapping parameters:

1.Enter the mapping designer tool.

2. Click on the “Mappings” tab and select “Parameters and Variables…”

3.Click on the icon to add a new parameter.

4. Define the parameter name, datatype and precision. Also define the type as “Parameter” or “Variable.” Click “OK.”

5.In the mapping designer workspace, double click on the expression transformation. Click on the “Ports” tab.

6.Add new port by clicking on “Add” icon. Define port name, datatype and precision. Make an Output port by deselecting Input checkbox

7. Click on popup icon and select the “Variable” tab. Open “Mapping parameters” folder and double click desired parameter. Click “OK.”

8. Connect parameterized output port from expression transformation to the target table through the filter transformation.

 

 

BI Applications and Designer

•Mapplets generally contain the Source Qualifier transformation

•Source Qualifiers have “Sql Overrides”

•Common SDE/SIL Parameters

•Dimension Lookups in Fact Table SILOS

•System Target Table Columns

•Update Logic

 

 

Mapplets generally contain the Source Qualifier transformation:

SDE are most commonly developed by Oracle with a Source Qualifier transformation inside a mapplet. 

This creates a clean appearance and allows for the source extract to be reusable.

 

Source Qualifiers have SQL Overrides:

Almost all SDE mappings will contain SQL overrides written in the Source Qualifier transformation. 

This allows the SDE mappings to efficiently push table joins to the source and help spread resource consumption across the environments.

 

Common SDE/SIL Parameters:

Most mappings will contain at least two parameters within their transformations.

 

Dimensional Lookups in Fact Table SILOS:

Fact table SIL mappings will contain most dimensional table lookup transformations in mapplets. This also helps create cleaner mappings, which are easier to trace and debug.

 

System Target Table Columns:

All target tables contain 10 to 15 system columns. These columns are used for, but not limited to, maintaining insert/update history, update transformation references, delete status and source identifier.

 

Update Logic in SILOS:

SILOS mappings contain complex insert/update calculations. The logic varies by mappings, but generally the calculation compares the source with the target and calculates if there is a difference (Ex. SYSTEMS_COLS_DIFF) or if it should be soft deleted.