Focal Point
[Sharing] Integrating with Informatica

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/258109154

May 27, 2010, 11:35 AM
Efrem
[Sharing] Integrating with Informatica
With WebFOCUS 4.3.6 Service Pack 2, a new feature called "Import from Sources" was added to Developer Studio whereby business metadata could be imported from Informatica's PowerCenter product.

This utility creates WebFOCUS metadata (Master File Description) by reading the metadata in the specified Database (SQL Server, Oracle, DB/2) and merges the business metadata from the PowerCenter repository. The business metadata include business name of table, business name of column, and description or narrative of column.

The Import from Sources utility is invoked by right-clicking on Master Files within an application in the WebFOCUS Environments section of the tree within
Developer Studio and selecting New/Import from Sources. The Informatica metadata adapter will appear.
The Import from Sources option shows up as disabled if the PowerCenter Client is not installed on the same machine as Developer Studio.

The Import From Sources utility prompts the user for Informatica authentication credentials before continuing with the metadata creation process.
A list of tables retrieved from the Informatica PowerCenter Repository from within Developer Studio is displayed.
The user will select the table(s) for the metadata that is to be created.
A prompt for the database containing the table(s) for the metadata to be created is displayed.
The Import from Sources utility would first perform a Create Synonym against the table(s) bringing in column metadata and then
automatically merge in the Business Names and Descriptions from the PowerCenter Repository.

FILE=CUSTOMERS, SUFFIX=SQLMSS, REMARK='Customer Master File (9.0)'
SEGNAME=CUSTOMERS, SEGTYPE=S0, $
FIELD=Customer_Number, ALIAS=CUSTOMER_ID, USAGE=I11, ACTUAL=I4, TITLE='Customer Number',
DESCRIPTION='Unique number representing a specific Ship-To customer', $
FIELD=Company_Name, ALIAS=COMPANY, USAGE=A50V, ACTUAL=A50V, MISSING=ON, TITLE='Company Name',
DESCRIPTION='Registered name of the company', $
FIELD=Given_name, ALIAS=FIRST_NAME, USAGE=A30V, ACTUAL=A30V, TITLE='Given name',
DESCRIPTION='First name of the main contact person', $
FIELD=Surname, ALIAS=LAST_NAME, USAGE=A30V, ACTUAL=A30V, TITLE='Surname',
DESCRIPTION='Last name of the main contact person', $
FIELD=Street_Address, ALIAS=ADDRESS1, USAGE=A72V, ACTUAL=A72V, TITLE='Street Address',
DESCRIPTION='Street number and Street of customer. Left blank if address is a P.O. Box', $
FIELD=PO_Box_Number, ALIAS=ADDRESS2, USAGE=A72V, ACTUAL=A72V, MISSING=ON, TITLE='PO Box Number',
DESCRIPTION='The PO Box for customer mailing address', $
FIELD=City, ALIAS=CITY, USAGE=A30V, ACTUAL=A30V, TITLE='City',
DESCRIPTION='The city for the customer location', $
FIELD=State_Province, ALIAS=STATE, USAGE=A2V, ACTUAL=A2V, TITLE='State Province',
DESCRIPTION='The state or province that the city is located in.', $
FIELD=Zip_Code_or_Postal_Code, ALIAS=POSTAL_CODE, USAGE=A10V, ACTUAL=A10V, TITLE='Zip Code or Postal Code',
DESCRIPTION='Zip Code for US addresses or Postal Code for Canadian addresses.', $
FIELD=Phone_Number, ALIAS=PHONE, USAGE=A30V, ACTUAL=A30V, TITLE='Phone Number',
DESCRIPTION='The phone number of the main contact', $
FIELD=Email_Address, ALIAS=EMAIL, USAGE=A30V, ACTUAL=A30V, MISSING=ON, TITLE='Email Address',
DESCRIPTION='The email address of the main contact.', $

It is also possible to report directly against the REP_FLD_MAPPING view within the PowerCenter Repository.
This give the user the ability to report against the data lineage for a specific target table.

TABLE FILE REP_FLD_MAPPING
PRINT
TARG_COL_BUSNAME AS 'Target Column Business Name'
SOURCE_NAME AS 'Source Name'
SOURCE_FIELD_NAME AS 'Source Column Name'
SRC_FLD_BUSNAME AS 'Source Column Business Name'
TRANS_EXPRESSION AS 'Transformation Expression'
BY TARGET_NAME AS 'Target Name'
BY TARGET_COLUMN_NAME AS 'Target Column Name'
WHERE TARGET_NAME EQ 'T_ITEM_SUMMARY';
END

If the Web Services Adapter is being licensed, a WebFOCUS Report can be used to invoke a specific workflow within PowerCenter.
It makes use of the Web Services packaged with Informatica's PowerCenter product.
This is useful when Informatica is used to create a datamart and then WebFOCUS is used to produce reports against the datamart.

TABLE FILE LOGIN
PRINT
SESSIONID
WHERE ( REPOSITORYNAME EQ 'PowerCenterRepository' ) AND ( USERNAME EQ 'admin' ) AND ( PASSWORD EQ 'admin' );
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &TOKEN.A200.

TABLE FILE STARTWORKFLOW
PRINT
FOLDERNAME
WORKFLOWNAME
WHERE ( SESSIONID EQ '&TOKEN' ) AND ( DOMAINNAME EQ 'Domain_IBI' ) AND ( SERVICENAME EQ 'PowerCenterIntegration' )
AND ( WORKFLOWNAME EQ 'wf_PhoneList' ) AND ( FOLDERNAME EQ 'Tutorial_Efrem' ) AND ( REQUESTMODE EQ 'NORMAL' ) AND ( ISABORT EQ 'true' );
END
May 30, 2010, 10:01 AM
Efrem
I just realized that the Import from Sources functionality might have to be licensed.
You might want to check with your local Information Builders branch.