ICIS Mini-Workshop 2009 Nunhems

From ICISWiki

Jump to: navigation, search

Contents

Updates from Nunhems

  • Application of the Virtual Private Database (VPD)
    • Every ICIS table has 4 additional fields: INSID (very important; filters data based on user roles), INSDATE, UPDID, UPDDATE
    • ICIS_SEC schema: ICIS_SESSION table stores info about every connection made to the database
    • ICISC schema: only two tables have content -- INSTLN and USERS tables
    • ICISL schema: all data stored here
    • Policy types: 1) Crop policies (GERMPLSM), 2) Team policies (LISTNMS), 3) No policies
  • ICIS Pre-launcher (created by Sebastien Frade)
    • Dynamically creates an ICIS INI file based on the user role. One person may have several roles. One role is one ICIS user.
    • ICIS_PRL schema: stores INI_FILE-icis user role mappings, list of INI keys, list of INI sections, list of default INI values
  • Warehouse tables
    • NUN_GERMPLSM_CHK: similar to wh_children_der tables (used by new ICIS webapp)
    • NUN_SEEDINVENTORY: warehouse for inventory information. Viewable through Setgen's "Fill with customized data" functionality, for example.
  • Data validation
    • Oracle procedures are in place
    • Procedures run every night
    • If errors are found, email is sent to Casper and Rob the next day.
    • Easier and faster to catch and correct errors
  • User training
    • Nunhems creates its own manual for its users adapted from ICISwiki articles
  • Testing
    • Requires more than one person; must have several testers who have different approaches in doing the same thing. (eg. taking different routes to get to one place. Must try all "routes")

Agenda Planning

June 15-26

  • Bug reports/fixes for Setgen, GMSSearch, Workbook, DLL (Candy/Casper/Warren)
  • June 16: Sebastien & Aaron will be joining
    • Data warehouse schema
    • Data validation
    • Data security
  • Data warehousing / data validation scripts (Ching/Rob)
  • ICIS INI keys: Compare notes (Ching/Warren/Candy/Casper)
  • June 25: Demo of ICIS Lite, 9-10:30am GMT+2 (via WebEx)

Data Validation

  • Capture errors encoded through ICIS applications AND through database migrations
  • Views were created for each ICIS table. Each view has multiple checks in it. (***Grouping of error messages is according to table***). 
..
..
SELECT ‘tablename’,
(
CASE
..
..
WHEN condition1
 THEN 'errorcode1'
WHEN condition2
 THEN 'errorcode2'

..
) err
From tablename
..
..
  • Problems encountered: Night procedure..INSERT INTO result table takes 7-8 hours to run! Changed the procedure: put view results into cursors..now only takes one hour. 
ICISL.PRO_MAIL_CHECK()
  • If error related to the table is found, processing exits the CASE statement, outputs one error message. The checks that follow are not performed.
  • How about splitting checks into two? Check GMS one night, check DMS another night?
  • Views check for two things:
1) Data Integrity: enforces foreign key references
2) Data validation: logic between data entities
  • Perhaps create a general function for checking naming conventions (customizable; client/crop/team-specific).
  • Seb: Is it possible to define a default set of checks for a certain user belonging to a team/crop research group? To be implemented via INI settings.
  • Better to automate data validation through a scheduled task.
  • Add frequency information about errors (e.g. GIDs that have most errors(?))
  • Data Validaiton Tool: Add checking of CHANGES table against changes actually made on ICIS tables. Very important!
  • Added functionality: Track/Undo changes to the database while updating CHANGES table-- AdminTool?
  • Oracle: Nunhems is using 10.2 Oracle 11g has “flashbacks” that can take snapshots of data; Oracle 11g is still for testing
  • Triggers? Performance will be affected. If you make changes using the ICIS programs, changes are logged.
  • Must track changes!!!!!!!!
  • Have links to ICIS applications (SetGen, Workbook) for easier error correction. Correct errors using applications only. (Instead of admin going in manually via the database).
  • Have some automatic corrections, some manual corrections. Tool should propose how to solve a data error.
  • Consider two different levels of users: Admin and Breeders (but with focus on Admins – more urgent need.)

Data Warehousing

  • Nunhems has a few warehouse tables, views created over them.
  • Stored procedures for updating warehouses called by Oracle jobs
  • Walk through SQLs in procedures to optimize performance wherever possible.
  • For Seed Inventory warehouse table: procedure runs every four hours. User has to wait 4 hours for changes to reflect on database warehouse table.
  • Wishlist: warehouses to make querying easier. One flat table?
  • Might be easier to query one table only. One table per crop. Database warehouse on crop level.
  • Intuitiveness of DMS Retriever is desirable for a tool for retrieving DMS data.
  • WH_DATA table: Separation of character data and numeric data? (one column for numeric, one column for character). Faster to query if separated because conversion not required.
  •  ??????? What changes might be interesting for nunhems? (in terms of warehousing) ???????
    • Combine wh_columns and wh_data tables (use name wh_dataBN). Dont use wh_datasets table (since Nunhems/Bayer only has one dataset per study. Refer to STUDY table instead).
    • Modifications applicable to Nunhems/Bayer: add INSID to every table. WH_DATA -- Keep dataval (text) and dataval (numeric) in different table columns (dataval_N, dataval_C).
    • Querying across studies: Some users may want to filter on TRAIT name, no regard for SCALE and METHOD.
    • Exclude: seed inventory, deleted studies, variates that have no data (i.e. empty cell in Workbook worksheet) in DATA_C, DATA_N.
    • Use of triggers when study data is modified. Update warehouse in real time?
    • Materialized views in Mysql, Postgresql? Need research on this.
    • For updating warehouse table: delete deleted studies, delete or update CHANGED dataval, add new records.

Data Security

  • Two levels of security
    • Database level
    • Application level
  • Data Model - Optimization: denormalized tables for faster querying
  • Issue with user access algo in DLL: user in USERS table with read-only privileges can actually still write on the database. Possibly grey out form components in ICIS apps for users with read-only privileges?

Feature Requests: User Access

  • Users belong to teams
  • A user can see LISTS + teamdata, teamdata + PUBLIC cropdata (set to "final")
  • There are several breeding teams at Nunhems. Plants from different breeding teams in the same glasshouses; so there must be no duplication of ENTRYCD!
  • Need read-write (RW) access up to team data level, but only read (R) access to crop data level.
  • SetGen needs to distinguish between a user's own team data and data not from his/her team (but belonging to the same crop). Change USERS.UACCESS dynamically?
  • Have table containing USERID-TEAM/PROJECT mapping
  • Shawn (AAFC) has a similar request to hide DMS studies from users who are not a member of the project/team.


Proposed schema changes for ICIS Schema 5.6

  • Nunhems-IRRI video conference
    • TEAMMEMB table
      • Optional table? Yes.
      • Not for restricting user access
      • Events are associated with: incoming seeds, outgoing seeds, collecting missions.
      • Rename to "EVENTMEM"
    • LISTNMS: Need new columns for storing event info?
      • store EVENTSTART,EVENTEND,EVENTSPONSOR in ATRIBUTS table (encoded through SetGen).
      • have EVENTREF as new column in LISTNMS (rename column to LREF)
      • Make structure of LISTNMS and STUDY uniform
    • VPD tables: adapt VPD tables from ICISSEC, integrate into ICIS Core schema but must give new names to these tables.
  • Further discussion (based on Excel file submitted by IRRI-GRC): ICIS 5.6 proposed schema changes

Comparison of INI file Nunhems - INI file IRRI

  • See Excel file (sent through email).
  • Was agreed upon that the ICIS INI file will be placed in a version control project on Cropforge to track changes easier.


ICIS Lite Demonstration

"ICIS Lite": SetGen + InTrack + Workbook rolled into one application.

Details: A Sample Breeding Workflow and the ICISworkbook

List creation

  • Has been observed that initial clicking of button for creating a list..it takes a while for worksheets to appear.
  • Highlighting of GID column: red color means the name is new; orange means it's already existing
  • Workbook has a "Search Germplasm" form (looks like GMS Search; has pedigree tree,etc)
    • User has option to show pedigree tree or not (via a checkbox)
    • "Input Germplasm" buttons leads to interface where user can input new germplasms
  • Process of loading a list is the same as loading a study

Inventory

  • Seb: it is possible to do conversion for the amount values (re: units)?. Warren: Yes, it's possible to shift from one unit to another.
  • Warren: Original iMS_Transaction/ims_lot schema is still being used. May, any suggestions re the schema?
  • User can create new units, define new units, reserve/deposit seeds
  • Login as seed administrator:
    • View Seed Transactions
      • As admin, he/she can check the seed lots that are available.
      • Seed lot info available at the bottom right panel
    • View seed stock balance
      • System flags seed lots with stocks below the minimum balance defined (stored in INI file)
    • Viability:
      • Data will be loaded through the DMS.
      • Same user interface form for displaying stock balances and viability (can choose via dropdown box).
    • Seed lot locations
      • Admin can set the seed lot location, reassign location, transfer from one seed lot to another: SRC_LOT --> DEST_AMOUNT, DEST_UNIT, DEST_LOC, DEST_LOT

Generate crosses

  • 2 views: view on the left allows user to select the list (like Setgen's list explorer), view on the right is form for generating crosses.
  • Can define the naming convention
    • Fix: Label is not correct "Use nextderivative name"

Advance a line

  • May: What if the method is not RANDOM BULK SF? Warren: you get an interface if you specify a numeric constant in the "Plants selected" field in the Naming Convention form.
  • Casper: what if you create the derivatives but you didnt load the list? List should be saved everytime (like in Setgen).

Create Study

  • If you would like to change something in your study, color a cell purple/"lavender" manually. Automatic coloring will be implemented.
  • Bug: coloring purple cells in Description sheet does not work --> not supported yet (only works for Observation sheet)


Q&A, comments

  • Casper: can you run GMS Search as a standalone? --> Yes.
  • Input Germplasm form: ...
  • Rob: Does it support multi-user access? --> YEs, the DLLs used (which already support multi-user access) by Setgen,GMS Search are used by the ICIS Lite application. Buffer file is for the use of the person inputting
  • May: It would be good to access the interface forms (ie. standalones) without having to go through the entire workflow.
  • Rob: re Customizing name standarization rules...
  • Casper: re Randomization and Layout...--> workbook accesses the cropstat dll; has interface where you can input your parameters
  • W:Creating pedigree field book is already functioning
  • May: How is the template map to a study
  • W:Map based on the interface; Select a study, ask for list of children; map in the Setup Mappings box;
  • W:Names in the template appears in the range; Names correspond to the values in the database
  • W: Newest addition to the tooldbar in Workbook - functions for the fieldbook
  • W:Comparison of functions; how are the functions meet their needs
  • M: Diff. concept of inventory; GID corresponds to a seedlot
  • C: Functions are useful; does not correspond much to Nunhems workflow; more on IRRI's workflow; some functions not available yet; differentiation bet. List and Study have become thin; hard to differentiate; one list, one Study; descriptions of functions should be differentiated bet. list and study fxns;
  • W: Yes, will differentiate the names of functions bet. List and Study
  • Casper: Will give a workflow based on Nunhems
  • C: How abt. label printing
  • W: Not available at themoment; Will hire a new sstaff; Responsible for the completion of Inventory system for ICIS lite;
  • C: Schedule of work for the new staff is very optimistic
  • W: Allows for flexibility
  • M: Have an Inventory schema in place
  • S: Pluggable to ICIS; made a link via seedlot; foreign key to plugin databases
  • M: How to gauge the monitoring part
  • S: Front end has to be designed; plugin or database is already there;
  • W: Asks abt. the funding
  • M: Ed's call.  On vacation till Aug.
  • M: Asks abt. Barcode
  • W: Not yet available; to be done by the new staff
  • C: Barcode entry?
  • W: Still functions in the workbook; searches for the designation
  • C: Barcode can be used as lookup tool and ....
  • W: Plan for ICIS Mobile; workbook in PDA; to be assigned to new staff; request by IRRI
  • M: Asks Seb abt.  2L ?
  • S: Not sure; mobile application has small database;
  • C: Possible for Oracle mobile interface
  • M: Will show workbook to breeders; pick up list is important; there are bugs in the version of some breeders
  • W: Similar problem in IRRI with the diff. environments; Vivay will check the versions
  • S: Do u plan to make dev't to Excel 2007; not using Excel 2007 yet; 
  • W: Yes
  • W: Limiting views is available in workbook; will not see all functions;
  • S: Presentation is amazing!
  • M: Not like the way it's set in the INI
  • C: Set more default values for new users so the form for functions will be cleaner/basic
  • W: workable; Will reduce the number of functions/icons to make it more user-friendly
  • C: Changes text file is useful/needed
  • W: Yes; Started a new document  format for debugging
  • C: Cropwiki and not in Cropforge?
  • W: It'll have a link
  • M: Asks abt. access rights;
  • W: We're still at it
  • C: Has the application version for Read only; being tested; Discussed abt. teams within the same crop to be able to read-only the lists belonging to other teams (currently it's read-write for all lists)
  • W: Asks for workflow
  • S & M: Agreed
  • == End of discussion ==
Retrieved from "http://www.icis.cgiar.org/icis/index.php/ICIS_Mini-Workshop_2009_Nunhems"
Personal tools