Transcript Advanced Reporting - Ministry of Forests, Lands and Natural

RESULTS Training
Generating Reports
Agenda
Advanced
Reporting
MOF Information Management Group
Module 6 – RESULTS Generating Reports
A1
2007
RESULTS Training
Generating Reports
AdvancedAgenda
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
A2
RESULTS Training
Generating Reports
Agenda
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
A3
RESULTS Training
Generating Reports
RESULTS Tables in LRDW
Agenda
& Simplified to CRS Reports
Prov
Forest Use
Timber Mark
Cut Block
Cut Block
Cut Block
FTA
A4
Cutblock
Open Admin
RESULTS
Opening
RESULTS Training
Generating Reports
RESULTS Tables in LRDW
Agenda
& Simplified to CRS Reports
Opening
RDD001
Activity
RDD006
Disturbance
RDD005
A5
Planting
RDD007
Standard Unit
stocking/ecology
RDD002
RDD003
RDD004
Forest
Cover
RDD008
RDD009
RDD010
Milestone
RDD011
Forest
Cover
Archived
Land
Status
RESULTS Training
Generating Reports
Standards
Unit
RDD002
Standards
Unit Layer
“I” or
“1”,”2”,”3”,”4”
RDD003
Standards Unit
Layer Species
RDD004
A6
RESULTS Tables LRDW
Agenda
& Simplified to CRS Reports
Forest Cover
RDD008
Forest Cover
Layer
“I” or
“1”,”2”,”3”,”4”
RDD009
Forest Cover
Layer
Species
RDD010
Forest Cover
Layer
“S” or
“1S”,”2S”,”3S”,”4S”
RDD009
Forest Cover
Layer
Species
RDD010
RESULTS Training
Generating Reports
Agenda
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
A7
RESULTS Training
Generating Reports
Agenda
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
A8
RESULTS Training
Generating Reports
Agenda
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
A9
RESULTS Training
Generating Reports
Keys
Agenda
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.
A10
RESULTS Training
Generating Reports
Keys
Agenda
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
(Interface that allows
RESULTS and FTA to talk)
Cut_Block_Open_Admin_ID
Opening_ID
Forest_File_ID
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
A11
RESULTS Training
Generating Reports
Keys
Agenda
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
A12
RESULTS Training
Generating Reports
Keys
Agenda
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
A13
RESULTS Training
Generating Reports
Joins
Agenda
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)
A14
RESULTS Training
Generating Reports
A15
Tables & JoinsAgenda
RESULTS
RESULTS Training
Generating Reports
A16
Tables & JoinsAgenda
- FTA
RESULTS Training
Generating Reports
Demo
Agenda
Download Data sources
-
CRS Data Download/CSV reports & LRDW (idir only)
Set up in MSAccess
Building Queries
A17
RESULTS Training
Generating Reports
Agenda
Steps to building
queries
1.
Breakdown the question to identify which subject
areas/business area you want to report from – translate back to
RDD report theme.
2.
Use only the RDD theme/tables you need; this reduces
complicated outputs due to potential many to many relationships.
3.
Establish all table links as required.
4.
Specify join type required.
5.
Drag fields into grid. Make sure you are aware of idiosyncrasies
related to data structure/model/table etc.
6.
Specify criteria, parameters, sorts, etc.
A18
RESULTS Training
Generating Reports
ResourcesAgenda
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
A19
RESULTS Training
Generating Reports
Where to find
HELP?
Agenda
Business Application Support
Via phone: 250-387-8888;
Via e-mail to :
In gov’t: APPHELP
Outside gov’t:
[email protected]
A20
RESULTS Training
Generating Reports
More TrainingAgenda
Sessions
RESULTS OVERVIEW
OBLIGATION
REPORTING
(SUBMISSIONS)
AMENDMENTS,
UPDATES, and
APPROVED
VARIATIONS
STOCKING
STANDARDS
PLANNING
GENERATING
REPORTS BASIC
PROJECTS
RESULTS
and
MAPVIEW
GENERATING
REPORTSAdvanced
Check: http://www.learningestore.com/bcmof
A21