/
Anh's status report - 2016

Anh's status report - 2016

Owned by Kim Anh Thi Le
Last updated: Apr 17, 2022 by Oluwasemilore Ajibola (Deactivated)Version comment

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.

KFSDW-1443 - Getting issue details... STATUS (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)

KFSDW-1496 - Getting issue details... STATUS  (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: 

KFSDW-1530 - Getting issue details... STATUS (Old SBS aging data files for previous month were sent to DW for this month-end October)

KFSDW-1532 - Getting issue details... STATUS  (Need to enhance the SBS Aging task to avoid duplicate records)

UAT:

KFSDW-1415 - Getting issue details... STATUS 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 KFSDW-1417 - Getting issue details... STATUS , KFSDW-1418 - Getting issue details... STATUS  

 

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

ETL: 

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

KFSDW-1395 - Getting issue details... STATUS (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

  KFSDW-1421 - Getting issue details... STATUS

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

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.

KFSDW-1290 - Getting issue details... STATUS  

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.

KFSDW-1292 - Getting issue details... STATUS

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.   KFSDW-1149 - Getting issue details... STATUS  is on production, closed.

Completed user acceptance test for the new rafg dimention and new changes in gl entries and balance fact tables  KFSDW-1089 - Getting issue details... STATUS .  It's now ready to deploy.

Completed fixing missing fund names.  Tested and verified.  KFSDW-1183 - Getting issue details... STATUS  is ready to deploy.

KFSDW-974 - Getting issue details... STATUS  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 KFSDW-899 - Getting issue details... STATUS

Closed KFSDW-895 - Getting issue details... STATUS

Closed KFSDW-973 - Getting issue details... STATUS .

Closed KFSDW-972 - Getting issue details... STATUS  

Closed KFSDW-590 - Getting issue details... STATUS

Completed on dev.  Need to verify before turning over to production KFSDW-586 - Getting issue details... STATUS

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. 

Fixed the informatica CAFP job because Analytix had wrong column name.  It now runs fine.

Completed and verified month end outbound loads Informatica CFSBAL, CAFP, and SMS workflows for September below:

[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_10_T]

[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_11_T]

[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_20_T]

[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_30_T]

[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_31_T]

[dss].[ods_kfs].[UCI_UCOP_CAFP_ACCOUNT_T] 

[dss].[ods_kfs].[UCI_UCOP_CAFP_FUND_T]

[dss].[ods_kfs].[UCI_SMS_FEED_SUMMARY_T]

[dss].[ods_kfs].[UCI_SMS_FEED_DETAIL_T]

 

Created queries to give Bryce to verify balance 1 job using account number and worked with Analytix on balance 1 job.  Test passed.  No mismatch.

Created a new variable and configured informatica dimensions, facts workflows so that they can be scheduled to run nightly on the test production to stage_kfs_infor to avoid colliding with the Talend jobs. 

Did trouble-shoot the failed tests that Bryce reported on the dim jobs: account, doc type, project, object code, chart.  Need to configure them to run in synched with Talend then retest them.

Anh’s Status 8/17/2015 – 8/28/2015  

Worked with the DBA, Middle Ware teams on INC0037523 INC0037600.  They now completed, closed.

Rolled out the Informatica jobs that load KFS Web Services successfully to production.  They’ve been running nightly without any issue.

Completed, closed the following jiras:

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

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

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

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

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

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

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

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

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

Fixed the informatica SMS Feed workflow because the design was not correct, truncated the sms feed detail table, wiped out history of data.  I recovered all history data to the table.  It now loads new records, keeping history data from previous months.

I was able to size the memory needed as 3G for the Unix talend dimension jobs to run on node3dev after getting error “…insufficient memory…”.  After that the jobs ran without error,  updated current data to all the dimension tables.  Analytix needed them to verify against their Informatica dimension jobs. 

Created queries to give Bryce to connect to mysql through the linked server because he doesn’t have mysql client installed on his PC. 

Investigated with Analytix the 3 ods jobs that failed all of a sudden today after they ran every night without any issue for the last couple weeks.  We should hear from them soon.

Anh’s Status 7/27/2015 – 7/31/2015 (1 week status)

Big thanks to Valerie for keeping production data load smoothly after Talend officially expired 2 days ago. 

Completed section:    

 

  • Created a list of ods jobs for Bryce to verify.  Tammy volunteered to help with this task.
  • Found problem, reported to Analytix their pcard workflow as to why they included all sessions for all FP_PR tables after pcard session loads those tables.
  • Warren came to ask me to help with doing queries to see the prefixes for employeeNumber.  I gave him records what what he needed. 
  • Rolled Informatica staging jobs to production.  Problem with some uci tables are not available during nightly run, but are there when running manually for debugging purpose.
  • Worked with Jeric from dba team on the intermitten when sql terminated a staging job at night.  Created now ticket
  • Created new jiras for verifying oltp, ods jobs:https://services.adcom.uci.edu/jira/browse/KFSDW-821, https://services.adcom.uci.edu/jira/browse/KFSDW-820
  • Most ODS, OLTP jobs had error running.  Solved problem by creating the table and data in it to set up environment below:

insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")

values ('MYSQL_PROD','dwhm_pps','ods_kbm','ODS_pps')

insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")

values ('MYSQL_PROD','dwhm_ppscurr','ods_kbm','ODS_ppscurr')

insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")

values ('ORACLE_PRD','KFSPRD','ods_ksams','ODS_ksams')

insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")

values ('ORACLE_PRD','KFSPRD','ods_kbm','ODS_kbm')

insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")

values ('ORACLE_PRD','KFSPRD','ods_kfs','ODS_kfs')

  • Debugging issue with Talend ods jobs didn’t load for  FP_PR tables.  
    Update:  As soon as we removed the duplicate records in the [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS] table, jobs loaded these tables fine.  We need to keep an eye on this table if the same problem occurs.  Thanks Austin.
  • Tammy and Bryce are doing a good job finding problems in the informatica jobs.  I already informed Analytix and asked to have their jobs (ods, oltp) ready by Monday. We’ll rerun the tool to verify them. 
  • Done fixing, verified the check_log.exe script on unix to page.

Not Completed section:

Work on OrgLevelsforLDAP and the ksams_webservices jobs.Work on outbound jobs to run on production.

Continue working with the Conversion team on the Informatica conversion task.

Continue supervising Bryce on his assigned tasks.

Anh’s Status 7/20/2015 – 7/24/2015 (1 week status)

Completed section:

Work is in progress with Outbound:  KFSDW-727 - Getting issue details... STATUS   KFSDW-750 - Getting issue details... STATUS 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.   

KFSDW-588 - Getting issue details... STATUS

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

Completed ODS jobs.  Austin helped with them, said that there’s remaining errors to be fixed.  I’ll get to those as soon as I can.

Responded to 9-page email full of queries and questions from Analytix to move forward beginning of the week.  More long emails from them since to respond to almost daily.  They’ve been asking us to rerun talend jobs averaging every day this week after they’ve either overridden talend tables, or found mismatches between them.  They also asked to drop talend dimension tables including all foreign keys constraints to start a fresh load.  We did all what they requested.  As of their status sent to us today on dimension jobs, none was fixed.  Their status hasn’t changed since last week.   9 out of 14 dimension jobs still have fail status.  

Completed seting up all necessary workflows for daily run and for Bryce to fill in the email notification.

Trained Bryce to get familiar with Informatica workflow manager in order to set up email notification for all informatica session in the daily workflows.  He has completed staging, dimension jobs on both dev and production.  He’ll continue with remaining sessions.

 

Not Completed section: 

Work on Talend jobs so they can run on Unix by next Wed when talend license will expire.

Continue working with the Conversion team on the Informatica conversion task.

Continue supervising Bryce on his assigned tasks.

Continue with the outbound jobs.

Anh’s Status 7/13/2015 – 7/17/2015 (1 week status)

Completed section:

Completed setting up and configuring connection to run informatica staging jobs on lunar to load oracle tables from production to stage_kfs_infor

Completed setting up and configuring informatica dim, fact jobs to run on lunar to dss schema

Completed setting up and configuring informatica dim, fact jobs to run on solar to dss_kfs schema

Done fixing staging krew job.  It ran on prod without problem.  Created queries to verify data.  They match.   I need to run it on dev so Bryce can use the tool to verify all data in the tables.

Supervised, provided input to Bryce to complete verifying all staging, dim, fact jobs that ran ok so far.  Send problems/mismatches to Conversion team to fix.

Created, updated jiras, status speadsheets for reporting problems, fixes for all teams to follow progress.

Requested dba to create oracle_prd linked server on lunar so we can load oracle production to stage.  It's available now.

Verified informatica staging job on production:

Talend: [stage_kfs].[dbo].[AP_SUM_ACCT_T] 75,157 recs

Informatica:[stage_kfs_infor].[dbo].[AP_SUM_ACCT_T] 75,162 recs (6 records more than Talend as expected due to the time when the job ran)

Interesting 6 records difference because the LST_UPDT_TS from the source was created after talend job ran, before informatica one ran.

Because of this reason, I rescheduled staging job to run at the same time as talend:  00:02 AM in order to accurately verify informatica jobs.

Requested Brian to create new mysql DBs for testing Informatica jobs: 

Worked with Conversion team to fix 6 staging tables, or else their dim,fact jobs won’t match:

CA_ACCOUNT_T did not pass due to differences in data
GL_ENTRY_T did not pass due to differences in data
KREW_DOC_TYP_T did not pass due to differences in column schema (SEC_XML)
UC_FUND_T did not pass due to differences in data
KREW_ACTN_TKN_T did not pass due to differences in data
KREW_DOC_HDR_T did not pass due to differences in data

scheduled to run daily for dim jobs at 9:00 AM, fact jobs at 9:30 AM on prod.

Coordinated with Tammy, DBA team that we run talend and informatica staging jobs on solar starting at the same time:  00:02 in order to get identical staging data for both.  So far no issue.

Done fixing staging krew job.  It ran on prod without problem.  Created queries to verify data.  They matched.   I need to run it on dev so Bryce can use the tool to verify all data in the tables.

Completed setting up OLTP jobs.  Tested run some jobs without error.  Will continue this task next week.

Not Completed section:

Continue  working on OLTP tasks.

Plan to work on trigger files.

Continue working with Conversion team to ensure they fix problems in their jobs.

Continue working issues with the converstion task.

 

Anh’s Status 7/6/2015 – 7/10/2015 (1 week status)

Completed section:

Completed adding stored procedure, latest sql files to https://services.adcom.uci.edu/jira/browse/KFSDW-590

Completed rescheduling on unix, deploy2 to run start running jobs on Sunday nights (was Monday nights) due to kfs running on Sunday.

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

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

INC0031493 for Jeric to create oracle_prd linked server on solar.  This is completed.

Austin created new connection stage_kfs_infor on lunar, stage_kfs_infor_prod on solar.

Status on load_budget_fund_groups_fromExcel:  We told them not to convert.  It is not needed since Durendal built a front end for users.

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

 

Not Completed section:

Configure jobs to run by file triggers

Set up and configure connection to run informatica staging jobs on lunar to load oracle tables from production to stage_kfs_infor

set up and configure informatica dim, fact jobs to run on lunar to dss schema

set up and configure informatica dim, fact jobs to run on solar to dss_kfs schema

Continue working, debugging with Analytix team

Anh’s Status 6/822/2015 – 7/03/2015

Completed section:

Completed the preliminary test of Informatica Asset fact job.  It failed because it loads way too many more records, inserts records every time the job runs without truncating the table.  The conversion team is working on fixing it.   Will need to continue test on this job as soon as they complete their fix.

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

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

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

Completed testing informatica jobs using queries from Manav.  They passed. 

Created a new tab "UCI Test By Queries" in the spreadsheet InformaticaJobsCompletionStatus_{today}.xlsx to test using queries.  I've found problems in their converted balance, gl entries fact jobs. I  sent them to the conversion team to fix. 

Completed INC0029671: Jeric to copy data from dss_kfs to dss schema.  Plan for informatica jobs to run on production to test. 

Created 13 new jiras (KFSDW-771 throught 783).  Per our conversation,  I’ve assigned most to Bryce, to myself the most difficult ones (balance, gl entries facts)to assign to Bryce to verify informatica jobs by comparing data between Talend and Informatica tables.

Worked with conversion team to debug their jobs:  They used wrong type as real instead of decimal(19,2) for amount columns.

Reopened jira for Jeric to create the sql link server on solar for production https://services.adcom.uci.edu/jira/browse/DWH-1604.  He finished.

INC0030044 Jeric to fix problem not able to see oracle tables in oracle _dev linked server on nebula. 

Added more amount columns to balance, gl entries fact table to test.  Sent problems found to Conversion team.

Cleaned up talenddev01 server to gain up to 7GB of disk space.  It was full, unable the Conversion team to perform their debugging.

wf_staging_cg has error running.  Reported to Conversion team.

Created tables KFS_D_DOC_FISCAL_PERIOD, KFS_D_PROJECT and their primary keys for talend and informatica schemas to get ready for sqldiff tool to run.

Not complete yet section: 

(A Friendly reminder:  UCI off on Friday 7/3.  My vacation day or loose it on Thurs. 7/2) 

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

  KFSDW-727 - Getting issue details... STATUS

KFSDW-746 - Getting issue details... STATUS

KFSDW-761 - Getting issue details... STATUS

KFSDW-768 - Getting issue details... STATUS

Export all talend jobs on new server

ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT    

Anh’s Status 6/8/2015 – 6/19/2015

Highlight:

Completed section:

Completed Talend pager duty week 6/6 - 6/12.

       Completed solving fiscal year end/month end problems in outbound jobs, CAFP and CFSBAL due to duplicated records error.  These jobs ran fine after that.  Data matched the Oracle side. 

       Worked with Analytix daily to solve issues.  We found out that some staging tables in Oracle kfscfg were empty or wiped out preventing them from doing their fact jobs.  Tom R. copied data from stage production to dev.  They had data to complete dimension and fact jobs.  

Completed KFSDW-740 - Getting issue details... STATUS

Completed KFSDW-660 - Getting issue details... STATUS

Completed KFSDW-486 - Getting issue details... STATUS

Completed KFSDW-747 - Getting issue details... STATUS

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Not complete yet section: 

Remaining fact jobs to verify: AssetFact, AssetPaymentFact, balance fact 3,4,5 (1,2 finished, matched records count between Informatica and Talend).

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

KFSDW-744 - Getting issue details... STATUS

  KFSDW-727 - Getting issue details... STATUS

           KFSDW-743 - Getting issue details... STATUS

           KFSDW-745 - Getting issue details... STATUS

           KFSDW-748 - Getting issue details... STATUS

           KFSDW-746 - Getting issue details... STATUS

KFSDW-761 - Getting issue details... STATUS

Export all talend jobs on new server

ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT    

Anh’s Status 5/25/2015 – 6/5/2015

Highlight: 

  • Completed section:

    Push all kfs jobs (1 by 1) back 1 hour:  KFSDW-726 - Getting issue details... STATUS

    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.

    KFSDW-744 - Getting issue details... STATUS
    KFSDW-727 - Getting issue details... STATUS
    KFSDW-740 - Getting issue details... STATUS

           KFSDW-743 - Getting issue details... STATUS

           KFSDW-745 - Getting issue details... STATUS

           KFSDW-747 - Getting issue details... STATUS

           KFSDW-748 - Getting issue details... STATUS

           KFSDW-746 - Getting issue details... STATUS

           KFSDW-486 - Getting issue details... STATUS  same as KFSDW-660 - Getting issue details... STATUS     

Export all talend jobs on new server

ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT    

Anh’s Status 5/11/2015 – 5/22/2015

 Highlight: 

  • Completed section:

Talend pager duty week 5/9 - 5/15.

Analytix Informatica staging_uci job is now solved, completed. 

They confirmed that they are able to access our Informatica server. 

System DSN Connection was created, inform them.

  KFSDW-697 - Getting issue details... STATUS

  KFSDW-686 - Getting issue details... STATUS  

KFSDW-722 - Getting issue details... STATUS

KFSDW-712 - Getting issue details... STATUS

  • Not complete yet section: 

KFSDW-711 - Getting issue details... STATUS

KFSDW-723 - Getting issue details... STATUS

KFSDW-698 - Getting issue details... STATUS

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.

Grace mentioned that she needs to have table, columns in MySQL.

Conversion:  on-going task.

Providing MySQL connections to Analytix as soon as DBA created them.

Export all talend jobs on new server

ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT 

Anh’s Status 4/27/2015 – 5/08/2015

 Highlight: 

  • Completed section:

    Competed KFSDW-676 - Getting issue details... STATUS
    Completed: KFSDW-660 - Getting issue details... STATUS
    Completed KFSDW-686 - Getting issue details... STATUS
    Completed sending DDL script to create ldap_peole table to Analytix
    Completed KFSDW-693 - Getting issue details... STATUS

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

  • Not complete yet section: 

KFSDW-697 - Getting issue details... STATUS

Informatica Conversion:  ongoing task. 

Talend pager duty next week 5/9 - 5/15

Export all talend jobs on new server

ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT 

Anh’s Status 4/13/2015 – 4/24/2015

 Highlight: 

  • Completed section:

1) Completed fixing staging krns job KFSDW-672 - Getting issue details... STATUS

2) Pager duty this week. 

3)  Completed KFSDW-671 - Getting issue details... STATUS . 

     Completed setting up to schedule this job to run nightly.

 

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

MYSQL Tables : title_db2

 SQLSERVER Tables:      UCI_LD_CSF_TRACKER_SNAPSHOT_T, employee_basic, agingreport_det_kfs, agingreportcon_kfs

To protect sensitive data in the file, I used webfiles with password to send agingreportdet.dat 

5)  Completed  KFSDW-486 - Getting issue details... STATUS

6) Completed new job syncInvoiceNumberSundryDebtors for   KFSDW-660 - Getting issue details... STATUS

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

  • Not complete yet section: 

1)  Make changes to new host sd-nova

2)  Export all talend jobs on new server

3)  ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT

4)  Change host name to ods talend jobs to replace Nebula

5)  Need to fix  KFSDW-676 - Getting issue details... STATUS

6)  Need to schedule syncInvoiceNumberSundryDebtors to run on production

 

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

 Highlight: 

  • Completed section:

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

2)  dwhreader: 

    • Completed ap,ar,cm,fp,fs,kr,krew,krim,ld,pdp,pur,sh,tem,uci 
    • Completed staging_uc:  Talend is buggy that it loads uci tables not just uc (without the i)  when using UC_%.  Talend sucks.  The syntax to make the staging_uc job work is:
      "1 = 2) union select table_name as tname from all_tables where (owner = '" + context.KFSINT1_Schema  + "' and TABLE_NAME like 'UC_%' and TABLE_NAME not like 'UC_HOME_DEPT_T' and TABLE_NAME not like 'UC_ACCT_NBR_GEN_T' and TABLE_NAME not like 'UCI_%'"
    • Completed  OLTP jobs: utilities_award_data_to_dwhm_fs, utilities_award_data_to_dwhm_ods, utilities_ldap_all_tables, utilities_CostSharing, utilities_webservices, copy_ods_data_dss, copy_dss_kfs_data_to_dss, copy_dss_data_to_dss_2, prod_verify_kfs_data, kill_long_suspended_queries, uciLoadTitle
    • Completed ODS jobs: ods_ca, ods_pur_po, prod_ods_etl_and_accounting, ods_kfs_pcard, ods_kfs_tem, ods_kfs_pdp, ods_kbm_employee_snapshot, ods_kfs_ldap_people_employee_basic, Outbound_External_Interface_Table_ETL, uciBud100PopulateTablesJob,uciStf100PopulateTablesJob, uciStf200PopulateTablesJob, uciUcopCafpJob, uciUcopCfsbalJob, uciUcopEfa100Job, uciSmsGlFeedJob, uciUcopEfa200Job

3)  Staging_TEM job ran fine after Warren cleaned up in Oracle side.

4)  Received ODBC installed on PC.  Informatica can connect to Oracle production

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

  • 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

          4)  Change host name to ods talend jobs to replace Nebula

5)  SBSO:  KFSDW-486 - Getting issue details... STATUS 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

 

Highlight: 

  • Completed section:

1)  Fixed prod_all_ldap_tables by increasing length of LAST_UPDATE_TS from 23 to 30. 

2)  Fixed doc details job from failing due to error "java.lang.OutOfMemoryError: GC overhead limit exceeded".   I increased to 15G.  Job ran fine.  Data is up-to-date.

3)  dwhreader: 

a)  Completed dev_AssetDimensionMaster_creates_trigger

b)  The staging_cg job failed at the iterate in toraclelist component due to tables as synonyms in dwhreader.   Thanks Valerie.  I put in the fix.  This job now ran fine.   Completed.

c)  Completed dev_dim_account_master_creates_trigger.

d)  Completed dev_staging_ca has the new change.

 

4)  JIRA KFSDW-612:  Users confirmed the fix.  It's now closed. 

5)  Fixed prod_dim_asset_master, prod_dim_account_master by using the older version in tac.  The cause of the jobs failed is unknown, and is under investigation.  The only change made was in tOracleList component in the staging_cg job in dev for talend to see dwhreader schema.   To solve this problem, I used the previous version before the change in tOracleList.  The job has been running fine without problem. 

 6)  Created JIRA,  linked with JIRA  

7)  Debugged prod_staging_krew job that failed on production last night.  I’m in the process of debugging all 50+ krew tables, 1 by 1. So far loaling table KREW_DOC_HDR_EXT_DT_T from kfsprd caused error "java.sql.SQLException: Only dates between January 1, 1753 and December 31, 9999 are accepted.".   Some records that with value of VAL (only date column in this table) has date that is out-of-range mentioned above.  I brought this up to Warren's attention.  He deleted these records.  The nightly job ran fine after that.

8)  Staging_TEM job failed with date out-of-range error.  Warren identified the mistyped date from users, said that he will clean up data this week end.

  • Not complete yet section:
        
          1)  dwhreader: 
        
                    a)       staging_uc has Incorrect syntax near ')' after trying many different      syntaxes.
                    b)       Remaining staging jobs:      ap,ar,cm,fp,fs,kr,krew,krim,krns,ld,pdp,pur,sh,tem,uci 
        
         2)  uciSmsGIFeed: The request was not approved for adding the time      next to the date to Jarrod's JIRA kfsi-11864.    I still      need to proceed with creating an additional key using batch_date in order      to load the Feb data.  Steps required are:  Move all data      from 'ods_kfs.UCI_SMS_FEED_DETAIL_T to a temp table before dropping      it in order to add another key to this table.  Move data from the      temp table back to it, including data for Feb that was saved in      [dss_kfs].[ods_kfs].[z_temp_from_oracle_UCI_SMS_FEED_DETAIL_T]
        
         3)  Export all talend jobs on new server
        
         4)  ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT

          5)  SBSO:  with the newest revision to the re-mapping logic

  6)  Change host name to ods talend jobs to replace Nebula