PowerCenter Product Changes from 8.6.1 to 9.5.1

© 2013 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. All other company and product names may be trade names or trademarks of their respective owners and/or copyrighted materials of such owners.

Abstract This article outlines the behavior changes and new features when you upgrade from 8.6.1 to 9.5.1.

Supported Versions •

PowerCenter 8.6.1 - 9.5.0

Table of Contents Overview .......................................................................................................................................... 3 Name Changes ................................................................................................................................ 3 Installation ........................................................................................................................................ 3 Pre-Installation (i9pi) System Check Tool ................................................................................... 4 Upgrade with Changes to Node Configuration ............................................................................ 4 Domain ............................................................................................................................................ 4 Domain Ports ............................................................................................................................... 4 Domain Configuration .................................................................................................................. 4 Security ........................................................................................................................................ 4 Custom Keystore File .............................................................................................................. 4 Permissions .............................................................................................................................. 4 Administration Console ................................................................................................................ 5 Administrator Tool URL Change .............................................................................................. 5 LDAP ............................................................................................................................................ 5 Node Diagnostics ........................................................................................................................ 5 Object Names .............................................................................................................................. 5 Object Name Length ................................................................................................................ 5 Shared Object Names .............................................................................................................. 6 Object Export and Import ............................................................................................................. 6 Licensing ...................................................................................................................................... 6 Database Support ............................................................................................................................ 6 Transformations ............................................................................................................................... 6 Data Masking Transformation ...................................................................................................... 6 Joiner Transformation .................................................................................................................. 7 Lookup Transformation ................................................................................................................ 7 SQL Transformation ..................................................................................................................... 7 XML Transformation .................................................................................................................... 8 Transformation Language .............................................................................................................. 8 Mappings ......................................................................................................................................... 8 Datatypes .................................................................................................................................... 8 Web Services Hub ....................................................................................................................... 8 Sources and Targets ................................................................................................................ 8 Maximum Heap Size ................................................................................................................ 8

2

Sessions .......................................................................................................................................... 9 Pushdown Optimization ............................................................................................................... 9 Teradata ................................................................................................................................... 9 Views ........................................................................................................................................ 9 Session Recovery ........................................................................................................................ 9 Real-time Sessions ...................................................................................................................... 9 Command Line Programs ................................................................................................................ 9 Environment Variables ................................................................................................................ 9 infacmd Command Line Program .............................................................................................. 10 Command Line Program Behavior Changes ............................................................................. 10 Logs ........................................................................................................................................... 11 PowerCenter Reports in JasperReports Server ............................................................................ 11 Mapping Architect for Visio ............................................................................................................ 11 Mapping Analyst for Excel ............................................................................................................. 11 Domains and Enumerations....................................................................................................... 12 Excel Add-in ............................................................................................................................... 12 Export Option ............................................................................................................................. 12 Exporting Metadata to Excel ...................................................................................................... 12 Multiple Mappings ...................................................................................................................... 12 Reusable Rules .......................................................................................................................... 12 Standard Mapping Specification Template ................................................................................ 12 User Interface............................................................................................................................. 13 Validation ................................................................................................................................... 13 Reference Table Manager ............................................................................................................ 13

Overview Informatica has major product updates from the 8.6.1 version. This article outlines PowerCenter new features and behavior changes you see when you upgrade from 8.6.1 to 9.5.1.

Name Changes • • • • •

The Repository Service is renamed to the PowerCenter Repository Service. The Integration Service is renamed to the PowerCenter Integration Service. The PowerCenter domain is renamed to Informatica domain. The Administration Console is renamed to Informatica Administrator (the Administrator tool). The domain configuration database is renamed to the domain configuration repository.

Installation This section covers the new features and behavior changes in the PowerCenter installation process.

3

Pre-Installation (i9pi) System Check Tool Before you install or upgrade the Informatica services, you can run the Pre-installation (i9pi) System Check Tool to verify that the machine meets the minimum system and database requirements for the installation. If you run the Pre-Installation (i9Pi) System Check Tool before you perform the installation, the installer pre-populates certain fields, including the database connection and port number fields, with the information you entered during the system check.

Upgrade with Changes to Node Configuration Informatica provides an upgrade option to change the node configuration.

Domain This section covers the following new features and behavior changes in the Informatica domain.

Domain Ports Each worker node in the Informatica domain uses the following port: Service Manager port Port number used by the Service Manager on the node. Each gateway node uses the following ports: Service Manager port Port number used by the Service Manager on the node. Informatica Administrator port Port number used by the Administrator tool. Informatica Administrator shutdown port Port number used by the Administrator tool to shut down. The Service Manager logs show the Service Manager port number. Previously, each node used a single domain port. On a worker node, the node and Service Manager on the node used the same domain port number. On a gateway node, the node, Service Manager on the node, and the Administration Console used the same domain port number. The Service Manager logs showed the domain port number.

Domain Configuration • •

You can use infasetup to back up and restore the domain. You create a license object when you install PowerCenter. You can also create license objects in Informatica Administrator. You can view the license and all licensed options in Informatica Administrator.

Security This section covers the following new features and behavior changes in security.

Custom Keystore File If you use a custom keystore file, you can specify the location and password when you upgrade a gateway node to a different node configuration.

Permissions You can view domain object permission details for a user or group.

4

The Permissions tab for any domain object in the Administrator tool shows up to 1,000 users or groups. If there are more than 1,000 users or groups, a message appears that asks you to create a filter to limit the number of users or groups. Previously, the Permissions tab did not have a limit on the number of users or groups that it could display. Search Filters You can use search filters to search for a user or a group when you assign permissions, view permission details, or edit permissions.

Administration Console The Informatica Administrator has a new interface. Some of the properties and configuration tasks from the PowerCenter Administration Console have been moved to different locations in Informatica Administrator. The Administrator tool has the following behavior changes: • •

The Administrator tool opens the Change Password application to change the password. Previously, you could change the password within the Administrator tool. PowerCenter Client Options license property in the Administrator tool has been renamed to Service Options.

Administrator Tool URL Change The host and port in the Informatica Administrator URL represent the host name of the master gateway node and the Informatica Administrator port number. You configure the Informatica Administrator port when you define the domain. You can define the domain during installation or using the infasetup DefineDomain command line program. If you enter any gateway or worker node port instead of the Administrator tool port in the URL, the browser is directed to the Administrator tool port. If the domain fails over to a different master gateway node, the host name in the Informatica Administrator URL will be equal to the host name of the elected master gateway node. Previously, the host and port in the Informatica Administrator URL represented the host name and port number of any gateway node.

LDAP This section covers the new features in LDAP.

Node Diagnostics You do not need to import SSL certificates to generate node diagnostics on a secure node. The Configuration Support Manager web application runs on the same web application as the Administrator tool. Previously, the Configuration Support Manager ran on a separate web application. If you wanted to ensure security when you connected to the Configuration Support Manager, you had to configure the nodes for security.

Object Names This section covers the new features with domain object names.

Object Name Length Domain object names, except folder names, can be up to 128 characters long. Previously, domain object names could be up to 80 characters.

5

Shared Object Names Some objects within a domain can have the same name. The following table lists whether domain objects can have the same name: Node

Folder

Grid

License

Service

Node

No

Yes

Yes

Yes

Yes

Folder

Yes

Yes, if they are not at the same level

Yes

Yes

Yes

Grid

Yes

Yes

No

Yes

Yes

License

Yes

Yes

Yes

No

Yes

Service

Yes

Yes

Yes

Yes

No

Previously, domain objects could not have the same name.

Object Export and Import You can use the infacmd command line program to migrate objects between different domains of the same version. You might migrate domain objects from a development environment to a test or production environment. The following table describes the infacmd commands that you can use to migrate objects: Command

Description

infacmd isp ExportDomainObjects

Exports native users, native groups, roles and connections to an XML file.

infacmd xrf GenerateReadableViewXML.

Generates a readable XML file from the export file. Review the readable XML file to determine if you need to filter the objects that you import

infacmd isp ImportDomainObjects

Imports native users, native groups, roles and connections into an Informatica domain.

If you do not want to migrate all objects, use a control file to filter the objects during the export or import.

Licensing The Informatica domain enforces the licensing restrictions on the number of CPUs and PowerCenter repositories.

Database Support You can connect to a SAP HANA database using ODBC.

Transformations This section covers the new features and behavior changes in transformations.

Data Masking Transformation The Data Masking transformation has the following new features: Advanced email masking Configure advanced email address masking in the Data Masking transformation to create a realistic email address. You can generate an email address from a dictionary file or from the transformation output ports in the mapping. You can apply expressions to create an email

6

address. You can also specify a domain dictionary to apply different domain names in the email address. Dependent masking Configure dependent data masking for a source column. With dependent masking, the Data Masking transformation masks more than one column of source data from the same row of data in a dictionary. You can maintain a relationship between the columns of source data, such as the relationship between city and state.

Social Insurance Number masking Configure data masking for Social Insurance numbers (SIN). Select the SIN masking type when you configure masking for the source SIN.

Substitution masking Replace datetime values, integers, and floating point numbers.

Repeatable output Create repeatable output for any data masking type.

Credit card number masking Mask credit card numbers. You can keep or replace the six-digit credit card issuer.

Joiner Transformation Case-sensitive string comparison in the Joiner transformation is disabled. Previously, you could enable case-sensitive string comparison even though the Joiner transformation could not perform this type of comparison.

Lookup Transformation Cache updates Update the lookup cache based on the results of an expression. When an expression is true, you can add to or update the lookup cache. You can update the dynamic lookup cache with the results of an expression. Database deadlock resilience Previously when the Integration Service encountered a database deadlock during a lookup, the session failed. Currently, the session does not fail. When a deadlock occurs, the Integration Service attempts to run the last statement in a lookup. You can configure the number of retry attempts and time period between attempts. Multiple rows return Configure the Lookup transformation to return all rows that match a lookup condition. A Lookup transformation is an active transformation when it can return more than one row for any given input row. SQL overrides for uncached lookups Previously, you could create an SQL override for cached lookups only. You can create an SQL override for uncached lookup. You can include lookup ports in the SQL query.

SQL Transformation Auto-commit for connections You can enable auto-commit for each database connection. Each SQL statement in a query defines a transaction. A commit occurs when the SQL statement completes or the next statement is executed, whichever comes first. Exactly-once processing The Integration Service provides exactly-once delivery of real-time source messages to the SQL transformation. If there is an interruption in processing, the Integration Service can

7

recover without requiring the message to be sent again. To perform exactly-once processing, the Integration Service stores a set of operations for a checkpoint in the PM_REC_STATE table. Passive transformation You can configure the SQL transformation to run in passive mode instead of active mode. When the SQL transformation runs in passive mode, the SQL transformation returns one output row for each input row.

XML Transformation The XML Parser transformation can validate an XML document against a schema. The XML Parser transformation routes invalid XML to an error port. When the XML is not valid, the XML Parser transformation routes the XML and the error messages to a separate output group that you can connect to a target.

Transformation Language Use the optional argument, match_from_start, with the REG_EXTRACT function to return the substring if a match is found from the start of the string. The REG_EXTRACT function uses the following syntax: REG_EXTRACT( subject, 'pattern', subPatternNum, match_from_start )

Mappings This section covers the new features in PowerCenter mappings.

Datatypes PowerCenter supports the Microsoft SQL Server datetime2 datatype. The datetime2 datatype has a precision of 27 and scale of 7.

Web Services Hub This section covers the new features in the Web Services Hub.

Sources and Targets You cannot edit web service sources and targets upgraded from previous versions of PowerCenter. To update a web service source or target from previous versions of PowerCenter, re-create the source or target.

Maximum Heap Size Specify the units for the Maximum Heap Size Property value for a Web Services Hub Service. Append one of the following letters to the property value to specify the units: • • • •

b for bytes. k for kilobytes. m for megabytes. g for gigabytes.

Previously, you specified the value in megabytes. When you upgrade, the Administrator tool appends “m” to the value.

8

Sessions This section covers the behavior changes for sessions in PowerCenter.

Pushdown Optimization This section covers the behavior changes for pushdown optimization.

Teradata The PowerCenter Integration Service does not push double or decimal implicit conversions linked to string fields or CONCAT() conversion functions that contain double or decimal arguments to Teradata. Previously, the PowerCenter Integration Service incorrectly pushed double or decimal implicit conversions linked to string fields or CONCAT() conversion functions that contain double or decimal arguments to Teradata.

Views You can disable the creation of temporary views for pushdown optimization to Teradata when the Source Qualifier transformation contains source filter, user defined joins, or SQL override. Previously, pushdown optimization on Teradata database would create and drop views when you have source filter, user defined joins, or SQL override at the Source Qualifier transformation.

Session Recovery The PowerCenter Integration Service resets mapping variables to the start value during session recovery.

Real-time Sessions You can limit the size of session logs for real-time sessions. You can limit the size by time or by file size. You can also limit the number of log files for a session.

Command Line Programs This section covers the new features and behavior changes in the command line programs.

Environment Variables PowerCenter includes the following new environment variables: INFA_ DEFAULT_CONNECTION_PASSWORD Database password to create connection with infacmd isp CreateConnection. INFA_DEFAULT_PWX_OSEPASSWORD Stores the encrypted password for the operating system. You can use INFA_DEFAULT_PWX_OSEPASSWORD to set an encrypted password or use the corresponding command line -e option on infacmd pwx commands. You can set this password to access the operating system. INFA_DEFAULT_PWX_OSPASSWORD Stores the plain text password for the operating system. You can use INFA_DEFAULT_PWX_OSPASSWORD to set a plain text password or use the corresponding command line -p option on infacmd pwx commands. You can set this password to access the operating system. INFA_PASSWORD Stores the encrypted password for miscellaneous options when you set LDAP connectivity, define gateway nodes, define worker nodes, create new users, reset user passwords, and

9

update SMTP options. You can use INFA_ PASSWORD to set a password or use the corresponding command line option. You can set this password to access your Informatica user account, data source, LDAP services, and outbound mail server. INFA_PC_REPOSITORY_PASSWORD PowerCenter repository password to create PowerCenter services or retrieve logs. INFA_REPOSITORY_PASSWORD Stores the default password for the PowerCenter Repository. You can use INFA_REPOSITORY_PASSWORD to set a password or use the corresponding command line option. You can set this password to create a PowerCenter Integration Service, an SAP BW Service, a Web Services Hub, and get log events for the most recent run of a session. INFA_DEFAULT_SECURITY_DOMAIN Default security domain name that a user belongs to.

infacmd Command Line Program The infacmd command line program is modified to include unique plug-ins for all products within the domain. PowerCenter uses the infacmd isp command line to administer the domain, security, and PowerCenter application services. The following section describes the new commands for the infacmd command line programs: Command Name

Description

infacmd ipc ImportFromPC

Converts a PowerCenter repository object XML file to a Model repository object XML file.

infacmd pwx UpgradeModels

Upgrades PowerExchange nonrelational data objects.

infacmd xref GenerateReadableViewXML

Generates a readable XML file from the export file.

infacmd xref UpdateExportXML

Updates the export file with the changes made to the readable XML file.

infacmd isp AssignGroupPermission

Assigns group permission on an object.

infacmd isp AssignUserPermission

Assigns user permission on an object.

infacmd isp ExportDomainObjects

Exports native users, native groups, roles, and connections from an Informatica domain to an XML file.

infacmd isp ImportDomainObjects

Imports native users, native groups, roles, and connections from an XML file into an Informatica domain.

infacmd isp ListGroupPermissions

Lists the domain objects that a group has permission on.

infacmd isp ListUserPermissions

Lists the domain objects that a user has permission on.

infacmd isp removeUserPermission

Removes permission on an object from a user.

infacmd isp removeGroupPermission

Removes permission on an object from a group.

infacmd isp SyncSecurityDomains

Synchronizes LDAP security domain.

infacmd isp UpdateMonitoringOptions

Updates general properties to monitor actions in the domain.

Command Line Program Behavior Changes This following section describes changes for the Informatica command line programs: Command Name

Description

infacmd sql AddGroupPermission

Revised command as infacmd sql AssignGroupPermission

infacmd sql AddUserPermission

Revised command as infacmd sql AssignUserPermission

infasetup

Updated the following infasetup commands for the –tls parameter to enable Transport Layer Security (TLS):

10

Command Name

pmrep

pmrep ExecuteQuery

Description •

DefineDomain

•

DefineGatewayNode

•

DefineWorkerNode

•

UpdateGatewayNode

•

UpdateWorkerNode

Updated the following pmrep commands for the –y parameter to display database type of sources and targets: •

ExecuteQuery

•

FindCheckout

•

ListObjects

•

ListObjectDependencies

•

Validate

The pmrep ExecuteQuery command includes an -n parameter. When you use this option, pmrep does not include the full parent path of non-reusable objects in the query result. This option can improve pmrep performance.

Logs Use infacmd isp ConvertLogFile command to view log files from PowerCenter 8.6.x. The infacmd isp ConvertLogFile command uses the following syntax: ConvertLogFile input_file_name [ format_TEXT_XML] [ output_file_name]

PowerCenter Reports in JasperReports Server You can associate a Reporting and Dashboard Service with the PowerCenter RepositoryService to view the PowerCenter reports in JasperReports Server. You can also launch the reports from the PowerCenter Client.

Mapping Architect for Visio This section covers the new features in Mapping Architect for Visio. Shortcuts You can configure a transformation to use a shortcut. You can create a mapping template from a mapping that contains shortcuts to reusable transformations. Mapping template You can include the following objects in a mapping template: - Custom transformation - Pipeline Normalizer transformation - PowerExchange source definition - PowerExchange target definition

Mapping Analyst for Excel This section describes changes to Mapping Analyst for Excel.

11

Domains and Enumerations You can configure domains and enumerations to define reference data within a mapping specification. A domain is a reference table. An enumeration includes the reference table values.

Excel Add-in Mapping Analyst for Excel includes an Excel add-in that adds a Metadata menu, or ribbon, to Microsoft Excel. Use the Metadata menu to complete the following tasks: •

Show and hide columns on a worksheet.

•

Annotate cells with descriptions from other worksheets.

•

Format a worksheet to resize the columns to fit the text.

•

Validate the mapping specification.

• Insert another worksheet of a specific type. Previously, you used the Validate button on each worksheet to validate data.

Export Option You do not configure the Operation export option. Mapping Analyst for Excel determines the type of export operation to perform. To configure the Format export option, you select the Standard mapping specification template. Previously, you configured the Operation export option and typed the name of a template in the Format export option.

Exporting Metadata to Excel The PowerCenter Repository Service does not export the Domains, Enumerations, Joins, Lookups, Filter, and Rules worksheet when you export metadata to Excel. The export of metadata to Excel is a reporting activity that represents a summary of the data lineage. The PowerCenter Repository Service exports the Models, Packages, and Mapping worksheets when you export metadata to Excel. Previously, the PowerCenter Repository Service exported the worksheets by including the worksheets that do not have summary lineage, when you export metadata to Excel. The worksheets exported were Models, Packages, Domains, Enumerations, Mappings, Joins, Lookups, Filter, and Rules worksheet.

Multiple Mappings You can configure multiple mappings in a single mapping specification based on the Standard mapping specification template.

Reusable Rules You can define reusable rules to use as expressions on the Mappings worksheet. You can use rules in a mapping specification to perform simple data cleansing.

Standard Mapping Specification Template Mapping Analyst for Excel includes a single mapping specification template named StandardBlank.xlsx. A mapping specification based on the Standard template contains a single or multiple mappings configured on multiple Excel worksheets. Mapping specifications based on this template can contain source definitions, target definitions, rules, and filter, join, lookup, aggregate, and non-aggregate expressions. Previously, you created a mapping specification based on one of the following mapping specification templates:

12

•

Custom template

•

Mapping template

• Source-Target-Matrix template You can no longer import from or export to mapping specifications based on these templates. If you have existing mapping specifications, you must reconfigure the mapping metadata in a mapping specification based on the Standard template.

User Interface Mapping Analyst for Excel includes the following user interface enhancements: •

Annotation of cells with descriptions from other worksheets.

•

Icons for the most common tasks.

•

Simple or advanced view of a worksheet by displaying the minimum or maximum columns available.

Validation When you validate a mapping specification, Mapping Analyst for Excel provides more detailed error messages.

Reference Table Manager The PowerCenter web application named Reference Table Manager is deprecated. It is now part of Informatica Data Quality, where you can use the Analyst tool or the Developer tool to create reference tables.

Author Sujitha Alexander Technical Writer

13