Versions Compared

Version Old Version 37 New Version Current
Changes made by

Kim Anh Thi Le

Oluwasemilore Ajibola (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Repair Jira Macros

Anh’s Status 9/1/2015 – 9/11/2015 (4 working days: 8th - 11th.  Off 4 vacation days Anh's Status 11/18 - 12/11

ETL

 

 

https://uci.atlassian.net/browse/KFSDW-1537  (Load CG_AGENCY_ADDR_T to dwhs_ods db on nebula/nova):  This task was completed, verified and was migrated to production.

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1443
(KFS_F_TEM_ACCTNG):  I was asked to help the Analytix team with the issue of the unique index on the field (TEM_ACCNT_LN_ID) of the KFS_F_TEM_ACCTNG fact table rejecting rows.  After analysis, Austin confirmed that the index is not required.  I removed it.  They were able to continue until they encountered another issue with the duplicate records in the source table.  These dup. records  are stemmed from an Oracle table.  A new jira KFSI-13867 was created for KFS to fix this issue.

UATs: 

 

 

1548 KFS_D_TEM_PROFILE:  passed test.   It’s ready for deploy.

1445 KFS_D_TEM_DOC:  passed test.   It’s ready for deploy.

1442 KFS_F_TEM_EXPENSE:  passed test.   It’s ready for deploy.

Next plan:

ETL

 

https://uci.atlassian.net/browse/KFSDW-1550 (Migrate the Informatica SBS aging to production server)

https://uci.atlassian.net/browse/KFSDW-1549 (SBS aging data files to the Informatica production server)

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1496
 (Modify Balance ETL for all ITD balances to remove SFG 2xxxxx)

UATs:

1443 (KFS_F_TEM_ACCTNG) Analytix’s development "In Progress" status. . Wait to start testing.

1448 (KFS_D_EMGCY_CNTCT_BRIDGE). Analytix’s development  "In Progress" status. Wait to start testing.

1450 (KFS_D_SPCL_CRCMSTNCS_BRIDGE) Analytix’s development  "In Progress" status. Wait to start testing.

1451 (KFS_D_TEM_RLTD_DOC_BRIDGE) Analytix’s development  "In Progress" status. Wait to start testing.

Anh's Status 11/4 - 11/17

ETL

INC0125556, INC0126590, https://uci.atlassian.net/browse/KFSDW-1532 (Need to enhance the SBS Aging task to avoid duplicate records):  Completed.

https://uci.atlassian.net/browse/KFSDW-1537 Completed  ETL development. 

UATs: 

1444 (KFS_D_TEM_EXPENSE) Test passed. Ready to deploy

1445 (KFS_D_TEM_DOC) Test passed. Ready to deploy

1446 (KFS_D_TEM_PERDIEM) Test passed.

1447 (KFS_D_EMGCY_CNTCT) Passed test.  Ready to deploy.

1449 (KFS_D_SPCL_CRCMSTNCS) Passed. Ready to deploy

Next plan:

ETL

https://uci.atlassian.net/browse/KFSDW-1537 plan to start testing as soon as the Informatica DB connection is provided for kfstst2 where requester placed test data.

UAT

1442 (KFS_F_TEM_EXPENSE) Analytix’s development "In Progress" status. Wait to start testing.

1443 (KFS_F_TEM_ACCTNG) Analytix’s development "In Progress" status. . Wait to start testing.

1448 (KFS_D_EMGCY_CNTCT_BRIDGE). Analytix’s development  "In Progress" status. Wait to start testing.

1450 (KFS_D_SPCL_CRCMSTNCS_BRIDGE) Analytix’s development  "In Progress" status. Wait to start testing.

1451 (KFS_D_TEM_RLTD_DOC_BRIDGE) Analytix’s development  "In Progress" status. Wait to start testing.

Anh's Status 10/21 - 11/3

 

ETL

https://uci.atlassian.net/browse/KFSDW-1290:  Completed loading new rafg data for October per user’s request

INC0125556, INC0126590, https://uci.atlassian.net/browse/KFSDW-1532 (Need to enhance the SBS Aging task to avoid duplicate records):  Completed the development of this task.  The final testing phase is in progress.

UATs:

  1. Disbursement

1361 (KFS_F_DSBRMNT_ITM) Encountered problem.  Waiting for Analytix to fix

1362 (KFS_F_DSBRMNT_ITM_ACCTNG) Encountered problem.  Waiting for Analytix to fix

2. TEM

1444 (KFS_D_TEM_EXPENSE) Encountered problem.  Waiting for Analytix to fix

1446 (KFS_D_TEM_PERDIEM) Encountered problem.  Waiting for Analytix to fix

1449 (KFS_D_SPCL_CRCMSTNCS) Encountered problem.  Waiting for Analytix to fi

Next plan:

ETL

UATs

     Continue with TEM as soon as receiving Analytix's fixes

Anh’s Status 9/23/2016 - 10/20/2016 (took vacation 9/28 - 10/11)

ETL: 

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1530
(Old SBS aging data files for previous month were sent to DW for this month-end October)

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1532
 (Need to enhance the SBS Aging task to avoid duplicate records)

UAT:

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1415
Waiting for Analytix to fix error found in their queries.  I already reminded them again today.  Both Bryce and I have been waiting for this fix first due to task dependencies in order to perform UATs for jiras
Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1417
,
Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1418
 

 

Anh’s Status 9/9/2016 - 9/22/2016

ETL: 

  • Jira Legacy
    serverSystem JIRA
    serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
    keyKFSDW-1495
    Added changes to s_m_ods_kbm_postInfo_Load_Ldgr_Only_T to filter out 'BALANC'.   Completed.  Closed  this jira.  
  • Month-end CAFP, CFSBAL:  I informed Jarrod that his triggers were not available as scheduled.  Then he sent them manually.  Our jobs ran as expected.  Completed and verified.
  • SBS Aging Month-end: 

on 9/12 SBS aging month-end records were duplicating due to triggers firing twice within 1 minute (at 04:02 am, then at 04:03 am).  I discussed, debugged this issue with Jarrod.   We haven’t discovered the cause yet, and continue to monitor this.  I removed duplicates in the backend.  These records maintain unique in these tables .  In the meantime I’ve been talking to Tu Dang on SBS data and columns in order for me to alter the sbs aging tables to add keys, also to enhance ETL to prevent duplicate records inserting into these tables. 

Annual DW Password changes (last group on production): 

Granted view access in secret server DB to appropriate owners who needed to view the new password to perform their changes.  Completed DW password changes for proper, dssoper, dwhoper, ppsoper. 

UAT:   

Today we received Analytix’s responses and fixes after I emailed the list of jiras to them.  We now can continue with UATs. 

https://uci.atlassian.net/browse/KFSDW-1416: Completed and ready to deploy

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1395
(KFS_F_PREQ_ITM_ACCTNG):  Same error “Incorrect syntax near '$' “ occured after their fix today, already informed them to fix.

Below are the remaining jiras that both Analytix and we are waiting for resolution when Pramod comes back from his vacation:

https://uci.atlassian.net/browse/KFSDW-1361

Not able to continue with uat due to column DOC_NBR_ID  not exists in the ETL Rule:  If PDP_PMT_DTL_T.FDOC_TYP_CD =  PREQ, CM, or DVCA then get corresponding CUST_PMT_DOC_NBR and lookup with KS_D_DOC_HDR.DOC_NBR_ID and get SID.

https://uci.atlassian.net/browse/KFSDW-1362: KFS_F_DSBRMNT_ITM_ACCTNG.  Invalid object name 'D_DATE'.".  Both etl developer, uat tester need Pramod’s solution on this.

Bryce will continue UATs while I'm away in Miami to help my niece and her newborn from 9/28 - 10/11.

Attended weekly DSS meeting

Attended weekly Analytix meeting

Anh’s Status 8/29/2016 - 9/8/2016

ETL development:

Completed reversing changes, closed this jira https://services.adcom.uci.edu/jira/browse/KFSDW-1460

Month-end activities:  Outbound SMS tonight, SBS aging on 7th.

UATs: 

https://services.adcom.uci.edu/jira/browse/KFSDW-1416 KFS_D_CRDT_MEMO_ITM:  Analytix is investigating error encountered during UAT “Invalid object name 'KFS_D_CRDT_MEMO_ITM'”

https://services.adcom.uci.edu/jira/browse/KFSDW-1417 KFS_F_CRDT_MEMO_ITM: UAT is in progress

https://services.adcom.uci.edu/jira/browse/KFSDW-1418 waiting for Analytix's unit test query

https://uci.atlassian.net/browse/KFSDW-1361 KFS_F_DSBRMNT_ITM: BadRecCount = 2 and JoinedRecCount = 3 for the first test query (dss_kfs.KFS_D_DSBRMNT_UCI_BNK).  Analytix is investigating.

https://uci.atlassian.net/browse/KFSDW-1362 KFS_F_DSBRMNT_ITM_ACCTNG: Encountered “Error converting data type nvarchar to numeric”.  Informed Analytix to investigate

https://uci.atlassian.net/browse/KFSDW-1394 KFS_F_PREQ_ITM: missing the name of the wf/session, unit test query.  Analytix said to provide soon.

DW annual passwords needing change:  Coordinated with teams including Tom B. who will make changes on their sides to get ready to make changes on 9/16 for proper, dssoper, dwhoper, ppsoper on PROD.

Attended weekly DSS mtg

Attended weekly Analytix mtg

 

Anh’s Status 8/15/2016 - 8/26/2016

Labor Ledger model task

https://services.adcom.uci.edu/jira/browse/KFSDW-1460: Done adding additional requirement to KBM.  It’s now ready for test.

Post ODS migration activities: 

 ods_kfs.TEM_TRVL_EXP_T:   Missing CREATED_EW_DTTM for insert date.  Fixed mapping in both dev, prod.  Users confirmed.

AR Reconciliation Balance Tables:

 Fixed in mapping and DB backend to add 2 decimal  points to all amount colums  to load data to [ods_kfs].[UCI_AR_RECON_BAL_DETAIL_T], UCI_AR_RECON_BAL_SUMMARY_T per jira  https://services.adcom.uci.edu/jira/browse/KFSDW-1253.  This jira is ready for test.

Updated:  Users confirmed that data looks good on dev, requested to deploy to production.  On production ran DDLs to drop, then to create these tables to have precision of 2 for all amount columns.  Trial ran wklt_UCI_AR on production before nighttime load without issue.  This task is ready for nightly load.

DW annual passwords needing change

Completed on production for dssreader, dwhreader, ppsreader: Completed.   To prepare for the last group (…oper) needing pw change on 9/16, per Thomas Bindewald’s request, I send him our annual DW passwords needing change kiwi page that shows all the login IDs so he could start request for access the production Unix servers.

UATs:

Found issues during user acceptance testing for Analytix ETLjiras below:

https://services.adcom.uci.edu/jira/browse/KFSDW-1415

Error when running the workflow: "Invalid object name '."  Informed Analytix to fix.

Requested Analytix to fix the invalid KFS_D_CRDT_MEMO_DOC object error found during UAT.

https://services.adcom.uci.edu/jira/browse/KFSDW-1416

Requested Analytix to fix the invalid 'KFS_D_CRDT_MEMO_IT object error found during UAT.

Requested Analytix to provide missing their unit test scripts for jiras below:

https://services.adcom.uci.edu/jira/browse/KFSDW-1417

https://services.adcom.uci.edu/jira/browse/KFSDW-1418

UATs for PO ETL completed for jiras below:

https://services.adcom.uci.edu/jira/browse/KFSDW-1258

https://services.adcom.uci.edu/jira/browse/KFSDW-1259

https://services.adcom.uci.edu/jira/browse/KFSDW-1261

https://services.adcom.uci.edu/jira/browse/KFSDW-1262

https://services.adcom.uci.edu/jira/browse/KFSDW-1263

https://services.adcom.uci.edu/jira/browse/KFSDW-1264

https://services.adcom.uci.edu/jira/browse/KFSDW-1265

https://services.adcom.uci.edu/jira/browse/KFSDW-1266

https://services.adcom.uci.edu/jira/browse/KFSDW-1267

https://services.adcom.uci.edu/jira/browse/KFSDW-1268

https://services.adcom.uci.edu/jira/browse/KFSDW-1269

https://services.adcom.uci.edu/jira/browse/KFSDW-1270

The above jiras are now ready for deploy. 

 

Anh’s Status 7/29/2016 - 8/12/2016

  • Completed processing special year-end for cafp, bal per Jarrod's request

dss.ods_kfs.UCI_UCOP_CAFP_ACCOUNT_T
dss.ods_kfs.UCI_UCOP_CAFP_FUND_T
dss.ods_kfs.UCI_UCOP_CFSBAL_RCRD_10 (Balance Sheet)
dss.ods_kfs.UCI_UCOP_CFSBAL_RCRD_11 (Fund Balance)
dss.ods_kfs.UCI_UCOP_CFSBAL_RCRD_20 (Revenue)
dss.ods_kfs.UCI_UCOP_CFSBAL_RCRD_30 (Expenditure Summary)
dss.ods_kfs.UCI_UCOP_CFSBAL_RCRD_31 (Expenditure Detail).

  • Resolved, closed following ETL jiras:

https://services.adcom.uci.edu/jira/browse/KFSDW-1403

 

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1421

https://services.adcom.uci.edu/jira/browse/KFSDW-1439

https://services.adcom.uci.edu/jira/browse/KFSDW-1440

  • Resolved, closed INC0107629:  Both Talend and Informatica had problem loading a record with kfs_org_ref_id  for post-doctor students.   Done fixing ETL, verified data.
  • Informatica ODS deployment: 

Attended ODS deployment planning meeting.

This evening,  I turned off all ODS Talend jobs on deploy2,  unscheduled wf_m_ods_load_gec_tables (in Tammy’s folder), wf_m_ods_kfs_Temporaryload (in Bryce's folder).

  • Attended Change Control meeting, announced roll out Informatica ODS this Friday.
  • Analytix’s ETL UAT: Supervised Bryce, requested Analytix to fix problems found during UATs.

Anh’s Status 7/15/2016 - 7/28/2016

  • Annual DW password change:  completed on dev for last group of (oper) usernames
  • ETL development:

Completed SBS aging ETL https://services.adcom.uci.edu/jira/browse/KFSDW-1386

Completed fixing fiscal year end problem in SMS Feed, CAFP tasks.

Started work on tasks below:

https://services.adcom.uci.edu/jira/browse/KFSDW-1403

Fix SBS Aging task every month-end until the ODS Informatica jobs go live because Talend jobs overwrite my fixes for previous month-end from JIRA

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1387

Add to post-informatica mapping to include Valerie’s additional requirement for KBM in jira https://services.adcom.uci.edu/jira/browse/KFSDW-731

  • Analytix ETL UATs: 

Completed below:

https://services.adcom.uci.edu/jira/browse/KFSDW-1258

https://services.adcom.uci.edu/jira/browse/KFSDW-1261

https://services.adcom.uci.edu/jira/browse/KFSDW-1266

Worked with Analytix, Bryce on failed test cases in the fact jiras:

https://services.adcom.uci.edu/jira/browse/KFSDW-1394

https://services.adcom.uci.edu/jira/browse/KFSDW-1395

As soon as Analytix added missing columns fixed errors found during UATs, we need to retest them.  They’re off Tomorrow, Monday (7/29,8/1).

Additional new dimension jiras from parent jira https://services.adcom.uci.edu/jira/browse/KFSDW-1393  are ready for test but one is missing unit test script, the other doesn’t have the name of the mapping to test.  We requested Analytix to provide their missing components in the jiras below:

https://services.adcom.uci.edu/jira/browse/KFSDW-1396 

https://services.adcom.uci.edu/jira/browse/KFSDW-1259

https://services.adcom.uci.edu/jira/browse/KFSDW-1397

Attended weekly DSS meeting

Attended weekly meeting with Analytix

Attended weekly Change Control meeting.  I announced the next DW Annual password change for production for first group (reader)

Anh’s Status 7/4/2016 - 7/14/2016

  • SBS Aging month-end

On dev: https://services.adcom.uci.edu/jira/browse/KFSDW-1282
Completed setting up triggers to load SBS aging reports.  The SBS data loaded fine.


On production:  https://services.adcom.uci.edu/jira/browse/KFSDW-1387
Fixed Talend types for aging data on production.  Valerie approved data is good.

Anh’s Status 6/17/2016 - 7/1/2016

 

Anh’s Status 6/3/2016 - 6/16/2016

  • Month-end outbound jobs: Per Jarrod’s request, I monitored the triggers, jobs run, verified monthend data for sms feed, BUD100, STF100, STF200, CAFP, CFSBAL
  • Regarding Oracle server move on the week end 6/11:  Per user’s request to load data asap, I modified Informatica ods jobs to configure production connections to load Loading_GL, PDP_PMT_GRP_T, ods_kfs.GL_ENTRY_T, ods_kfs.PDP_PMT_GRP_T tables on production.   After loading I put back the development connection.  Talend ods jobs continued to load after changing to the new Oracle connection.
  • https://services.adcom.uci.edu/jira/browse/KFSDW-1290.  This jira is ready to be deployed.  Provided details to deploy in jira https://services.adcom.uci.edu/jira/browse/KFSDW-1338
  • Completed planning in excel sheet for all DW passwords needing change including proposed dates in the wiki page  in the section 10 item b. at: https://uci.atlassian.net/wiki/pages/viewpage.action?pageId=68025573.
    After sending the page out to the key stakeholders to confirm proposed schedules, they confirmed, I updated.  Completed the final draft.  It’s ready to email to everyone.

Anh’s Status 5/20/2016 - 6/2/2016 (Memorial Day Holiday, 1 day vacation off)

https://services.adcom.uci.edu/jira/browse/KFSDW-1258

Completed UAT.  Test passed.  This task is ready for deploy. 

https://services.adcom.uci.edu/jira/browse/KFSDW-1284

Deleted kbm snapshot tables for cleaning up purpose

https://services.adcom.uci.edu/jira/browse/KFSDW-1264

Completed UAT.  Test passed.  This task is ready for deploy.

Completed mapping to add length for the rafg columns per Pramod's request.  The  [ods_kbm].[rafg_codes] table is  now ready for the rafg dimension.

https://services.adcom.uci.edu/jira/browse/KFSDW-974

On going ODS:  Fixing problem/issue found by Cognos developers during their testing. 

Anh’s Status 5/9/2016 - 5/19/2016

https://services.adcom.uci.edu/jira/browse/KFSDW-1262

Develop ETL for PURAP: PO subject area: Table KFS_D_DOC_HDR.  Completed.  Ready for deploy.

https://services.adcom.uci.edu/jira/browse/KFSDW-1196

Implement SCD for Account dimension.  Completed.  Ready for deploy.

https://services.adcom.uci.edu/jira/browse/KFSDW-1197

Implement SCD for Object dimension.  Completed.  Ready for deploy.

https://services.adcom.uci.edu/jira/browse/KFSDW-974

On going ODS:  Fixing problem/issue found by Cognos developers during their testing:

Add tables: AP_PMT_RQST_T, PUR_COMM_T, PDP_CUST_PRFL_T, PDP_PMT_GRP_T, PDP_PMT_DTL_T, all tables on Eufemia’s , Matt’s lists.

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1290
 

Modified the rafg code dimension mapping to expand length of the rafg code column from 3 to 4 characters in order to load all records from user’s data this month when they sent rafg codes with more than 3 characters.  Completed this task.  Verified.  It's ready for test.

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1292

Add GOE and OOE tables to OLTP ODS for Web Services.

Jarrod confirmed all tables available in dwhs_ods.  This jira is ready for him to test.

Anh’s Status 4/25/2016 - 5/6/2016

Anh’s Status 4/11/2016 - 4/22/2016

  • Outbound month end:  Found duplicate records in cafp (duplicated centr_seq_nbr),stf100(duplicated ID).  I created a jira for the problem: https://services.adcom.uci.edu/jira/browse/KFSDW-1242
  • SBS task:  Completed fixing wrong types in the sbs againgreport tables.   Its jira is  https://services.adcom.uci.edu/jira/browse/KFSDW-1243
      Next, Jarrod and I set up the trigger from zena to create trigger file to informatica server to load these tables.   
  • Account Dimension: Completed  UAT for https://services.adcom.uci.edu/jira/browse/KFSDW-1216 (Modify ETL to populate ACCOUNT_SUB_FUND_CD on Account Dim from UC_FUND_T). 
  • ODS: Completed https://services.adcom.uci.edu/jira/browse/DWH-1801 (Add new columns to existing mapping in ODS due to Warren's recent additional changes in Oracle)
  • Cleaning up tables (infor, talend) that are no longer needed in our databases in lunar, solar.  For the same purpose, I created now ticket INC0084270 for the DBA team to remove stage_kfs_infor.  Today this ticket is resolved, closed.
  • Analytix task:  
           Completed UAT for the pending entries fact task.
           Attended meeting with Analytix on the KFS Purchase Order.
           On-going with the UATs.
  • Supervised Bryce.

Anh’s Status 3/25/2016 - 4/8/2016

  •  Completed enhancing the fund name for the scenario where there’s no fund name in the source, make them N/A (fund_code) so that they can be unique.  
             The purpose is that when users report by name only, they don’t roll up.   The code changes were rolled out to production last night.  Durendal and I verified data.
  •  Prepare ODS to roll out to production:  Completed verifying all ODS tables ODS jobs.  They’re ready to roll out to production as soon as cognos report developers confirm that they don’t break their reports.
  •  SBS task:

Mappings of ods_sbs.agingreportcon_kfs, agingreport_det_kfs all have string type for all columns including date, amount.  These tables have accumulative records.  I’m in the process of fixing these mappings, export data, create new tables in DB to correct type  then import data from current tables (containing wrong types) to the new tables (with correct types).

  • Month-end Outbound task:  The SMS Feed jobs loaded fine last night after they saw Jarrod’s triggers.  I verified data. 
  •  Analytix task:  on-going with the user acceptance tests.
  •  Supervised Bryce on connections with DBs per his request.

Anh’s Status 3/2/2016 - 3/24/2016

Completed adding new columns KFS_ORG_COA_CD, KFS_ORG_CD to ods_kfs.UC_ACCOUNT_T, modifing UC_ACCOUNT_T ETL.  

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1149
 is on production, closed.

Completed user acceptance test for the new rafg dimention and new changes in gl entries and balance fact tables 

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1089
.  It's now ready to deploy.

Completed fixing missing fund names.  Tested and verified. 

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-1183
 is ready to deploy.

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-974
 is now ready for test after completing below:

  Fixed ODS jobs that failed due to missing synonyms on the new Oracle server, missing links to target ports, mismatched length, types.

  Replaced most mappings that needed both source and target to utilize shortcuts.

  Fixed TEM,KRIM jobs in ODS due to tables not exists so they no longer failed.

  Added additional mapping to load new tables as required such as adding [dss].[ods_kfs].[UCI_AP_PMT_RQST_EXT_T], uci_tem_airport_t to load on prod.

  Corrected from $$KBM_Owner to $$ODS_Owner for tables below: UCI_AP_PMT_RQST_EXT_T,UCI_CM_AST_ELIM_ACCT_MTRX_T,UCI_FP_GEC_ENTRY_REL_T,UCI_PDP_PMT_DTL_EXT_T

  Completed creating mapping to load uc_account_t, uc_annual_rpt_code_t to run on dev.  Need to remove this when ready to migrate because there’s a session that load these 2 tables on production. 

  Completed converting all tables in ods_kbm

  March Month-end of the Outbound jobs:  sms feed, cafp and cafsbal loaded on production fine.  Jarrod and I verified all.  They all matched Oracle as expected. 

  Supervised Bryce. 

Anh’s Status 2/18/2016 - 3/3/2016

Resolved INC0071250, INC0067892

Completed converting ods_kbm_ld_override.  It’s now ready for test https://services.adcom.uci.edu/jira/browse/KFSDW-1119

Supervised Bryce

Weekly meeting working with Analytix team.

Completed all ODS new and modified tables.  Remaining talend jobs to convert to Informatica:  uci, pdp. 

Completed https://services.adcom.uci.edu/jira/browse/KFSDW-1149.  It's ready for test. 

Anh’s Status 2/04/2016 – 2/17/2016

Completed configuring all outbound jobs to run periodically via triggers

Add shared objects for all outbounds jobs

Closed https://services.adcom.uci.edu/jira/browse/DWH-1673

Added trigger for  s_m_UCI_UCOP_EFA100_T, s_m_UCI_UCOP_EFA200_T.  They're ready whenever the data is available. 

Ods jobs:  Completed converting the post informatica jobs.  The jira  below is “ready for test”

https://services.adcom.uci.edu/jira/browse/KFSDW-731 

 

Anh’s Status 1/04/2016 – 1/14/2016

Completed and closed the following jiras:

https://services.adcom.uci.edu/jira/browse/KFSDW-1022

https://services.adcom.uci.edu/jira/browse/KFSDW-1043

https://services.adcom.uci.edu/jira/browse/KFSDW-1037

https://services.adcom.uci.edu/jira/browse/KFSDW-1047

Completed outbound jobs for this month-end to be cumulative:

UCI_UCOP_CFSBAL_RCRD_10

UCI_UCOP_CFSBAL_RCRD_11

UCI_UCOP_CFSBAL_RCRD_20

UCI_UCOP_CFSBAL_RCRD _30

UCI_UCOP_CFSBAL_RCRD_31

UCI_UCOP_CFSAST_RCRD_12

UCI_UCOP_CFSAST_RCRD_13

UCI_SMS_FEED_DETAIL_T

UCI_SMS_FEED_SUMMARY_T

UCI_LD_BUD100_STAGING_T

UCI_LD_STF100_STAGING_T

UCI_LD_STF200_STAGING_T

 

Anh’s Status 12/03/2015 – 12/17/2015

Closed https://services.adcom.uci.edu/jira/browse/KFSDW-586

Closed https://services.adcom.uci.edu/jira/browse/KFSDW-986

Closed  https://services.adcom.uci.edu/jira/browse/KFSDW-974

https://services.adcom.uci.edu/jira/browse/KFSDW-1017

https://services.adcom.uci.edu/jira/browse/KFSDW-1036

Anh’s Status 11/12/2015 – 11/25/2015

Closed

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-899

Closed

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-895

Closed

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-973
.

Closed

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-972
 

Closed

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-590

Completed on dev.  Need to verify before turning over to production

Jira Legacy
serverSystem JIRA
serverIdd1c6a489-217f-3ae9-bd3a-01dc24609c18
keyKFSDW-586

Anh’s Status 10/01/2015 – 10/29/2015

  •       Worked with the DBA team to investigate kfs daily jobs failed this week.   The cause of the failure was that the backup of our databases collided with our nightly jobs.  DBA team  
    rescheduled it at a different time.  The problem was solved.  Daily jobs have been running fine. 
  •      Verified, fixed to add value 2 for precision to all amounts in the fact columns.
  •      Worked with BI team to investigate, fixed mismatches between Talend and Informatica that they found in the kfs informatica ods, dimension and fact.   Several amount columns were incorrect in both versions such as gl_income_amt due to missing fiscal period joins.  They were fixed, ready to roll out.
  •      ODS:  Solved mismatches, identified the sources for missing tables in Informatica.  They will need to be created before roll out.
  •      Dimension:  Mismatches were fixed, verified.  These jobs are ready to be rolled out to production this w/e.
  •      Fact:  Mismatches were fixed, verified.   Verified, fixed to add value 2 for precision to all amounts.  It's ready to be rolled out this w/e.
  •      All verify queries and their results were recorded in the status sheet in folder \\abs.uci.edu\dfs\adcom\shares\docs\SystemDev\KUALI Project\D-1 Data Warehouse\ETL\UCI
  •      Created jiras to turn over Informatica dimension, fact to production. 
  •      Wrote plan to roll out in JIRA  https://services.adcom.uci.edu/jira/browse/KFSDW-898
  •      Coordinated with Web Service and our teams to run their outbound jobs for month-end next month while I'll be in Mexico attending my niece's wedding.  My 6-day vacation will be 11/3 - 11/10.
  • Supervised Bryce with verifying Informatica data using recently creating queries including the ones to calculate gl balance amounts to test.   He completed, closed his jiras.

Plan on Friday 11/29 - Saturday 11/30 for roll out Informatica dimension, fact jobs to production: 

Friday:  Setup, verify connections to production DB.  Unplug Talend dimension, fact jobs.

Saturday: Jobs run, verify results, debug, fix if necessary.

11/2 Update :  Migration went well on the week end.  This task is completed.

Anh’s Status 9/14/2015 – 9/25/2015

  • I was able to solve problem: Exception in thread "main" java.lang.Error: Unresolved compilation problem: at routines.UciUtils.fiscalYearDWH(UciUtils.java:485) by making copies of all available jar files on the talend server. 
  • Balance fact:  Worked with Analytix team to get balance jobs fixed.  No more mismatches.   Both balance and gl entries jobs are now ready for Cognos developers to test before they can be rolled out.
  • Doc details dim:  Worked with Analytix team to solve problems in doc detail dim job.  They fixed it but their solution is not portable due to overwrite sql query in the design.   We’re working with them to enhance the solution.
  • Purchasing fact jobs:  I exported all 9 jobs to Unix to run them due to more memory needed.  Ran all 9. Configured each job to save in their tables to compare for mismatches in sub jobs.  Reported to them to fix.
  • Configured and verified dim, fact tables were written at the test production DBs as expected ie account_1-3 to kfs_stage_infor, the last account table to dss_kfs DBs.    They’re ready to be scheduled.   I scheduled them to run on the test production to stage_kfs_infor, dss_kfs DBs.
  • Scheduled daily (Tue - Sat) at 6 AM for dim jobs, 9 AM for fact jobs on the test production.
  • Cloned test environment from Talenddev01 to oit docs because Matt doesn’t have access to the one on talenddev01
  • Trained Matt on informatica, mapping, queries to test informatica dim, fact jobs.
  • Completed jira https://services.adcom.uci.edu/jira/browse/KFSDW-876

Anh’s Status 9/1/2015 – 9/11/2015 (4 working days: 8th - 11th.  Off 2 vacation days and Labor Day)

Regarding "Investigated with Analytix the 3 ods jobs that failed all of a sudden", they edited these jobs, dropped prefix DB schema on tables.  I fixed them.  They've been running fine since. 

...

Work is in progress with Outbound: 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-727
 
Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-750
Completed setting file trigger for uciBud100PopulateTablesJob,uciStf100PopulateTablesJob.  For testing purpose, these jobs ran when their trigger files are created then it moved them to a directory for archiving purpose.  Because the Informatica scheduler doesn’t have option for month end date, these jobs are currently scheduled to run monthly on the 5th.  Because we have jobs that run every 2 months, 6 months, quarterly, etc.  I think that it’s a better approach to have a batch file to turn the flag true or false when it sees the file trigger then when the flag is true then it'll trigger to run the job.    I wrote a batch script to return value true if it finds the file otherwise false.  I need to research how to make informatica event trigger to see this value.   

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-588

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-746
Completed OLTP jobs.  Only job that failed is uci load title because of the Oracle table has primary key criteria causing duplicate key error in this job.  

...

Remaining fact jobs to verify: Need to add more tests on gl entries fact, AssetFact, AssetPaymentFact, Account dimension. 

 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-727

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-746

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-761

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-768

Export all talend jobs on new server

...

Completed

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-740

Completed

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-660

Completed

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-486

Completed

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-747

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

...

Need to work with DBA to restore data to dss schema.  Plan for informatica jobs to run on production to test.

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-744

 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-727

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-743

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-745

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-748

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-746

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-761

Export all talend jobs on new server

...

  • Completed section:

    Push all kfs jobs (1 by 1) back 1 hour: 

    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-726

    Completed modifying unix cripts to push back as well to alert when jobs are not done. 

    Created service now ticket INC0025478 to request zena installation on informatica server so that Jarrod's app can create etl file triggers.  Update:  Jason Lin said that there's no more license.  We're replanning this task.

    Provided input to Analytix regarding ods jobs, mysql linked server.

    Resolved discrepancies in ODS jobs with Analytix. 

    Completed integrating tests for all staging jobs which took double amount of time to run.  They're fixing these so that the can complete running nightly within 1-hour window. 

    Created ldap_people,ldap_people_his under schema 'people_m' in MYSQL per Analytix's request.

  • Not complete yet section: 
    Talend pager duty week 6/6 - 6/12.

    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-744

    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-727

    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-740

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-743

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-745

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-747

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-748

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-746

          

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-486
 same as
Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-660
    

Export all talend jobs on new server

...

System DSN Connection was created, inform them.

 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-697

 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-686
 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-722

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-712

  • Not complete yet section: 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-711

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-723

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-698

Talked to Jarrod to have trigger files created on the Informatica server (Windows) for Informatica jobs to run for month-end.  He mentioned that the best approach is to have Zena agent install on this server so his app can tell Zena to create files there.  We need to discuss this more as soon as Analytix finishes their tests with smsfeed jobs which they already imported in our repository.

...

  • Completed section:

    Competed
    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-676

    Completed:
    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-660

    Completed
    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-686

    Completed sending DDL script to create ldap_peole table to Analytix
    Completed
    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-693


    Informatica Conversion:  Analytix sent the first job, staging_uci.   This job encountered errors (timestamp type columns, non-existing tables in kfscfg).  After debugging it, I sent them the errors with suggestions to resolve.

    Updated
    Jira Legacy
    serverOIT System JIRA
    serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
    keyKFSDW-590


  • Not complete yet section: 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-697

Informatica Conversion:  ongoing task. 

...

1) Completed fixing staging krns job

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-672

2) Pager duty this week. 

3)  Completed

Jira Legacy
server
OIT
System JIRA
serverId
252346bd
d1c6a489-
c418
217f-
34af
3ae9-
94a0
bd3a-
0709b2108812
01dc24609c18
keyKFSDW-671
. 

     Completed setting up to schedule this job to run nightly.

 

4)  Emailed ddls scripts to Analytix per their request to create tables below:

...

5)  Completed 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-486

6) Completed new job syncInvoiceNumberSundryDebtors for  

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-660

7) Completed the change to sd-nebula to remaining talend jobs

...

5)  Need to fix 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-676

6)  Need to schedule syncInvoiceNumberSundryDebtors to run on production

 

Anh’s Status 3/28/2015 – 4/10/2015

...

1)  Fixed uciSmsGIFeed.  ods_kfs.UCI_SMS_FEED_DETAIL_T table now has all records for Feb. data with batch date ’06-MAR-15’, matching Oracle side:  31,647 records, debit amount total is $3,647,165,482.52. 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-633

2)  dwhreader: 

...

5)  Analytix:  Provided our versions of our DBs that they asked.  They confirmed access to talenddev server.  Attended weekly status meeting this Friday morning.  They're going to send me a list of sqlsver tables so I can send them DDLs to them to create these tables.

6)  uciSmsGIFeed:  The job loaded month-end data this month without problem.  FYI -  Following day, this was the 2nd time this month that Jarrod reran, regenerated the trigger.  The job ran fine 2nd time. 

7)  Completed

Jira Legacy
server
OIT
System JIRA
serverId
252346bd
d1c6a489-
c418
217f-
34af
3ae9-
94a0
bd3a-
0709b2108812
01dc24609c18
keyKFSDW-656

  • Not complete yet section: 

    1)  Staging krns job loaded all tables but Talend sent false notification.  I need to debug this job.
    2)  Export all talend jobs on new server
    3)  ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT

...

5)  SBSO: 

Jira Legacy
serverOIT System JIRA
serverId252346bdd1c6a489-c418217f-34af3ae9-94a0bd3a-0709b210881201dc24609c18
keyKFSDW-486
with the newest revision to the re-mapping logic:  When I came back from my 1-day vacation (due to many accumulated vacation hours, use it or loose it), I was told that we hold off on this task until further direction. 

6) Pager duty next week. 

 

Anh’s Status 3/16/2015 – 3/27/2015

...