1 / 21

Advanced Reporting

Advanced Reporting. MOF Information Management Group 2007 Module 6 – RESULTS Generating Reports. Advanced Training Agenda. What we will cover: Overview presentation RESULTS data model, relationship Database concepts, query building Show and tell

ndrew
Download Presentation

Advanced Reporting

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. Advanced Reporting MOF Information Management Group 2007 Module 6 – RESULTS Generating Reports

  2. Advanced Training Agenda What we will cover: Overview presentation RESULTS data model, relationship Database concepts, query building Show and tell RESULTS – Running CSV report Setting up MSAccess database Query Screen Orientation Setting up Queries Demonstrating applied MSAccess (choice of NRFL or silv opportunities) Questions and Answers

  3. Learning Objectives • Orientation of RESULTS data structure and relationships • Overview of general relational database concepts • Overview of running CSV Report and get data into MSAccess • Process of setting up tables and building queries using MS Access • Demonstrate the use of RESULTS data to address operational questions

  4. Cut Block Cut Block Cut Block RESULTS Tables in LRDW & Simplified to CRS Reports Prov Forest Use Timber Mark Cutblock Open Admin Opening FTA RESULTS

  5. Forest Cover Archived Land Status RESULTS Tables in LRDW & Simplified to CRS Reports Opening RDD001 Standard Unit stocking/ecology RDD002 RDD003 RDD004 Activity RDD006 Forest Cover RDD008 RDD009 RDD010 Disturbance RDD005 Planting RDD007 Milestone RDD011

  6. Standards Unit Layer Species RDD004 Forest Cover Layer Species RDD010 Forest Cover Layer Species RDD010 RESULTS Tables LRDW & Simplified to CRS Reports Standards Unit RDD002 Forest Cover RDD008 Forest Cover Layer “I” or “1”,”2”,”3”,”4” RDD009 Forest Cover Layer “S” or “1S”,”2S”,”3S”,”4S” RDD009 Standards Unit Layer “I” or “1”,”2”,”3”,”4” RDD003

  7. Types of Relationships • One to One • Cutblock can have one opening • One ACTIVITY_TREATMENT_UNIT record with (DN, HV) will have associated DISTURBANCE_RESULTS record. • One to Many • Opening can have one or more cutblock (multi-tenure) • Opening can have one or more standards unit • Opening can have one or more harvesting and/or silv treatment • Opening can have one or more forest cover polygon

  8. Types of Relationships • Mandatory versus Optional • Business rules determine which relationships are mandatory and which are optional. • Cutblock may have an Opening (eg. SP Exempt) • Cutblock must have Tenure (forest file id) • Opening should have forest cover if harvested • FPC/FRPA created Opening must have standards

  9. Types of Relationships • All these relationships impact on your query results. • Data may not exist where expected due to these relationships. • Or you can get strange results due to relating different sets of information that ends up having a many-to-many relationship (eg. no inferential rules between activity treatment units and forest cover polygons). • Always check your query output against web RESULTS to ensure that your data is correct: - created the correct links - correct assumptions

  10. Keys Key – allows us to tie together different sets of data in a relational database (eg. MS Access) Primary key – enables us to uniquely identify a record in a table (eg. SIN can only exist for one person) Foreign key – primary key of another table that resides in a resident table to enable the sharing of information.

  11. Keys RESULTS keys are typically ending with “ID” suffixes FTA often uses multiple keys Table Primary Key(s) Foreign Key Opening Opening_ID none Cut Block Open Admin Cut_Block_Open_Admin_ID Opening_ID (Interface that allows Forest_File_ID RESULTS and FTA to talk) Cut_Block_ID Timber_mark Cutting_Permit_Id Cut Block Cut_Block_Id Forest_File_ID Timber_mark Cutting_Permit_Id Timber Mark Timber_mark Forest_File_Id Cutting_Permit_Id

  12. Keys RESULTS keys are typically ending with “ID” suffixes Table Primary Key(s) Foreign Key(s) Opening Opening_ID none Stocking Ecology Stocking_Ecology_ID Stocking_Standard_Unit_ID Opening_ID Stocking Standards Unit Stocking_Standard_Unit_Id Opening_ID Standards_Regime_ID Stocking Layer Stocking_Layer_ID Stocking_Standard_Unit_ID Opening_ID Stocking Layer Species Stocking_Layer_ID Stocking_Layer_ID Silv_Species_Code

  13. Keys RESULTS keys are typically ending with “ID” suffixes Table Primary Key(s) Foreign Key Forest Cover Forest_Cover_ID Opening_ID Stocking Standard ID Forest Cover Layer Forest_Cover_Layer_ID Forest_Cover_ID Forest Cover Layer Species Forest_Cover_ID Forest_Cover_Layer_ID Species_Order

  14. Joins Joins allows us to relates different sets of information together. (eg. forest cover (polygon) with associated layer information) To create joins – drag from one field (key) of one table to similar field (key) of the other table. Joining between primary keys with foreign keys. Inner join – Used to show common records that exists (MS Access – straight line with no arrows) Outer join – Used for locating data gaps or exceptions (MS Access – line with arrow)

  15. Tables & Joins RESULTS

  16. Tables & Joins - FTA

  17. Demo • Download Data sources • CRS Data Download/CSV reports & LRDW (idir only) • Set up in MSAccess • Building Queries

  18. Steps to building queries • Breakdown the question to identify which subject areas/business area you want to report from – translate back to RDD report theme. • Use only the RDD theme/tables you need; this reduces complicated outputs due to potential many to many relationships. • Establish all table links as required. • Specify join type required. • Drag fields into grid. Make sure you are aware of idiosyncrasies related to data structure/model/table etc. • Specify criteria, parameters, sorts, etc.

  19. Resources RESULTS Main Website – key documents http://www.for.gov.bc.ca/his/results/ RESULTS Self-Study Workbook http://www.for.gov.bc.ca/his/results/ReportWorkbook.pdf Integrated Data Dictionary Diagrams – ERD column http://www.for.gov.bc.ca/his/datadmin/models/models.htm#models Table & columns definitions (idir only) http://www.for.gov.bc.ca/pScripts/isb/idd/iddmain.asp SIA (old Silviculture ISIS MS Access document) – useful intro to MSAccess, but data model is not current http://www.for.gov.bc.ca/his/isis/sia/training.htm

  20. Where to find HELP? • Business Application Support • Via phone: 250-387-8888; • Via e-mail to : • In gov’t: APPHELP • Outside gov’t: FORHISP.APPHELP@gems5.gov.bc.ca

  21. More Training Sessions RESULTS OVERVIEW OBLIGATION REPORTING (SUBMISSIONS) STOCKING STANDARDS GENERATING REPORTS - BASIC RESULTS and MAPVIEW AMENDMENTS, UPDATES, and APPROVED VARIATIONS PLANNING PROJECTS GENERATING REPORTS- Advanced Check: http://www.learningestore.com/bcmof

More Related