HR Update

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','000000
  0002')

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
(No Transcript)
15
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

16
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_Dat
  e
• Created views for date stamped tables for the max
  dated records
• Changed front-end Terminating Price Agreement
  report to exclude deleted records

17
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

18
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