Anh's Status report - 2017

11/17 - 12/14

ETL:   

• KFSDW-1728 Completed and verified.  It’s ready for test.  Completed CHG0033310 to deploy from kfs7 to dev, production.  Before my work on this ETL enhancement, it took more than 1 hour to load the ledger fact table nightly.  After I completed the ETL,  it now only takes about 10 minutes to load incrementally.
• kfsdw-1727:Completed deploying changes, to KFS7.  Completed verifying. Completed CHG0033312
• kfsdw-1732:  Completed using variables for stage DB connection in the code for all KBM ETL.  Migrated these objects to KFS7. 
• https://jira.oit.uci.edu/browse/KFSDSU-252  Completed verifying with cognos developers.  They confirmed that their reports show accurate data.
• The Daily_oltp night job sometimes failed on dev due to the conflict schedule in KFS7.  This affected the web services apps.  The problem was solved by moving schedule at a later time in KFS7.  I worked with web services developers to ensure they accessed data ok now.  I also suggested that they deploy their apps to production where data there has been available.  I was told that they did.

Misc.:

Attended 1-on-1 weekly status meeting. 

Weekly KFS Decision Support Technical Status

Attended KFS Upgrade for Decision Support meeting

Next plan: 

On-going ETL development to maintain and upgrade KFS DW

On-going KFS upgrade related activities

Informatica admin related activities

11/3 - 11/16

ETL:   

• • Completed the last bridge table of the Award model.  The jira https://jira.oit.uci.edu/browse/KFSDW-1671 is ready for test. 
    Update:  Kevin said to me to wait for him when I can go ahead to replicate all ETL objects relating to award model in the KFS7.
  • KFSDW-1728 - Getting issue details... STATUS  Completed ETL and testing.  I'm waiting for Pramod when I can deploy it in the upgrade environment.
  • KFSDW-1727 - Getting issue details... STATUS Completed ETL for the followings:
    
    • m_ods_kbm_postInfo_Load_YTD_FTE
  • m_ods_kbm_postInfo_Load_Current_FTE
  • m_ods_kbm_postInfo_Load_Current_FTE_2
  • m_ods_kbm_postInfo_Load_Final_Bal_FTE
  • m_ods_kbm_postInfo_Load_LDGR_ONLY_T
  • m_ods_kbm_postInfo_Load_LD_Ldgr_Bal _V02_T
    The lead testers approved to deploy them (CHG0033273).  Tomorrow these will be deployed to production by Austin, to upgrade by me.
    
    
  • Started https://jira.oit.uci.edu/browse/KFSDW-1732. 

Misc.:

Attended 1-on-1 weekly status meeting. 

Weekly KFS Decision Support Technical Status

Attended KFS Upgrade for Decision Support meeting

Next plan: 

On-going ETL development to maintain and upgrade KFS DW

On-going KFS upgrade related activities

Informatica admin related activities

10/20 - 11/2

ETL: 

• Completed the last bridge table of the Award model.  The jira https://jira.oit.uci.edu/browse/KFSDW-1671 is ready for test.
• Worked with appropriate team members and discussed solution to solve the problem with the LD_LDGR_ENTR_T missing records in the KFS7 due to entry_ID not being generated in sequence from the application side.  We're waiting for the application developer (Jarrod) to fix his logic then we'll reload it. 
• For the KFS upgrade activities,  attended discussion meetings to gather requirements to load the fact table kfs_f_ledger_t in the KFS7 environment.  Created a new JIRA to start working on it:  https://jira.oit.uci.edu/browse/KFSDW-1728.
• To replicate all ETL objects relating to award model in the KFS7, I've created jiras to work on this task.   They're below:

https://jira.oit.uci.edu/browse/KFSDW-1720

https://jira.oit.uci.edu/browse/KFSDW-1721

https://jira.oit.uci.edu/browse/KFSDW-1722

https://jira.oit.uci.edu/browse/KFSDW-1723

https://jira.oit.uci.edu/browse/KFSDW-1724  

Misc.:

Completed the draft version of Achieve.  Plan to complete the final version then submit it ASAP.

Attended 1-on-1 weekly status meeting

Weekly KFS Decision Support Technical Status

Attended KFS Upgrade for Decision Support meeting

Next plan: 

On-going ETL development to maintain and upgrade KFS DW

On-going KFS upgrade related activities

Informatica admin related activities

10/6 - 10/19

ETL: 

Completed, closed https://jira.oit.uci.edu/browse/KFSDW-1713

Attended meeting to discuss with Pramod and Kevin to clarify detail requirements for the dss_kfs.KFS_CG_ORG_GRPMBR table.   

Misc.:

Attended 1-on-1 weekly status meeting

Weekly KFS Decision Support Technical Status

Attended KFS Upgrade for Decision Support meeting

Next plan: 

Continue etl for the last requirement in the KFS_CG_ORG_GRPMBR task as soon as the detail information is available.

KFS upgrade related activities

Informatica admin related activities

9/22 - 10/5

ETL: 

Completed all items from peer review 9/17 for the following jiras:

https://jira.oit.uci.edu/browse/KFSDW-1656

https://jira.oit.uci.edu/browse/KFSDW-1669

https://jira.oit.uci.edu/browse/KFSDW-1670

https://jira.oit.uci.edu/browse/KFSDW-1672 

Completed https://jira.oit.uci.edu/browse/KFSDW-1646

Completed CHG0032947

KFS upgrade related activities:  Completed the following jiras to copy objects to the tst_dev kfs fodler:

https://jira.oit.uci.edu/browse/KFSDW-1712

https://jira.oit.uci.edu/browse/KFSDW-1713: Completed the dss_kfs7.ods_kbm.StaffingbyAcctorOrg_T_New. 

Misc.:

Attended 1-on-1 weekly status meeting

Attended KFS Upgrade for Decision Support meeting

Next plan: 

Continue etl for the last requirement in the KFS_CG_ORG_GRPMBR task as soon as Kevin clarifies.

KFS upgrade related activities

Informatica admin related activities

9/8 - 9/21

ETL:

https://jira.oit.uci.edu/projects/KFSDW/issues/KFSDW-1167: Completed, Closed

https://jira.oit.uci.edu/browse/KFSDW-1656: Ready For Test.

https://jira.oit.uci.edu/browse/KFSDW-1669:  Completed applying code review.  It’s Ready For Test

https://jira.oit.uci.edu/browse/KFSDW-1670:  Completed applying code review.  It’s Ready For Test

https://jira.oit.uci.edu/browse/KFSDW-1671:  Received confirmation from the last requirement from Pramod and Kevin.  Started working on this.

Misc.:

Held debugging session with Saurav on the last requirement for the ETL of the KFS_CG_ORG_GRPMBR

Attended 1-on-1 weekly status meeting

Attended KFS Upgrade for Decision Support meeting

Attended Weekly KFS Decision Support Meeting

Next plan:

KFSDW-1167 - Getting issue details... STATUS

KFSDW-1707 - Getting issue details... STATUS

Continue with the Award task

KFS upgrade related activities

Informatica admin related activities

8/25 - 9/7 (campus closed on Labor Day on the 4th)

ETL:

KFSDW-1702 - Getting issue details... STATUS  Completed ETL.  It's now ready for test.

KFSDW-1668 - Getting issue details... STATUS

Fixed CGAWD_ENTRY_DT.  This data is now available.

Fixed ETL for ucinetids.  This data is now available.  Regarding the multiple project director ucinetids, Kevin said that he'll get back to me on my questions to confirm on requirements for getting this data.   

  KFSDW-1290 - Getting issue details... STATUS   Completed ETL.   It’s now ready for test 

Misc.:

Worked with Kevin from the Accounting on multiple project director ucinetids issues found in the ward task.

Worked with Eufemia from the Cognos team to finalize additional requirement for KBM.

Attended Weekly KFS Decision Support Meeting

Next plan:

KFSDW-1167 - Getting issue details... STATUS

KFSDW-1707 - Getting issue details... STATUS

Continue with the Award task

KFS upgrade related activities

Informatica admin related activities

8/11 - 8/24 

ETL:

Met with Kevin to gather detail information/logic on the remaining requirement: To create CG_ORG_GRP_KEY and CG_ORG_GRP_SID: Concat string using criteria of FIN_COA_CD,ORG_CD,CGPRPSL_NBR, CGAWD_PRM_ORG_IND from the CG_AWD_ORG_T  table in the CG_ORG_GRP table.  Then in the KFS_D_CG_AWD table, create the key using the same criteria to create a key, then use this key to look up in the CG_ORG_GRP table to get the value of the CG_ORG_GRP_SID (integer). 

Completed adding null handling for  KFS_D_CG_AGNCY, 100+ columns in the KFS_D_CG_AWD table.

Completed KFS_CG_ORG_GRP,  KFS_D_CGAWD_BDGTPER.  They’re ready for test.

Informatica admin task: 

Completed, closed tickets below:

INC0184544

RITM0016143

Misc.:

Meeting with Kevin from Accounting on remaining requirements of the Award task

Attended Weekly KFS Decision Support Meeting

Attended Weekly KFS Decision Support Technical Status

 Next plan:

Continue with the Award task

KFS upgrade related activities

Informatica admin related activities

7/28 - 8/10 

7/14 - 7/27 

7/3 - 7/13 

6/16 - 6/30 

6/12 - 6/15 

ETL:

On my first day back from vacation, I was asked to fix daily_oltp failure on dev.   Completed.  It has been running nightly without any issue.

Misc.:

Worked with the DBA team to complete  INC0171185

Attended 1-on-1 weekly status meeting

Attended KFS upgrade and Award dimension meeting

 Next plan:

Work on recently assigned task for the award etl https://jira.oit.uci.edu/browse/KFSDW-1656

KFS upgrade related activities

Informatica admin related activities

5/19 - 6/1

ETL:

• Completed kfsdw-1646.  The staging LD_LDGR_ENTR_T table has been loading incrementally nightly on dev as expected without any issue with data.  Per Austin, we don’t need to make any changes to ods because it loads from staging.  Per Austin’s plan to deploy, this task will be migrated some time later after the first week of June.
• Completed kfsdw-1638.  Both tables LD_LDGR_ENTR_PI_T, LD_LDGR_BAL_PI_T have been loading incrementally nightly on dev as expected without any isuue with data.
• Completed validating all dependencies of the LD_LDGR_ENTR_T without any error:

Mapping: m_KBM_LD_LDGR_ENTR_T, m_LOAD_LD_L

Session: s_m_KBM_LD_LDGR_ENTR_T, s_m_LOAD_LD_L

Worklet:   wklt_LD

I checked in all the above.

• Kfsdw-1496:   This jira has been waiting for Kevin to test.

Informatica admin related activities:

Resolved INC0167165, INC0167478

Misc.:

My vacation is  6/2-9.  I’ll be back to work on 6/12^th.

Next plan:

Deploy kfsdw-1646, 1638

Merge changes of the kfsdw-1496 as soon as Kevin approves to the main folder then deploy. 

KFS upgrade related activities

Informatica admin related activities

4/5 - 5/18

ETL:

Completed KFSDW-1645 

Completed KFSDW-1404 

Completed KFSDW-1638.  Added 2 new sessions into the Daily_OLTP workflow.  This jira is now ready for deploy.

Attended weekly DSS meeting where I was told that there will be add on changes to KBM task coming soon.

Informatica admin related activities:

Worked with DBA to add write access to kfscnv7 database per  INC0163772

Misc.:

Helped and solved problem for Lam T, Jan K., Jarrod L. with their problems in Informatica per their requests.

Next plan:

Integrate KFSDW-1646 into daily_GL_Staging

Update all related mappings so they match and have all the data elements of kfsprd.ld_ldgr_entr_t in KFS

KFS upgrade related activities

Informatica admin related activities

4/21 - 5/4

ETL

• • Had meeting with Jarrod to gather requirements for the ledger and balance from kfsstage for the PI task,  https://jira.oit.uci.edu/browse/KFSDW-1638.
  • Coordinated with Austin to replace dwhreader account with ppsoper so that ledger, balance data for PI can be loaded to dwhs_ppscurr database.
  • Completed setting up a new ODBC on PC for Informatica client to connect to dwhs_ppscurr.
  • Created new mappings, sessions for this task.  Completed ETL development for loading them to sqlserver in the dwhs_ppscurr. 
  • Helped Alvin per his request to verify ledger PI data coming from his java app.  We confirmed that data is correct as expected.
  • Completed configuring a control timer (to wait for ledger completes loading in oracle kfsstg database) in Daily_OLTP to schedule to load ledger PI data nightly starting tonight.
  • Attended weekly DSS meeting where I reminded that I'm waiting for Kevin to finish testing   KFSDW-1496 - Getting issue details... STATUS  in order to migrate it to production. 
    Today I was told that he's not ready for it yet.
  • Attended weekly one-1-one status meeting.

Next plan

• ETL: Continue with balance PI for  KFSDW-1638 - Getting issue details... STATUS  
• Informatica Admin related activities

4/7 - 4/20

ETL

• The updated kbm ledger entry table was migrated to production and has been running nightly without issue.  Closed jiras kdfsdw-1617, 1634.
• Completed reconfiguring missing components on the new PC that my development environment requires as soon as desktop replaced my old pc with the new one.
• Month-end outbound jobs ran fine, emailed success emails.  I verified data which looks good as expected.
• Attended weekly DSS meeting where I mentioned to Pramod that I’m available to help with KFS ETL jiras.
• Attended weekly one-1-one status meeting
• Completed and sent a list of accomplishment for PE to Austin. 

Next plan

• KFSDW-1496 (Modify Balance ETL for all ITD balances to remove SFG 2xxxxx).
• KFS related activities, KFS upgrade.

3/24- 4/6

ETL

• • dwhm_ods_UC_ACCOUNT_T on dev

Created a new session s_m_dwhm_ods_UC_ACCOUNT_T to load uc_account_t table from oracle (kfsday2) to dwhm_ods. 

Added $s_m_dwhm_ods_UC_ACCOUNT_T.Status = Succeeded to the Daily_OLTP wk. 

The dwhm_ods_UC_ACCOUNT_T is now up to date and scheduled to run daily.

• Completed development of the new requirement for the following jiras:

https://jira.oit.uci.edu/browse/KFSDW-1634

https://jira.oit.uci.edu/browse/KFSDW-1617

I worked with Cognos developers to help clarify  new data for these new requirements as they needed. 

• Month-end Outbound jobs

 They are running, ready to receive triggers as soon as they arrive to load monthly data.  Worked with Jarrod on these.  He confirmed that that the benefit load won’t affect these jobs.

• Meetings:
   
  Attended weekly status meeting.
  Attended weekly DSS meeting.
  Attended meeting to discuss designing the KFS nightly load times for the historical benefit load.

Next plan

KFS ETL related activities

UATs:  Continue with UAT related activities

3/10 - 3/23

ETL

• KFSDW-1496 (Modify Balance ETL for all ITD balances to remove SFG 2xxxxx).  Completed coding, unit test.  The status of this JIRA is ready for test. 
• KFSDW-1625 Worked with DBA team to restore the source sms feed table from kfsprd for month-end in Feb.  Verified that there was no missing data for the month-end.   No need to reload records.  Resolved, closed this JIRA. 
• KFSDW-1628 Added email notifications to all outbound sessions both on dev, production. 
• KFSDW-1617 (Create an Actuals Column for the Labor Ledger with Negatives/Positives):  Had a meeting with Kevin for requirements for this task.  Completed coding and unit testing.  Status is now ready for test.  

UAT

After reporting to Analytix their missing mapping for KFSDW 1458,1459.  They recreated them.  During our UATS, we discovered that they provided wrong test scripts for PREQ tables instead of the REQ table.  They said that they're correcting them.

Next plan

ETL  Work on KFSDW-1496,  KFSDW-1617

UATs:  Continue with UAT related activities

2/25 - 3/9

ETL

• Completed putting in changes for https://jira.oit.uci.edu/browse/KFSDW-1604.  It's now ready for test by users.
• KFSDW-1496 - Getting issue details... STATUS   Today Pramod confirmed that the changes I put in in the mapping in my personal folder look good.  I'm going ahead to continue to migrate the changes to the real table in the balance fact table for users to test.
• Completed 4 days intensive training in Informatica ETL level 1 & 2.
• KFSDW-1626 - Getting issue details... STATUS  Because the Informatica server was restarted on (March 4) during the jobs currently ran and before the triggers arrive (March 7, 10) to run these jobs to load, the Informatica scheduler updated to April;  Therefore these jobs were scheduled to run in April, didn't run when the triggers arrived in March.  I was told that there's no other way for the Informatica scheduler to reset to current month schedule after it was restarted.  The solution:  I manually restart the jobs. Results:  all data for this month-end is available.  

UATs: 

Missing mappings in KFSDW-1458 - Getting issue details... STATUS , KFSDW-1459 - Getting issue details... STATUS .  Today's status meeting with Analytix, they said that they'll provide the mappings then we can test.

Next plan

ETL  Work on KFSDW-1628,  KFSDW-1604, KFSDW-1604, KFSDW-1617

UATs:  Continue with UAT related activities

2/10 - 2/23

ETL

KFSDW-1496 - Getting issue details... STATUS :   Per Valerie’s suggestion, I sent details, changes I made to the balance fact ETL to Pramod for his confirmation.  I’m waiting for his confirmation.

https://jira.oit.uci.edu/browse/KFSDW-625

After my investigation of the job that failed loading data to nebula dwhs_ods from gl entry table plus several ledger amounts from the ledger fact table  below:

LEDGER_BASE_BUDGET_AMT

LEDGER_CURRENT_BUDGET_AMT

LEDGER_MONTH_BUDGET_AMT

LEDGER_BUDGET_REVERSION_AMT

LEDGER_ENCUMBRANCE_AMT

LEDGER_ACTUALS_AMT

LEDGER_CB_BEG_BAL_AMT

LEDGER_BB_BEG_BAL_AMT

LEDGER_MD_AMT

I followed up with Jarrod on this which he requested it.   He confirmed that it’s no longer needed.  I unscheduled this job, asked him (has access as a jira reporter) to close no change this jira.

UATs:

KFSDW-1456, 1457 competed.  They are ready for deploy

Next plan

ETL

https://jira.oit.uci.edu/browse/KFSDW-1604

KFSDW-1496 - Getting issue details... STATUS

KFS related upgrade activities

UATs  

Continue with UATs related activities

1/17 - 2/9

ETL

• • Per email request from the Cognos developers, I provided code, information on differences for the tables: stage_kfs.LD_LDGR_ENTR_T, dss.ods_kbm.LD_LDGR_ENTR_T, dss.ods_kbm.LD_LDGR_ENTR_V02_T.
  • https://jira.oit.uci.edu/projects/KFSDW/issues/KFSDW-1496: 
    I’ve completed coding for both scenarios. 
  • First scenario: using the sql overwrite.   During testing,  the sql overwrite shows expected results: zeros out the amounts of the columns GL_ITD_ACTUALS_PROJ_ACCTS, GL_ITD_AVAIL_BAL_PROJ_ACCTS, GL_ITD_BUDGET_PROJ_ACCTS, GL_FUND_BAL_ITD.  Other amounts in the balance fact table remain the same as expected. 

• • Second scenario that Austin prefers:  using the informatica mapping.  during testing, the informatica mapping shows incorrect results.  It doesn’t zero out the amounts of the columns GL_ITD_ACTUALS_PROJ_ACCTS, GL_ITD_AVAIL_BAL_PROJ_ACCTS, GL_ITD_BUDGET_PROJ_ACCTS, GL_FUND_BAL_ITD. 

I was able to solve the issue by changing the columns in the joiner in the mapping, m_J_Fact_KF_F_GL_BALANCE5

From: chart_cd, sub_account_costshr_account_nbr, sub_account_costshr_subaccount_nbr 

To:    chart_cd, account_nbr, sub_account_nbr

It works now: zeros out amounts for the columns (GL_ITD_AVAIL_BAL_PROJ_ACCTS, GL_ITD_ACTUALS_PROJ_ACCTS, GL_ITD_BUDGET_PROJ_ACCTS, GL_FUND_BAL_ITD).  The other amounts remain the same as expected.  For verifying amounts purpose, I created queries to sum all the amount columns for each table (dss_kfs.dss_kfs.KFS_F_GL_BALANCE_JIRA1496, dss_kfs.dss_kfs.KFS_F_GL_BALANCE), then compared them.   The columns affected by the changes show expected values.  They show the amounts either equal or less as expected. 

Per Valerie’s suggestion, I send details to Pramod to confirm the changes.  He said that he’s going to look at it.  

• • Month-end:   This is the first month-end that these mappings ran from the informatica production server.  Most ran successfully.  Couple failed.  Details below:
  •   SBS aging:  Both sessions s_m_Load_agingreportdet, session s_m_Load_agingreportcon failed loading because the path was incorrect in the source.  After correcting them  

From: $PMSourceFileDir\dat\sbs  To: $PMSourceFileDir\UCI_DW_KFS_ODS\sbs, they reran successfully.

• •  Outbound CFAST:  failed due to incorrect DB connection:   I fixed the issue by correcting it from dss_kfs to dss in the $PMRootDir\BWParam\OnDemand_Outbound.txt file.

UATs: Reported problem to Analytix.  Waiting for them to fix jiras below:

https://jira.oit.uci.edu/browse/KFSDW-1456

https://jira.oit.uci.edu/browse/KFSDW-1457 

Next plan

Continue with https://jira.oit.uci.edu/projects/KFSDW/issues/KFSDW-1496

Work on KFS upgrade related activities

Continue with UATs

1/13 - 1/26

ETL:

Completed user's request from incident: INC0139850 Create indexes for the tables under dwhs_ods for NEBULA and NOVA.

Completed user's request from INC0141372 Processed month-end, quarterly outbound task. 

The outbound, SBS aging tasks were migrating to the production Informatica server for the following workflows: 

Workflow: Monthly_ODS_Outbound_SmsGlFeed 

Workflow:  Monthly_ODS_Outbound_Cfsbal. 

Workflow:  Monthly_ODS_Outbound_Cafp.   

Workflow:  FiveTimesPerYear_ODS_Outbound_Stf100.  

Workflow:  FiveTimesPerYear_ODS_Outbound_Stf200.  

Workflow:  SixTimesPerYear_ODS_Outbound_Bud100.  

Workflow:  Yearly_ODS_Outbound_Cfast

Workflow:  Yearly_ODS_Outbound_Efa100

Workflow:  Yearly_ODS_Outbound_Efa200

The outbound paths for the trigger on the production server is as the same as on dev server (D:\OIT\infa_shared\TriggerFiles)

Updated appropriate paths for the trigger files on the server for the the workflow wf_load_SBS_aging_data

D:\OIT\infa_shared\SrcFiles\UCI_DW_KFS_ODS\sbs\agingreportcon.dat

D:\OIT\infa_shared\SrcFiles\UCI_DW_KFS_ODS\sbs\ agingreportdet.dat

Updated schedule on dev to run on demand for the all the workflows that were migrated to production.

Done verifying Jarrod’s app sending triggers on the production informatica server.

Closed the following jiras:  

  KFSDW-1550 - Getting issue details... STATUS

          KFSDW-1549 - Getting issue details... STATUS

          KFSDW-1579 - Getting issue details... STATUS

UAT:

Test failed, reported to Analytix to fix the following jiras:

https://jira.oit.uci.edu/browse/KFSDW-1417 (KFS_F_CRDT_MEMO_ITM)

https://jira.oit.uci.edu/browse/KFSDW-1418 (KFS_F_CRDT_MEMO_ITM_ACCTNG)

Next plan

   ETL

Work on KFS related activities.

Continue KFSDW-1496 - Getting issue details... STATUS   

  UATs

     Continue with this activity.

1/3 - 1/12

ETL

     Completed, verified SBS, Outbound month-end.

     Completed https://jira.oit.uci.edu/projects/KFSDW/issues/KFSDW-1496  (Modify Balance ETL for all ITD balances to remove SFG 2xxxxx).  Unit test is in progress.

UATs:

     Following jiras are completed, ready for deploy:

          KFSDW-1443 - Getting issue details... STATUS  (KFS_F_TEM_ACCTNG)

          KFSDW-1436 - Getting issue details... STATUS  (KFS_F_DSBRMNT_VCHR)

Training

   My UCI extension Data Modeling course has began this week. 

Next plan

   ETL

     Complete testing to be ready for deploy https://jira.oit.uci.edu/projects/KFSDW/issues/KFSDW-1496 (Modify Balance ETL for all ITD balances to remove SFG 2xxxxx). 

     As soon as migration completes on the production informatica server and to prepare for the next month-end, I need to work with Jarrod to verify, test the following jiras to ensure that his aps send triggers to the appropriate locations:

          KFSDW-1550 - Getting issue details... STATUS

          KFSDW-1549 - Getting issue details... STATUS

          KFSDW-1579 - Getting issue details... STATUS

  UATs

     Continue with this activities.