DWH Team Status Report for 02/14/2014
DWH KFS Decision Support JIRA Tasks - Status Chart
Prior Two Week Activity
Kuali Financial System
Report List
Module | Report ID | Report Title | Equivalent Legacy Reports | Number of Drill-Through Reports | Priority (1-Critical, 2-Before go-live, 3-After go-live ) | JIRA | Due Date | Spec Status | Data Certified? | Report Development Status | Report Development Assigned To | User Acceptance Completed Date | Comments |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| KREW | Workflow Action List Report | 2 | KFSDW-288 | TBD | Specs Needed | Incomplete | TBD | This report needs to be real-time. Will be live against KFS db, will use SQL statement from Simran. We will need to build a special Cognos package for just this report. | |||||
| AP | Use Tax Payment Detail Report | UCAP*MUT | 2 | KFSDW-245 | TBD | Specs Needed | Incomplete | TBD | Specs needed. | ||||
| GL | KFS-GL-001 | Trend Analysis Report | new | 3 | KFSDW-222 | TBD | On Hold | On Hold | Jason Jensen | Based off Paige's report in Excel | |||
| KBM | 1 | KFSDW-224 | 1/31/2014 | Complete | In Progress | Susie Nies | |||||||
| AP | KFS-AP-001 | Stale Dated Check Report | PAL214 | 2 | KFSDW-243 | TBD | Specs Needed | Incomplete | TBD | Specs needed. | |||
| AP | Purchase Order Status | 3 | TBD | Specs Needed | Incomplete | TBD | |||||||
| CAM | Plant Capitalization Worksheet | FS0879H | 3 | KFSDW-234 | 11/30/2014 | Complete | In Progress | Jason Jensen | Request for additional ETL balances by Jason 12-26 Specs complete & ready for development. Report is not needed until January 2015. | ||||
| GL | KFS-GL-002 | Pivot Object Code Transaction Report | FS0105/106 (Source/Reference control Ledger reports) | 1 | 12/31/2013 | Backfill Specs | Completed | Jason Jensen | |||||
| KSAMS | Organizational User Access Report | n/a | 3/1/2014 | n/a | Incomplete | KSAMS team | To be done by KSAMS team | ||||||
| COA | Organizational Hierarchy | n/a | 1 | n/a | 12/31/2013 | n/a | Completed | ||||||
| GL | KFS-GL-003 | Object Trans Code Report | FS0212 reports (STIP) | 2 | KFSDW-275 | TBD | Specs Needed | Incomplete | TBD | Waiting on feeds with trans code objects As of 2014-1-2 do not have AGC for transcodes. Need this to group by AGC. | |||
| GL | KFS-GL-004 | Object Summary Report | FS0100, FS0200 | 2 | 1 | KFSDW-274 | 1/31/2014 | Backfill Specs | Completed | Jason Jensen | |||
| GL | KFS-GL-005 | Object Summary (AS LI) | FS0200B | 2 | 1 | KFSDW-273 | 1/31/2014 | Backfill Specs | Completed | Jason Jensen | |||
| GL | KFS-GL-006 | Income Object Code Summary Report | FS0200R reports for income summaries | 1 | KFSDW-272 | 1/31/2014 | Backfill Specs | Completed | Jason Jensen | ||||
| GL | KFS-GL-007 | C&G INCOME Object Code Summary Report | FS200R RC for C&G only Acitivity | 1 | KFSDW-271 | 1/31/2014 | Backfill Specs | Complete | Jason Jensen | Need to have students format and send to QA area. | |||
| GL | KFS-GL-008 | Multi-year Account Transaction Search | FS0100, DWH multi-year GL query | 3 | KFSDW-270 | 5/31/2015 | Specs Needed | Incomplete | TBD | This is based off of the KFS DS focus group's request. Specs are still needed. This is not needed until we have more than one fiscal year in KFS. | |||
| CG | Letter of Credit Report | FS0209 series | 2 | KFSDW-262 | TBD | Specs Needed | Incomplete | TBD | Last time of inquiry (October) LOC code not being populated. Is this being populated and where? ETL work needs to be done, and data model changes are needed. | ||||
| CAM | Inventorial Equipment Report | EQ920 | 3 | KFSDW-237 | 11/30/2014 | Complete | Incomplete | Jason Jensen | Report will be generated 2x/year. Specs require tying an asset to a purchase order, which is problematic because an asset can have multiple POs associated with it. All assets migrated from EQS will not have a PO associated with it, so what do we show for these? | ||||
| CAM | Inventorial Asset Disposal Report | EQS032C1 | 3 | KFSDW-238 | 11/30/2014 | Complete | Incomplete | Jason Jensen | |||||
| CAM | Fixed Asset Balance Sheet Report | FS0866C | 3 | KFSDW-240 | 11/30/2014 | Complete | In Progress | Jason Jensen | 14-1-2 Parsing ledger description field to generate sub report. This may be too labor intensive to accommodate. Jason needs a list of object codes that are needed for this report. | ||||
| GL | KFS-GL-009 | Encumbrance Report | FS0109 series | 2 | KFSDW-266 | 3/31/2014 | Backfill Specs | Complete | Jason Jensen | Needed daily | |||
| AP | EFT Approved Vendor Monthly Summary | UCAP*EFM | 2 | KFSDW-244 | 4/30/2014 | Specs Needed | Incomplete | Specs needed. | |||||
| GL | KFS-GL-010 | Designated Object Summary for Central Office | FS0218 (specific object code ranges only) | 1 | KFSDW-265 | 12/31/2013 | Backfill Specs | Complete | Jason Jensen | Will hard code in lieu of configuring and saving report. as of 1-2-14 need students to edit and post. | |||
| CAM | Depreciation Report | EQS240A | 2 | KFSDW-239 | 3/31/2014 | Complete | Incomplete | Jason Jensen | Book value needs to be populated in fact table. ETL changes needed. | ||||
| GL | KFS-GL-011 | Control Account Summary Report - Central Office | FS0150 (Fund Summary) | 4 | 1 | KFSDW-124, KFSDW-263 | 12/31/2013 | Backfill Specs | Completed | Jason Jensen | |||
| GL | KFS-GL-012 | Control Account Summary Report - Departmental | FS0150 (Fund Summary) - Showing expenses only | 4 | 1 | KFSDW-264 | 1/25/2014 | Backfill Specs | Incomplete | Jason Jensen | |||
| COA | Chart of Accounts Listings/Lookups | too many to list | 1 | KFSDW-135 | 12/31/2013 | N/A | Completed - revisions requested | Tammy Phan & Students | Focus group has requested some changes | ||||
| GL | KFS-GL-013 | Campus Report | FS0400 series (401-407) | 1 | KFSDW-261 | 1/31/2014 | Backfill Specs | In Development | Jason Jensen | Final touch-ups needed. Fund group ranges are needed. Will we use a KFS field to determine fund groupings or create our own fund group table? | |||
| KBM | 2 | KFSDW-226 | 3/31/2014 | Complete | Incomplete | Susie Nies | KBM | ||||||
| KBM | Budget Adjustment Document Report | FS0BUD Reports, FS0FJ10 | ? - need to discuss with stakeholders (Budget) | KFSDW-260 | TBD | Specs Needed | Incomplete | TBD | Report may not even be needed. There are processes in KFS that potentially eliminate the need for this report. More discussion with Budget Office needed. Allows users to pick by document type and breaks on document number. Specs are still needed. | ||||
| CAM | Asset Inventory List | EQS? | 1 | KFSDW-259 | 2/28/2014 | Specs Needed | Incomplete | TBD | Most likely needed before go-live. Specs are still needed. | ||||
| KBM | 2 | KFSDW-225 | 2/28/2014 | Complete | Incomplete | Susie Nies | |||||||
| GL | KFS-GL-014 | Account Transaction Report | FS0100 (multiple), FS0213 | 1 | KFSDW-257 | 12/31/2013 | Backfill Specs | Completed | Jason Jensen | ||||
| AP | 1042S Reporting (list of NRA tax withholdings paid from AP) | 2 | KFSDW-24 | TBD | Specs Needed | Incomplete | TBD | list of NRA tax withholdings paid from AP. Specs needed. | |||||
| CAM | Construction in Progress` | FS0865A - Capitalizable Asset/Building Report | 1 | KFSDW-256 | 12/31/2013 | Backfill Specs | Complete | Jason Jensen | Need to Submit to QA area. | ||||
| GL | KFS-GL-015 | Credit Balances C&G Only | FSO219 Series reports. For accounts and or object codes with credit balances. All Accounts C&G ONLY versions | 1 | KFSDW-255 | 12/27/2013 | Backfill Specs | Complete | Jason Jensen | Will modify object summary report to return only those accounts and or object codes with credit balances... AS OF with C&G only variants. as of 1-2-14: Need to have students edit and format.
| |||
| GL | KFS-GL-016 | Fund Overdrafts by Agency | FS0221 - Fund Over Drafts by Agency | 1 | KFSDW-254 | 2/14/2014 | Backfill Specs | Incomplete | Jason Jensen | We do not have award/proposal numbers in data warehouse/dss_KFS. We need this to complete the report. | |||
| GL | KFS-GL-017 | Total Fund Overdraft Expenditures for C&G | FS0220 C & G ONLY | 1 | KFSDW-253 | 12/28/2013 | Backfill Specs | Complete | Jason Jensen | Will modify account summary report to grab only C&G Subfund groups and accounts that have negative variances. Need students to edit and post. | |||
| GL | KFS-GL-018 | Overhead Transaction Report | FS0213 | 1 | KFSDW-252 | 1/31/2014 | Backfill Specs | Incomplete | Jason Jensen | All Transcode object codes do not have AGC data in source system. This is necessary for the report. Accounting systems needs to let OIT know when AGC is assigned to all transcode object codes in KFS. | |||
| GL | KFS-GL-019 | Loan Fund Balances & Receivables | FSO 203A | 1 | KFSDW-251 | 12/31/2013 | Backfill Specs | Complete | Jason Jensen | ||||
| AP | DW-AP-005 | Uncleared Checks by Chart Code | UCAP*FMCS | 3 | KFSDW-267 | 3/31/2015 | Specs Needed | Incomplete | TBD | not needed until FYEC 2015 | |||
| SBS | SBS Aging Report (Detail & Consolidated) | DWH BLISS Query SBSAging & SBSAgingConsolidated | KFSDW-297 | 2/28/2014 | Specs Needed | Incomplete | Tammy Phan | Still need to work with Linda to determine requirements. |
Completed
- Worked with consultant to install Talend on Solaris
- Configuration of TalenD on Solaris in new version 5.3
- Migration of Talend jobs to new version 5.3
- Cognos framework development for KSAMS (in testing)
- Review security options for Cognos reports with stakeholders
- Complete testing of TalenD install on UNIX
- Turnover of scripts to add KFS chart of accounts to people tables and Administrative LDAP
In Progress
- Kuali Budget Reporting - Consultant is developing Sub 0 report
- Develop SMS and Workflow Cognos packages
- Report spec developing for CAM and PURAP
- Ongoing meetings with Focus group
- Build data warehouse tables in production server for KFS, move Cognos reports to point to production database
- Training materials are in development (Kevin)
- Scripts to add KFS attributes to legacy data warehouse (employee, payroll, fs). Coding is complete - currently testing every night for pps daily and every pps month-end. (Grace)
- Conversion of KFS data into legacy tables for use by Payroll Certification and legacy DWH (Grace)
Data Warehouse Support
DWH is operating well as usual, Grace is acting as primary DWH production support.
Upgrade to Java 7 - testing in progress
Next two week planned activities and deliverables
- Continue making bug fixes to ETL scripts and testing
- Continue report development for highest priority reports
- Present Sub 0 report to budget office
- Meet with Financial Services to finalize requirements
Production support trends in terms of tickets either in JIRA or Footprints.
DWH Production Support 10%, New development 90% (except Grace)
Staffing issues or personnel related matters
Budget requests for any new software or hardware
Training requests
Things learned, suggestions for improvements or anything else you think might be important
UCOP has contacted us regarding Phase 2 of the Spend Analytics project. We will need some additional resources to work on this project next year.