Etl Development Standards 39113f

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 istration and Maintenance ............................................................................................................................... 5 Informatica Server Reboot Procedures ...................................................................................................................................................................................................5

Appendix B: Appendix C: Appendix D: Appendix E: Appendix F:

BAIRS Outages.............................................................................................................................................................................. 6 Issues Log....................................................................................................................................................................................... 6 Month-End Procedures .................................................................................................................................................................. 7 Archiving Procedures ..................................................................................................................................................................... 7 Student Data Warehouse ................................................................................................................................................... 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 ins updt scd copy -

Delete Insert Update Slowly Changing Dimensions 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__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 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 (_RO and _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 id = ‘BISWJC’ Where oprid = ‘011502567’ (or ‘011504738’)

2.

Apply for security access through SARA (HRMS Dept. Security, ister 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] Enter objects to be moved into TSO MIGMGR - describe what needs to be moved and when. (e.g. move xxx from EDW.PUB.STAGE.INCLIB to ASD.P.CTM.BIS.AEVARS).

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

Appendix A: Informatica istration 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 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 s cannot run reports. Post a message in the report portal as soon as it is available. • Send listserves per doc in folder. • Ask Michael to make specific reports or folders unavailable to s so we can allow other s to run reports until fin/pb load is complete. • When loads have completed, tell Quin/DBA’s: please reset access so s 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\\Financials\Production\AP PO Archive procedure.doc for details on AP/PO archiving procedures.

Appendix F: Student Data Warehouse Access Request Process • • • • • •

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

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 = _TREE

Page 7

ETL Team Development Standards - DRAFT Column = _CODE Abbreviation = Full English Name = 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 • Shared environment for DBA’s, ETL and Report Developers. • Not for data validation • Not intended for functional s

QA • • •

Data validation Performance testing Non-production data can be loaded for test cases and then refreshed with production data (must be coordinated).

Page 8

Production • Ready for general access