Valerie Jones Bi-weekly Status 2015-04-03
- vjones (Unlicensed)
Issues/Items worked on:
New UC accounts and UC funds are being created without being linked to the old FS hierarchy as proposed last year (/wiki/spaces/UCIKFS/pages/23298549). This is causing some issues for downstream systems including Data Warehouse Payroll Expense queries. All known issues have been documented on the wiki: /wiki/spaces/UCIKFS/pages/23298060. I-Rong asked Keiko to help figure out a solution, so I met with Keiko to discuss the problem and she proposed using the UCOP account table which has the new 5 level hierarchy maintained by the Budget office.
Met with Cassandra Jue Low at ICS to discuss her issues downloading payroll expense data using BOPayrollExpense2 for fiscal year 1314. The queries are timing out on her and she says she was able to query this data before. This query uses a subquery that is resource intensive and it is not ideal to run an entire fiscal years' worth of data in one query. I have worked with Grace to try to index and optimize tables without success. I will work to submit a ticket to MySQL support to see if they can help find a solution. In the meantime I have asked Cassandra to use BOPayrollExpense instead to see if that gives her the results that she is expecting. She will try this query again later and get back to me.
Spoke with Guna regarding Informatica Usage at the UCIMC. He would like to use our development environment for now to develop mappings to load their own tables for a project he is working on. They would like to evaluate Informatica to see if this is a tool they want to use in the future for UCPath related conversion work. I will give him our training credentials so that he can take the onDemand courses.
Met with OIR to discuss mapping of home departments to financial hierarchy
Met with IdM team to discuss plans for building an IdM data warehouse
Started ongoing meetings with the KFS team and Financial Services to discuss how to resolve Linda's issues with A/R reconciliations. DWH team will need to modify ETL that loads KFS ledger transactions to fix/augment the receivables transactions so that Financial Services is able to reconcile. DWH team will also need to re-work the existing script to migrate the open FS receivables into the KFS data warehouse. These meetings will occur weekly until the issues are resolved. This is an urgent issue that needs to be resolved in time for FYEC. I am currently working on the specs to fix all of the ledger transactions per instructions from the SBS team. They want this work done within the next two weeks.
Analytix is currently on-target for the migration by the end of May. Weekly meetings are going well.
Bug found with Ledger Balances - I investigated the issue and found that in the ETL jobs, float was used instead of decimal which caused rounding issues for some of the columns. Job has been tested and is fix is now in production.
Columns added to the object dimension in Erwin, test tables created on lunar. Tammy is assigned ETL changes to populate these columns.
Updating Wiki to clean up Data Warehouse pages, document after-hours job support information
Reviewed SLA agreements shared by UCB, continued working on SLA planning for DWH
Responded to questions from Purchasing regarding their project proposal to normalize vendor names
Met with Keiko, Warren, Eufemia and Tammy to figure out the logic needed for implementing the GEC <120 days report. This report requires flagging transactions that fall within certain audit criteria, and has some complicated logic. Tammy will work on developing SQL to build a table and then build the package before handing off to the report development team.
Help troubleshoot problems with Talend not being able to see tables in non-owned schemas in Oracle. This is part of the work needed to move to using dwhreader to connect to Oracle instead of using the dbo accounts. Planned turnover date for all changes is by 4/11/2015.Anh is the primary developer assigned to this task.
Talend's connection to LDAP is broken. I am unable to add any new users to TAC, which I need to do before I can hand off operational support to the Operations team. Brian tried to help troubleshoot the issue but it looks like I will have to wait until Jason gets back for help on this issue.
Troubleshooting issues with Payroll Certification caused by UC Account/Fund and hierarchy linkage problems
To Do:
Present information about the BICG to the UCI UCPath project group at next week's UCPath project meeting
Schedule a meeting with Marc, Katherine, OIR (Ryan and Bernadette) and others to discuss options for the UC Account/Fund and FS hierarchy linkage issues.
Schedule a meeting with Scott C. to go over Talend nighttime support procedures for operations
Write technical specs for conversion of historical open receivables (from FS) to the KFS data warehouse based on requirements given by the SBS team. This task will be assigned to Anh.
Work with Analytix to determine pricing for ETL work needed