ETL Team Development Standards - DRAFT

Table of Contents
ETL Development Checklist ................................................................................................................................................................................ 2
Technical Specification Development Guidelines ..................................................................................................................................................................................2
Informatica Naming Conventions...........................................................................................................................................................................................................2
Informatica Version Control Guidelines.................................................................................................................................................................................................3
Documentation........................................................................................................................................................................................................................................4
Miscellaneous Guidelines .......................................................................................................................................................................................................................4
Optimization/Security Considerations ....................................................................................................................................................................................................4
Change Management Considerations .....................................................................................................................................................................................................4
Appendix A: Informatica Administration and Maintenance ............................................................................................................................... 5
Informatica Server Reboot Procedures ...................................................................................................................................................................................................5
Appendix B: BAIRS Outages.............................................................................................................................................................................. 6
Appendix C: Issues Log....................................................................................................................................................................................... 6
Appendix D: Month-End Procedures .................................................................................................................................................................. 7
Appendix E: Archiving Procedures ..................................................................................................................................................................... 7
Appendix F: Student Data Warehouse Support................................................................................................................................................... 7
Access Request Process ..........................................................................................................................................................................................................................7
Appendix G – Gaelen Standards........................................................................................................................................................................... 7
Appendix H - Peoplesoft Table Considerations ................................................................................................................................................... 8
Appendix I - Environment Objectives.................................................................................................................................................................. 8

Page 1
 ETL Team Development Standards - DRAFT

ETL Development Checklist

Develop and publish Technical specifications based on functional requirements
Create Informatica mapping specifications from ETL template
Publish to Sharepoint ETL Home Æ Shared Documents. Create new folder as necessary.

Develop new Informatica map in development folder.

For revisions to existing Informatica mappings, copy mappings from info_prod repository to info_dev repository within Designer tool. Change version number
per version control guidelines below. For new Informatica mappings, create mapping in info_dev repository using Designer tool, per Informatica Naming
conventions.
Update “Informatica Maps in Development” list on ETL Sharepoint site – or drag separate copy of map into “In Development” folder.
Maps may be developed in personal folders within the info_dev repository, but should be migrated to the dev copy of the relevant production folder after initial
development is complete and before QA testing begins.
Review design with teammates.

Technical Specification Development Guidelines

To develop technical specifications for a project, use the PROJECT NAME Data Requirements template under Data Warehouse Groups > Deliverable
Templates: https://calshare.berkeley.edu/sites/RAPO/EDW/reporting/Deliverable%20Templates/Forms/AllItems.aspx

Informatica Naming Conventions

Map Naming Conventions

Within Informatica Designer, maps should be named using the following template: Area_TargetName_Qualifier_Action_vX_Y

where:

Area is stg – Staging

dw – EDW fact/dimension tables

TargetName is the final target table name all in upper case

Action is del - Delete

ins - Insert
updt - Update
scd - Slowly Changing Dimensions
copy - Copy (no transformation logic between sources and targets – mainly used for source to stage copies, creation of test data,
and ad hoc data movement.)

Page 2
 ETL Team Development Standards - DRAFT

Qualifier is A description of the functionality of the mapping. This only needs to be added if multiple mappings use the same target table.

“v” stands for version

“X” is the major version number. It is initially set to 1 when a map is first created and is incremented by one for each subsequent major
change to that mapping. Major changes involve fundamental changes to a map design, e.g. new sources, transformations and/or
targets, replacing or significantly augmenting existing functionality. For minor mapping revisions, the major version number
remains constant.

“Y” is the minor version number. It is initially set to 0 when a map is first created and is incremented by one for each minor change to a
given mapping (e.g. a change to a Filter transformation condition or a change to derived values within an Expression tranformation).
When the Major Version number (“X” above) is incremented, the minor version number is re-set to “0”.

Session Naming Conventions

Within Informatica Workflow Manager, sessions should be named using the following template: s_MappingName_Qualifier

where:

s stands for “session”

MappingName is the name of the Informatica mapping associated with a given session

Qualifier is A description of the functionality of the session. This only needs to be added if the mapping is associated with multiple
sessions.

Workflow Naming Conventions

Within Informatica Workflow Manager, Workflows should be named using the following template: wf_WorkflowName_Frequency

where:

wf stands for “Workflow”

WorkflowName is a description of the functionality contained within the workflow, e.g. “HR_ADM_WKFORCE”

Frequency is how often the workflow runs e.g. “Monthly”, “Daily”, “Weekly”. “Daily” can be used for workflows which run Monday
through Saturday or Monday through Friday.

Informatica Version Control Guidelines

Version control in Informatica is managed as follows:

Page 3
 ETL Team Development Standards - DRAFT

• Maps and associated sessions should share the same version number
• See naming conventions above for details on how version numbers should be maintained for mappings and sessions.

Documentation
Documentation should be posted on the appropriate BearShare site: BearShare is backed up nightly with 2 hour snapshots taken during the day – more info is available
at : https://bearshare.berkeley.edu/C4/Implementing%20BearShare/default.aspx. Michael Leefers is contact for bearshare questions.

Miscellaneous Guidelines
• Source to Stage Mapping Guidelines
• Update strategy: maintenance of code values not matching in the source system
• Workflows should be updated in info_dev repository to match production before new/revised maps, sessions and/or workflows are moved to production.
• Include considerations of shortcut folder management when existing maps are to be modified.

All documents on this list should be updated as part of any new development work.
• Report Inventories: https://bearshare.berkeley.edu/sites/RAPO/EDW/reporting/Reports/Shared%20Documents/Forms/AllItems.aspx - contains three
documents containing report inventories for BAIRS, BIS and HR. It would be useful to augment these spreadsheets by adding the underlying tables associated
with listed views.

Optimization/Security Considerations
• Developer Roles (ADM_RO and ADM_HR_RO) should always be given read access to new database objects.
• Access privileges may prevent developers from being able to view data contained in database Views. Two ways to deal with this:
1. Update HRMS_OPR_XREF table (in QA) to allow access. Example below:
Update BAIR_HRMS_OPR_XREF
Set userid = ‘BISWJC’
Where oprid = ‘011502567’ (or ‘011504738’)
2. Apply for security access through SARA (HRMS Dept. Security, Administer Workforce)

Change Management Considerations

Resources:

Page 4
 ETL Team Development Standards - DRAFT

• ASD_EDW_change_process_notification_flow, under Data Warehouse Groups Shared Documents

(https://bearshare.berkeley.edu/sites/RAPO/EDW/reporting/Shared%20Documents/Forms/AllItems.aspx ).

To request mainframe production changes: send 2 emails:

• [email protected]
• [email protected]

Enter objects to be moved into TSO MIGMGR - describe what needs to be moved and when. (e.g. move members xxx from EDW.PUB.STAGE.INCLIB to
ASD.P.CTM.BIS.AEVARS).

Report Migrations
Contact [email protected] for report migration requests. Non-standard between 5-6 PM , 8-9 AM. Report users/ESS staff to review before general access is
allowed.

Appendix A: Informatica Administration and Maintenance

Informatica Server Reboot Procedures

To bring down the Informatica server:

• Confirm that no jobs are running.
• Insure all developers have saved work and closed desktop clients.
• In Server Manager, right-click on modoc_712 icon.
• Choose “Shut down server”
• Log onto modoc as informat
• ps –ef | grep pm
• Open Admin console, connect to modoc
• Repositories Æ info_prod Æ stop
• Modoc Æ shutdown (repository server)

To bring up the Informatica server:

• From Unix as informat on modoc:

Page 5
 ETL Team Development Standards - DRAFT

cd /apps/informatica7.1.2/repository_server
pmrepserver pmrepserver.cfg

cd ../server
./pmserver pmserver_prod.cfg

• From Unix as informat on tehama:

cd /apps/informatica7.1.2/
pmrepserver pmrepserver.cfg

cd server
./pmserver pmserver_prod.cfg

Appendix B: BAIRS Outages

If there’s a problem loading EDW

• Ask Quin/oracledoctor to put the database in restricted mode so users cannot run reports. Post a message in the report portal as soon as it is available.
• Send listserves per doc in Support folder.
• Ask Michael to make specific reports or folders unavailable to users so we can allow other users to run reports until fin/pb load is complete.
• When loads have completed, tell Quin/DBA’s: please reset access so users can run reports (take the ucbdw1p database out of restricted access mode)
• Add [email protected] to any BAIRS outage notifications so Kevin Haney can post it on the CCS Status page.

Appendix C: Issues Log

All production issues should be entered in the Sharepoint issues log at
https://bearshare.berkeley.edu/sites/RAPO/EDW/reporting/Lists/DW%20Production%20Issues/AllItems.aspx. Issues include
• Reconciliation problems
• Informatica production map failures
• Etc.

Page 6
 ETL Team Development Standards - DRAFT

Appendix D: Month-End Procedures

Duplicate rows in Fact_JD_Open were inserted on 10/21, the day after we processed Foundation JD data for Period 3 close.Next time we can avoid this by waiting an
extra day before starting the monthly process for Foundation JD data. This is due to our current daily process loads 2-day worth of Fact JD data in to the Open table.

Appendix E: Archiving Procedures

EDW data is backed up to tape on the following schedule:
• Nightly backups, retained for 30 days
• Weekly backups, retained for 90 days
• Monthly backups, retained for 1 year

Backups are not encrypted. Tapes are stored by Iron Mountain. Hope this helps - let me know if you have any more questions.

See X:\RAPO\EDW\CCS-EDW\Support\Financials\ProductionSupport\AP PO Archive procedure.doc for details on AP/PO archiving procedures.

Appendix F: Student Data Warehouse Support

Access Request Process

• Request for access to pilot SDW is approved through Dennis or designate.
• Approval is forwarded to Oracle DBA's
• DBA's verify that the allotted number of users (24) is not exceeded.
• If user count is greater than 24, Dennis or designate is requested to provide a user to have access removed.
• DBA's grant/remove appropriate access and send notification to Dennis.
• Dennis or designate notifies user.

Appendix G – Gaelen Standards

Worksheet ALL – contains all table, columns, “words” from HRMSDIM, BAIRDIM and BAIRFACT broken out as follows
For example owner = BAIRDIM
Table = ACCOUNT_TREE

Page 7
 ETL Team Development Standards - DRAFT

Column = ACCOUNT_CODE
Abbreviation = ACCOUNT
Full English Name = ACCOUNT

Worksheet Glossary – contains all the distinct abbreviations and full English name. The ones highlighted in red are outstanding questions that I would like to
go over.

Worksheet Class words – Contains a set of class words I am proposing to use here at Berkeley. All columns would end with a class word.

Worksheet Questions – a number of abbreviations I do not know the “full English name”. If you could fill out as much as you know and send it back I would
appreciate it

Appendix H - Peoplesoft Table Considerations

Translate table in People Soft will be extracted into the staging area, with Current flag, DW_FEFF_DT and DW_LEFF_DT, then moved to the ODS/DW, changing
target names where necessary. Views will be set up for the codes as the need arises. One view for the current code set another view of the active code set.

Appendix I - Environment Objectives

Development QA Production
• Shared environment for DBA’s, ETL and • Data validation • Ready for general user
Report Developers. • Performance testing access
• Not for data validation • Non-production data can be loaded for test cases and then
• Not intended for functional users refreshed with production data (must be coordinated).

Page 8