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 introductio n................................. ................ ................................... ................................... ................................... ................................... ................... .. 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 GOVERNANCE - NEW V E D N O I T A C I L P P A
INFORMATICA ADMIN REQUEST
DESIGN SUBMISSION
E C N A N R E V O G W D A
INFORMATICA FOLDER CREATION & SECURITY
DESIGN REVIEW
V E D N O I T A C I L P P A
PROD MIGRATION
E C N A N R E V O G W D A
DEVELOPMENT
UAT MIGRATION
REVIEW PERFORMANCE RESULTS
II.
CODE REVIEW
QA MIGRATION
REVIEW SYSTEM TEST RESULTS
Re-design change process Overview
INFORMATICA GOVERNANCE - ENHANCEMENTS V V E E D D N N O I O I T T A A C I C I L L P P P P A A E E C C N N A A N N R R E E V V O O G G W W D D A A V V E E D D N N O I O I T T A A C I C I L L P P P P A A
ENHANCEMENT / CHANGE
DESIGN
REQUEST
SUBMISSION
IMPACT REVIEW
PROD MIGRATION
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) 4) 5) 6)
PowerCenter - Group maintenance PowerCenter - Folder maintenance PowerCenter - Incident summary [To report error/issues] 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
4423
4462
4430
5376
5476
2029
` DEV/QA 4 Intel(R) Itanium
UAT
PROD
PRD(etl02)
PRD(etl01)
PRD(etl03)
8 Intel(R)
12 Intel(R)
8 Intel(R) Itanium
2 Intel(R) Itanium(R)
5 Intel(R)
Itanium
Itanium
8
16
24
14
8
8
74.00%
42.00%
32%
TBD
Boxes
Stages CPU Cores
Logical
Itanium
processors Used by
49%
INFA (%)
49.00%
RAM GB
64
64
128
64
64
64
NG-ADR Setup
None
None
None
None
Yes
Yes
OS
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
3) Informatica Environments
Development & QA
UAT
bdhp4423
bdhp4462
ADW_DEV_REP
ADW_QA_REP
ADW_DEV_REP
ADW_QA_REP
etlgt101
Production adhn5376 (etl02)
bdhp4430
sdhn2029 (etl03)
sdhn5476 (etl01)
ADW_UAT_REP
ADW_PRD_REP
ADW_PRD_AP_REP
ADW_S01_PRD_REP
ADW_UAT_REP
ADW_PRD_REP
ADW_PRD_AP_REP
ADW_S01_PRD_REP
etlgu101
etlgp101
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
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
…
…
…
UNIX Boxes
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
