Informatica Reference

Archive for the ‘Informatica 9 Features’ Category

These are the new features in informatica 9.

  • Informatica 9 supports data integration for the cloud as well as on premise. You can integrate the data in cloud applications, as well as run Informatica 9 on cloud infrastructure.
  • Informatica analyst is a new tool available in Informatica 9.
  • There is architectural difference in Informatica 9 compared to previous version.
  • Browser based tool for business analyst is a new feature.
  • Data steward is a new feature.
  • Allows unified administration with a new admin console that enables you to manage power centre and power exchange from the same console.
  • Powerful new capabilities for data quality.
  • Single admin console for data quality, power centre, power exchange and data services.
  • In Informatica 9, Informatica data quality (IDQ) has been further integrated with the Informatica Platform and performance, manageability and reusability have all been significantly enhanced.
  • The mappings rules are shared between the browser based tool for analysts and the eclipse based development leveraging unified metadata underneath.
  • The data services capabilities in Informatica 9 , both over sql and web services ,can be used for real time dash boarding.
  • Informatica data quality provides world wide address validation support with integrated geocoding.
  • The ability to define rules and view and run profiles is available in both the Informatica developer (Thick client) and Informatica analyst (browser based tool-Thin client).these tools sit on a unified metadata infrastructure. Both tools incorporate security features like authentication and authorization ensuring..
  • The developer tool is now eclipse based and supports both data integration and data quality for enhanced productivity. It provides browser based tool for analysts to support the types of tasks they engage in, such as profiling data, specifying and validating rules & monitoring data quality.
  • There will a velocity methodology. Soon it’s going to introduce on I9.
  • Informatica has the capability to pull data from IMS, DB2 on series and series and from other several other legacy systems (Mainframe) environment like VSAM, Datacom, and IDMS etc.
  • There are separate tools available for different roles. The Mapping architect for Vision tool is designed for architects and developers to create templates for common data integration patterns saving developer’s tremendous amount of time.
  • Informatica 9 does not include ESB infrastructure.
  • Informatica supports open interfaces such as web services and can integrate with other tools that support these as well including BPM tool.
  • Informatica 9 complements existing BI architectures by providing immediate access to data through data virtualization, which can supplement the data in existing data warehouse and operational data store.
  • Informatica 9 supports profiling of Mainframe data. Leveraging the Informatica platform’s connectivity to Mainframe sources.
  • Informatica 9 will continue support feature of running the same workflow simultaneously.
  • Browser based tool is a fully functional interface for business analysts.
  • Dashboards are designed for business executives.
  • There are 3 interfaces through which these capabilities can be accessed. Analyst tool is a browsed tool for analyst and stewards. Developers can use the eclipse based developer tool. Line of business managers can view data quality scorecards

Refer : http://www.folkstalk.com/2010/02/new-features-of-informatica-9.html

Advertisements

Replaces characters in a string with another character pattern. By default, REG_REPLACE searches the input string for the character pattern you specify and replaces all occurrences with the replacement pattern. You can also indicate the number of occurrences of the pattern you want to replace in the string.

Syntax
REG_REPLACE( subject, pattern, replace, numReplacements )

Argument
Required/
Optional
Description
subject
Required
String datatype. Passes the string you want to search.
pattern
Required
String datatype. Passes the character string to be replaced. You must use perl compatible regular expression syntax. Enclose the pattern in single quotes. For more information, see “REG_EXTRACT” on page 113.
replace
Required String datatype. Passes the new character string.
numReplacements
Required
Numeric datatype. Specifies the number of occurrences you want to replace. If you omit this option, REG_REPLACE will replace all occurrences of the character string.

Return Value

String

Example

The following expression removes additional spaces from the Employee name data for each row of the Employee_name port:

REG_REPLACE( Employee_Name, ‘\s+’, ‘ ’)

Employee_Name                      Return Value
Adam Smith                                 Adam Smith
Greg      Sanders                         Greg Sanders
Sam            Cooper                      Sam Cooper

Returns whether a value matches a regular expression pattern. This lets you validate data patterns, such as IDs,telephone numbers, postal codes, and state names.

Note: Use the REG_REPLACE function to replace a character pattern in a string with a new character pattern.

Syntax
REG_MATCH( subject, pattern )

Argument
Required/
Optional
Description
subject
Required
String datatype. Passes the value you want to match against the regular expression pattern.
pattern
Required
String datatype. Regular expression pattern that you want to match. You must use perl compatible regular expression syntax. Enclose the pattern in single quotes. For more information, see “REG_EXTRACT” on page 113.

Return Value

TRUE if the data matches the pattern.

FALSE if the data does not match the pattern.

NULL if the input is a null value or if the pattern is NULL.

Example

You might use REG_MATCH in an expression to validate telephone numbers. For example, the following expression matches a 10-digit telephone number against the pattern and returns a Boolean value based on the match:

REG_MATCH (Phone_Number, ‘(\d\d\d-\d\d\d-\d\d\d\d)’ )

Phone_Number
Return Value
408-555-1212
TRUE
NULL
510-555-1212
TRUE
92 555 51212
FALSE
650-555-1212
TRUE
415-555-1212
TRUE
831 555 12123
FALSE

Tip

You can also use REG_MATCH for the following tasks:

  • To verify that a value matches a pattern. This use is similar to the SQL LIKE function.
  • To verify that values are characters. This use is similar to the SQL IS_CHAR function.

To verify that a value matches a pattern, use a period (.) and an asterisk (*) with the REG_MATCH function in an expression. A period matches any one character. An asterisk matches 0 or more instances of values that follow it.

For example, use the following expression to find account numbers that begin with 1835:

REG_MATCH(ACCOUNT_NUMBER, ‘1835.*’)

To verify that values are characters, use a REG_MATCH function with the regular expression [a-zA-Z]+. a-z matches all lowercase characters. A-Z matches all uppercase characters. The plus sign (+) indicates that there should be at least one character.

For example, use the following expression to verify that a list of last names contain only characters:

REG_MATCH(LAST_NAME, ‘[a-zA-Z]+’)

Extracts subpatterns of a regular expression within an input value. For example, from a regular expression pattern for a full name, you can extract the first name or last name.
Note: Use the REG_REPLACE function to replace a character pattern in a string with another character pattern.

Syntax
REG_EXTRACT( subject, ‘pattern’, subPatternNum )

Using perl Compatible Regular Expression Syntax
You must use perl compatible regular expression syntax with REG_EXTRACT, REG_MATCH and REG_REPLACE functions.

The following table provides perl compatible regular expression syntax guidelines:

Syntax
Description
(a period)
Matches any one character.
[a-z]
Measurement is the amount determined by observation.Matches one instance of a character in lower case. For example, [a-z] matches ab. Use [A-Z] to match characters in upper case.
\d
Matches one instance of any digit from 0-9.
\s
Matches a whitespace character.
\w
Matches one alphanumeric character, including underscore (_)
()
Groups an expression.For example, the parentheses in (\d-\d-\d\d) groups the expression \d\d-\d\d,which finds any two numbers followed by a hyphen and any two numbers, as in 12-34.
{}
Matches the number of characters. For example, \d{3} matches any three numbers, such as 650 or 510. Or, [a-z]{2} matches any two letters, such as CA or NY.
?
Matches the preceding character or group of characters zero or one time. For example, \d{3}(-{d{4})? matches any three numbers, which can be followed by a hyphen and any four numbers.
* (an asterisk)
Matches zero or more instances of the values that follow the asterisk. For example, *0 is any value that precedes a 0.
+
Matches one or more instances of the values that follow the plus sign. For example, \w+ is any value that follows an alphanumeric character.

For example, the following regular expression finds 5-digit U.S.A. zip codes, such as 93930, and 9-digit zip codes, such as 93930-5407:

\d{5}(-\d{4})?
\d{5} refers to any five numbers, such as 93930. The parentheses surrounding -\d{4} group this segment of the expression. The hyphen represents the hyphen of a 9-digit zip code, as in 93930-5407. \d{4} refers to any four numbers, such as 5407. The question mark states that the hyphen and last four digits are optional or can appear one time.

Converting COBOL Syntax to perl Compatible Regular Expression Syntax

If you are familiar with COBOL syntax, you can use the following information to write perl compatible regular expressions.

The following table shows examples of COBOL syntax and their perl equivalents:

Cobol Syntax
Cobol Syntax
Description
9
\d
Matches one instance of any digit from 0-9.
9999
\d\d\d\d
or
\d{4}
Matches any four digits from 0-9, as in 1234 or 5936.
x
[a-z] Matches one instance of a letter.
9xx9
\d[a-z][a-z]\d
Matches any number followed by two letters and another number, as in 1ab2.

Converting SQL Syntax to perl Compatible Regular Expression Syntax

If you are familiar with SQL syntax, you can use the following information to write perl compatible regular expressions.

The following table shows examples of SQL syntax and their perl equivalents:

SQL Syntax
perl Syntax
Description
%
. *
Matches any string.
A%
A.*
Matches the letter “A” followed by any string, as in Area.
_
. (a period) Matches any one character.
A_
A.
Matches “A” followed by any one character, such as AZ.

Return Value
Returns the value of the nth subpattern that is part of the input value. The nth subpattern is based on the value you specify for subPatternNum.
NULL if the input is a null value or if the pattern is null.

Example
You might use REG_EXTRACT in an expression to extract middle names from a regular expression that matches first name, middle name, and last name. For example, the following expression returns the middle name of a regular expression:

REG_EXTRACT( Employee_Name, ‘(\w+)\s+(\w+)\s+(\w+)’,2)

Employee_Name                      Return Value
Stephen Graham Smith                 Graham
Juan Carlos Fernando                   Carlos