ICIS Data Warehouse Creation and Maintenance FAQs

From ICISWiki

Revision as of 05:44, 23 July 2010 by Mhabito (Talk | contribs)

(diff) ← Older revision | Current revision (diff) | Newer revision → (diff)

Jump to: navigation, search

1 What are the Data Warehouse tables needed by ICIS and when are they used?
2 Data Warehouse Tables for Pedigree Tree creation
3 Data Warehouse Tables for single study dataset retrieval
4 Data Warehouse Tables for cross study queries
5 Creation: When are Data Warehouses tables generated?
6 Updating: When and how should the Data Warehouses be updated?
7 Implementation Details: Data Warehouse tables creation
- 7.1 How long does it take to generate data warehouse tables for ICIS databases?
- 7.2 Testing of script for data warehouse tables creation
8 How is an ICIS database prepared/updated?

What are the Data Warehouse tables needed by ICIS and when are they used?

To generate Pedigree trees in web application:

wh_der_children
wh_man_children
wh_germplasm_details (new as of Dec 2009)

See Pedigree Viewer for Developers

For getting all entries in a dataset for a given study in web application

wh_columns
wh_data
wh_datasets

For cross-study queries (web application)

wh_data
wh_countdatapoints (new as of Sept 2009)
factors (table adapted from original CropFinder by J.C. Alarcon)
fieldsetup (table adapted from original CropFinder by J.C. Alarcon)
variates (new as of Sept 2009; same concept as factors table. Columns defined by top 175 variates with most number of datapoints in wh_countdatapoints table)

Data Warehouse Tables for Pedigree Tree creation

Schema of wh_der_children

Fields (as of Dec 22, 2009..in database iris_myisam_20091222)

columns NVAL,PREFID removed as of Dec 22, 2009

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
gid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
chgid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references

Indexes (as of Dec 1, 2009)

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
wh_der_children	1	wh_der_children_idx1	1	gid	A	620029	(NULL)	(NULL)	YES	BTREE
wh_der_children	1	wh_der_children_idx2	1	chgid	A	1860087	(NULL)	(NULL)	YES	BTREE

MySQL Data Definition script (modified Dec 22, 2009):

CREATE TABLE wh_der_children (
 gid int(11), 
 chgid int(11)            -- removed columns NVAL,PREFID 20091222 mhabito
)ENGINE=MyISAM CHARSET=utf8;

Sample data(for rice..in database iris_myisam_20091222):

Schema of wh_man_children

Table name: wh_man_children

Fields (as of Dec 22, 2009...in database iris_myisam_20091222)

columns NVAL,PREFID removed as of Dec 22, 2009

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
gid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
chgid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references

Indexes (as of Dec 1, 2009)

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
wh_man_children	1	wh_man_children_idx1	1	gid	A	305905	(NULL)	(NULL)	YES	BTREE
wh_man_children	1	wh_man_children_idx2	1	chgid	A	305905	(NULL)	(NULL)	YES	BTREE

MySQL Data Definition script (as of Dec 22, 2009...in database iris_myisam_20091222):

CREATE TABLE wh_man_children (
 gid int(11), 
 chgid int(11)              -- removed columns NVAL,PREFID 20091222 mhabito
)ENGINE=MyISAM CHARSET=utf8;

Sample data (for rice..in database iris_myisam_20091222):

Schema of wh_germplasm_details

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
gid	int(11)	(NULL)	YES	MUL	(NULL)	select,insert,update,references
gdate	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
prefname	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
prefnameid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
prefnamedate	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
prefnameloc	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
prefid	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references	Preferred ID (NAMES.NSTAT = 8) or "Unique Sample ID" of gid. If no preferred ID exists: For GID > 0, "Unique Sample ID" = crop installation + germplasm user + local GID. Example: IRIS 10-393 ...where 3 = germplasm user (GERMPLSM.GERMUID), -3632 = local GID (GERMPLSM.LGID) For GID < 0, "Unique Sample ID" = crop installation + germplasm user + GID. Example: IRIS 108-19098 ...where 108 =germplasm user (GERMPLSM.GERMUID), -19098 = GID (GERMPLSM.GID)
prefidnid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
prefiddate	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
prefidloc	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
prefabrev	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
loccreated	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
loccreatedid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
loccreateddes	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
ctrycreated	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
ctryid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
ctrycode	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
oricountry	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
methname	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
methtype	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
maintenancerootgid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references	Loop back through the GPID2 while METHTYPE = 'MAN'
gpid1	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
mgid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
gpid2	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
userid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
username	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
wh_germplasm_details	1	wh_germplasm_details_idx1	1	gid	A	2553811	(NULL)	(NULL)	YES	BTREE
wh_germplasm_details	1	wh_germplasm_details_idx2	1	maintenancerootgid	A	2553811	(NULL)	(NULL)	YES	BTREE
wh_germplasm_details	1	wh_germplasm_details_idx3	1	gpid1	A	2553811	(NULL)	(NULL)	YES	BTREE
wh_germplasm_details	1	wh_germplasm_details_idx4	1	mgid	A	2553811	(NULL)	(NULL)	YES	BTREE
wh_germplasm_details	1	wh_germplasm_details_idx5	1	gpid2	A	2553811	(NULL)	(NULL)	YES	BTREE
wh_germplasm_details	1	wh_germplasm_details_idx6	1	userid	A	2553811	(NULL)	(NULL)	YES	BTREE

MySQL Data Definition script (as of April 8, 2010...in database iris_myisam_20100330):

CREATE TABLE wh_germplasm_details (
	gid int(11) DEFAULT NULL, 
	gdate int(11) DEFAULT NULL,  
        prefname varchar(255) DEFAULT NULL,   
	prefnameid int(11) DEFAULT NULL, 
	prefnamedate int(11) DEFAULT NULL, 
	prefnameloc varchar(255) DEFAULT NULL, 
	prefid varchar(255) DEFAULT NULL, 
	prefidnid int(11) DEFAULT NULL, 
	prefiddate int(11) DEFAULT NULL, 
	prefidloc varchar(255) DEFAULT NULL, 
	prefabrev varchar(255) DEFAULT NULL, 
	loccreated varchar(255) DEFAULT NULL, 
	loccreatedid int(11) DEFAULT NULL, 
	loccreateddes varchar(255) DEFAULT NULL, 
	ctrycreated varchar(255) DEFAULT NULL, 
	ctryid int(11) DEFAULT NULL, 
	ctrycode varchar(255) DEFAULT NULL, 
	oricountry varchar(255) DEFAULT NULL, 
	methname varchar(255) DEFAULT NULL, 
	methtype varchar(255) DEFAULT NULL,
	maintenancerootgid int(11) DEFAULT NULL,   
	gpid1 int(11) DEFAULT NULL,				   
	mgid int(11) DEFAULT NULL,                
	gpid2 int(11) DEFAULT NULL,				   
	userid int(11) DEFAULT NULL,			   
	username varchar(255) DEFAULT NULL		   
) ENGINE=MyISAM CHARSET=utf8;

Sample data (in iris_myisam_20100330):

How they are used

wh_der_children

used for showing children of a given germplasm and of all other entities in the derivative neighborhood

wh_man_children

used for showing children of a given germplasm and of all other entities in the maintenance neighborhood

wh_germplasm_details

used for storing additional info (names,locations,method,etc) of a given germplasm

Data Warehouse Tables for single study dataset retrieval

Schema of wh_datasets

MySQL Data Definition script:

DROP TABLE IF EXISTS `wh_datasets`;
CREATE TABLE `wh_datasets` (
 `study` int(11) default NULL,               -- the study ID 
`dataset` int(11) default NULL,              -- dataset number (a.k.a. REPRESNO)
`maxrow` int(11) default NULL,               -- maximum number of rows for the dataset
`maxcol` int(11) default NULL,               -- maximum number of columns for the dataset
`desc`	varchar(255) default NULL              -- description of the dataset
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Schema of wh_columns

MySQL Data Definition script:

DROP TABLE IF EXISTS `wh_columns`;
CREATE TABLE `wh_columns` (
 `study` int(11) default NULL,              -- the study ID
`dataset` int(11) default NULL,            -- this is the REPRESNO (representation number) 
 `col` int(11) default NULL,                -- column number (1 to n)
`colname` varchar(255) default NULL,       -- column name: combination of trait name-scale name-tmethod name
`fieldid` int(11) default NULL,            -- contains the LABELID or VARIATID, depending on coltype
`coltype` char(1) default NULL,            -- "F" (factor) or "V" (variate) 
`valtype` char(1) default NULL,            -- "C" if text, "N" if numeric
`traitid` int(11) default NULL,            -- link to TRAIT table
`scaleid` int(11) default NULL,            -- link to SCALE table
`tmethid` int(11) default NULL            -- link to TMETHOD table
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample data (for rice):

Schema of wh_data

wh_data

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
study	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
dataset	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
datarow	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
ounitid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
datacol	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
coltype	char(1)	utf8_general_ci	YES		(NULL)		select,insert,update,references
valtype	char(1)	utf8_general_ci	YES		(NULL)		select,insert,update,references
dataval	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
datavaldesc	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
traitid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
scaleid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
tmethid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
wh_data	1	wh_data_idx1	1	dataset	A	1482	(NULL)	(NULL)	YES	BTREE
wh_data	1	wh_data_idx2	1	ounitid	A	1275564	(NULL)	(NULL)	YES	BTREE
wh_data	1	wh_data_idx3	1	datacol	A	141	(NULL)	(NULL)	YES	BTREE
wh_data	1	wh_data_idx5	1	datarow	A	163272	(NULL)	(NULL)	YES	BTREE
wh_data	1	wh_data_idx6	1	traitid	A	240	(NULL)	(NULL)	YES	BTREE
wh_data	1	wh_data_idx7	1	scaleid	A	378	(NULL)	(NULL)	YES	BTREE
wh_data	1	wh_data_idx8	1	tmethid	A	427	(NULL)	(NULL)	YES	BTREE

MySQL Data Definition script:

CREATE TABLE `wh_data` (
`study` int(11) default NULL,                -- link to study ID in wh_columns table
`dataset` int(11) default NULL,              -- link to dataset # in wh_columns table
`datarow` int(11) default NULL,              -- row number (1 to n)
`ounitid` int(11) default NULL,	      -- the observation unit ID (link to OINDEX table)
`datacol` int(11) default NULL,              -- column number (1 to n)
`coltype` char(1) default NULL,      -- "F" (factor) or "V" (variate) (from wh_columns table)
`valtype` char(1) default NULL,       -- "C" if text, "N" if numeric (from wh_columns table)
`dataval`	varchar(255) default NULL,	-- the data value
`datavaldesc`	varchar(255) default NULL,	-- the description of the data value (if available in SCALEDIS)
`traitid` int(11) default NULL,         
`scaleid` int(11) default NULL,     
`tmethid` int(11) default NULL	 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample data (for rice):

Data Warehouse Tables for cross study queries

Schema of wh_data

See above

Schema of wh_countdatapoints

Modified Nov 24, 2009: Now stores info for factors and variates (across all datasets). Table to be used for data quality checking as well.

Fields (as of Nov 24, 2009)

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
variablename	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
valtype	varchar(2)	utf8_general_ci	YES	MUL	(NULL)		select,insert,update,references
traitid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
scaleid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
tmethid	int(11)	(NULL)	YES	MUL	(NULL)		select,insert,update,references
count_datasets	int(11)	(NULL)	YES		0		select,insert,update,references
count_datapoints	int(11)	(NULL)	YES		0		select,insert,update,references
count_datasets	int(11)	(NULL)	YES		0		select,insert,update,references
count_core_datapoints	int(11)	(NULL)	YES		0		select,insert,update,references
count_cf_datasets	int(11)	(NULL)	YES		0		select,insert,update,references
count_cf_datapoints	int(11)	(NULL)	YES		0		select,insert,update,references

Indexes (as of Nov 24, 2009)

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
wh_countdatapoints	1	wh_countdatapoints_idx1	1	valtype	A	1	(NULL)	(NULL)	YES	BTREE
wh_countdatapoints	1	wh_countdatapoints_idx2	1	traitid	A	200	(NULL)	(NULL)	YES	BTREE
wh_countdatapoints	1	wh_countdatapoints_idx3	1	scaleid	A	300	(NULL)	(NULL)	YES	BTREE
wh_countdatapoints	1	wh_countdatapoints_idx4	1	tmethid	A	601	(NULL)	(NULL)	YES	BTREE

MySQL Data Definition Script (as of Nov 24, 2009):

CREATE TABLE wh_countdatapoints (
 variablename varchar(255),
 coltype varchar(2),    -- added 20091123 mhabito: F or V
valtype varchar(2),    -- C or N
traitid int,
scaleid int,
tmethid int,
count_datasets int default 0,          -- count datasets in wh_ warehouse tables
count_datapoints int default 0,        -- count datapoints in wh_ warehouse tables
count_core_datasets int default 0,     -- added 20091123 mhabito: count datasets in ICIS core tables
count_core_datapoints int default 0,   -- added 20091123 mhabito: count datapoints in ICIS core tables
count_cf_datasets int default 0,       -- added 20091123 mhabito: count datasets in Cropfinder warehouse tables
count_cf_datapoints int default 0      -- added 20091123 mhabito: count datapoints in Cropfinder warehouse tables
) Engine=MyISAM DEFAULT CHARSET=utf8;

Sample data (for rice):

Schema of factors

Important: The table structure for FACTORS varies for different crops. Its structure is defined by the set of factors (traitid-scaleid-tmethid combinations) used by all datasets within a crop database.

Naming convention for columns: TRAITID.SCALEID.TMETHID (for columns other than ounitid,studyid,represno)

Fields (for Rice as of Oct 2009)

Field          Type          Collation        Null    Key     Default  Extra   Privileges                       Comment
-------------  ------------  ---------------  ------  ------  -------  ------  -------------------------------  -------
ounitid        int(11)       (NULL)           NO      MUL                      select,insert,update,references         
studyid        int(11)       (NULL)           NO      MUL                      select,insert,update,references         
represno       int(11)       (NULL)           NO      MUL                      select,insert,update,references         
202.151.131    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
202.151.202    double        (NULL)           YES             (NULL)           select,insert,update,references         
205.146.133    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
205.1536.852   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
205.1537.852   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.94.258     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.94.854     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.95.258     double        (NULL)           YES             (NULL)           select,insert,update,references         
215.96.258     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.221.258    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1522.847   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1523.847   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1524.847   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1529.0     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1529.847   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1530.0     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1530.847   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1531.0     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
215.1531.847   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
217.98.18      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
217.99.18      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
217.672.849    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
217.1533.0     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
217.1533.849   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
218.224.225    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
218.1521.846   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
218.1528.0     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
218.1528.846   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
219.100.19     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
219.107.19     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
219.107.721    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
219.118.717    int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
219.226.19     double        (NULL)           YES             (NULL)           select,insert,update,references         
219.1493.717   double        (NULL)           YES             (NULL)           select,insert,update,references         
219.1509.848   int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
219.1525.721   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
219.1532.848   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
220.101.20     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
229.173.179    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
229.173.857    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.91.17      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.91.495     double        (NULL)           YES             (NULL)           select,insert,update,references         
251.91.656     double        (NULL)           YES             (NULL)           select,insert,update,references         
251.91.856     double        (NULL)           YES             (NULL)           select,insert,update,references         
251.92.17      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.93.17      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.97.17      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.103.17     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.103.91     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.104.17     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.104.92     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.115.17     int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
251.135.17     double        (NULL)           YES             (NULL)           select,insert,update,references         
251.147.0      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.147.17     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.237.17     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.672.17     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.1533.17    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.1535.17    int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
251.1536.17    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.1537.17    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
251.1538.17    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
253.295.281    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
255.237.751    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
255.1123.751   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
313.666.822    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
313.1489.822   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
501.504.508    double        (NULL)           YES             (NULL)           select,insert,update,references         
503.510.499    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
503.516.497    double        (NULL)           YES             (NULL)           select,insert,update,references         
1549.659.657   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1550.660.658   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1552.1152.668  varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1588.93.797    int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
1588.1037.797  double        (NULL)           YES             (NULL)           select,insert,update,references         
1588.1312.797  double        (NULL)           YES             (NULL)           select,insert,update,references         
1588.1480.797  int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
1624.1527.850  varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1624.1534.712  varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1624.1534.850  varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1625.1544.855  varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
factors	1	factors_idx_01	1	ounitid	A	1249622	(NULL)	(NULL)		BTREE
factors	1	factors_idx_02	1	studyid	A	1249622	(NULL)	(NULL)		BTREE
factors	1	factors_idx_03	1	represno	A	1249622	(NULL)	(NULL)		BTREE

Sample data (for rice, REPRESNO = 2 / Dataset No. 2):

Schema of fieldsetup

Structural changes

As of January 2009 (on database iris_myisam)
- Length of columns FIELDNAME, FIELDTEXT, TABLEALIAS increased to 255.
- Column TMETHID added (to handle the trait-scale-method definitions in ICIS)

As of September 2009 (on database iris_myisam_20090904, iris_myisam_20091006)
- Unused columns deleted
- Four new columns added for storing separately the trait name, scale name, tmethodname and colname (FNAME if factor, VNAME if variate)

Data/content changes:

Value of "GROUPOFTRAITS" column in FIELDSETUP table	Column in FIELDSETUP table	Original format/content	Modified format/content (as of March 27, 2009)	Modified format/content (as of July 17, 2009)	Modified format/content (as of September 9, 2009)	Modified format/content (as of October 29, 2009)
"Traits" (changed to "Variates" as of Oct 29, 2009)	fieldname	"dvalue"	TRAITNAME_SCALENAME_TMETHODNAME	TRAITNAME_SCALENAME_TMETHODNAME	TRAITNAME (SCALENAME) TMETHODNAME [VARIATENAME]	TRAITNAME (SCALENAME) TMETHODNAME ([VARIATENAME] removed)
	fieldtext	TRAITNAME_SCALENAME (then truncated to 50 characters max only)	TRAITNAME_SCALENAME_METHODNAME.VARIATEID (where VARIATEID = cast(variatid as char)) --> no truncation	VARIATENAME (variate.vname)	TRAITID.SCALEID.TMETHID_C or TRAITID.SCALEID.TMETHID_N	TRAITID.SCALEID.TMETHID ("_C", "_N" removed)
	fieldid	(autonumber) 1 to etc...unique, sequential	unique VARIATID (variatid from ICIS "variate" table)	unique VARIATID (variatid from ICIS "variate" table)	unique VARIATID (variatid from ICIS "variate" table)	first-occurence VARIATID (only one variatid from ICIS "variate" table..previously all variatids were stored)
	tablealias	If datatype = 'Text': data_c_fieldname If datatype = 'Real': data_n_fieldname (then truncated to 50 characters max only)	If datatype = 'Text': data_c_fieldname If datatype = 'Real': data_n_fieldname --> no truncation	If datatype = 'C': data_c_fieldname If datatype = 'N': data_n_fieldname --> no truncation	"Variates" (referring to new warehouse table "Variates")	"Variates" (referring to new warehouse table "Variates")
"Factors"	fieldtext	FACTORNAME	FACTORNAME.LABELID (where LABELID = cast(labelid as char))	FACTORNAME_C (if factor.ltype = "C") / FACTORNAME_N (if factor.ltype = "N")	TRAITID.SCALEID.TMETHID_C or TRAITID.SCALEID.TMETHID_N	TRAITID.SCALEID.TMETHID ("_C", "_N" removed)
"Factors"	fieldid	(autonumber) 1 to etc...unique, sequential	unique LABELID (labelid from ICIS "factor" table)	unique LABELID (labelid from ICIS "factor" table)	unique LABELID (labelid from ICIS "factor" table)	first-occurrence LABELID (only one labelid from ICIS "factor" table..previously all labelids were stored)

fieldsetup

Fields (as of Sept 2009: on database iris_myisam_20090904, iris_myisam_20091006)

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
datatype	varchar(50)	utf8_general_ci	YES		(NULL)		select,insert,update,references	"C" or "N"
description	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
fieldid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references	Either the first-occurrence LABELID (if factor) or VARIATID (if variate) given a traitid
fieldname	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references
fieldtext	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references	Link to "TRATID.SCALEID.TMETHID" column found in FACTORS,VARIATES tables
groupoftraits	varchar(50)	utf8_general_ci	YES		(NULL)		select,insert,update,references	"Factors" or "Variates" or "Study"
outputgroup	varchar(50)	utf8_general_ci	YES		(NULL)		select,insert,update,references	"Factors" or "Variates" or "Study"
outputorder	int(11)	(NULL)	YES		(NULL)		select,insert,update,references
tablename	varchar(50)	utf8_general_ci	YES		(NULL)		select,insert,update,references	"Factors" or "Variates" or "Study"
tablealias	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references	"Factors" or "Variates" or "Study"
traitid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references	TRAITID from FACTOR/VARIATE table
scaleid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references	SCALEID from FACTOR/VARIATE table
tmethid	int(11)	(NULL)	YES		(NULL)		select,insert,update,references	TMETHID from FACTOR/VARIATE table
traitname	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references	TRNAME from TRAIT table
scalename	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references	SCNAME from SCALE table
tmethodname	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references	TMNAME from TMETHOD table
colname	varchar(255)	utf8_general_ci	YES		(NULL)		select,insert,update,references	Either the first-occurrence FNAME (if factor) or VNAME (if variate)

Sample data (for rice):

Schema of variates

Important: The table structure for VARIATES differs for different crops. Its structure is defined by the set of factors (traitid-scaleid-tmethid combinations) used by all datasets within a crop database.

Naming convention for columns: TRAITID.SCALEID.TMETHID (for columns other than ounitid,studyid,represno)

Fields (as of Oct 29, 2009: in iris_myisam_20091006)

 Field          Type          Collation        Null    Key     Default  Extra   Privileges                       Comment
-------------  ------------  ---------------  ------  ------  -------  ------  -------------------------------  -------
ounitid        int(11)       (NULL)           NO      MUL                      select,insert,update,references         
studyid        int(11)       (NULL)           NO      MUL                      select,insert,update,references         
represno       int(11)       (NULL)           NO      MUL                      select,insert,update,references         
222.641.639    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
401.112.100    double        (NULL)           YES             (NULL)           select,insert,update,references         
405.113.248    double        (NULL)           YES             (NULL)           select,insert,update,references         
405.113.249    double        (NULL)           YES             (NULL)           select,insert,update,references         
405.113.827    double        (NULL)           YES             (NULL)           select,insert,update,references         
406.115.103    double        (NULL)           YES             (NULL)           select,insert,update,references         
408.111.99     double        (NULL)           YES             (NULL)           select,insert,update,references         
501.500.508    double        (NULL)           YES             (NULL)           select,insert,update,references         
501.501.508    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
501.503.508    double        (NULL)           YES             (NULL)           select,insert,update,references         
501.1542.508   int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
501.1543.508   int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
1003.9.30      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1003.153.30    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1003.1535.851  int(11)       (NULL)           YES             (NULL)           select,insert,update,references         
1004.7.28      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1004.622.620   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1005.8.29      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1005.8.203     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1005.1529.29   varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1006.4.24      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1006.4.792     double        (NULL)           YES             (NULL)           select,insert,update,references         
1006.613.610   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1007.1.21      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1008.6.27      varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1008.618.615   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1009.2.25      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1009.133.25    double        (NULL)           YES             (NULL)           select,insert,update,references         
1009.645.643   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1010.5.26      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1012.3.23      varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1012.625.623   double        (NULL)           YES             (NULL)           select,insert,update,references         
1013.70.22     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1013.70.706    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1013.1478.793  varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1014.643.642   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1015.627.625   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1019.635.640   double        (NULL)           YES             (NULL)           select,insert,update,references         
1019.642.640   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1019.642.641   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1021.609.606   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1028.195.200   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1051.638.636   double        (NULL)           YES             (NULL)           select,insert,update,references         
1051.1540.36   double        (NULL)           YES             (NULL)           select,insert,update,references         
1052.637.635   double        (NULL)           YES             (NULL)           select,insert,update,references         
1053.1539.853  double        (NULL)           YES             (NULL)           select,insert,update,references         
1057.150.163   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1059.630.628   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1062.633.632   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1065.639.637   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1066.617.614   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1071.626.624   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1073.269.262   varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1073.651.648   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1075.632.631   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1076.631.629   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1077.650.647   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1078.656.653   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1079.646.644   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1080.649.646   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1082.621.619   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1085.655.652   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1086.657.654   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references             
1086.658.655   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1087.608.605   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1088.628.626   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1088.629.627   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1090.611.608   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1091.610.607   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1101.163.334   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1101.163.335   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1101.164.44    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1101.164.119   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1102.37.52     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1104.21.157    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1105.35.50     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1114.39.54     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1118.23.45     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1152.121.39    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1152.121.111   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1152.121.112   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1152.121.273   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1156.18.40     varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1167.19.198    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1202.30.8      varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1204.171.47    varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1205.55.80     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1205.123.118   varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1206.28.48     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1214.29.6      varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1215.27.4      varchar(150)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1251.11.33     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1253.16.38     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1255.13.35     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1256.12.34     varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1257.89.108    double        (NULL)           YES             (NULL)           select,insert,update,references         
1260.10.32     double        (NULL)           YES             (NULL)           select,insert,update,references         
1261.90.109    double        (NULL)           YES             (NULL)           select,insert,update,references         
1262.640.638   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1264.636.634   double        (NULL)           YES             (NULL)           select,insert,update,references         
1266.615.612   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1538.607.604   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1539.612.609   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1540.619.616   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1542.623.621   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1543.624.622   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1544.634.633   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1545.648.645   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1546.652.649   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1547.653.650   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references         
1548.654.651   varchar(255)  utf8_general_ci  YES             (NULL)           select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
variates	1	variates_idx_01	1	ounitid	A	1249622	(NULL)	(NULL)		BTREE
variates	1	variates_idx_02	1	studyid	A	1249622	(NULL)	(NULL)		BTREE
variates	1	variates_idx_03	1	represno	A	1249622	(NULL)	(NULL)		BTREE

Sample data (for rice):

Creation: When are Data Warehouses tables generated?

AFTER conversion of any ICIS database from MS Access to MySQL
See MS Access to MySQL conversion of ICIS databases

Updating: When and how should the Data Warehouses be updated?

(from manacleto Aug 19, 2009) Right now, there is no clear procedure. A script is needed to add only the new entries to the data warehouses. Right now, you will have to generate them all again everytime an update(probably new conversion task) is made on the ICIS mysql databases.

Implementation Details: Data Warehouse tables creation

The script can be found in Cropforge project named “icisjavatools”. The script may be viewed on the browser, the link is:
http://cropforge.org/plugins/scmsvn/viewcvs.php/projects/ICISDatabaseTools/src/sql/mysql/warehouse_tables/?root=icisjavatools

Example of commands made to create the warehouse tables:

mysql> use iris_myisam;
mysql> source create_icis_dms_warehouse.sql;
mysql> call activate_icis_dms_warehousing('iris_myisam');

How long does it take to generate data warehouse tables for ICIS databases?

(Castor) On Castor server with the following specs: Server OS: Linux Debian. Hardware specs: 8 processors; Intel(R) Xeon(R) CPU; E5405 @ 2.00GHz 24 GB Memory

(CRIL4) On CRIL4 server with the following specs: Server OS: Linux Debian. Hardware specs: Pentium D 2.8Ghz, 2GB Memory, 500GB HD

IRIS 7 hours 41 mins 54.28 secs [for wh_columns, wh_data, wh_datasets, wh_countdatapoints] (on CRIL4) --As of Oct 20, 2009

IWIS 14 hours 25 min 34.73 sec + approx 4 hrs for factors and fieldsetup (on Castor)--As of Aug 19, 2009

IMIS

Testing of script for data warehouse tables creation

Data Warehouse Creation: Test Results

How is an ICIS database prepared/updated?

I. Convert from MS Access to MySQL database (on local machine/PC)

http://cropwiki.irri.org/icis/index.php/MS_Access_to_MySQL_conversion_of_ICIS_databases

II. Create a mysqldump file of your local Mysql database

mysql> mysqldump -usomeusername -p --default-character-set=utf8 iris > iris_yyyymmdd.sql

This local database should NOT have any indices at this stage.

III. Transfer the mysqldump file to the "database preparation server"

For Castor server, the "preparation server" is CRIL4.

IV. MySQL Database Setup (on database preparation server):

Notes:

1. Use MyISAM as the storage engine (specified in database table definitions: GMS_TABLES.sql, DMS_TABLES.sql, GEMS_TABLES.sql).

2. Use utf8 as the default character set for the Mysql database (also specified in database table definitions: GMS_TABLES.sql, DMS_TABLES.sql, GEMS_TABLES.sql).

3. Increase the value of max_connections Mysql system variable (in my.cnf) to greater than 100 (100 is the default).

The examples shown below are for using the MySQL command prompt via SSH.

1. Create empty databases

1. Login remotely (via SSH) to Mysql on the preparation server. Example:

   someuser@cril4:~$ mysql -usomeusername -p

2. Create empty databases. Example:

   mysql> CREATE DATABASE iris_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;
   mysql> CREATE DATABASE imis_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;
   mysql> CREATE DATABASE iwis3_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;

where: iris_myisam_yyyymmdd is the database for RICE data, imis_myisam_yyyymmdd for MAIZE data, and iwis3_myisam_yyyymmdd for WHEAT data

2. Define table structure and populate ICIS Core tables with data

"Core" = included in the standard ICIS schema (used by standalone applications).

Rice data

   mysql> use iris_myisam_yyyymmdd;
   mysql> source iris_myisam_yyyymmdd.sql;

Wheat data

   mysql> use iwis3_myisam_yyyymmdd;
   mysql> source iwis3_myisam_yyyymmdd.sql;

Maize data

   mysql> use imis_myisam_yyyymmdd;
   mysql> source imis_myisam_yyyymmdd.sql;

3. Define indices on ICIS Core tables

Index definition files for:

Genealogy Management System (GMS) tables: GMS_INDICES.sql
Data Management System (DMS) tables: DMS_INDICES.sql
Gene Management System (GEMS) tables: GEMS_INDICES.sql

Download here

Usage:

mysql> use <name of database>;
mysql> source GMS_INDICES.sql;
mysql> source DMS_INDICES.sql;
mysql> source GEMS_INDICES.sql;

4. Define UNIQUE indices on ICIS Core tables

Index definition files for:

Genealogy Management System (GMS) tables: GMS_UNIQUEKEYS_CENTRAL.sql
Data Management System (DMS) tables: DMS_UNIQUEKEYS.sql
Gene Management System (GEMS) tables: GEMS_UNIQUEKEYS.sql

Download here

Usage:

mysql> use <name of database>;
mysql> source GMS_UNIQUEKEYS.sql;
mysql> source DMS_UNIQUEKEYS.sql;
mysql> source GEMS_UNIQUEKEYS.sql;

5. Set the Scale Type (SCTYPE) in the SCALE table

Script for setting the Scale Type (continuous [C] /discrete [D]) is here.

Usage (as of Sept 8, 2009) :

mysql> use <name of database>;
mysql> source set_scale_sctype.sql;
mysql> CALL set_scale_sctype();

6. Create and populate warehouse tables (based on ICIS Core tables)

GMS:

1) wh_create_children.sql Download

(wh_der_children, wh_man_children)

Usage (as of Dec 22, 2009: preferred ID/unique sample ID removed from tables):

mysql> use <name of database>;
mysql> source wh_create_children.sql;
mysql> call activate_children_warehousing();  -- IN parameter removed 20091222

2) wh_create_germplasm_details.sql Download
(wh_germplasm_details)
Usage:

mysql> use <name of database>;
mysql> source wh_create_germplasm_details.sql;
mysql> call activate_wh_germplasm_details('crop installation');

...where: crop installation = 'IRIS' for rice, 'IWIS3' for wheat, 'IMIS' for maize.

3) create_passport.sql Download Usage:

mysql> use <name of database>;
mysql> source create_passport.sql;
mysql> call activate_create_passport('name of database');

DMS (per-dataset): create_icis_dms_warehouse.sql Download

(wh_datasets, wh_columns, wh_data, wh_countdatapoints)

Usage:

mysql> use <name of database>;
mysql> source create_icis_dms_warehouse.sql;
mysql> CALL activate_icis_dms_warehousing('name of database');

DMS-StudyMart (cross-dataset): create_cropfinder_warehouse.sql Download

Note: Facility for querying across datasets is now known as the "StudyMart".

(fieldsetup, factors, variates)

Usage:

mysql> use <name of database>;
mysql> source create_cropfinder_warehouse.sql;
mysql> CALL activate_cropfinder_warehousing('name of database');

(Threshold removed as of Nov 9, 2009)

7. Validate warehouse data against data in ICIS core tables

I. Count the number of records in every ICIS core table. Use icis_rec_count.sql Download

(This count (on the prep server) will be compared to the count (on the main server Castor) to see if database restoration was successful, at least at the record count level).

II. Compare the number of datapoints in the ICIS core tables with the number of datapoints in the warehouse tables. Use icis_datapoint_count.sql Download

V. Create mysqldump file of database on preparation server then transfer to main server

Rice data

    someuser@cril4:~$ mysqldump -uroot -p --disable-keys iris_myisam_yyyymmdd | gzip -9 > iris_myisam_yyyymmdd.sql.gz

Wheat data

   someuser@cril4:~$ mysqldump -uroot -p --disable-keys iwis3_myisam_yyyymmdd | gzip -9 > iwis3_myisam_yyyymmdd.sql.gz

Maize data

   someuser@cril4:~$ mysqldump -uroot -p --disable-keys imis_myisam_yyyymmdd | gzip -9 > imis_myisam_yyyymmdd.sql.gz

When transferring mysqldump files to Castor server:

Rice: Store compressed mysqldump files in /databases/ICIS_mysqldump/IRIS/iris_myisam_yyyymmdd/
Wheat: Store compressed mysqldump files in /databases/ICIS_mysqldump/IWIS3/iwis3_myisam_yyyymmdd/
Maize: Store compressed mysqldump files in /databases/ICIS_mysqldump/IMIS/imis_myisam_yyyymmdd/

VI. MySQL Database Setup (on main server (e.g. Castor)):

IMPORTANT:

A. Use utf8 as the default character set for the Mysql database and utf8_general_ci as the collation.

Example:

CREATE DATABASE iris character set utf8 collate utf8_general_ci;

B. Tuning MySQL through the my.ini file (Please contact a CRIL SysAd for assistance)

i. Increase the value of max_connections Mysql system variable (in my.cnf) to greater than 100 (100 is the default).

Example:

max_connections=200

ii. Set case insensitivity:

       lower_case_table_names=1

iii. Set collation upon connect (prevents "illegal mix of collations" error when using ICIS standalone applications)

[mysqld]
init_connect='SET collation_connection = utf8_general_ci'

*** Make sure to restart Mysql after modifying my.ini contents. ****

The examples shown below are for using the MySQL command prompt via SSH.

1. Create empty database

1. Login remotely (via SSH) to Mysql on the main server (eg. Castor). Example:

   someuser@castor:~$ mysql -usomeusername -p

2. Create empty databases. Example:

   mysql> CREATE DATABASE iris_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;
   mysql> CREATE DATABASE imis_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;
   mysql> CREATE DATABASE iwis3_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;

where: iris_myisam_yyyymmdd is the database for RICE data, imis_myisam_yyyymmdd for MAIZE data, and iwis3_myisam_yyyymmdd for WHEAT data

2. Restore the database

Use the mysqldump file created on the database preparation server.

Rice data

  someuser@castor:~$ time gunzip < iris_myisam_yyyymmdd.sql.gz | mysql -uroot -p iris_myisam_yyyymmdd;

Wheat data

  someuser@castor:~$ time gunzip < iwis3_myisam_yyyymmdd.sql.gz | mysql -uroot -p iwis3_myisam_yyyymmdd;

Maize data

  someuser@castor:~$ time gunzip < imis_myisam_yyyymmdd.sql.gz | mysql -uroot -p imis_myisam_yyyymmdd;

3. Setup user privileges

(Done by admin/root user only) Please contact Ching (m.habito@cgiar.org) or Jay (f.consolacion@cgiar.org) for assistance.

1. Create a MySQL user called datasourceuser

mysql> CREATE USER 'datasourceuser'@'%' identified by 'somepassword';

2. Grant privileges to user datasourceuser:

mysql> GRANT SELECT on iris_myisam_yyyymmdd.* TO 'datasourceuser'@'%' IDENTIFIED BY 'somepassword';
mysql> GRANT SELECT on iwis3_myisam_yyyymmdd.* TO 'datasourceuser'@'%' IDENTIFIED BY 'somepassword';
mysql> GRANT SELECT on imis_myisam_yyyymmdd.* TO 'datasourceuser'@'%' IDENTIFIED BY 'somepassword';

ICIS Data Warehouse Creation and Maintenance FAQs

From ICISWiki

Contents

What are the Data Warehouse tables needed by ICIS and when are they used?

Data Warehouse Tables for Pedigree Tree creation

Schema of wh_der_children

Schema of wh_man_children

Schema of wh_germplasm_details

How they are used

Data Warehouse Tables for single study dataset retrieval

Schema of wh_datasets

Schema of wh_columns

Schema of wh_data

Data Warehouse Tables for cross study queries

Schema of wh_data

Schema of wh_countdatapoints

Schema of factors

Schema of fieldsetup

Schema of variates

Creation: When are Data Warehouses tables generated?

Updating: When and how should the Data Warehouses be updated?

Implementation Details: Data Warehouse tables creation

How long does it take to generate data warehouse tables for ICIS databases?

Testing of script for data warehouse tables creation

How is an ICIS database prepared/updated?

I. Convert from MS Access to MySQL database (on local machine/PC)

II. Create a mysqldump file of your local Mysql database

III. Transfer the mysqldump file to the "database preparation server"

IV. MySQL Database Setup (on database preparation server):

1. Create empty databases

2. Define table structure and populate ICIS Core tables with data

Wheat data

Maize data

3. Define indices on ICIS Core tables

4. Define UNIQUE indices on ICIS Core tables

5. Set the Scale Type (SCTYPE) in the SCALE table

6. Create and populate warehouse tables (based on ICIS Core tables)

7. Validate warehouse data against data in ICIS core tables

V. Create mysqldump file of database on preparation server then transfer to main server

Rice data

Wheat data

Maize data

VI. MySQL Database Setup (on main server (e.g. Castor)):

1. Create empty database

2. Restore the database

Rice data

Wheat data

Maize data

3. Setup user privileges

Views

Personal tools

Navigation

Search

Toolbox