slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Data Warehouse Users’ Group Meeting January 15, 2004 PowerPoint Presentation
Download Presentation
Data Warehouse Users’ Group Meeting January 15, 2004

Loading in 2 Seconds...

play fullscreen
1 / 18

Data Warehouse Users’ Group Meeting January 15, 2004 - PowerPoint PPT Presentation


  • 77 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Data Warehouse Users’ Group Meeting January 15, 2004' - nassor


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
joining sum employee with bkg remarks
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.

joining bkg esml with bkg remarks
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.

general tips to improve query efficiency
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')

general tips to improve query efficiency1
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.
general tips to improve query efficiency2
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.
general tips to improve query efficiency3
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.
general tips to improve query efficiency4
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.
general tips to improve query efficiency5
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.
general tips to improve query efficiency6
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
finding po history in the data warehouse
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.
sum commodity table
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
completed work orders
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
completed work orders1
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
upcoming changes
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