242340104 1 Checklist For Informatica New Project Setup

Checklist for Informatica New Project Setup Contents Introduction ..................................................................................................................................... 2 1.

New Project Approval Process ................................................................................................ 2

2.

Request to create new project ................................................................................................ 3

3.

Check new project setup ......................................................................................................... 3

4.

Personal Access Apply ............................................................................................................. 4

5.

Informatica Security ................................................................................................................ 4

6.

Start developing ...................................................................................................................... 5

7.

Useful tools introduction......................................................................................................... 5

8.

Migration ................................................................................................................................. 5

9.

Running an Informatica Workflow via Ctrl M Server .............................................................. 6

10.

Change Management .......................................................................................................... 6

11.

Appendix .............................................................................................................................. 7

I.

New Project Process Overview........................................................................................... 7

II.

Re-design change process Overview .................................................................................. 7

III.

Standards & Best Practices in ADW Knowledge in ADW EIM share point - Highlights 8

IV.

Informatica Requests ...................................................................................................... 8

V.

Informatica Platform Architecture ..................................................................................... 9

VI.

Directories settings in Informatica ............................................................................... 11

VII.

Versioning ..................................................................................................................... 13

VIII.

Incident Reporting & Troubleshooting ........................................................................ 13

IX.

Resources ...................................................................................................................... 13

X.

ETL Shared Services Team ................................................................................................ 14

XI.

Service Manager Link .................................................................................................... 14

Introduction Informatica is an ETL tool, setup on ADW Platform, both ADW projects on ADW Platform and NON-ADW projects on other Platforms can use this tool for their ETL processing by following proper procedures. This checklist is a guide that what procedures and tasks a new project should finish; it is applied to all ADW and NON-ADW projects. Pay attention to last chapter ‘Appendix’, including a lot of information, all referred documents in this guide can be found via P&G Document Center link listed in ‘Resources’ chapter, when you go to the link at first time, it is strongly recommended that download all documents from there for future reference, please read this guide carefully when new project setup on Informatica Platform.

1. New Project Approval Process Any new project setup on Informatica platform should get approval firstly via below steps. There is an Overview for approval process in last chapter ‘Appendix’, please also refer to it. 1) Fill ‘New Project Request.doc’ and return document to Raj Mohan ([email protected]) and CC to Informatica Support Team ([email protected]) in the email. Document reviewed and project approved (3 days turnaround) 2) Design Review, INFA design review will be conducted by Informatica Governance team (Raj Mohan), including the High Level Design and Low Level Design, and then provide the necessary sign off for both. 3) Determine UNIX HLD, this is done during the new project approval with Raj Mohan, Based on type of projects.

HLD in Development: • • • •

ADW projects: /var/opt/adwgd Other Platform and ETL Only projects: /var/opt/oetld Gillette projects: /var/opt/glltd – very limited usage EZOOM projects: /var/opt/zoomd

If your project has special request for UNIX folders and links creation, please freely discuss it during new project approval. 4) Determine Project name shortcut, min length is 4 chars and max length is 8 chars, shortcut is meaningful, this is also done during the new project approval.

5) Submit Capacity Planning request to CPM team for approval

2. Request to create new project After approval from Raj Mohan and CPM team, project team can request to create new Project in Informatica platform. 1) Via Service Manager, create ticket with new project setup template. Template path: Service Manager --> Application Software (non pc) --> INFORMATICA->PowerCenter - New project set up. Service Manager link is attached in last chapter ‘Appendix’. 2) Fill all fields in the template with your project info, and attach approvals from Raj Mohan and CPM 3) Send the ticket to Informatica Support Team(G.INFFTS) 4) Record ticket number so you can track project setup status

3. Check new project setup When Informatica support team completes ticket back, all creation objects for your project are also attached to the ticket, please check them, and freely contact Informatica Support team if you have any question about these objects. Below standard objects created for each new project: Informatica Level: Two folders: PRJ_ [project name shortcut], PRJ_ [project name shortcut] _SHARED Project deployment group: DRP_ [project name shortcut] Project group: GRP_ [project name shortcut] Project user: USR_ [project name shortcut] ADW3rd Support group has access to ADW Platform project’s folders and deployment groups, and adw_operator has permission to run their workflows. UNIX Level eight Standard directories and corresponding links: 1) Eight standard Directories. [project name shortcut] is lowercase, $HLD is determined in previous step. /var/opt/$HLD/data/out/PRJ_[project name shortcut] /var/opt/$HLD/data/in/PRJ_[project name shortcut] /var/opt/$HLD/param/PRJ_[project name shortcut] /var/opt/$HLD/bin/sql_sp/PRJ_[project name shortcut] /var/opt/$HLD/data/lkp/PRJ_[project name shortcut] /var/opt/$HLD/log/sesslogs/PRJ_[project name shortcut] /var/opt/$HLD/log/workflowlogs/PRJ_[project name shortcut] /var/opt/$HLD/data/bad/PRJ_[project name shortcut]

2) Eight corresponding links. [Project name shortcut] is uppercase for links, ${INFA_HLD} is etld/etlq/etlu/etlp based on stage.

/var/opt/pwrcntr/${INFA_HLD}/SessLogs/PRJ_[project name shortcut]->/var/opt/$HLD/log/sesslogs/PRJ_[project name shortcut] /var/opt/pwrcntr/${INFA_HLD}/WorkflowLogs/PRJ_[project name shortcut]->/var/opt/$HLD/log/workflowlogs/PRJ_[project name shortcut] /var/opt/pwrcntr/${INFA_HLD}/BadFiles/PRJ_[project name shortcut]->/var/opt/$HLD/data/bad/PRJ_[project name shortcut] /var/opt/pwrcntr/${INFA_HLD}/TgtFiles/PRJ_[project name shortcut]->/var/opt/$HLD/data/out/PRJ_[project name shortcut] /var/opt/pwrcntr/${INFA_HLD}/SrcFiles/PRJ_[project name shortcut]->/var/opt/$HLD/data/in/PRJ_[project name shortcut] /var/opt/pwrcntr/${INFA_HLD}/Param/PRJ_[project name shortcut]->/var/opt/$HLD/param/PRJ_[project name shortcut] /var/opt/pwrcntr/${INFA_HLD}/ExtProc/PRJ_[project name shortcut]->/var/opt/$HLD/bin/sql_sp/PRJ_[project name shortcut] /var/opt/pwrcntr/${INFA_HLD}/LkpFiles/PRJ_[project name shortcut]->/var/opt/$HLD/data/lkp/PRJ_[project name shortcut]

4. Personal Access Apply After project setup, developers should apply personal access to the project’s folders for development. 1) Create the ticket with account maintain template, Template path: Service Manager --> Application Software (non pc) --> INFORMATICA-> PowerCenter Account maintenance. You need to get approval from your project manager or lead and attach it to the ticket, or your request will be rejected. For more information, refer to the document ‘Power Center Account creation user guide.doc’. 2) Once ticket completed back to the caller, an email for login detail also sent, caller needs to check email. There is personal folder created for user on Dev. 3) UNIX access to the Informatica Servers for developers is also required and should be requested via a separate Service Manager ticket. This is handled by UNIX team, contact them for more detail. The Template: Service Manager --> Application Software (NON PC) --> ADW\ADW ORACLE).Since all the ETL servers are on the same Unix policy model, once access to bdhp4423 is granted, the same login/password can be used on other ETL boxes. When requesting UNIX access, request to be added to the groups. If you are an ADW projects, groups are adwgd and adwgq , for NON-ADW projects, it should be oetld, oetlq. 4) Remember to apply all access you require for development.

5. Informatica Security 1) All Developers will have separate Informatica accounts (T#). All Developers will be assigned to the Project Group – GRP_Short_Name 2) The Project Folders will be owned by the Project User and Project Group

3) User folders will be owned by the User 4) Developer access is only allowed to the Development environment 5) Certain key project resources will be granted read only access to the QA and UAT repositories. Only Service Management will be granted access to the Production repository. 6) ADW3rd Support group has access to all ADW project’s folders and deployment group 7) adw_operator will be added to ADW projects group, so it has permission to run workflows 8) Developers have full permission to project’s UNIX folders after added to corresponding groups when apply access to UNIX Boxes.

6. Start developing Until now, project setup and access finished, developers can start develop according logical design. Standards and best practices for development are shared in ‘Appendix ‘chapter, to develop standardize and good performance Informatica codes, please refer to it carefully.

7. Useful tools introduction In current Informatica platform, there are some useful tools to associate with project development; they are Control M/Harvest/CIB/IN13/FileWatcher. 1) Control M is the ESS recommended Scheduling tool since HP supports Control M. Other Scheduling tools cannot be used. About this tool, refer to site http://control-m.na.pg.com or document ‘Control M Guide.doc’ 2) Harvest has to be used to migrate non-Informatica objects between environments (shell scripts, SQL scripts and other files) 3) CIB should be used to move files to and from the Informatica server 4) IN13 should be used for metadata management 5) There is an ADW FileWatcher tool should be used as detecting system for incoming files 6) Documentation on Harvest, CIB, IN13, File Watcher can be found in the EIM Architecture team space under “Training” http://tinyurl.pg.com/url/6lz NOTE: Control M set up usually starts at QA stage, and takes time and has been responsible for delaying project rollout. Please start working on Control M & Harvest & CIB set up right away. Service Manager Tickets are required to set up access.

8. Migration Once development on Dev, project will plan to migrate to next stages (QA/UAT/PRD), here are steps for migration. 1) Migration path: DEV->QA->UAT->PRD, for migration to PRD, refer to below change management.

.

2) Code Review Process INFA Code review will be conducted by project team via standard check tool at the end of the development phase, please attach review results to migration ticket when request migration from DEV to QA, INFA Support Team will check review results before doing migration. Migration will be rejected when there is no review result or there is any object which not following standard in results. To check your code review result, refer to document ‘Standard Check Output Guide.doc’ and ‘Informatica ETL Standards v2.1’ 3) Create ticket with migration template. Template path: Service Manager --> Application Software (non pc) --> INFORMATICA-> PowerCenter - Migration request. 4) For migration process, please follow the document ‘Migration guide for project team.doc’. 5) above three steps are for Informatica code migration, if your project also include other codes, such as PL/SQL, UNIX SHELL, please remember to migrate them.

9. Running an Informatica Workflow via Ctrl M Server Previous chapter already said that Control M is the ESS recommended scheduling tool; please learn to use this tool completely. After migration to QA, project team can setup control m jobs, and it is optionally. Here, introducing how to setup Ctrl M jobs to automatically run Informatica workflows. According to project’s type, please refer to following documents. ADW projects: ‘PowerCenter 8 UNIX Environment Setup for ADW’ NON-ADW projects: ‘PowerCenter 8 UNIX Environment Setup for OETL’

10.

Change Management

1) Before Migration to the Production environment, Informatica Support team will review performance and capacity results generated on QA/UAT. 2) Migration to the Production environment requires a change request; it should be reviewed and approved by the Change manager on the Change Board (CAB) during the weekly meeting on Wednesdays. Please allow enough time for the review and approval. 3) If objects are shared between projects and need to be modified, the communication required among projects.

4) When there is re-design change, please follow Overview for re-design change in last chapter ‘Appendix’

11.

Appendix I.

New Project Process Overview

INFORMATICA ADMIN REQUEST

DESIGN SUBMISSION

PROD MIGRATION

ADWGOVERNANCE GOVERNANCE ADW

DEVELOPMENT

INFORMATICA FOLDER CREATION & SECURITY

DESIGN REVIEW

APPLICATIONDEV DEV APPLICATION

ADWGOVERNANCE GOVERNANCE APPLICATION APPLICATIONDEV DEV ADW

INFORMATICA GOVERNANCE - NEW

UAT MIGRATION

REVIEW PERFORMANCE RESULTS

II.

CODE REVIEW

QA MIGRATION

REVIEW SYSTEM TEST RESULTS

Re-design change process Overview

EE

APPLICATIONDEV DEV APPLICATION

ADWGOVERNANCE GOVERNANCE APPLICATION APPLICATIONDEV DEV ADW

INFORMATICA GOVERNANCE - ENHANCEMENTS

ENHANCEMENT / CHANGE REQUEST

IMPACT REVIEW

PROD MIGRATION

DESIGN SUBMISSION

Decision

DESIGN REVIEW

UAT MIGRATION

DEVELOPMENT

CODE REVIEW

QA MIGRATION

III.

Standards & Best Practices in ADW Knowledge in ADW EIM share point - Highlights

1) 2) 3) 4) 5) 6)

Read ETL Standards document in the http://tinyurl.pg.com/url/6lz For most projects, use Informatica Power Center for ETL processing Any usage of PL/SQL has to be justified Follow object naming convention Add descriptive comments for each object Database connection names should not be environment specific (no “dev” “tst” etc.). This will allow connections to be migrated between environments without any changes 7) Use parameterized connections wherever possible 8) Use Session variables followed by /[Project Name] for all Informatica path settings. 9) Set Session parent to fail if child fails 10) Set Session to Fail on 1 error (unless there is reason not to) 11) Logs should be created with timestamps 12) Do not use the default Cache settings. Please estimate the size of your cache and set the Cache size properties accordingly. There is a Cache calculator available in the team space. Caching huge data sets may be less efficient than accessing the database – run tests to determine whether to cache or not 13) Avoid file based integration. Extract data directly from the source system wherever possible. 14) Typically one workflow contains only one session to better manage restorability, but this is not required. 15) Design mappings, session, workflows, and control M jobs keeping restorability in mind 16) Data profiling Option has been purchased and installed, so please use it to profile source data. 17) Build archiving into process design. There are specific archive directories set up for this purpose. Eg. /var/opt/adwgd/arch 18) Use design templates (embedded in ETL Standards doc) to document mapping design. Schedule review session with Informatica Governance team prior to development.

IV.

Informatica Requests

All requests including incident reporting are made through Service Manager, INFA GOV group: G.INFFTS. Template Category --> Service Manager --> Application Software (non pc) --> INFORMATICA: 1) PowerCenter - New project set up [For any new projects] 2) PowerCenter - Account maintenance [Personal accounts]

Refer to ‘Power Center Account creation user guide.doc’ 3) PowerCenter - Group maintenance 4) PowerCenter - Folder maintenance 5) PowerCenter - Incident summary [To report error/issues] 6) PowerCenter - Migration request [Migrating INFA objects] Refer to ‘Migration guide for project team.doc’ 7) PowerCenter – Performance issue

V.

Informatica Platform Architecture

1) Informatica Products Licensed Version:

PowerCenter 9.1.0 HotFix2 Options: Supported source and target: Team Based Development Microsoft SQL Server, ODBC, Oracle, Teradata Mapping Generation DB2, Informix, SyBase, Partitioning PowerExchange for SAP NetWeaver - BW Data Profiling PowerExchange for SAP NetWeaver – mySAP Server Grid PowerExchange for Salesforce.com Data Analyzer PowerExchange for Netezza Please go to software.pg.com for download Informatica 9 Client; you have to input your P&G intranet username and password for login. Section for Informatica Client in website, please choose installer 910HF1_Client_Installer_win32-x86.zip.

2) ETL boxes Capacity UNIX Boxes Stages CPU Cores

4423

4462

4430

5376

5476

2029

` DEV/QA 4 Intel(R) Itanium

UAT

PROD

PRD(etl02)

PRD(etl01)

PRD(etl03)

8 Intel(R) Itanium 16

12 Intel(R) Itanium 24

8 Intel(R) Itanium

2 Intel(R) Itanium(R)

5 Intel(R) Itanium

14

8

8

74.00%

42.00%

32%

TBD

Logical 8 processors Used by INFA (%)

49%

RAM GB

64

64

128

64

64

64

NG-ADR Setup OS

None

None

None

None

Yes

Yes

HP-UX 11.31

HP-UX 11.31

HP-UX 11.31

HP-UX B.11.31

HP-UX B.11.31

HP-UX B.11.31

49.00%

3) Informatica Environments

Development & QA

UAT

bdhp4423

bdhp4462

Production adhn5376 (etl02)

bdhp4430

ADW_DEV_REP

ADW_QA_REP

ADW_UAT_REP

ADW_PRD_REP

ADW_PRD_AP_REP

ADW_S01_PRD_REP

ADW_DEV_REP

ADW_QA_REP

ADW_UAT_REP

ADW_PRD_REP

ADW_PRD_AP_REP

ADW_S01_PRD_REP

etlgu101

etlgp101

etlgt101

sdhn2029 (etl03)

sdhn5476 (etl01)

etlap101

ADW_PRD_03_REP

ADW_PRD_03_REP

etlgp201

etlgp301

Applications on bdhp4430 will be migrated to other three PRD boxes, and bdhp4430 will be decommissioned, in the future, we’ll have three PRD boxes, etl01, etl02, etl03.

4) Informatica repositories connection information Stage

DEV & QA

UAT

PRD

PRD(etl02)

PRD(etl01)

PRD(etl03)

Repository Name

ADW_DEV_REP ADW_QA_REP

ADW_UAT_REP

ADW_PRD_REP

ADW_PRD_AP_REP

ADW_S01_PRD_REP

ADW_PRD_03_RE P

Domain Name

Domain_Bdhp4423

Domain_Bdhp4462

Domain_Bdhp4430

Domain_adhn5376

Domain_sdhn5476

Domain_etl03

Gateway Host

bdhp4423.na.pg.com

bdhp4462.na.pg.com

bdhp4430.na.pg.com

Adhn5376.na.pg.com

Sdhn5476.na.pg.com

etl03.na.pg.com

Security Domain

Native

Native

Native

Native

Native

Native

Gateway Port

6001

6001

6001

6001

6001

6005

5) UNIX environments

ADW4D, ADW5D, ADW3D

Database(ADW)

ADW4U, ADW5U, ADW3U

ADW4P, ADW5P

ADW3P

Informatica

UNIX Boxes

ADW_DEV_REP

ADW_QA_REP

ADW_UAT_REP

bdhp4423 /var/opt/adwgd /var/opt/oetld …

bdhp4423 /var/opt/adwgq /var/opt/oetlq …

bdhp4462 /var/opt/adwgu /var/opt/oetlu …

ADW_PRD_REP ADW_S01_PRD_REP ADW_PRD_03_REP

bdhp4430(to be retired) sdhn5476(etl01) sdhn2029(etl03) /var/opt/adwg /var/opt/oetl…

ADW_PRD_AP_REP

adhn5376(etl02) /var/opt/zoom …

Ctrl M Server bdhp4586

VI.

bdhp4540

bdhp4430->bdhp4713 sdhn5476->adhn5328 sdhn2029->TBD

Directories settings in Informatica

Informatica has “PM” server variables that are used by default in all session/workflow directory settings. Do not hardcode any paths. PM server variables

values

$PMRootDir:

/var/opt/pwrcntr/etld var/opt/pwrcntr/etlq var/opt/pwrcntr/etlu var/opt/pwrcntr/etlp

$PMSessionLogDir:

$PMRootDir/SessLogs

$PMBadFileDir:

$PMRootDir/BadFiles

$PMCacheDir:

$PMRootDir/Cache

$PMTargetFileDir:

$PMRootDir/TgtFiles

$PMSourceFileDir:

$PMRootDir/SrcFiles

adhn5328

$PMExtProcDir:

./ExtProc

$PMTempDir:

$PMRootDir/Temp

$PMWorkflowLogDir:

$PMRootDir/WorkflowLogs

$PMLookupFileDir:

$PMRootDir/LkpFiles

$PMStorageDir:

$PMRootDir/Storage

Informatica has “PM” server variables that are used by default in all session/workflow directory settings

– – – – – – –

For example: The Session Log Directory setting in an Informatica Session defaults to $PMSessionLogDir These variables are used by all Projects in the repository, therefore each project needs to qualify the path with their Project name so Informatica knows to read/write from the project specific directories Informatica will read from and write to the root /var/opt/pwrcntr/eltd subdirectories in development (etlq – qa, etlu- uat, etlp – prd) There are subdirectories under this root directory for BadFiles, SessLogs, WorkflowLogs, SrcFiles, TgtFiles etc. Under each of these subdirectories will be Project Name links that redirect Informatica to the directories specific to the project. Informatica Support Team is responsible for creating the Links and the directories that these links point to at project set up time. The project is responsible for setting up their non Informatica related UNIX directories All Projects used the same Temp and Cache directories under /var/opt/pwrcntr/etld Example:

VII.

Versioning

1) Versioning is turned on in all repositories 2) Please include descriptive comments when checking in objects. Developers should avoid checking in objects until all changes are complete 3) Please make sure that all objects are checked in (via Checked Out report under Versioning menu) before requesting a migration. If there are objects that are checked out, the migration request ticket will be rejected resulting in delays due to missed migration windows.

VIII.

Incident Reporting & Troubleshooting

Please open a Service Manager ticket to the G.INFFTS queue IMMEDIATELY (using the Powercenter – Incident Summary template) if you experience any problems with the Informatica service. All troubleshooting requests need to be made via a ticket to the G.INFFTS queue.

IX.

Resources

1) P&G Share point Documents Center: http://teamspace.pg.com/sites/EIM/ADW/ADW%20Platform%20Wiki/Informatica.aspx

2) Informatica knowledge library: Launch any of Informatica Client Tools, E.g. Informatica Power Center Repository Manager->Menu->Help->Contents. You can get a lot of useful knowledge from here. 3) Raise ticket to Informatica Company: As limit of login, Informatica support team raises tickets to Informatica Company instead of project teams. Please contact Informatica Support Team when you need to raise ticket to Informatica Company.

X.

ETL Shared Services Team

1) Informatica P&G Owner and Governance: Raj Mohan [email protected]

2) Informatica P&G Operation: Kossek Tomasz [email protected]

3) Informatica HP Service Owner : Allen Zuo [email protected]

4) Informatica HP Team Lead: Cyndy Chen [email protected]

5) HP Informatica Support Team Email: [email protected] MOC: [email protected] Service Manager Group: G.INFFTS

XI.

Service Manager Link

Access below link with your P&G intranet username and password to create and track tickets http://servicemanager.pg.com/SM/index.do