1 / 60

WELCOME

WELCOME. Data Warehouse User’s Group Meeting. April 15 th , 2004. Agenda . New E-mail Addresses and Discussion Lists HR Update and Discussion Current and History Tables on HR Warehouse Update to HR Data Warehouse Front-end HR Data Model Viewer HR Position Control Changes

ordell
Download Presentation

WELCOME

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. WELCOME

  2. Data Warehouse User’s Group Meeting April 15th, 2004

  3. Agenda New E-mail Addresses and Discussion Lists HR Update and Discussion • Current and History Tables on HR Warehouse • Update to HR Data Warehouse Front-end • HR Data Model Viewer • HR Position Control Changes • HR Turnover Reports • New Deferred Compensation Report

  4. Agenda (cont) Financial Update and Discussion • Index Change on Purchase Order tables • New MOBIUS report for MBE and WBE Expenditures • Price Agreement by User Agency Report • Join Changed on Total Orders by Commodity Code by Responsible Agency Report • Fixed Asset tables

  5. New E-mail Addresses and Discussion Lists • State of Missouri e-mail addresses are changing to @AGENCY.mo.gov • Please remember to subscribe your new e-mail address • Please remember to unsubscribe your old e-mail address

  6. HR Update and Discussion

  7. Current vs. History Tables The following tables have been separated into current tables vs. history tables: SUM_BENEFITS (MFDTS782)/SUM_BEN_HISTORY (MFDTS880) SUM_DEDUCTIONS (MFDTS783)/SUM_DED_HISTORY (MFDTS881) SUM_LEAVE (MFDTS786)/SUM_LEAVE_HISTORY (MFDTS882) SUM_PAYROLL (MFDTS787)/ SUM_PAY_HISTORY (MFDTS883)

  8. Partitioned by GTN_RUN_NUM, HOME_AGCY_CD, and AGCY_CD • Indexes have not changed • History and Current tables have the same layout, no changes in formats • History tables contain gross to net run numbers less than or equal to ‘000172’, or pay period end dates <= 20020615 • Current tables contain gross to net run numbers ‘000173’ and higher, or pay period end dates >= 20020630

  9. HR Data Warehouse front-end updates give user a choice of using history or current tables. The following front-end areas were updated: AGENCY DETAIL BENEFITS AGENCY DETAIL DEDUCTIONS AGENCY DETAIL LEAVE AGENCY DETAIL PAYROLL AGENCY DETAIL PAYROLL ACCOUNTING SUMMARY BENEFITS SUMMARY DEDUCTIONS SUMMARY LEAVE SUMMARY PAYROLL

  10. HR Web Front-endCurrent vs. Historical Demonstration

  11. Reporting on “Current” Data • This portion of the demonstration will produce a front-end report containing state comp leave balances for employees in Agency 300, Organization 3160 as of the last regular pay cycle (GTN_RUN_NUM ‘000303’ pay period end date 3/31/04). • In this demonstration, the agency view of the SUM_LEAVE table will be used to produce the report.

  12. http://modata.intra.state.mo.us/dwprod/user/welcome.cfm

  13. Click on the HR/Payroll Button

  14. Click on the Agency Detail Leave Area

  15. Click on the Create Reports Tab.

  16. Choose Agency on Create Reports Tab

  17. The “Current” option for data retrieval is selected

  18. For an ‘as of ‘ report, choose the GTN Run Number Option

  19. Select an organization from the Home COA Tab

  20. Choose the SCOMP Leave Event Category from the Attr Tab

  21. Select the fields you would like on your report on the Group By Tab

  22. Review your selections using the ‘Review Selections’ Button

  23. Click the ‘View Results’ Button to run your report.

  24. Click the ‘View SQL’ Button to view the statement you generated. Notice that in this demonstration our report used the SUM_LEAVE_300 table to retrieve information.

  25. This is a portion of the report that was generated by this process.

  26. Reporting on “Historical” Data • This portion of the demonstration will produce a front-end report containing state comp leave balances for employees in Agency 300, Organization 3160 as of March 31st of 2002. (GTN_RUN_NUM ‘000153’ pay period end date 3/31/02). • In this demonstration, the agency view of the SUM_LEAVE_HISTORY table will be used to produce the report.

  27. Click on the Agency Detail Leave Area

  28. Click on the Create Reports Tab.

  29. Choose Agency on Create Reports Tab

  30. Choose the Historical Option for reporting.

  31. Choose the GTN Run Number option to create an ‘as of’ report.

  32. Select an organization from the Home COA Tab

  33. Choose the SCOMP Leave Event Category from the Attr Tab

  34. Select the fields you would like on your report on the Group By Tab

  35. Review your selections using the ‘Review Selections’ Button

  36. HR Data Model Viewer • The HR Data Model Viewer has been updated to reflect the history table additions • The HR Data Model Viewer will be updated with position control changes after that moves into production. A new viewer will be posted at that time

  37. Position Control Changes The following HR Data Warehouse changes are included in the position control work order which will be moving to production soon: • SNAP_EMPLOYEE (MFDTS861) • SNAP_POSITION (MFDTS860) • BKG_PSAT (MFDTS588) On all three tables, the field FUTURE_FTE was removed. The field FUTURE_INCUMBENTS was renamed FUTURE_TRIGGERS and all corresponding views were updated.

  38. NEW HR Turnover Reports Four new HR Reports dealing with employee turnover have been posted to the SAMII website. These reports will be posted quarterly, with a retention period of one year.

  39. Turnover Rate Report of All Executive Branch Classes • Turnover Rate Report by Agency and Region • Turnover Rate Report for Executive Branch Agencies • Turnover Rate Report for Non-Executive Branch Agencies

  40. New Deferred Compensation Report • New MOBIUS report will select employees who are receiving the employer match for deferred comp, but whose employee contribution amount deduction is less than $12.50 • Report will show employee’s who are eligible for employer match, but are not receiving it • Sort by agency/pay location/employee • Retention period of 2 regular pay cycles on MOBIUS

  41. QUESTIONS?

  42. Financial Update and Discussion

  43. Index Change on Purchase Order Tables Users were experiencing problems when joining data that spanned fiscal years, so FISC_YEAR was moved to the end of the index promoting the use of other indexes first. DOC_PO_HDR DOC_PO_LN DOC_PO_COMM_LN

  44. New MOBIUS Report for MBE/WBE Expenditures • Two new MOBIUS reports • MBE Utilization Report (ODW407R1) • WBE Utilization Report (ODW417R1) • Reports will contain expenditures to certified MBE/WBE vendors for non-excludable object codes • Reports will be sorted by object code, vendor, and agency.

  45. Price Agreement by User Agency Report • Selection Criteria was changed on the Price Agreement by User Agency report on the Front-end Data Warehouse Procurement Reports. • Selection added for requesting all price agreements or only price agreements with an end date after the current date.

  46. Total Orders by Commodity Code by Responsible Agency Report • Front-end Data Warehouse Procurement Report was doubling amounts in some cases. The join has been changed which should eliminate this problem.

  47. Fixed Asset Tables Four main tables on the warehouse will assist you in accounting for your fixed assets: LED_ASSETS LED_EXPENDITURES FASFBT_JOIN table (ref_fixed_assets and ref_fa_betterment joined) REF_FHIS

  48. LED_ASSETS • Contains data from balance sheet account >1700 and <1731 for conversion • Could contain data from the General Fixed Asset Account Group or transactions before GASB 34 • Contains some JV data • Contains data for fixed asset disposal documents

  49. LED_EXPENDITURES • Contains data from expense/expenditures for balance sheet accounts >1700 and <1731 • Could contain some JV data

  50. Balance sheet to FA Type Crosswalk Balance SheetFA TypeFA Description • 1701 L Land • 1703 I Improvements (nonbldg) • 1706 B Buildings • 1709 E Equipment • 1712 V Vehicles • 1715 T Tools • 1720 C Construction in Progress • 1721 R Right of Way In Progress • 1722 G Infrastructure In Progress • 1725 F Infrastructure • 1726 A Art/Historical Treasures • 1730 M Non-CAFR

More Related