Page Comparison

12/4/2017 - 12/22/2017

...

• Fixed issue with a KFS source table changed causing the daily ODS workflow to fail.
  • Column removed from the UCI_LD_BUDGET_CYCLE_T table.
• New Awards by Campus Cognos report design per VCR's request.
  • Collapsed schools row.
  • Created a new drill-through report from the school to dept level.
• Updated all OR annual reports to default to the most recent year in the prompt page prompt.
• Corrected OR dashboard proposals and awards comparison gauge displaying incorrect fiscal periods.
• Updated Aggregated Sponsored Funding Data dashboard to include most recent 5 fiscal years and limited award transaction details to top 20.
• Kanban project board created for OR DWH.
• Updated the Daily_ODS workflow to fail any sessions with rejected rows.
• KFS DWH temporary workflow schedule implemented in preparation for balance forward data.
• Middleware team LDAP update QA/code review session.
• JIRAs completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1604

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1687

• Service-now incidents completed:
  • INC0177686
  • CHG0032546

...

• Informatica Advanced Edition upgrade completed on production.
  • All existing Oracle ODBC connections converted to native.
• KFS cost sharing workflow failure troubleshooting.
  • Issue with bulk load on tables with indexes.
• All Informatica MySQL connection objects created as *_test to point to new DB server.
• New Cognos dashboard reports completed.
  • Aggregated sponsored funding
  • School to dept drill-through
  • Clinical trials vs. Non clinical trials
• Office of Research annual reports review.
• Numerous changes needed to existing reports around report content and formatting. 
• Deployment completed:
  • CTASK0018238
  • CHG0032228
  • CHG0032271
  • CHG0032295
• Service-now incidents completed:
• INC0168571
• INC0168767
• INC0169414
• INC0170149
  

In Progress

• OR Cognos dashboard design version 2.
• Initial planning phases of KC Clinical Trials and Subaward dimensional models.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.

...

• Discussion with POM on refining KFS workflow overnight support.  
  • Adjusted the Ops escalation instructions to call primary support if job isn't restarted within 15 minutes.
• Activity by department and quarter Cognos report completed.
• KFS daily dev workflow schedule changed to resolve failures caused by KFSDAY2 schema refresh.
  • Dev schedule updated to run at 12:05am (confirmation from Kevin Chung).
•  KFS daily workflow enhanced to minimize missteps in POM ops support.
  • Updated all sessions to abort upon failure to minimize ops failure notification time.
  • Updated timer logic to run LD sessions in the event the workflow is started after 2:35am.
• Working with admissions to establish Informatica connections to the sisedw database.
• Testing Informatica connection to the new MySQL dev database (voyager).
• Service-now incidents completed:
• INC0167208
• INC0168461

In Progress

• OR Cognos dashboard design version 2.
• Initial planning phases of KC Clinical Trials and Subaward dimensional models.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.

...

• Informatica Advanced Edition upgrade completed on Dev.
  • Native Oracle driver included with our AE purchase as agreed upon.
• Troubleshooting slow write performance on AWS hosted DBs resolved.
• Converted existing SIS related connection objects to use the native Oracle connector.
• Write performance seems to be drastically faster with Oracle native based on a few small load tests.
• New MySQL DB (Voyager) connection testing.
• Informatica World 2017 conference attendance in San Francisco (5/15-5/18).
• Service-now incidents completed:
  • INC0164989
  • CHG0032086
  • CHG0032084
    

In Progress

• Activity by department Cognos report design/publish.
• OR Cognos dashboard design version 2.
• Initial planning phases of KC Clinical Trials and Subaward dimensional models.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.

...

• Cognos Dashboard initial draft design completed.
• KFS nightly workflow scheduling and monitoring migration to Zena and Operations group complete.
• All OBDC DSNs/Informatica connections changed to use the new production SPA application database.
• New employee (Khader Abdul) onboarding.
• Access requests/general troubleshooting
• UCI Informatica environment overview session
• New Secret Server group (Informatica Admins) created for Informatica support requests.
• Informatica Advanced Edition purchase finalized.  Received software/license keys.
• Awards by Agency (Physical Sciences) Cognos report for OR.
• Office of Research Data Summit event with Stanford.
• Trip planning for Informatica World 2017.
• Service-now incidents completed:
• INC0160061

In Progress

• Troubleshooting slow write performance on AWS hosted DBs.
  •  Researching on how row-by-row processing is done by Informatica when using generic ODBC driver.
• OR Cognos dashboard design version 2.
• Initial planning phases of KC Clinical Trials and Subaward dimensional models.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.

...

• Informatica KSAMS roles with elevated privileges creation as well as removal of old role complete.
• Technical Issue with Informatica sessions going into wait mode.
• Issue being caused by long running LDAP sessions and exceeding the max processes allowed in the dev domain.
• Technical discussion on Informatica file transfer method for the KFS data integration process.
• Order document for Informatica AE upgrade finalized.
• Discussions with Informatica sales rep and licensing specialist to clarify Metadata Manager connector licensing.
  • MDX includes one connector to a RDBMS of choice (Oracle).
  • Need to purchase one additional (SQL Server) to see all data lineage for our KFS mapping.
• Attended the Amazon Web Services Education Data Event.
• Service-now incidents completed:
• INC0158088
• INC0158895
• CHG0031918
• CHG0031927

In Progress

• Cognos Dashboard initial draft design based on feedback from Barbara on requested changes.
• Convert LD_LDGR_ENTR_T table dynamic loading to a native Informatica mapping to minimize impact on KFS nightly loading time.
• Initial planning phases of KC Clinical Trials and Subaward dimensional models.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.
• KFS nightly workflow scheduling and monitoring migration to Zena and Operations group.
  • All prep work completed and ready to deploy Zena Informatica schedule!
  • Planning for a Thursday (4/27) deployment.

...

• Informatica KSAMS roles with elevated privileges creation as well as removal of old role complete.
• Office of Research Cognos Dashboard proof of concept design complete.
  • Initial design include proposal/award activity totals with interactive prompts.
  • Drill-through reports requested for next draft.
• Labor ledger loading with benefits load testing from KFSCURRENT complete.
  • Complete load with historical records (~24m)  takes an additional 157 minutes.
• Nightly KFS loading technical discussion with DWH team to discuss alternative methods to handle the additional processing time with the labor ledger.
  •  Need to separate out the LD_LDGR_ENTR_T table from the dynamic staging and convert to native Informatica.  Reduces load time by ~2hrs.
• Kuali Research Kickoff meeting at Office of Research.
• OR dev database migration connectivity re-testing due to IP address/firewall rule changes.
• Onboarding/orientation for new student intern (Anthony Nguyen).
• Onboarding/orientation for KFS Informatica Consultant (Saurav Chakraborty).
• Service-now incidents completed:
• INC0154838
• INC0156859

In Progress

• Convert LD_LDGR_ENTR_T table dynamic loading to a native Informatica mapping to minimize impact on KFS nightly loading time.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.
• KFS nightly workflow scheduling and monitoring migration to Zena and Operations group.
  • SSH keypair configuration on etl-dev and etl0 in progress.

...

• Complete SPA reports inventory analysis complete for SPA Award dimensional model.
• Final pricing for Informatica Advanced Edition complete.
  • $36,600 for edition upgrade.  Down from initial $50k pricing.
• Performance testing requested by Ellucian of Informatica for SIS to locally hosted Oracle DB complete.
  • No performance issues when writing to KFSDEV.
  • Severe performance issues on Ellucian cloud hosted servers.
• Bio Science dept Talend dashboard demo with Office of Research.
• New development SPA database migration completed.
  • All Informatica/Cognos connections as well as ODBC DSNs updated to new host.
  • Firewall rules now in place for all informatica servers and workstation.
• Onboarding prep work for student intern/Anthony Nguyen complete. 
• Service-now incidents completed:
• INC0153876
• INC0153968
• INC0154073

In Progress

• Informatica KSAMS roles with elevated privileges creation.
  • 2 new roles created.
  • Existing privileges migration
    • Users (complete)
    • Folders (complete)
    • Connection (in progress)
  • Current User role users need to be transitioned to new Developer role.
  • Removal of current User role needs to be requested after PRD deployment is complete.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.
• KFS nightly workflow scheduling and monitoring migration to Zena and Operations group.
  • Workflow execution testing through SSH from Zena host successful on both etl-dev and etl0.
  • Currently waiting for WSG to grant Informatica server access to qaoper.

...

• Informatica Developer Training (3/6-3/9).
• Half-year Activity by School and Year Cognos report design completed and published.
• New development SPA database migration.
  • All Informatica/Cognos connections updated and tested.
• Aiding the Middleware team in configuring/troubleshooting Informatica connectivity to LDAP update only servers.
  • Connectivity failures on port 700 and 701 identified as firewall and resolved.
• JIRA's completed

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1621

• Service-now incidents completed:
• INC0149192
• INC0149685
• INC0150227
• INC0150790
• INC0151578
• CHG0031737
• CTASK0016581

In Progress

• Working on new Informatica KSAMS roles with elevated privileges
  • 2 new roles created.
  • Current User role users need to be transitioned to new Developer role.
  • Removal of current User role needs to be requested after PRD deployment is complete.
• Negotiating pricing for Informatica Advanced Edition as well as core capacity addition.
• New production SPA database migration.
  • All Informatica/Cognos connections as well as ODBC DSNs need to be updated.
  • Firewall rules for production need to be adjusted.
• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.
• KC proposal dimension daily loading mapping design.
• Clinical trials Cognos report for OR (On-hold) 
  • Need additional fields in the proposal ODS table.
  • ETL and Cognos framework package needs to be adjusted.
• Zena Agent install on etl-dev.
  • Mike Story currently testing workflow execution from Zena host.

...

• Additional fact table for KC Negotiation star completed.
• Informatica workflow updated.
• Cognos framework package updated with new star schema grouping updated.
• OR data warehouse related tables migrated to Solar for OIR to access.
• Common award dimension feasibility analysis. 
  • SPA/KFS GAP analysis.
  • Accounting will have Janine create a separate award dimension.
  • Will revisit at a later date.
• Aiding the Middleware team in configuring/troubleshooting Informatica LDAP loading workflows.
  • Connectivity issue to ldap-dev resolved.  Issue related to TLS setting in addition to port setting on the Informatica connection object.
• JIRA's completed

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1611

• Service-now incidents completed:
• INC0146378
  

In Progress

• SPA awards dimensional model design
  • Dimensional modeling
  • Informatica mapping design
  • Cognos Framework model design.
• Informatica session statistics auditing capability survey for COMPASS workflows.
  • Designing POCs for several different design approaches. 
• KC proposal dimension daily loading mapping design.
• Clinical trials Cognos report for OR (On hold) 
  • Need additional fields in the proposal ODS table.
  • ETL and Cognos framework package needs to be adjusted.
• New OR dev and prod database migration.
• All Informatica/Cognos connections need to be changed.
• Firewall rules for production.
• Connectivity testing for production. 
• Zena Agent install on etl-dev.
  • Waiting on firewall exceptions to be completed by the security team.

...

• New awards by purpose Cognos report complete.
• Code review for all PURAP TEM objects for production deployment.
• UCLA Informatica object import for UCPath. 
  • Shared folders created for all source, target, and transformations. 
• Informatica developer training prep work complete.
  • Confirmed for 3/6-3/9 @AIRB1020
• Presentation slides prep work for OIR DW information session.
• JIRA's completed

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1596

• Service-now incidents completed:
• INC0141357
• INC0145432
• REQ0012856
• REQ0012926

In Progress

• Additional fact table creation for KC Negotiation star.
• Aiding the Middleware team in configuring/troubleshooting Informatica LDAP loading workflows.
  • Also migrating/testing connectivity to new ldap-dev server.
• Clinical trials Cognos report for OR (On hold) 
  • Need additional fields in the proposal ODS table.
  • ETL and Cognos framework package needs to be adjusted.
• New OR dev and prod database migration.
• All Informatica/Cognos connections need to be changed.
• Firewall rules for production.
• Connectivity testing for production. 
• Zena Agent install on etl-dev.
  • Waiting on firewall exceptions to be completed by the security team.

...

• UCLC Training
  • Sexual Harassment Prevention
  • Cyber Security Awareness
• Awards by purpose Cognos report for 2015-2017 fiscal years
• Awards by sponsor by school Cognos report for Q1 2017.
• Code Review for OIR Compass ETL.
• Daily GL dev nightly loading changed back to KFSDAY2.
• ETL data flow overview meeting for OIR reporting team.
• SISDEV connectivity from Informatica.
  • stunnel setup complete.
  • ODBC source setup and testing complete on etl-dev.
• JIRA's completed

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1573

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1579

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1580

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1587

• Service-now incidents completed:
• INC0139201
• INC0139206
• INC0140907
• INC0140906
• INC0140557
• INC0142722

In Progress

• UCLA Informatica object import UCPath.
  • Need to create shared folders. 
• Informatica developer training proposal prep work.
  • AIRB training room requested for (3/6-3/9)
  • Received adjusted price quote for Level 1 Developer course.
  • Received customization confirmation from Informatica University.
• Clinical trials Cognos report for OR (On hold) 
  • Need additional fields in the proposal ODS table.
  • ETL and Cognos framework package needs to be adjusted.
• New OR dev and prod database migration.
• All Informatica/Cognos connections need to be changed.
• Firewall rules for production.
• Connectivity testing for production. 
• TEM data mart development code review and deployment prep work.
• Zena Agent install on etl-dev.
  • Waiting on firewall exceptions to be completed by the security team.

...

• KC/SPA Proposals Half-Year report.
  • Due date:  1/13/17
  • Activity by school and month Cognos report completed (1/9).
  • Activity by sponsor and quarter Cognos report completed (1/13).
  • KC null dept code list reconciliation.
• New OR dev and database migration.
  • Firewall rules addition
  • Connectivity testing complete
• Informatica LDAP connector setup on dev server. 
• JIRA's completed

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1573

• Service-now incidents completed:
• INC0138708

...

• Clinical trials Cognos report for OR.
  • Need additional fields in the proposal ODS table.
  • ETL and Cognos framework package needs to be adjusted.
• Issue found with KFS source records with duplicate FDOC_LINE_NBRs for the KFS_F_TEM_ACCTNG fact table.

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSI-13867

• Missing dimension table found KFS_D_TEM_PROFILE

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1441

• TEM data mart development code review and deployment prep work.
• Possible issue with incorrect SIDs in the PO fact tables.
• Zena Agent install on etl-dev.
  • Waiting on firewall exceptions to be completed by the security team.

...

• ODI Technical Training (11/15-11/17) 
• Disbursement data mart development objects code reviewed and deployed to production.
• Encountering discrepancies between the table DDL (unique indexes) and the functional specs.
• Troubleshooting ETL performance issues from etl0 to solar.
  • Resolved after Solar databases migrated to new SSD data center appliance.
• UCI Informatica environment overview for Barathi. 
• Informatica LDAP connector purchase approved and PO submitted.
• JIRA issues completed:

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1543

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1521

• Service-now incidents completed:
  • INC0128157
  • INC0127686

...

• Troubleshooting/fixing new development by Analytix for KFS data mart additions.
  • PO
  • Disbursement
• KFS PO subject area loading merged with daily loading workflow and deployed to production.
• Sponsor category code reconciliation for SPA reports.
• KC Negotiations module dimensional model Cognos Framework design.
  • Sample Cognos reports design as POC for OR functional staff.
• Informatica PowerExchange for LDAP price quote request and purchase justification.
  • Re-negotiated original price from $36k down to $21.6k
• D_DATE dimension holiday column update process completed using staff holiday list compiled by Allison.
• JIRA issues completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1467

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1515

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1257

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1528

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1527

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1534

In Progress 

...

• KC Negotiations module dimensions/fact ETL design
• Discussion with Michael Story regarding implementation of Zena Informatica scheduling.
  • Informatica app server configuration changed to allow for batch scripting for scheduling.
  • Currently waiting for de-allocation of existing Zena client license.
  • Script for command prompt workflow execution sent to Michael.
• Informatica overview presentation slides for brown bag session.
• JIRA issues completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1467

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1478

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1497

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1511

In Progress 

...

• Annual password changes for Informatica connections
• PO data mart Informatica mappings and sessions
  • Merging Analytix development back to the main Daily_* workflows.
  • Audited/corrected slew of issues related to objects not following proper dev standards, unconnected ports, unconfigured session options, and other misc. issues mentioned on numerous occasions.
• Informatica on-site training proposals discussions.
  • Received quotes from 3 vendors - Informatica University, LearnQuest, and ExistBI. 
• Fixed invalid date values in KC.
• In-depth discussion on VM cluster performance degradation.
• Assisting Jane troubleshoot her Informatica development for UC Path. 
• JIRA issues completed:

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1497

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1493

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1495

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1487

  
   

In Progress 

...

• KC Negotiations module dimensional model design completed.
• KC Negotiations module staging ETL completed.
• Testing difference session configurations to optimize session read/write performance.
• KFS balance fact table incremental load strategy review.
• Production deployments completed:

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1472

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1461

• CTASK0012584

...

• KC Negotiations module review for dimensional model design.
• Office of Research Cognos user access migrated from individual to KSAMS roles.
• Resolved firewall issues from Cognos app servers to the KFS ODAs.
• Informatica session log attachment removed from all failed session emails due to inbox attachment file size limit.
• D_DATE dimension table schema moved to edw.
• Annual password change for prod reader accounts.
• JIRAs issues completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1452

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1453

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1408

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1253

• Production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1461

     

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1058

In Progress 

...

• OR Cognos reports demo/training sessions preparation
  • Reviewing functionality of Workspace Advanced
  • Demo reports creation per VC or Research email on data request. 
• SPA COR system reports conversion
  • Currently reviewing the stored procs to convert into an ETL process.
  • Cognos report design. 

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-864

• Legacy data warehouse query for Research Administration missing data issue.
• Glenn has implemented a fix to the spa_dwh extract ETL.
• Still missing 4 records (spa: 2096 vs. spa_dwh: 2092)
• Delayed Informatica dev nightly loading schedule to accommodate ODS data validation.
  • Schedule needs to be restored and connections changed back to KFSDAY2 once complete.
• SIS developer applicants review. 
• Informatica SRAQ.
• Need to schedule meetings with security team.

...

• Annual password change for Informatica - Phase 1
• Cognos Author Professional Reports Training.
• KBM override jobs troubleshooting and validation.
  • Issue with shifted columns affecting the YTD_TRN_TOTAL_HR results resolved.
  • Issue with missing NAME resolved.
• KFS ODS workflow deployment preparation/clean-up.
  • Replaced sources and targets not using shortcuts.
  • Removed redundant sessions.
  • Renamed objects not following naming conventions.
• Insufficient permissions issue resolved for the dwhreader account on KFSPROD.
• KC ODS report specifications documentation write-up.
• JIRA/Service Now Tasks completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1376

  • PRJTASK0019725

In Progress 

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-864

• Legacy data warehouse query for Research Administration missing data issue.
  • OR users reported low proposal counts.
  • Narrowed the issue down to the DTS extracts from the SPA database.
  • Issue may be related to records with and without timestamps.
• Delayed Informatica dev nightly loading schedule to accommodate ODS data validation.
  • Schedule needs to be restored and connections changed back to KFSDAY2 once complete.
• IBM Cognos Report Studio Author Active Reports training registration.
• Informatica SRAQ.
• Need to schedule meetings with security team.

...

• All config changes for the KFS/KC production database migration to ODAPRD completed without issues.
• SFTP shell script template design for ETL developers.
• Informatica production server (etl0) CPU core count addition completed and verified.
  • CPU utilization down to approx ~50% at peak times.
• Tenable security center training.
• KFS_D_FISCAL_PERIOD current flag not being correctly set.
  • Temporary fix implemented until Pramod has the new fiscal period logic in place.
  • Need to remove the post sql update (~end of June) in the Informatica session. 
• Assisting team members with any connectivity issues (firewall, DSN, etc.) to new KFSPRD, KFSSTAGE.
• JIRA/Service Now Tasks completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1324

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1338

  • PRJTASK0020116
  • PRJTASK0019726

...

• Kuali Coeus ODS table field additions requested by OR for proposal workflow reporting.
  • Informatica ETL adjusted with additional logic.
  • Cognos framework model updated.
  • Cognos report adjusted with new fields.
• Compiled/executed DDLs to merge all differences between the dss and dss_kfs databases.
  • Prep work for dss_kfs to dss migration. 
• JIRA issues completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1255

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1256

• Code review/production deployments completed:

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1217

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1247

• Resolved KFS_F_LEDGER_ENTRIES loading issue causing transaction duplication.
  • Adjusted the mapping and converted the dss/dss_kfs database name to a variable to account for dev/prod DB name difference.
• Aiding in troubleshooting various Informatica ODS workflows issues/enhancements.

...

• Common KFS/SPA award dimension discussion.
• Production deployment standards/checklist discussion with Analytix.
• JIRA issues completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1231

• Code review/production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1210

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1223

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1204

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1235

• Aiding OIR members with Informatica usage and troubleshooting.
• Aiding in troubleshooting various Informatica ODS workflows issues/enhancements.

...

• Code review - dimension loading session deployed but waiting on Analytix for completion of the facts table loading mappings. 

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1217

• Informatica SRAQ.

...

• Technical specs documentation for completed SPA Cognos reports.
• Code review with Analytix for completed Informatica objects.
• MySQL database timeout error resolved.
  • Converted lookups/SQL overrides into local joiner transformations and set timeouts thresholds in the session.
• Award dimension for the EDW discussion.
  • Need a follow up discussion to decide on level of granularity (award vs. synopsis cycle) 
• Code review/production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1171

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1183

• D_DATE dimension adjusted with additional fiscal date columns and renamed existing columns according to established DWH standards.
• Aiding in troubleshooting various Informatica ODS workflows issues/enhancements.

...

• Code review

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1116

• Informatica SRAQ.

...

• Changed all connections from old oda2 server to the new odadev server.
  • Firewall rule changes.
  • Informatica connection objects (KFSDEV, KCDEV)
  • System DSNs
  • Linked servers (Lunar, Nebula)
• Updated all Informatica OLTP sessions/workflows on dev/prod with schema variables to avoid job failures for any future schema migrations in Oracle.
• Code review/production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1151

• Aiding in troubleshooting various Informatica ODS workflows issues/enhancements.

...

• All requested SPA system tables added to existing Cognos framework model.
• Direct database connectivity setup to Tick@Lab "export" database completed.
• Code review/production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1120

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1128

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1143

• Informatica dev repository objects cleaned up to remove duplicates and renamed to proper standards.
• Cognos Framework Manager training session for OR.
• Troubleshooting KFS_D_ORG loading issue.

...

• Technical specs documentation for completed SPA Cognos reports.

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1151

• Informatica SRAQ.

...

• Technical specs documentation completed for initial set of published SPA annual reports.
• Production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1101

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1108

• Cognos Workspace Advanced demo for OR.
  • New data elements around Prime Sponsor and Research Agreements requested to be added to existing SPA framework.
  • Looking into where the data can be sourced from and how to add to the ODS.
• Cognos data source connectivity issue to the SPA system troubleshooting.
  • Issue resolved after adding firewall egress rule to allow from the Cognos servers to SPA host machine. 
• Informatica client setup/KSAMS role request for Andrew Do complete.

...

• Mapping template for ODS loading workflow conversion completed for Bryce.
• Talend to Informatica KSAMS web services conversion is ready for deployment and test running on dev.
• Production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1077

• Meetings with Raj regarding new requests from the OR team.
• Informatica version control deployment on the dev repository.
• Informatica tutorial sessions.

...

• Informatica dev to prod deployment strategy using deployment groups.
• PowerExchange for Web Services activated.
  • Informatica sample mapping from public web service.
• Production deployments completed:

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1068

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1052

In Progress 

...

• KFS_F_LEDGER_ENTRIES loading logic change from full to incremental load.
• Loading time reduced from approx. 2hrs to <5mins.
• Source schema for the dev daily KFS refresh changed to KFSDAY2 to maintain scrubbed data.
• Informatica ETL to check for a valid current flag row in the KFS_D_FISCAL_PERIOD dimension completed.
• Informatica TEM_* tables staging loading mapping/session completed.
• Production deployments completed:

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-992

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1001

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1005

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1008

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1023

  • Jira Legacy
    server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-1030

• UCI_LD_CSF_TRACKER_SNAPSHOT missing data troubleshooting.
  • Talend ETL logic miscalculation causing same period of the previous year to be deleted in addition to current year.
  • Restored data from July 2015 database backup.

...

• Sponsored Projects legacy data warehouse java code review.
• Sponsored Projects legacy data warehouse converted to an Informatica ETL process.
• Made adjustments to the KFS daily staging workflow to suspend/notify if loading errors are encountered.

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-883

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-888

• Medical center Informatica support.
  • Resolved some connectivity issues between med center databases.
• Informatica client installation/configuration for Matt 

...

• Office of Research annual report design.
• Contract vs. Grant
• Recipients of Awards 1 million plus
• Top 20 Recipients of Contract and Grant Awards
• OR team Informatica client software installation/configuration complete.

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-828

In Progress 

• Aiding with troubleshooting/testing of Talend/Informatica conversion.
• Waiting for new ARC payments files (from James Hsu) to re-run DWHDUMP.GL_AR_RECONCILIATION table.

...

• SQL stored procedure review for additional OR annual reports.
• Adjusting OR related ETL mappings to accommodate new reporting requirements.
• UC_ACCOUNT_T Informatica mapping migration and scheduling.
• Informatica domain security policy audit.
• Office of Research annual report design.
  • Awards by Campus Area
  • Awards by Purpose and Campus Area
  • Awards by Source and Campus Area
  • New and Renewal Awards by Campus Area
  • New and Renewal Proposals by Campus Area
  • New and Renewal Proposals by Research Unit
  • Proposals by Campus Area
  • Proposals by Research Unit

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-792

In Progress 

• Informatica debugger feature troubleshooting.
  • Waiting for firewall team to open ports.
• Aiding with troubleshooting/testing of Talend/Informatica conversion.

...

• Code review and Informatica usage tutorial with Durendal for UC_ACCOUNT_T dev to prod deployment.

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-486

• Worked with Matt to rename the production Informatica repository to resolve dev/prod duplicate name issues.
  • Duplicate name won't cause any errors but will make it more difficult to deploy objects between repositories.
• System DSN and Informatica connection object created for KC dev and prod databases.
  • KC table source definition import and relationships set.
• Office of Research report SQL query review.

...

• Troubleshooting production Informatica repository connectivity from local machine.
  • Matt was able to retain the fully qualified domain name in the Informatica admin console so no significant adjustments were necessary from the client.
• Informatica overview powerpoint design and presentation for OIT audience.
• Setup MySQL Workbench and MySQL drivers to help with Informatica connectivity.
• System DSN and connection objects creation for the MySQL databases.
• Informatica mapping creation for repopulation of MySQL UC_ACCOUNT_T table.

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-721

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-737

• Setup Oracle SQL Developer and connections to KC dev/prod databases.

...

• Awards by Funding Agency (Cognos)
• New and Renewal Awards by Funding Agency (Cognos)
• Proposals by Funding Agency (Cognos)
• New and Renewal Proposals by Funding Agency (Cognos)
• Award Totals for Last 10 Years (Cognos)
• Proposal Totals for Last 10 Years (Cognos)
• SPA ODS / Cognos report presentation for the OR OIT team.
• Research administration data warehouse dataflow overview slide.
• Oracle to SQL Server TIMESTAMP data type compatibility issue resolved.

• Jira Legacy
  showSummary true server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-615

• Jira Legacy
  server OIT System JIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18 key KFSDW-707

• Kuali Coeus proposal workflow overview.

...