Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 107 Next »

12/4/2017 - 12/22/2017

Completed

  • KC clinical trials dimension tables loading ETL design completed.
  • KC clinical trials dimension tables initial loading workflow completed by Allison.
  • KC clinical trials fact table loading workflow completed.
  • LDAP connection object cutover to ldap-auth.oit.uci.edu.
  • Monthly spend radar workflow deployed to the Tst folder for loading validation.
  • Tst Daily_OLTP schedule removed due to conflicts with the Dev workflow writing to the same database causing missing tables for KFS web services.
  • Informatica core licensing expansion justification discussions.
  • Adjusted the COMPASS Informatica workflow schedule to start outside of the nightly KFS workflows to reduce CPU load.
  • KFS7 upgrade deployment discussions.
    • Need a weekend deployment of all KFS related workflows on 2/17 (Sat).
    • Schedule first run in production post upgrade for 2/18 (Sunday).
  • Prod OR-PROD-DB server migration.
    • All production Informatica connection objects updated with new hostname.
  • Troubleshooting nightly Informatica workflow sessions in "waiting" status.
    • Issue caused by a failed MySQL database backup job causing blocking for the Daily_OLTP workflow and downstream sessions to wait for an empty run slot.
    • Increased the maximum concurrent sessions configuration from 10 to 20 to alleviate the issue.
    • Production Incident Report
  • Informatica intro session for the HR IT team.
  • Service-now incidents completed:

In Progress

  • Waiting for confirmation on STG_LOAD_GEC_ERROR_CHECK_D_FUND_OVERDRAFT session removal from ODS workflows.
  • Reviewing Cognos 11 Dev.
    • All Office of Research content re-imported with most recent developed reports.
    • Regression testing all OR reports.
    • Reviewing the revamped UI and new features.

Planned

  • Subaward dimensional model design.
  • KC proposal dimensional model design.
  • SPA awards dimensional model design (On-hold for KR launch).


11/20/2017 - 12/1/2017

Completed

  • Informatica core addition pricing negotiation.
    • Purchase by 12/15/17 - License: $57,420 / Maintenance: $12,632 / Total First Year: $70,052
  • Dev OR-DEV-DB server migration.
    • All dev Informatica connection objects updated with new hostname. 
  • KC clinical trial staging loading ETL design.
  • Informatica connectivity issue with Oracle 12c database instance TICKTEST.
    • Updated the connect string to use ODBC DSN name in lieu of direct connect.
  • Work estimate for Pinnacle (new telephone billing system) data integration task.
  • KR COI application workflow project meeting.
    • COI team needs a daily report (Cognos) of awards with the current conflict of interest disclosure form submission info.
  • Temporary schedule change for KFS7 Tst workflows.
  • Daily OLTP dev workflow troubleshooting for missing web services tables.
    • Issue being caused by conflict with both the dev and tst workflow writing to the same database instance (Nebula). 
  • New UCPath/KFS consultant (Sujith Gadagottu) onboarding.
  • Service-now incidents completed:

In Progress

  • Waiting for confirmation on STG_LOAD_GEC_ERROR_CHECK_D_FUND_OVERDRAFT session removal from ODS workflows.
  • Reviewing Cognos 11 Dev.
    • All Office of Research content re-imported with most recent developed reports.
    • Regression testing all OR reports.
    • Reviewing the revamped UI and new features.

Planned

  • Subaward dimensional model design.
  • KC Proposal dimensional model design.
  • SPA awards dimensional model design (On-hold for KR launch).


11/6/2017 - 11/17/2017

Completed

  • ACHIEVE performance evaluation forms.
  • KFS cost sharing workflow troubleshooting.
    • Session config issue related to custom DTM buffer size.
  • Troubleshooting KFS DWH workflow slowness on Solar.
    • Issue looks to be related to blocking from row count validation sproc.
  • Unnecessary LOAD_PUR_SEPARATED_CONTR_MGR_T session removed from dev and prod ODS workflow.
  • Service-now incidents completed:

In Progress

  • KC clinical trial staging loading ETL design.
  • Informatica core addition pricing negotiation.
  • Reviewing Cognos 11 Dev.
    • All Office of Research content re-imported with most recent developed reports.
    • Regression testing all OR reports.
    • Reviewing the revamped UI and new features.

Planned

  • Subaward dimensional model design.
  • KC Proposal dimensional model design.
  • SPA awards dimensional model design (On-hold for KR launch).


10/23/2017 - 11/3/2017

Completed

  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).
    • Cognos folders permissions updated to allow the "OR Cognos Report Viewer" KSAMS role read permissions.
    • Testing and validation of access/functionality with new permissions in the Office of Research folder completed.
  • OR Decision Support Zot!Portal portlet deployed to production.
    • Permissions adjusted to reflect the OR Cognos Report Viewer role accsess.
    • Allows streamlined access to the PI Credit Tool.
  • Investigating prompt saving issue related to max character limit.
    • Currently limitation exists in the Cognos stored procedure call - 8000 character limit.
    • Per Durendal, there are plans to address the limitation (make split calls) as part of the Cognos 11 upgrade - no ETA.
  • KC Clinical Trials dimensional model completed and tables created.
  • Existing OR dimensional models migrated to new "dss_or" schema from the "dss_kc" schema.
  • Schedule change to 2am for KFS7 test workflow.
  • New version of the PI Credit Report tool published.
    • Added a second drill through to allow for viewing all PIs and Co-PIs involved in an award. 
  • KFS_F_LEDGER_T loading strategy review.
    • Currently only in dev loading to prod from its own workflow.
    • Session lasts approximately 1 hour possibly passing our ETL SLA.
    • Need to change load to incremental.
  • Service-now incidents completed:

In Progress

  • Reviewing Cognos 11 Dev.
    • All Office of Research content re-imported with most recent developed reports.
    • Regression testing all OR reports.
    • Reviewing the revamped UI and new features.
  • ACHIEVE performance evaluation forms.

Planned

  • Subaward dimensional model design.
  • KC Proposal dimensional model design.
  • SPA awards dimensional model design (On-hold for KR launch).


10/9/2017 - 10/20/2017

Completed

  • All Research Administration annual reports modified to allow for quarterly reporting.
    • Added filters to all report queries to compare fiscal quarter based on "processing date".
    • Updated prompt pages to allow for a quarter selection.
  • Working with OR to fix a SPA data issue related to an incorrect cumulative award amount.
  •  Additional data elements added to the Office of Research Cognos report package to allow for SPA Officer reporting.
    • Added additional ETL logic to the Informatica mapping.
    • SPA_OFFICER_FIRST_NAME, SPA_OFFICER_LAST_NAME, SPA_OFFICER_FULL_NAME columns added. 
  • Gathering work and price estimates for the Informatica v10 upgrade.
    • EKS&H - $14,920 ($17,380 with contingency budget)
    • Informatica Professional Services - $57,360 (Onsite) / $35,652 (Onsite/Offshore blended)
    • KPI Partners - $70,800 (Onshore) / $32,400 (Offshore)
  • SSL certificate renew completed for etl-dev.
  • Service-now incidents completed:

In Progress

  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).
    • Currently adjusting Cognos folders permissions to allow the "OR Cognos Report Viewer" KSAMS role read permissions.
    • Barbara is in the process of adding all the School Deans to the report viewer role to allow access to the PI Credit Tool. 
    • Working with OR student worker (Omar) to validate correct access/functionality with new permissions in the Office of Research folder.
  • Clinical Trials Dimensional Model design. 

Planned

  • Subaward dimensional model design.
  • KC Proposal dimensional model design.
  • SPA awards dimensional model design (On-hold for KR launch).

9/25/2017 - 10/6/2017

Completed

  • Design a "Credit" reporting tool to allow school deans to view proposals/awards numbers for all PIs/Co-PIs (dual credit).
    • ETL design completed.
    • Cognos report draft cut completed.
    • Currently waiting on final feedback on design and any changes required if necessary.
  • Office of Research annual reports redesign discussion to allow for quarterly reporting.
    • Vice chancellor requests for quarterly trend analysis.
  • Attended Symplectic Dimensions training session.
    • Vendor tool that allows for comparing research funding across institutions globally.
  • Service-now incidents completed:

In Progress

  • Annual reports modification.
    • Analyze how to match same time period year over year in official snapshots.
    • Cognos report prompting design to allow for a quarter selection.
  • Gathering work and price estimates for the Informatica v10 upgrade.
    • Working with Informatica Professional Services, EKS&H, and KPI Partners.
    • EKS&H - $14,920 ($17,380 with contingency budget)
    • Informatica Professional Services - $57,360 (Onsite) / $35,652 (Onsite/Offshore blended)
    • KPI Partners - $70,800 (Onshore) / $32,400 (Offshore)
  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).
  • SSL certificate expiration/renewal for etl-dev.

Planned

  • 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.


9/11/2017 - 9/22/2017

Completed

  • Assisting in getting the Informatica servers connected to DCE PowerCampus LIve database.
  • Test KFS workflows changed to read from the KFS7CNV schema.
  • Assisting the middleware team with converting Informatica workflow schedules to Zena schedules.
  • Symplectic Elements Demo attendance.
  • OR data collaboration meeting to discuss data challenges in both access and reconciliation.
  • Service-now incidents completed:

In Progress

  • Design a "Credit" reporting tool to allow school deans to view proposals/awards numbers for all PIs/Co-PIs (dual credit).
    • Analysis needs to be done on source tables to figures out the logic to pull the correct data.
    • Cognos report design discussion with OR.
    • ETL design in progress.
  • Gathering work and price estimates for the Informatica v10 upgrade.
    • Working with Informatica Professional Services, EKS&H, and KPI Partners.
  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).
  • SSL certificate expiration/renewal for etl-dev.

Planned

  • 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.


8/28/2017 - 9/8/2017

Completed

  • SPA sponsor category code list update.
    • Data validated in maintenance table as well as transactional tables in OR Dev DB.
  • Post deployment support for KFS workflow for Modesto.
    • Issues with target load type setting and scheduling.
  • Proposals data extract for Jonathan.
    • Identified 3 duplicate proposals in KC that needs to be removed.
  • Service-now incidents completed:

In Progress

  • Design a "Credit" reporting tool to allow school deans to view proposals/awards numbers for all PIs/Co-PIs (dual credit).
    • Cognos report design discussion with OR.
    • ETL design in progress.
  • Gathering work and price estimates for the Informatica v10 upgrade.
    • Working with Informatica Professional Services and EKS&H.
  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).
  • SSL certificate expiration/renewal for etl-dev.

Planned

  • 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.


8/14/2017 - 8/25/2017

Completed

  • Data Warehouse presentation slides for Kian's OIT All Staff Meeting.
    • Complied metrics (counts for data elements and tables) for all data domains currently in dev as well as prod.
  • New "OR Decision Support" portlet created in staging Zot portal for Annual reports access for the campus.
  • SPA data extract for Awards by Purpose by Funding Source Cognos report.
  • Connectivity issue from etl0 to production LDAP resolved.
    • Issue resolved after SSL setting change on LDAP server.
  • Testing JIRA project/project board access for non KFS roles.
  • Symplectics Elements demo.  Web-based vendor tool that can use in-house data to create report and dashboards with faculty funding, finances, protocol, and publication info.
  • Informatica onboarding for Middleware team developer (backup for Jan-Ling).
  • Service-now incidents completed:

In Progress

  • Gathering work and price estimates for the Informatica v10 upgrade.
    • Working with Informatica Professional Services and EKS&H. 
  • Design a "Credit" reporting tool to allow school deans to view proposals/awards numbers for all PIs/Co-PIs (dual credit).
  • SPA sponsor category code list update.
    • Deployment scheduled for 9/6/17. 
  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).
  • SSL certificate expiration/renewal for etl-dev.

Planned

  • 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.


7/31/2017 - 8/11/2017

Completed

  • Creation of new set of KSAMS role for access control to OR data through Cognos.
    • Looking into controlling access to folders as well as Cognos capabilities.
  • Compiled list of awards and proposals showing "ICS"  as department to reconcile dept mapping.
  • Temporary schedule change for KFS DWH loading in preparation for the balance forward data.
  • 2018 fiscal year cutover for KFS DWH loading.
  • Service-now incidents completed:

In Progress

  • Creating a new portlet in the Zot portal for OR decision support quick access.
  • SPA sponsor category code list update.
  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).

Planned

  • Design a "Credit" reporting tool to allow school deans to view proposals/awards numbers for all PIs/Co-PIs (dual credit).
  • 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.

 

7/17/2017 - 7/28/2017

Completed

  • 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:
  • Service-now incidents completed:

In Progress

  • Creating a new portlet in the Zot portal for OR decision support quick access.
  • SPA sponsor category code list update.
  • Creation of new set of KSAMS role for access control to OR data through Cognos.
    • Looking into controlling access to folders as well as Cognos capabilities.
  • Identifying solutions to allow for granular access to OR Cognos folders for report viewing (i.e. School Deans).

Planned

  • Design a "Credit" reporting tool to allow school deans to view proposals/awards numbers for all PIs/Co-PIs (dual credit).
  • 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.

7/3/2017 - 7/14/2017

Completed

  • SPA data issues for correct reporting resolved.
    • Changing award numbers across fiscal years.
    • Missing sponsor codes.
    • Multiple official snapshots for the same fiscal year.
  • 250GB additional disk space added to both dev/prod Informatica servers.
  • Latest iteration of Office of Research Cognos annual reports design completed with a complete visual design overhaul and interactive report prompting. 
    • Awards by Campus Area
    • Awards by Funding Agency
    • Awards by Purpose by Campus Area
    • Awards by Purpose by Funding Source
    • Awards by Source by Campus Area
  • Testing/creation of all Informatica connections for the new MySQL DB (Voyager) migration.
  • Schedule changed for fiscal year end processing for KFS daily workflows.
  • New JIRA project created for OR research administration DWH to keep track of OR related tasks.
  • Assisting with the mapping design of the award dimension table.
  • Additional process dependency put in place on Zena schedule for KFS workflows to check for KFS batch processing completion.
  • Assisting students with Oracle client install.
  • Service-now incidents completed:

In Progress

  • SPA sponsor category code list update.
  • Creation of new set of KSAMS role for access control to OR data through Cognos.
    • Looking into controlling access to folders as well as Cognos capabilities. 
  • 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.

Planned

  • 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.
  • Informatica SRAQ.

6/19/2017 - 6/30/2017

Completed

  • Office of Research annual reports design.
    • First iteration of the awards by campus area report completed. 
  • Annual reports requirements discussion meetings
    • Overall design changes around user interaction, drill-throughs, removal/addition of data elements.
    • Home dept vs. admin dept for all reports.  Possible need to have dynamic report generation based on user selection. 
    • Remove decrepit annual reports.
    • Combine multiple reports into one with interactive prompts.
  • Created test environment for the KFS7 upgrade.
    • Exported/re-imported all daily workflows into their own set of folders for testing.
    • Created new set of connection objects that point to new KFS7 DBs.
  • Assisting with the mapping design of the award dimension table.
  • Updated UCPath repository folders permissions to allow Sushma full control.
  • Deployments completed:
  • Service-now incidents completed:

In Progress

  • Working to resolve multiple SPA data issues for correct reporting.
    • Changing award numbers across fiscal years.
    • Missing sponsor codes.
    • Multiple official snapshots for the same fiscal year.
  • Dev workflows failing due to disk space issue on the Infa server.
    • Need to request additional space for both servers. 
  • 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.

Planned

  • 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.
  • Informatica SRAQ.

6/5/2017 - 6/16/2017

Completed

  • 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:
  • Service-now incidents completed:

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.

Planned

  • 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.
  • Informatica SRAQ.

 

5/22/2017 - 6/2/2017

Completed

  • 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:

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.

Planned

  • 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.
  • Informatica SRAQ.


5/8/2017 - 5/19/2017

Completed

  • 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:

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.

Planned

  • 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.
  • Informatica SRAQ.


4/24/2017 - 5/5/2017

Completed

  • 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:

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.

Planned

  • 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.
  • Informatica SRAQ.

 

4/10/2017 - 4/21/2017

Completed

  • 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:

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.

Planned

  • New production SPA database migration  (On-hold due to issues with other OR application).
  • Informatica session statistics auditing capability survey for COMPASS workflows (On-hold).
    • Designing POCs for several different design approaches.
  • 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.
  • Informatica SRAQ.

 

3/27/2017 - 4/7/2017

Completed

  • 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:

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.

Planned

  • New production SPA database migration  (On-hold due to issues with other OR application).
  • Informatica session statistics auditing capability survey for COMPASS workflows (On-hold).
    • Designing POCs for several different design approaches.
  • 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.
  • Informatica SRAQ.

 

3/13/2017 - 3/24/2017

Completed

  • 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:

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.

Planned

  • New production SPA database migration  (On-hold due to issues with other OR application).
  • Informatica session statistics auditing capability survey for COMPASS workflows (On-hold).
    • Designing POCs for several different design approaches.
  • 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.
  • Informatica SRAQ.


2/27/2017 - 3/10/2017

Completed

  • 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
  • Service-now incidents completed:

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.

Planned

  • Informatica session statistics auditing capability survey for COMPASS workflows (On-hold).
    • Designing POCs for several different design approaches.
  • Informatica SRAQ.


2/13/2017 - 2/24/2017

Completed

  • 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
  • Service-now incidents completed:

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.

Planned

  • Informatica SRAQ.


1/30/2017 - 2/10/2017

Completed

  • 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
  • Service-now incidents completed:

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.

Planned

  • Informatica SRAQ.


1/16/2017 - 1/27/2017

Completed

  • 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
  • Service-now incidents completed:

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.

Planned

  • Additional fact table creation for KC Negotiation star.
  • Informatica SRAQ.


1/3/2017 - 1/13/2017

Completed

  • 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
  • Service-now incidents completed:

In Progress

  • Informatica developer training proposal prep work.
    • AIRB training room requested for (3/6-3/9)
    • Received adjusted price quote for Level 1 Developer course.
  • SISDEV connectivity from Informatica.
    • Need stunnel setup 
    • ODBC source setup and testing.
  • 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.

Planned

  • Additional fact table creation for KC Negotiation star.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.


12/19/2016 - 12/23/2016

Completed

In Progress

  • KC/SPA Proposals Half-Year report.
    • Due date:  1/13/17
    • Outcome of KC technical discussion:  OR team will adjust SPA import process to include proposals that have a submit date in KC.
  • Clinical trials Cognos report for OR (Currently on hold due to half year report) 
    • 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.
    • Connectivity testing. 
  • 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.

Planned

  • Additional fact table creation for KC Negotiation star.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.

 

12/5/2016 - 12/16/2016

Completed

  • Issue with KFS source records with duplicate FDOC_LINE_NBRs for the KFS_F_TEM_ACCTNG fact table.
    • KFS team notified of bug.  Issue fixed with newest release of KFS. 
  • Missing dimension table (KFS_D_TEM_PROFILE) development/testing complete.
  • Incorrect SIDs in the PO fact tables resolved
    • Pramod identified the PO_ITM_SID column in dev was incorrectly setup as an identity field.
  • Performance logs setup on etl-dev.
  • Received the setup files and license key for the Informatica LDAP connector.
    •  Software installed and necessary config changes completed.
  • Core Supervision Training Courses
    • Interactive Process/Reasonable Accommodation and FMLA
    • Worklife and Engagement
  • Service-now incidents completed:

In Progress 

  • KC/SPA Proposals Half-Year report.
    • Due date:  1/13/17
  • Clinical trials Cognos report for OR (Currently on hold due to half year report) 
    • 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.
    • Connectivity testing. 
  • 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.

Planned

  • Additional fact table creation for KC Negotiation star.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.

 

11/21/2016 - 12/2/2016

Completed

  • Negotiations star schema demo and Cognos training sessions for OR.
  • SPA award count logic adjustment to exclude administration cycles and add two additional measures.
  • SPA quarterly report design with new award count logic and additional measures.
  • SPA On Time Proposal Submissions by Dept.
  • Low proposal totals (counts and dollar amounts) troubleshooting.
    • Issue due to timestamps in source proposal records integrated from KC to SPA system.
  • SFTP transfer to Informatica server for zot codes file testing completed.
  • Service-now incidents completed:

In Progress 

  • 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.
  • Missing dimension table found KFS_D_TEM_PROFILE
  • 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.

Planned

  • Additional fact table creation for KC Negotiation star.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.


11/7/2016 - 11/18/2016

Completed

  • 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:
  • Service-now incidents completed:

In Progress 

  • SPA award count logic adjustment to exclude administration cycles and add two additional measures.
    • Award history table.
  • SPA quarterly report design with new award count logic and additional measures.
  • Continuing to encounter various issues with incorrect unique indexes on tables for TEM.
    • KFS_D_TEM_EXPENSE
    • KFS_D_TEM_PERDIEM 
  • 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.
  • Various Informatica support for developers/new hires.

Planned

  • Informatica SRAQ.
    • Need to schedule meetings with security team.


10/24/2016 - 11/4/2016

Completed

  • SPA award count logic adjustment to exclude administration cycles and add two additional measures.
    • Award table loaded with new measures. 
  • SPA monthly report conversion (stored procedure to Cognos report) complete.
    • ETL adjustments complete.
  • Informatica Analyst overview and usage presentation.
  • Banner ODS training.
  • Informatica LDAP connector purchase request approved by the Acquisition Review Team.
  • Service-now incidents:

In Progress 

  • SPA award count logic adjustment to exclude administration cycles and add two additional measures.
    • Award history table.
  • SPA quarterly report design with new award count logic and additional measures.
  • Zena Agent install on etl-dev.
    • Waiting on firewall exceptions to be completed by the security team.
  • Troubleshooting ETL performance issues from etl0 to solar.
    • Continuing to experience semaphore timeouts from Solar.

Planned

  • Informatica SRAQ.
    • Need to schedule meetings with security team.


10/10/2016 - 10/21/2016

Completed

  • 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:

In Progress 

  • SPA award count logic adjustment to exclude administration cycles and add two additional measures.
    • Administrative cycle count
    • Award cycle count. 
  • SPA monthly/quarterly reports design.
    • Based on review of the sprocs, ETL adjustments are required for the daily OR loading.
    • New Cognos reports need to be designed.
  • Zena Agent install on etl-dev.
    • Waiting on firewall exceptions to be completed by the security team.
  • Troubleshooting ETL performance issues from etl0 to solar.
    • Continuing to experience semaphore timeouts from Solar.

Planned

  • Informatica SRAQ.
    • Need to schedule meetings with security team.


9/26/2016 - 10/7/2016

Completed

  • 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:

In Progress 

  • Troubleshooting/fixing new development by Analytix for KFS data mart additions.
    • PO
    • Disbursement
  • 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.
  • UCI staff holiday list compilation for update to the date dimension.
  • Zena Agent install on etl-dev.
    • Waiting on firewall exceptions to be completed by the security team.
  • Troubleshooting ETL performance issues from etl0 to solar.
    • Incremental fact table loading deployed to alleviate performance issues.
    • Continuing to encounter semaphore timeouts from Solar.

Planned

  • Informatica SRAQ.
    • Need to schedule meetings with security team.

 

9/12/2016 - 9/23/2016

Completed

  • 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:

In Progress 

  • KC Negotiations module dimensions/fact ETL design.
  • Zena Agent install on etl-dev.
    • Stalled installation due to firewall issues. 
  • 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.
  • Troubleshooting ETL performance issues from etl0 to solar.
    • Write bottleneck suspected from the session logs on solar.
    • Data Center team has moved the solar server vm to same physical host as lunar.
    • DBA team suspect an abundance of CPUs causing cxpacket waits and will add 2 additional CPUs to lunar to confirm slowness.

Planned

  • Informatica SRAQ.
    • Need to schedule meetings with security team.

8/29/2016 - 9/9/2016

Completed

  • 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:

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.
  • Troubleshooting ETL performance issues from etl0 to solar.
    • Write bottleneck suspected from the session logs on solar.
    • Data Center team has moved the solar server vm to same physical host as lunar.
    • DBA team suspect an abundance of CPUs causing cxpacket waits and will add 2 additional CPUs to lunar to confirm slowness.

Planned

  • Informatica SRAQ.
    • Need to schedule meetings with security team.


8/15/2016 - 8/26/2016

Completed

  • 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:
  • Production deployments completed:

In Progress 

  • Troubleshooting ETL performance issues from etl0 to solar.
    • Write bottleneck suspected from the session logs on solar.
  • Testing difference session configurations to optimize session read/write performance.
  • KFS balance fact table incremental load strategy review.
  • KC negotiations ETL staging workflow.

Planned

  • Informatica SRAQ.
    • Need to schedule meetings with security team.
  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).


8/1/2016 - 8/12/2016

Completed

  • Update all ODS worklets to check succeeded sessions status.
  • On-time Proposal Submission Data and Graph Cognos report completed per OR user request.
  • SPA COR system reports conversion 
    • ETL to populate spa proposal and spa award tables in ODS.
    • SPA Award Data on given PI Cognos report completed.
    • SPA Proposal Data on given PI Cognos report completed.
  • Fiscal year logic addition to KC proposal ODS table.
  • User date prompt addition to all KC Cognos reports.
  • All OLTP loading sessions changed from drop table/load to truncate/load.
  • PREQ and disbursement Analytix mapping design code review.
  • ODS loading workflow Informatica production deployment.

In Progress 

  • 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)
  • KC Negotiations module review for dimensional model design. 
  • Informatica SRAQ.
    • Need to schedule meetings with security team.

Planned

  • KFS balance fact table incremental load strategy review.
  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).


7/18/2016 - 7/29/2016

Completed

  • OR related staging/ODS tables and Informatica ETL overhauled to be more consistent with updated DWH modeling standards.
    • ETL updated to remove redundant data - certain staging session removed and changed to pull directly from source DB.
    • Renamed table to be more relevant to OR users.
    • Updated all SPA/KC reports to reflect new table names.
  • OR Cognos reports demo/training sessions preparation
    • Reviewing functionality of Workspace Advanced
    • Demo reports creation per VC or Research email on data request.
  • Fixed ODS session - s_m_LOAD_UCI_L
    • Loading error related to KFS production deployment for table - UCI_LD_A21_PC_JOB_CD_T
  • Reviewing loading error on UCI_SMS_FEED_DETAIL.
    • Need to adjust mapping remove duplicate records from key columns that make up the clustered index.

In Progress 

  • Update all ODS worklets to check sessions status = Succeeded.
  • SPA COR system reports conversion
    • Currently reviewing the stored procs to convert into an ETL process.
    • Cognos report design. 
  • 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.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.

Planned

  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).


7/5/2016 - 7/15/2016

Completed

  • Annual password change for Informatica - Phase 2
  • Cognos Author Active Reports training
  • Daily_ODS workflow adjusted to add in an artificial delay for the ldap_people and employee_basic loading.
    • DWH UNIX jobs needs to complete before the Informatica process can run.
  • SPA reports bug fixed reported by Glenn.  Scholarship records were not being included in totals in the SPA stored proc.
    • Awards by purpose by campus area
    • Awards by purpose by funding source
    • Informatica mapping ETL logic adjusted to pre calculate report grouping description.
  • All KC Cognos reports adjusted per OR user request.  Added in user prompts to filter proposal creation dates.
  • Informatica working session with Josh Jackson for COBOL file processing.

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. 
  • KFSDW-864 - Getting issue details... STATUS
  • 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.

Planned

  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).


6/20/2016 - 7/1/2016

Completed

  • 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:

In Progress 

  • KFSDW-864 - Getting issue details... STATUS
  • 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.

Planned

  • Informatica working session with Josh Jackson for COBOL file processing.
  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).

 

6/6/2016 - 6/17/2016

Completed

  • 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:

In Progress 

  • Assisting Anh with annual password change.
  • KC ODS report specifications documentation write-up.
  • IBM Cognos Report Studio Author Active Reports training registration.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.

Planned

  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).

 

5/23/2016 - 6/3/2016

Completed

  • Analytix data validation discussion for completed Informatica tasks.
  • KFS production database server migration prep work and configuration changes
    • Firewall rules in place for Informatica app servers to ODAPRD..
    • KFSSTAGE/KFSPROD DSNs/Infa connection objects setup complete.
    • dwhreader account requested and verified working on KFSPROD.
  • GL_BALANCE loading emergency fix for NULL RAFG SID values.
    • Updated hard coded database name to a parameter file value.
  • PUR_REQS_EXT_T Oracle schema change verification for affected Informatica objects.
  • Service Now Tasks completed: 

In Progress 

  • KC ODS report specifications documentation write-up.
  • KFS production database server migration prep work and configuration changes
    • KCPROD DSNs/Informatica connections.
    • dwhreader for KCPROD creation.
  • SFTP shell script template design for ETL developers.
  • Informatica production server (etl0) CPU core count addition.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.

Planned

  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).

 

5/9/2016 - 5/20/2016

Completed

  • Adjusted KC ODS Informatica mappings to add additional logic to account for KC proposal workflow change.
    •  New logic based on pre and post 10/21/2015 cutoff dates.
  • Proposal quarterly average trends Cognos report.
  • SPA reports specification review/discussion with OR functional staff (on-going).
    •  Source of RPT_DEPT_CODE changes in the awards over fiscal years identified and documented.
  • KFS_D_FISCAL_PERIOD delayed row active indicator review.
  • Powershell scripts execution issue in Informatica successfully resolved.
  • ODS table inventory list completed to reduce confusion in the Informatica ODS conversion.
  • Web services annual password change update for Informatica workflows completed.
  • Informatica code reviews:
    • PUR_VNDR_SUPP_DVRST_T
    • KFS_D_BLDG_ROOM_LOAD

In Progress 

  • KC ODS report specifications documentation write-up.
  • KFS production database server migration prep work and configuration changes
    • Firewall rules for connectivity to ODAPRD.
    • DSNs/Infa connection objects need to be setup.
    • Currently awaiting KFSPRD refresh on ODAPRD.  Will test to KFSSTG until refresh is complete.
  • Informatica SRAQ.
    • Need to schedule meetings with security team.
  • On-going Informatica training sessions for Jane Gao.

Planned

  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).

 

4/25/2016 - 5/6/2016

Completed

  • Dimensions/Facts/ODS workflows load testing to dss and troubleshooting errors all completed and successful.
  • Cognos training session for Barbara/Jonathan on using Workspace Advance.
  • Investigating NULL values in PRELIM_REVIEW_DT from KC.
    • Related to a KC workflow change that was deployed in Oct. 2015.
    • Need to adjust ETL logic. Also affects other fields using the ROLE_NM and RSP_ID fields.
  • KC proposal average workflow Cognos report completed per request by OR.
  • Aiding in troubleshooting various Informatica ODS workflows issues/enhancements.

In Progress 

  • Adjust KC ODS Informatica mapping to add additional logic to account for KC proposal workflow change.
    • Logic needs to be based on pre and post 10/21/2015 cutoff date groups.
  • Proposal quarterly average trends Cognos report for Nancy/Bruce (will commence after above fix). 
  • SPA reports specification review/discussion with OR functional staff (on-going).
    • Nancy/Barbara have mentioned that the award counts may need to be adjusted to reflect distinct awards.
    • Investigating source of RPT_DEPT_CODE change in the awards over fiscal years.
  • Informatica SRAQ.
    • Completed network diagram/data flow diagram. 

Planned

  • Zena working session with Michael Story for automation of workflow failures (Currently waiting to meet with Michael Story).
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).


4/11/2016 - 4/22/2016

Completed

  • 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:
  • Code review/production deployments completed:
  • 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.

In Progress 

  • Troubleshooting ETL loading hanging issue.
    • Currently trying to isolate the Cognos report that is causing blocking in the database.
  • SPA reports specification review/discussion with OR functional staff (on-going).
    • Nancy/Barbara have mentioned that the award counts may need to be adjusted to reflect distinct awards.
  • Informatica SRAQ.

Planned

  • Dimensions/Facts/ODS workflows load testing to dss and troubleshooting errors.
  • Zena working session with Michael Story for automation of workflow failures.
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).


3/28/2016 - 4/8/2016

Completed

  • Common KFS/SPA award dimension discussion.
  • Production deployment standards/checklist discussion with Analytix.
  • JIRA issues completed:
  • Code review/production deployments completed:
  • Aiding OIR members with Informatica usage and troubleshooting.
  • Aiding in troubleshooting various Informatica ODS workflows issues/enhancements.

In Progress 

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

Planned

  • Zena working session with Michael Story for automation of workflow failures.
  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).

 

3/14/2016 - 3/24/2016

Completed

  • 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:
  • 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.

In Progress 

  • Code review
  • Informatica SRAQ.

Planned

  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).

 

2/29/2016 - 3/11/2016

Completed

  • 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:
  • Aiding in troubleshooting various Informatica ODS workflows issues/enhancements.

In Progress 

  • Troubleshooting MySQL database timeout error.
  • Technical specs documentation for completed SPA Cognos reports.
  • Informatica SRAQ.

Planned

  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).


2/15/2016 - 2/26/2016

Completed

  • 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:
  • 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.

In Progress 

  • Technical specs documentation for completed SPA Cognos reports.
  • KFSDW-1151 - Getting issue details... STATUS
  • Informatica SRAQ.

Planned

  • Defining relationships for all SPA system tables for the Cognos framework model (Currently waiting for documentation from Eric).

 

2/1/2016 - 2/12/2016

Completed

  • Technical specs documentation completed for initial set of published SPA annual reports.
  • Production deployments completed:
  • 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.

In Progress 

  • Technical specs documentation for completed SPA Cognos reports.
  • In the process of adding all requested SPA system tables to existing Cognos framework model.
  • Informatica SRAQ.

Planned

  • Direct database connectivity setup to Tick@Lab "export" database and Cognos framework model creation.


1/18/2016 - 1/29/2016

Completed

  • 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:
  • Meetings with Raj regarding new requests from the OR team.
  • Informatica version control deployment on the dev repository.
  • Informatica tutorial sessions.

In Progress 

  • Technical specs documentation for completed SPA Cognos reports.
  • Informatica SRAQ.

Planned

  • Cognos Workspace Advanced demo and new requirements gathering for OR.

 

1/4/2016 - 1/15/2016

Completed

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

In Progress 

  • Informatica SRAQ.
  • Developing template ETL for dynamic ODS table loading conversion to use stage_kfs as source.
  • Troubleshooting KSAMS web services authentication through PowerExchange.

Planned

  • OR dimensional model design based on feedback from the functional teams.

12/14/2015 - 12/23/2015

Completed

  • 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:
  • 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.

In Progress 

  • Informatica SRAQ.

Planned

  • OR dimensional model design based on feedback from the functional teams.
  • Developing an Informatica dev to prod deployment strategy that can be used as a standard across all teams.


11/30/2015 - 12/11/2015

Completed

  • Troubleshooting deleted LDAP users in Informatica.
    • Adjusted the LDAP sync schedule to once per day to avoid overhead on Informatica repository.
  • Troubleshooting unexpected workflow restart after failed workflow recovery.
    •  Per Informatica support, the automatic restart is intended behavior and is caused by missed scheduled runs.
  • Updated all current workflows to use the default parameter file location on the Informatica server.  
  • Developed additional workflow logic to check session statuses and email success notifications to all the nightly workflows.
  • KFS_F_GL_BALANCE loading session config changes to use the truncate table table option instead of the Pre-SQL.
  • Lunar daily staging/dim/fact refresh turned on for dss_kfs per the request of the report developers.

In Progress 

  • KFS_F_LEDGER_ENTRIES loading logic change from full to incremental load.
    • Loading time should reduced significantly also making it easier for error recovery. 

Planned

  • OR dimensional model design based on feedback from the functional teams.
  • Developing an Informatica dev to prod deployment strategy that can be used as a standard across all teams.


11/16/2015 - 11/25/2015

Completed

  • Created performance counters to collect peak CPU, memory, and disk IO latency statistics on both dev and prod servers.
    • Scheduled to run during the nightly load window (12am - 5am).
    • Need to have at least 2 additional CPU cores to the production Informatica server - currently only dual core.
  • KFS dimensions/facts workflows successfully deployed to the production server.
  • Informatica version control overview and usage presentation.
    • Created a version control tutorial confluence wiki page as a prerequisite to turning on versioning on the dev repository.
  • Completed part of the Level 2 Developer course labs to help with future user training (expired on November 20th).
  • Attended a PowerCenter 10 technical deep dive presentation.

In Progress 

  • Developing an Informatica dev to prod deployment strategy that can be used as a standard across all teams.
  • Troubleshooting deleted LDAP users in Informatica.
    • Possible sync issues with the LDAP server. 

Planned

  • OR dimensional model design based on feedback from the functional teams.


11/2/2015 - 11/13/2015

Completed

  • KFS workflow designed to execute staging/dimensions/facts in sequence based on successful run status.
  • KFS Informatica staging workflow successfully deployed to the production server.
  • Troubleshooting several unknown workflow failures on dev. 
    • Issue discovered to be disk space related during cache file creation.
    • 100GB added to the D: drive on both dev and prod servers.
  • Informatica client installation/configuration for new sqltools server for the OIR team completed.
    • Issue was related to an additional firewall rule requirement - added the rgstools to the inform-users group.
  • KFS monthly SMS feed job completed.
    • Need to confirm one workflow with validation errors (STF100).

In Progress 

  • KFS Informatica workflows for dimensions/facts production server migration.

Planned

  • OR dimensional model design based on feedback from the functional teams.


10/19/2015 - 10/30/2015

Completed

  • KFS_F_GL_BALANCE table reconciliation between dss/dss_kfs.
    • All column mismatches have been resolved.
  • KFS Informatica workflows for dimensions/facts production deployment prep work.
  • Attended a WSU (Washington State University) Office of Research operational dashboard demo.
  • Cognos 10.2.2 upgrade testing for all Office of Research reports.
  • Environment variables setup for Informatica workflow usage.

In Progress 

  • Aiding with troubleshooting/testing of Talend/Informatica conversion.
  • Informatica client installation/configuration for new sqltools server for the OIR team.
  • Developing a working POC for a complete workflow for the KFS staging/dims/facts loading.

Planned

  • OR dimensional model design based on feedback from the functional teams.

 

10/5/2015 - 10/16/2015

Completed

  • Cognos security permissions review for Office of Research related reports.
  • Office of Research data warehouse phase II discussion.
    • Functional staff mentioned needing data around timing between proposal statuses and how long in each status.
    • Reviewing KC database schema for data that will allow for capturing historical data to satisfy reporting.
  • New KSAMS roles proposal for Informatica.

In Progress 

  • Aiding with troubleshooting/testing of Talend/Informatica conversion.
  • Cognos 10.2.2 upgrade testing.
    • Found an issue with outputting any reports other than html causing an error.
  • KFS_F_GL_BALANCE table reconciliation between dss/dss_kfs.
    • Currently, six columns do not match in the KFS_F_GL_BALANCE3 table
      • GL_BEG_CURR_ASSETS_AMT, GL_BEG_FUND_BALANCE, GL_BEG_CUR_LI_AMT, GL_BEG_BAL_AS_LI_FB, GL_BEG_FUND_BAL_IN_ACCTS, GL_BEG_CASH_ASSETS_AMT

Planned

  • OR dimensional model design based on feedback from the functional teams.

 

9/21/2015 - 10/2/2015

Completed

  • Sponsored projects Informatica data loads validated against results from DWH portal.
  • Cognos Framework model/package design for sponsored projects.
    • Sample report tested and validated.
  • Meeting prep for Office of Research data warehouse phase II.
    • KC proposal sample cognos report.
  • Date dimension table creation and Informatica ETL design.
  • Informatica support for OIR.

In Progress 

  • Aiding with troubleshooting/testing of Talend/Informatica conversion.
  • Cognos security permissions review for Office of Research related reports.
  • Communication link failure on SQL Server investigation.
    • Proposed possible solution related to SynAttackProtect setting.  DBA team planning on running network monitor on DB servers.

Planned

  • OR dimensional model design based on feedback from the functional teams.

 

9/7/2015 - 9/18/2015

Completed

  • 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.
  • KFSDW-883 - Getting issue details... STATUS
  • KFSDW-888 - Getting issue details... STATUS
  • Medical center Informatica support.
    • Resolved some connectivity issues between med center databases.
  • Informatica client installation/configuration for Matt 

In Progress 

  • Validating sponsored projects Informatica data loads against results from DWH portal.
  • Aiding with troubleshooting/testing of Talend/Informatica conversion.
  • Cognos security permissions review for Office of Research related reports.
  • Communication link failure on SQL Server investigation.

Planned

  • Cognos Framework model/package design for sponsored projects.
    • Sample report design and validation.

 

8/24/2015 - 9/4/2015

Completed

  • Awards by Campus Area report data discrepancy review request from Office of Research.
    • Found records that were added through the SPA system at the very end of the fiscal year.
  • Kuali Coeus proposal workflow sample data for David Tieu/OR team review.
  • Sponsored Projects DWH page review.
    • Identified the SQL Server processes/logic that populates and outputs data files that are loaded to the dwhm_fs.rgs* tables in MySQL.

In Progress 

  • Aiding with troubleshooting/testing of Talend/Informatica conversion.
  • Cognos security permissions review for Office of Research related reports.
  • Sponsored Projects DWH java code review to identify source tables/fields and any additional report logic.

Planned

  • Sponsored Projects DWH Informatica ETL process design.
    • Source data directly from source DB and create a SQL Server table structure that will be used in a cognos framework package.

 

8/10/2015 - 8/21/2015

Completed

  • Troubleshooting/resolution of Informatica "communication link failure" error causing workflow session failure.
    • Requested the DBA to turn off the TCP Chimney Offload setting on the Windows server.
  • Informatica production environment prep work for dev to prod deployment.
    • Prod repository security settings configured.
    • Created all Systems DSNs and Informatica connection objects.
  • GL_AR_RECONCILIATION table loading process adjusted with new requirements and new ARC payments file integrated for daily processing.
  • Troubleshooting/resolution missing staging tables from workflow run.
    • Built in an additional delay in the UCI staging session.
  • Oracle production schema change prep work.
  • Informatica OLTP production deployment prep work.

In Progress 

  • Aiding with troubleshooting/testing of Talend/Informatica conversion.
  • Aiding OR team with Informatica mapping design.

 

7/27/2015 - 8/7/2015

Completed

  • 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.
  • KFSDW-828 - Getting issue details... STATUS

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.

Planned

  • Informatica production environment prep work for dev to prod deployment.

 

7/13/2015 - 7/24/2015

Completed

  • Office of Research annual report design.
    • Awards by Research Unit
    • New and Renewal Awards by Research Unit 
    • Awards Top 20 Admin Unit
    • Awards Top 20 Depts
  • Informatica debugger feature troubleshooting.
    • Confirmed connectivity through firewall.
  • Resolved duplicate records read issue when reading data from ODBC sources (excluding SQL Server).
    • OptimizeODBC Read/Write property to the Integration Service configuration. 

In Progress 

  • Aiding with troubleshooting/testing of Talend/Informatica conversion.
  • Helping the OR team install/configure the Informatica client software.

Planned

  • Office of Research annual report design.
    • Contract vs. Grant
    • Recipients of Awards 1 million plus
    • Top 20 Recipients of Contract and Grant Awards

 

6/29/2015 - 7/10/2015

Completed

  • 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
  • KFSDW-792 - Getting issue details... STATUS

In Progress 

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

Planned

  • Office of Research annual report design.
    • Awards by Research Unit
    • New and Renewal Awards by Research Unit 
    • Awards Over 4 million
    • Awards Top 20 Admin Unit
    • Awards Top 20 Depts
    • Contract vs. Grant
    • Recipients of Awards 1 million plus
    • Top 20 Recipients of Contract and Grant Awards

 

6/15/2015 - 6/26/2015

Completed

  • Sandbox access to KC application.
  • Initial ODS table build in in our dev DB environment.
  • ETL design to populate initial ODS.
  • Initial SPA annual Report presentation for the Office of Research staff.
  • KC proposal ODS data and sample report demo.
  • Informatica Analyst connection error resolved.
  • Created a sample data profile on KC table.

In Progress

  • Profiling KC database schema and data.
  • Testing Informatica Analyst feature set and functionality.
  • Aiding with troubleshooting/testing of Talend/Informatica conversion.

Planned

  • Continue to profile KC database schema and data.
  • Additional annual reports requested by Office of Research staff.

 

6/1/2015 - 6/12/2015

Completed

  • Code review and Informatica usage tutorial with Durendal for UC_ACCOUNT_T dev to prod deployment.
  • KFSDW-486 - Getting issue details... STATUS
  • 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.

In Progress

  • Profiling KC database schema and data.
  • Setting up Informatica Analyst tool to aid in data profiling. 
    • Getting familiar with the usage. 
    • Troubleshooting profile run error.
  • Compiling a KC data specification document.
  • Sandbox access to KC application.
  • Aiding with troubleshooting/testing of Talend/Informatica conversion.

Planned

  • Continue to profile KC database schema and data.
  • Initial ODS table build in in our dev DB environment.
  • ETL design to populate initial ODS.

 

 5/18/2015 - 5/29/2015

Completed

  • 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.
  • KFSDW-721 - Getting issue details... STATUS
  • KFSDW-737 - Getting issue details... STATUS
  • Setup Oracle SQL Developer and connections to KC dev/prod databases.

In Progress

  • Reviewing KC proposal report SQL queries.
  • Profiling KC database schema and data.
  • Compiling a KC data specification document.
  • Sandbox access to KC application.
  • Aiding with troubleshooting/testing of Talend/Informatica conversion.

Planned

  • Continue to profile KC database schema and data.
  • Initial ODS table build in in our dev DB environment.

  

5/4/2015 - 5/15/2015

Completed

  • 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.
  • KFSDW-615 - Getting issue details... STATUS
  • KFSDW-707 - Getting issue details... STATUS
  • Kuali Coeus proposal workflow overview.

In Progress

  • Troubleshooting production Informatica repository connectivity from local machine.  Waiting for oit.uci.edu to be added to DNS search list.
  • Informatica overview presentation design for meeting on 5/28.

Planned

  • Review KC proposal report SQL queries.
  • Access to KC sandbox environment as well as KC database access.

  

4/20/2015 - 5/1/2015

Completed

  • Working through Matt for uci_spa dev/prod SystemDSN creation on production Informatica server.
  • Informatica mappings to load from uci_spa DB to stage_kfs/dss_kfs staging/ODS tables.
  • Meetings with Durendal to set direction for Cognos reporting on Informatica session statistics.      - Replicate Informatica repository database to another DB instance (question).      - Develop Informatica nightly workflow to extract required data (question).
  • Cognos Framework Model for SPA ODS. 
  • Awards by Purpose by Funding Source Cognos Report.      - Demo for Eric Taggart.  Planning on attending OR team meeting on 5/7.

In Progress

  • Awards by Funding Agency Cognos Report.
  • Troubleshooting production Informatica repository connectivity from local machine.      - Suspect firewall issue on port 6005 but JIRA ticket closed by John Lenning stating port already allowed.
  • Looking into Oracle to SQL Server TIMESTAMP data type compatibility issue for KFS conversion.

Planned

  • New and Renewal Awards by Funding Agency
  • Proposals by Funding Agency
  • New and Renewal Proposals by Funding Agency
  • Award Totals for Last 10 Years  

 

 

 

 

 

 

  • No labels