1 / 18

Data Warehouse Users’ Group Meeting January 15, 2004

Data Warehouse Users’ Group Meeting January 15, 2004. HR Update & Discussion. Joining SUM_EMPLOYEE With BKG_REMARKS. SUM_EMPLOYEE.GHRS_IN_EMPL_ID_NO = BKG_REMARKS.GHRS_IN_EMPL_ID_NO AND SUM_EMPLOYEE.APPT_ID = BKG_REMARKS.APPT_ID

nassor
Download Presentation

Data Warehouse Users’ Group Meeting January 15, 2004

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. Data Warehouse Users’ Group Meeting January 15, 2004

  2. HR Update & Discussion

  3. Joining SUM_EMPLOYEE With BKG_REMARKS SUM_EMPLOYEE.GHRS_IN_EMPL_ID_NO = BKG_REMARKS.GHRS_IN_EMPL_ID_NO AND SUM_EMPLOYEE.APPT_ID = BKG_REMARKS.APPT_ID AND SUM_EMPLOYEE.LAST_UPDT_DATE = BKG_REMARKS.UPDATE_DATE AND SUM_EMPLOYEE.TIME_STAMP_9 = BKG_REMARKS.UPDATE_TIME_9.

  4. Joining BKG_ESML With BKG_REMARKS BKG_ESML.GHRS_IN_EMPL_ID_NO = BKG_REMARKS.GHRS_IN_EMPL_ID_NO AND BKG_ESML.APPT_ID = BKG_REMARKS.APPT_ID AND BKG_ESML.UPDATE_DATE = BKG_REMARKS.UPDATE_DATE AND BKG_ESML_UPDATE_TIME = BKG_REMARKS.UPDATE_TIME.

  5. General Tips to Improve Query Efficiency: • Avoid using "OR". Using a value list is much more efficient. In explanation, avoid queries written in the following manner: WHERE GHRS_IN_EMPL_ID_NO = '0000000001' OR GHRS_IN_EMPL_ID_NO = '0000000002' For improved performance, write queries using a value list as below: WHERE GHRS_IN_EMPL_ID_NO IN ('0000000001','0000000002')

  6. General Tips to Improve Query Efficiency: • Avoid comparisons where fields are 'not' equal. The 'not equal' comparison will cause a table space scan almost every time, no matter what else you've got going on in the query.

  7. General Tips to Improve Query Efficiency: • Avoid using "Like" comparisons. The "Like" comparison will usually cause a table space scan. It is a bad performer. It is much more efficient to compare items that are equal.

  8. General Tips to Improve Query Efficiency: • Ensure that the fields you are comparing have the same data definitions. i.e. the same type and length.

  9. General Tips to Improve Query Efficiency: • When querying the EXT tables, include the field GTN_RUN_NUM in your criteria whenever possible. These tables are all partitioned and organized by GTN_RUN_NUM.

  10. General Tips to Improve Query Efficiency: • When joining tables, review the indexes on both tables. It would be ideal to hit indexes on both tables in your joins whenever possible or at least to make use of an index on one of the tables involved. You will find a listing of indexes on each table in the HR data model viewer.

  11. General Tips to Improve Query Efficiency: • If you experience a problem with query efficiency and need help optimizing your query or identifying problems, please call the help desk. 522-1500

  12. Financial Update & Discussion

  13. Finding PO History in the Data Warehouse • DOC_PO_HDR, DOC_PO_COMM_LN and DOC_PO_LN tables are updated from the SAM II OPPH, OPCL, and OPPL tables • DOC tables are updated to reflect the current status of the PO • History for POs can be found on the ledger tables by selecting the TR_CODE, TR_NUM_AGY and TR_NUM_NUM • Accounting line information is found on the LED_ENCUMBRANCES table • Commodity line information can be found on the LED_COMMODITY table.

  14. SUM_COMMODITY Table • One amount bucket includes amounts from AMs, requisitions, POs, receivers and invoices • Transaction code not included on the table • Reports against the table are inaccurate • Options: • Complete redesign to create different buckets for the different documents types on LED_COMMODITY • Remove the table. Use LED_COMMODITY table • A standard report for expenditures by commodity code is available under the expenditure area of the front end. The report uses the LED_COMMODITY table

  15. Completed Work Orders: • Sub Org field added back to LED_EXPENSES • ACTIVITY & FUNCTION fields added back to LED_ASSET_OFFSETS • Added PO_COMM_LN_NUM field to LED_EXPENDITURES • Removed the REF_REVENUE_BUDGET table from the Data Warehouse • Added fields to REF_FAS_FBT, MFDJN146: Ref­_Trans_Code, Ref_Trans_Num_Agy, Ref_Trans_Num_Num fields and Last_Depreciation_Date • Created views for date stamped tables for the max dated records • Changed front-end Terminating Price Agreement report to exclude deleted records

  16. Completed Work Orders: • Corrected quantity field on LED_EXPENDITURES • Updated the data model available under the help section in the front-end for the REF_REVENUE_CLS table • Changed the transaction detail report on the front-end for JCs to return the JOB_FULL_COST_AMT field instead of the JOBS_ AMT field • Loaded missing JC documents to LED_JOBS and made changes to monthly job process so they would not be missed in the future • Corrected problems with LED_RECEIVABLES load that was truncating first character of the job number • Added House bill section to the monthly job to update the SUM_APPROPRIATIONS and SUM_EXPENSE_BUDGET tables if HB section is changed on an AP document

  17. Upcoming changes: • Removing quantity field from LED tables where not populated • Adding vendor address fields to the DOC_CHECK_HDR table • Changing criteria on the Price Agreement by User Agency report to select only price agreements with an end date after the current date • Correcting the quantity and encumbrance amount fields on the front-end procurement report Total Orders by Commodity Code by Responsible Agency Report

More Related