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:
- KFSDW-1604 - Getting issue details... STATUS
- KFSDW-1687 - Getting issue details... STATUS
Service-now incidents completed:
- INC0177686
- CHG0032546

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

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

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:

I NC0160061

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

KFSDW-1621 - Getting issue details... STATUS

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

KFSDW-1611 - Getting issue details... STATUS

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.

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

KFSDW-1596 - Getting issue details... STATUS

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

KFSDW-1573 - Getting issue details... STATUS
KFSDW-1579 - Getting issue details... STATUS
KFSDW-1580 - Getting issue details... STATUS
KFSDW-1587 - Getting issue details... STATUS

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
- KFSDW-1573 - Getting issue details... STATUS
Service-now incidents completed:

INC0138708

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

Service-now incidents 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:
- INC0130004
- INC0131006

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.

KFSI-13867 - Getting issue details... STATUS

Missing dimension table found KFS_D_TEM_PROFILE
- KFSDW-1441 - Getting issue details... STATUS
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:

KFSDW-1543 - Getting issue details... STATUS
KFSDW-1521 - Getting issue details... STATUS

Service-now incidents completed:
- INC0128157
- INC0127686

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

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:
- KFSDW-1467 - Getting issue details... STATUS
- KFSDW-1515 - Getting issue details... STATUS
- KFSDW-1257 - Getting issue details... STATUS
- KFSDW-1528 - Getting issue details... STATUS
- KFSDW-1527 - Getting issue details... STATUS
- KFSDW-1534 - Getting issue details... STATUS

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:
- KFSDW-1467 - Getting issue details... STATUS
- KFSDW-1478 - Getting issue details... STATUS
- KFSDW-1497 - Getting issue details... STATUS
- KFSDW-1511 - Getting issue details... STATUS

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:

KFSDW-1497 - Getting issue details... STATUS
KFSDW-1493 - Getting issue details... STATUS
KFSDW-1495 - Getting issue details... STATUS
KFSDW-1487 - Getting issue details... STATUS

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:

KFSDW-1472 - Getting issue details... STATUS
KFSDW-1461 - Getting issue details... STATUS
CTASK0012584

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:
- KFSDW-1452 - Getting issue details... STATUS
- KFSDW-1453 - Getting issue details... STATUS
- KFSDW-1408 - Getting issue details... STATUS
- KFSDW-1253 - Getting issue details... STATUS
Production deployments completed:
- KFSDW-1461 - Getting issue details... STATUS
- KFSDW-1058 - Getting issue details... STATUS

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:
- KFSDW-1376 - Getting issue details... STATUS
- PRJTASK0019725

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:
- KFSDW-1324 - Getting issue details... STATUS
- KFSDW-1338 - Getting issue details... STATUS
- PRJTASK0020116
- PRJTASK0019726

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:
- PRJTASK0020066
- PRJTASK0019796

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:
- KFSDW-1255 - Getting issue details... STATUS
- KFSDW-1256 - Getting issue details... STATUS
Code review/production deployments completed:

KFSDW-1217 - Getting issue details... STATUS
KFSDW-1247 - Getting issue details... STATUS

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:
- KFSDW-1231 - Getting issue details... STATUS
Code review/production deployments completed:
- KFSDW-1210 - Getting issue details... STATUS
- KFSDW-1223 - Getting issue details... STATUS
- KFSDW-1204 - Getting issue details... STATUS
- KFSDW-1235 - Getting issue details... STATUS
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.
- KFSDW-1217 - Getting issue details... STATUS
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:
- KFSDW-1171 - Getting issue details... STATUS
- KFSDW-1183 - Getting issue details... STATUS
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
- KFSDW-1116 - 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/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:
- KFSDW-1151 - Getting issue details... STATUS
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:
- KFSDW-1120 - Getting issue details... STATUS
- KFSDW-1128 - Getting issue details... STATUS
- KFSDW-1143 - Getting issue details... STATUS
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:
- KFSDW-1101 - Getting issue details... STATUS
- KFSDW-1108 - Getting issue details... STATUS
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:
- KFSDW-1077 - Getting issue details... STATUS
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:

KFSDW-1068 - Getting issue details... STATUS
KFSDW-1052 - Getting issue details... STATUS

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:
- KFSDW-992 - Getting issue details... STATUS
- KFSDW-1001 - Getting issue details... STATUS
- KFSDW-1005 - Getting issue details... STATUS
- KFSDW-1008 - Getting issue details... STATUS
- KFSDW-1023 - Getting issue details... STATUS
- KFSDW-1030 - Getting issue details... STATUS
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 . - Develop Informatica nightly workflow to extract required data .
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

Browser not supported