Como Criar uma Especificação de Mapeamento

Prévia do material em texto

How to Create a Mapping Specification
© 2012 Informatica Corporation. No part of this document may be reproduced or transmitted in any form, by any means
(electronic, photocopying, recording or otherwise) without prior consent of Informatica Corporation.
Abstract
In Informatica Analyst, you can create mapping specifications to define business logic that transforms and moves data from
source to target. Use a mapping specification to define business logic that populates a target table with data that you can
leverage to report on the target table.
Supported Versions
¨ Data Services 9.1.0 HotFix 3
¨ PowerCenter 9.1.0 HotFix 3
Table of Contents
Mapping Specification Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Before You Begin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Create Data Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Task 1. Create the Customers Flat File Data Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Task 2. Create the Accounts Flat File Data Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Task 3. Create the Customer_Accounts Table Data Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Create a Mapping Specification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Task 1. Create a Mapping Specification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Task 2. Edit the Customer_Accounts Target. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Task 3. Add a Simple Filter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Task 4. Create a Rule. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Task 5. Load the Mapping Specification Results to the Target. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Mapping Specification Overview
A mapping specification is an object that describes the movement and transformation of data from a source to a target. Use
a mapping specification to define business logic that populates a target table with data that you can leverage to report on the
target table.
For example, if you need to integrate current balance data from multiple branches in a financial organization, you can create
a mapping specification to develop business logic. The mapping specification generates current balance data for active
customer accounts for a set of financial products in the target table. You can then leverage the data in the target to run reports.
Before You Begin
You need to set up Informatica Analyst before you can create a mapping specification.
Complete the following tasks:
¨ Create a tutorial project and a folder called Customer in the project.
¨ You have a MySupport account to download the flat files and SQL script file to use as examples.
¨ Download the Accounts.txt and Customers.txt flat files to use as examples. You can download the Accounts.txt file 
here and the Customers.txt file here. You must have a MySupport account to download the flat files.
¨ You have the Oracle client installed to create the Customer_Accounts target table data object example.
2
https://communities.informatica.com/multimedia/onlinehelp/Accounts.txt
https://communities.informatica.com/multimedia/onlinehelp/Customers.txt
¨ You have Oracle database connectivity details.
¨ You have the target.sql table example script. You can download the script here. Use Oracle SQL Plus to run SQL
statements to create a target table.
Create Data Objects
In the Analyst tool, a data object is a representation of data based on a flat file or relational database table. You create a flat
file or table data objects and then use the flat file and table data objects in a mapping specification.
You can analyze data stored in flat files and relational tables and perform data integration tasks.
To create data objects, complete the following tasks:
¨ Upload the flat files to the flat file cache location and create flat file data objects.
¨ Import a target table data object into the Analyst tool.
¨ Preview the data for the data objects.
Task 1. Create the Customers Flat File Data Object
In this task, you use the Add Flat File wizard to create a flat file data objects from the Customers data file.
1. In the Navigator, select the Customers folder in your tutorial project.
Note: You must select the project or folder where you want to create the flat file data object before you can create
it.
2. On the Contents panel, click Actions > New > New Flat File.
The Add Flat File wizard appears.
3. Select Browse and Upload, and click Browse.
4. Browse to the location of Customers.csv and click Open.
5. Click Next.
The Choose type of import panel displays Delimited and Fixed-width options. The default option is Delimited.
6. Click Next.
7. Under Specify lines to import, select Import from first line to import column names from the first non-blank line.
8. Click Show.
The details panel updates to show the column headings from the first row.
9. Click Next.
The Column Attributes panel shows the datatype, precision, scale, and format for each column.
10. Edit the following column attributes:
Column Name Datatype Precision Scale
CHECKING_BALANCE Number 38 0
SAVINGS_BALANCE Number 38 0
11. Click Next.
12. Click Finish.
The Customers flat file data object appears in the folder contents for the Customers folder.
3
https://communities.informatica.com/multimedia/onlinehelp/Target.sql
Task 2. Create the Accounts Flat File Data Object
In this task, you use the Add Flat File wizard to create a flat file data objects from the Accounts data file.
1. In the Navigator, select the Customers folder in your tutorial project.
Note: You must select the project or folder where you want to create the flat file data object before you can create
it.
2. On the Contents panel, click Actions > New > New Flat File.
The Add Flat File wizard appears.
3. Select Browse and Upload, and click Browse.
4. Browse to the location of Accounts.csv and click Open.
5. Click Next.
The Choose type of import panel displays Delimited and Fixed-width options. The default option is Delimited.
6. Click Next.
7. Under Specify lines to import, select Import from first line to import column names from the first non-blank line.
8. Click Show.
9. Click Next.
The Column Attributes panel shows the datatype, precision, scale, and format for each column.
10. Click Next.
11. Click Finish.
The Accounts flat file data object appears in the folder contents for the Customers folder.
Task 3. Create the Customer_Accounts Table Data Object
In this task, you use the Add Tables Wizard to add a table to a project. To add a table, select the connection, select the
schema and tables, and add the table.
1. In the Navigator, select the Customers folder in your tutorial project.
2. On the Contents panel, click Actions > New > Table.
The Add Tables wizard appears.
3. Select a connection.
If a connection does not exist in the domain, create a connection.
4. Click Next.
5. Select the Customer_Accounts table.
6. Click Next.
The wizard displays the table to add to your folder or project.
7. Click Finish.
The Customer_Accounts table data object appears in the folder contents for the Customers folder.
4
Create a Mapping Specification
Develop a mapping specification that generates current balance data for active customer accounts for a set of financial
products in the target table.
Task 1. Create a Mapping Specification
In this task, you create a mapping specification with two sourcesand a target. You specify a normal join between the sources.
1. In the Navigator, select the Customers folder in your tutorial project.
2. On the Project Contents panel, click Actions > New > Mapping Specification.
The New Mapping Specification wizard appears.
3. Enter Customer_Data as the mapping specification name.
4. Select the Customers folder in your tutorial project.
5. Click Next.
6. On the Source Object panel, select the Accounts and Customers sources from the Customers folder.
Use the check boxes to select both sources.
7. Click Next.
8. In the Join Type field, enter Normal.
9. Click the Add button to enter a join condition that displays those customer accounts that have purchased financial
products.
Specify the following join condition:
¨ Name. Select the ACCOUNTS.ACCOUNT_CUSTOMER column.
¨ Operator. Select the "=" operator.
¨ Name. Select the CUSTOMERS.CUSTOMER column.
10. Click Next.
11. On the Target Object panel, select the Customer_Accounts table data object.
12. Click Finish.
The Customer_Data mapping specification opens in a new tab.
Task 2. Edit the Customer_Accounts Target
In this task, you drag the relevant source columns to the target columns to build the mapping specification logic for the target.
1. On the Sources panel of the mapping specification, drag the following Customers source columns to the target
columns in the Transformations and Target Columns panel:
Sources panel Transformation and Target Columns panel
CUSTOMER CUSTOMER
ACCOUNT_NO ACCOUNT_NO
PROD_ID PROD_ID
LAST_MONTH_AVERAGE LAST_MONTH_AVERAGE_BALANCE
STATUS STATUS
5
Sources panel Transformation and Target Columns panel
NAME NAME
YEARS_WITH_BANK YEARS_WITH_BANK
ADDRESS ADDRESS
CITY CITY
STATE STATE
POSTAL_CODE POSTAL_CODE
COUNTRY COUNTRY
PHONE PHONE
HOME_BRANCH HOME_BRANCH
CHECKING_BALANCE CHECKING_BALANCE
SAVINGS_BALANCE SAVINGS_BALANCE
2. On the Sources Panel of the mapping specification, drag the following Accounts source columns to the target
columns in the Transformations and Target Columns panel:
Sources panel Transformation and Target Columns panel
ACCOUNT_CUSTOMER ACCOUNT_CUSTOMER
ACCOUNT_NUMBER ACCOUNT_NUMBER
ACCOUNT_TYPE ACCOUNT_TYPE
ACCOUNT_NAME ACCOUNT_NAME
CURRENT_BALANCE CURRENT_BALANCE
3. Click Actions > Validate Mapping Specification to validate the mapping specification.
The Analyst tool displays a message that states the mapping specification is valid.
Task 3. Add a Simple Filter
In this task, you add a simple filter to the mapping specification to hide the "B" accounts that have the Bronze status and are
no longer valid.
1. On the Transformations and Target Columns panel, click Actions > Add > Filter.
The Edit Filter window appears.
2. Select Simple.
3. Click the Add button to add the following filter condition:
ACCOUNTS.ACCOUNTS.TYPE != B
4. Click Show to preview the data.
6
5. Click Save.
6. Click Actions > Validate Mapping Specification to validate the mapping specification.
The Analyst tool displays a message that states the mapping specification is valid.
Task 4. Create a Rule
In this task, you create a rule that sums up the current balances from the checking and savings accounts that have
increased by 15 percent.
1. On the Transformations and Target Columns panel, select the CURRENT_BALANCE target column.
2. Click Actions > Add > Rule.
3. Select Create a rule.
4. Click Next.
5. Enter the following expression in the Expression Editor:
(CUSTOMERS.CHECKING_BALANCE+CUSTOMERS.SAVINGS_BALANCE)*1.15
6. Click the Validate button to validate the expression.
The Analyst tool displays a message that states the expression is valid.
7. Click Finish.
Task 5. Load the Mapping Specification Results to the Target
In this task, you load the results of the mapping specification to the Customer_Accounts target table in the Model repository.
The Customer_Accounts target matches the structure and properties of the target in the mapping specification.
1. On the Customer_Data mapping specification tab, click Actions > Run Mapping Specification > Load to Data
Object.
The Load to Data Object wizard appears. The mapping specification run is named Customer_Data.
2. Click Next.
3. In the Target Object panel, select the CUSTOMER_ACCOUNTS target.
4. Click Finish.
The Analyst tool displays a message that states you can click the Job Status tab link to monitor the mapping
specification run.
5. Optionally, click Manage > Monitor Job Status to monitor the load operation on the Job Status tab.
Authors
Radhika Singh
Senior Technical Writer
Bridget Bevens
Technical Writer
7
	Abstract
	Supported Versions
	Table of Contents
	Mapping Specification Overview
	Before You Begin
	Create Data Objects
	Task 1. Create the Customers Flat File Data Object
	Task 2. Create the Accounts Flat File Data Object
	Task 3. Create the Customer_Accounts Table Data Object
	Create a Mapping Specification
	Task 1. Create a Mapping Specification
	Task 2. Edit the Customer_Accounts Target
	Task 3. Add a Simple Filter
	Task 4. Create a Rule
	Task 5. Load the Mapping Specification Results to the Target
	Authors