ICIS MiniWorkshop 2008

From ICISWiki

Back to Main Page > Workshops and Meetings

Contents 1 Agenda 2 Minutes 2.1 April 14 2.1.1 Schema Discussion 2.1.2 ICIS 5.5.1 Feature 2.2 April 15 2.3 April 16 2.4 April 17 2.5 April 18 2.6 April 21 2.7 April 22 2.7.1 Plan Targets in the Workshop 2.7.2 Discussion on the Data Selection Tool (DST) (Nunhems/Bayer replacement of ICIS-RTV) 2.8 April 23 2.8.1 Discussion of Features 2.8.2 Teleconference with AAFC/CIMMYT 2.8.2.1 VPD Presentation 2.8.2.2 Data Comparison Tool 2.9 April 24 2.10 April 25 2.10.1 Business Object Enterprise Presentation 2.10.2 Testing

Agenda

• Simultaneuos loading of studies (related with multi-access issues)
• Add Germplasm forms of SetGen - how to make it more user-friendly
• InTrack/IMS Tables
• ICIS 5.5.1 features
• RTV to Data Selection Tool (in combination with Oracle-VPD)
• WebEx meeting with Shawn-Graham
• Strategy for customization

Minutes

April 14

Schema Discussion

• Four tables will be added to the DMS with LEVELNO, OUNITID, REPRESNO, EFFECTID as primary keys

- Refer to ICIS 5.5. 1 features for table definition

ICIS 5.5.1 Feature

• Workbook

- multi-user

- manipulation of folders/studies (e.g. moving studies to another folders, deleting folders, renaming folders etc)

- selecting variates to retrieve

- loading of large datasets

• GMSSearch

- refinement of the layout

- sorting of the columns of the result the search (by name, location, unique ID etc)

- sizable columns of the list result

- tabs and setting of default tab

- multiple selection in tab & copy&paste mechanism

- showing of evaluation data

• issue: how to show the data if there are several records from different studies with different treatments? Shall normal averaging enough?

possible approach: DLL to execute a customized SQL statement that will be specified in a text file. The text file will be specified in the ICIS.INI

• SetGen

- there should be an active list before doing +,-,X,B-,B+,BX, Import from Excel etc. The listname on those forms will be deactivated as it will be based on the open list in the Edit Window

- refinement of the Edit Germplasm form

- importing of germplasm information (germplasm location, date, name type etc) to be incorporated in the Import From Excel

- calling of the Workbook from SetGen with automatic filling of a template based on the list selected in the List Manager

April 15

• Discussion about Oracle-VPD with Sebastien Frade
• Installation at CITRIX, and demo, of first release of ICIS Pre-Launcher in combination with Oracle-VPD

April 16

• Implementation and Testing of changes

April 17

• ICIS32.DLL v5.5.0.12

- the problems and issues about multi-load of study were handled in the ICIS32.DLL v5.5.0.12

- DLL was tested

- the following SQLs are used to test if there is conflict in the data. There should be no record returned to know that there is no conflict in the LevelNo and OUNITIS

LEVELS

 select levels.levelno, level_c.factorid, levels.factorid, level_c.lvalue, count(levels.levelno) 
 from levels right join level_c on levels.levelno = level_c.levelno
 where levels.FACTORID <> level_c.factorid 
 group by levels.levelno, level_c.factorid, levels.factorid, level_c.lvalue
 order by levelno asc

OUNITID

 select obsunit.ounitid, obsunit.EFFECTID, represtn.EFFECTID
 from represtn inner join (obsunit inner join oindex on obsunit.ounitid = oindex.ounitid)
 on represtn.represno = oindex.represno
 where obsunit.effectid <> represtn.effectid

April 18

• Implementation and Testing of changes

April 21

• Modification of the Add Germplasm/EPIT window in SetGen

- EPIT window to look like the Edit Germplasm window with some additional text boxes and buttons

- Issue

- should there be tree buttons(add, change, remove) for manipulating the Other Progenitors section or two buttons (Add, remove) are enough?

-Refer to #Cropforge#902 for details

• Additional features in the Import from Excel in SetGen

-Refer to document (SetGen_Addition_CF_Number_771.doc) in cropforge for details Cropforge#771

• Enhancement of the InTrack module integrated with SetGen

-Refer to feature request Cropforge#928

• Customizable Fill up Menu and filling up of customized data for the entries in the list. Refer to Cropforge#930

- suggested solution is to put in a text file the menu items and the corresponding customized SQL statement for each menu item

April 22

Plan Targets in the Workshop

• Workbook

- multi-user test

- drag and drop

- considering the study status in displaying the studies

- selecting variates to retrieve

- bug fixing

• SetGen

- Add/Edit Germplasm form modification

- Bug fixing

- Integrating the modified GMS Search (to check the effect of replacing the list box control with grid control)

- Other features

• GMSSearch

- Retrieving DMS data

- Export values in the tab

- Bug fixing

• InTrack

- Updated form called from SetGen

• Launcher

- Bug fixing

Discussion on the Data Selection Tool (DST) (Nunhems/Bayer replacement of ICIS-RTV)

• There will be one template DST in Nunhems. The DST template will be copied to each temp folder of the user.
• The Switchboard table is modified with additional fields because all users will use the same Switchboard table but with VPD applied. The Switchboard is an Oracle table now but queried using pass-through queries in DST
• Issues:

- How can the DST still accomodate queries defined by users without clearing it out everytime they run the DST.

- The pass through queries are always created everytime the DST is accessed.

- The setting of sessionID is always executed because of the refreshing done by ODBC. Possible solution is to set the ODBC Refresh in the Tools->Options->Advanced in Access

April 23

Discussion of Features

• LISTDATA.LRECID - implement as globally assigned

Issues:

- The current LRECID should be reassigned. It can be part of ICIS Diagnostis tool

- The IMS_Transaction will be affected with the change.

• Customized "Fill up" menu items and customized query. Refer to Cropforge#930 for details

Issues:

- Is the retrieval of a varaite from several studies where data will be shown with study as crosstab columns can be a generic query that should be part of SetGen Fill up? But there are complications attached to it. One is that are all values of the study comparable with each other even if the treatment is different? What happen with replicated data in a study? It was suggested to implement it as a customized query

• Automatic assignement of Names (like IRGC Number in IRRI GRC, LOT NUMBER name in Bayer, IR Number for IRRI Plant breeding)

- It was possible to implement it as part of the customized Fill up and query.

• Public status of list

- For Nunhems: this is to make the list viewable to every member of the crop using VPD

- For IRRI-PBGB and other IRRI locals: this is to tell the central adminstrator what list to upload.

- What should be the behaviour of that public status? Is it synonymous to finalized status?

If finalized status is used, list become uneditable but can be browsed and copied

But the list owner should be able to make it unfinalized (private) again.

The user with admin right (UTYPE>=422)within that installation can unfinalized the list.

The menu term in SetGen should be changed (for Nunhems, it will be termed as publicize). A resource file can be used to change the labels of the application menu and headers.

- Casper decided for the ICIS 5.5.1: What Nunhems/BCS need is to change the label of Final to Public. For labels of application widgets and controls that need customization, they will be put in a resource file (.RES)

• Public status of Study

- How will the public/finalized study? Should it behave like the list? Can someone upload data to a finalized study?

Teleconference with AAFC/CIMMYT

• Participants:

-CIMMYT: Graham, Guy and William

-AAFC: Fran and Shawn

- Nunhems Workshop participants: Casper, Brenda, Rob, Arllet, May, Candy, Beth, Warren

VPD Presentation

Casper: It was discovered that some of the errors in the application were caused by the VPD not applied to the table (e.g. NAMES). Hence, all tables should have VPD applied to it. This solved some problems.

Graham: My understanding is that you used additional fields to apply the VPD

Casper: Yes. Currently (inold situation), there is a local database per team. So there is no interaction among teams in terms of data sharing. But now, there is a great need to interact and share data. So, now, there will be one local Schema for all the teams and crops. In the Retriever, we made changes and we call it now Data Selection Tool. Now, we will apply the VPD which in simple term is using a WHERE clause in the database level.

In ICIS-Global Nunhems (ICIS 5.5.1), there will crops, teams which are members of crops and there are users belonging to teams.

Public data means the data are available in crop level, Private data means the data are available in team level

Germplasm/Attributes/Names/Seed Stocks - publically available (crop-level)

Lists/Studies - privately available (team-level)

Graham: how will the user make a list public?

Casper: we had discussion today about it and we will use the Final status already available in SetGen.

For VPD 5 extra tables are added.

ICISSEC schema which contains the 5 tables related to VPD

ICIS_SESSION table - Session ID (+CLient Info) and user information

VPD_LEVEL table - The levels are Family/Group, Crop, Team, Breeding Program, Location Team, Person; One person can have different roles and a role is one (ICIS-) USERID

VPD_LEVEL_VALUE - (Ex. Carrot for Crop-Level, Cucumber Long for Team-Level, Nunhems for Location-Team, etc)

VPD_USER- columns are Userid, LevelID, ValueID (defines each user-role = ICIS USERID)

VPD_VALUE_HIERARCHY defines hierarchy of Crops, Teams, Users

All ICIS tables have additional tables (ex. INSID, INSDATE, UPDID, UPDDATE) with INSID as the most important field to filter by VPD. INSID field refers to VPD_USER table in ICISSEC schema

CLIENT_INFO is set by the DLL based on value in ICIS.INI

• DEMO of the VPD

• Discussion:

Shawn: This will be in Oracle only, What I see here is having additional tables. Also in other db-engines?

Casper: There are tables. There are functions. There are policies.

Graham: Shawn, what we see are functionalities supported by the Oracle database platform.

Shawn: We can add tables but it will require many works to make it work like that. This is similar to what I suggested last year during the workshop at AAFC in 2007.

Casper: We have consultants who helped us with the VPD implementation that are very knowledgeable about security (consultant also has much experience with VPD in financial industry).

Casper: Arllet created an INI key that will tell the DLL to call a certain function where the VPD will be applied

Casper: For tables with records that can be public or private, we added extra field (VPD_PUBLIC). This field will be filled by a trigger. For example, if a list will be finalized (made public), its status will be changed and there will be a trigger to update the VPD_PUBLIC field

Guy: Postgres is developing something similar to VPD

Shawn: We tried migrating to mySQL with its suite of applications. So,we need to maybe stick for it for a while.

Casper: It happened to us. For the initial implementation we called our IT department. But we asked for admin rights and so we are making them ourselves.

Candy: There is also VPD solution for mySQL already.

Casper: The DLL is already ready for VPD strategy so even if you will use mySQL, it will be okay.

Data Comparison Tool

• Presentation by Shawn

Shawn: I used VP.Net with this one.

- There is a Line vs Line Comparison

- There is a study Retriever - retrieves

- Germplasm List Retriever - SetGen List and retrieves the studies where the lines in the list are tested

- There are additional keys in the INI file for the database of the molecular studies

• Discussion

Casper: does your user ask to show the data in graphical way?

Shawn: The users want to display and analyse it in different ways. So, we just decided to give them the data but it is up to them to choose the application for displaying

Casper:Is there a reason why the phenotype and the genotype studies are in separate databases?

Shawn: It is to disguish the studies.

Graham: Arllet, do we have a field that groups the trait so that we can group molecular traits.

Arllet: IRIS has TRGROUP but that is not pard of standard ICIS. The study type can be used to distinguish them.

Shawn: One of the problems I had when I used IRIS, there are the same factor names but with different scale and method. So, that affects the list of Factors shown in the application.

Graham: You are lucky that it is standard in AAFC but not in IRRI. People can use the same factor name but in different scales and methods . But you can use the combination of Trait, Scale and Method to have distinct list of factors instead of the factor name.

Shawn: I can try that one. But do you think the DataComp is useful for you and others?

Arllet: Yes, definitely. May Ann is already interested on it for Bayer.

Arllet: Shawn, did you use the DLL or other technology to access the database?

Shawn: I did not use the DLL because I encounter problem when I used in the .NET.

Graham: Arllet, is there a way to call the DLL in .NET?

Arllet: Well, the other issue too is the speed of the DLL. I think it will really be faster to find ways to query the DMS than the DLL just like what Shawn did and what I did in the Retriever.

Guy: Arllet, is there a plan to integrate the cropfinder warehouse tables.

Arllet: Right now, none of the standalone applications are in need of it. But I think we definitely need it for the Web. I'll ask Ching or Weng to automatically create the warehouse when we upload to central. It is best to put it in the central because it will never be populated in the local through triggesr as not all database engine has such capability like Access. But in the future, when we need to do DMS queries in application like SetGen, I think the Cropfinder warehouse will be very useful.

April 24

• Testing and Debugging
• Workbook

-debugging of the errors

-Testing of simultaneous loading of studies.

-manipulation of the folders

-setting of the status of study and their behaviour

• SetGen

-Add GErmplasm form

-Save List - does not work when AUTOCOMMIT=YES. The DLL is modified to commit the GMS_addListData when AUTOCOMMIT=YES. The SetGen function is also modified

• GMSSearch

-The More button for Attribute Search

-The integration with SetGen

-the sorting of names in the list

April 25

Business Object Enterprise Presentation

- In BO, user does not need to know the data model. In the server, a data model can be defined in what is called Universe but user don't need to do it. In Client side, they will just access that Universe. That is one difference with the Crystal Report where the user needs to know the model to create a report

- However, the policy applied in a VPD does not apply in BO

- A programming language (e.g. VB) can be used to manipulate what user can see.

- But it has a very easy drag-and-drop way to select fields to create the report

Testing

• SetGen

-When importing entries from Excel, the entryid is assigned to the LRECID. Now that the latter is globally assigned, the entryid is now negative. (to be corrected)

• GMS Search

-hiding of tab, find attribute

• Workbook

-folder operation and setting of study/folder status