Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

RESULTS

VIEWS: 18 PAGES: 21

									      RESULTS Training
     Generating Reports
                                         Agenda




                                       Advanced
                                       Reporting

      MOF Information Management Group             2007
      Module 6 – RESULTS Generating Reports

A1
       RESULTS Training
      Generating Reports
                            AdvancedAgenda Agenda
                                     Training

     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
                           RESULTS Tables in LRDW
      Generating Reports
                                        Agenda Reports
                               & Simplified to CRS

       Prov
     Forest Use




     Timber Mark            Cut Block       Cutblock
                             Cut Block                     Opening
                                           Open Admin
                               Cut Block




                    FTA                          RESULTS




A4
      RESULTS Training
                             RESULTS Tables in LRDW
     Generating Reports
                                          Agenda Reports
                                 & Simplified to CRS

                                        Opening
                                        RDD001




                                       Standard Unit      Forest
               Activity              stocking/ecology     Cover
               RDD006                     RDD002         RDD008
                                                                   Land
                                          RDD003         RDD009    Status
                                          RDD004         RDD010



                                                         Forest
 Disturbance              Planting     Milestone
                                                         Cover
   RDD005                 RDD007       RDD011
                                                        Archived




A5
      RESULTS Training
                            RESULTS Tables LRDW
     Generating Reports
                                         Agenda Reports
                                & Simplified to CRS

           Standards
                                                  Forest Cover
              Unit
                                                    RDD008
            RDD002


                                        Forest Cover           Forest Cover
           Standards                        Layer                  Layer
           Unit Layer                       “I” or                 “S” or
               “I” or                  “1”,”2”,”3”,”4”      “1S”,”2S”,”3S”,”4S”
          “1”,”2”,”3”,”4”               RDD009                    RDD009
             RDD003



                                       Forest Cover              Forest Cover
        Standards Unit                    Layer                     Layer
        Layer Species                    Species                   Species
           RDD004                        RDD010                    RDD010




A6
      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          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


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
                                         Agenda
                           Tables & Joins RESULTS




A15
       RESULTS Training
      Generating Reports
                                         Agenda
                           Tables & Joins - FTA




A16
       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
                                   Agenda
                           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

A19
       RESULTS Training
      Generating Reports
                                       Agenda
                           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




A20
       RESULTS Training
      Generating Reports
                           More TrainingAgenda
                                         Sessions

                            RESULTS OVERVIEW


          OBLIGATION         STOCKING   GENERATING   RESULTS
          REPORTING         STANDARDS    REPORTS -     and
        (SUBMISSIONS)                      BASIC     MAPVIEW



        AMENDMENTS,
        UPDATES, and
         APPROVED
         VARIATIONS                                     GENERATING
                             PLANNING     PROJECTS
                                                         REPORTS-
                                                         Advanced




  Check: http://www.learningestore.com/bcmof
A21

								
To top