<%@ Language=JavaScript %> <% var page=Request.ServerVariables("PATH_INFO"); var refPage=Request.ServerVariables("HTTP_REFERER"); var ip=Request.ServerVariables("REMOTE_ADDR"); var browser=Request.ServerVariables("HTTP_USER_AGENT"); var host=Request.ServerVariables("HTTP_HOST"); var serverSW=Request.ServerVariables("SERVER_SOFTWARE"); var serverPR=Request.ServerVariables("SERVER_PROTOCOL"); var root_path= ""+Request.ServerVariables("PATH_TRANSLATED"); root_path=root_path.substr(0,root_path.lastIndexOf("\\")+1); var OBJconn= Server.CreateObject("ADODB.Connection"); OBJconn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + root_path + DBpath; OBJconn.Open(); var OBJrs = Server.CreateObject("ADODB.Recordset"); OBJrs.Open("Accesses",OBJconn,adOpenForwardOnly,adLockPessimistic,adCmdTable); OBJrs.AddNew(); OBJrs("PATH_INFO")=page; OBJrs("HTTP_REFERER")=refPage; OBJrs("REMOTE_ADDR")=ip; OBJrs("HTTP_USER_AGENT")=browser; OBJrs("HTTP_HOST")=host; OBJrs("SERVER_SOFTWARE")=serverSW; OBJrs("SERVER_PROTOCOL")=serverPR; OBJrs.Update(); OBJrs.Close(); OBJconn.Close(); delete OBJrs; delete OBJconn; %> Bioinformatics software - GAAS: Gene Array Analyser Software [data management]

GAAS - Data Management

Definition of a new input structure template for a gene expression dataset

With MS-Access open the database file MasterDB.mdb in the GAAS/SystemDB directory;

In the Table view panel select and open (left mouse button - double click) the table named InputStructure;

Select the table content by clicking (left mouse button - single click) the left upper grey square in the table; all table rows will be highlighted; then click the right button;

in the popup menu that appears select and click 'new record '; complete the fields of the new inserted row as follow:

in the StructID field (required) insert an alphanumeric code starting with an A (8 characters); if you insert a code already present the MS-Access warns you;

in the StructInName field (required) insert a name for the new input structure template you are creating;

in the DataTypeID field (required) insert 'D0000001' if you are creating a new input structure template for microarray data, else if the new template is for nylon filter data insert 'D0000000';

in the Right-Left field (required) check the box if in the dataset for each experiment data have been split into two files (one for left and one for right side of the analyzed array);

in the BkgrAnalisysbyPrimary field (required) check the box if in the dataset spot data have been grouped in substructures, called primary, and a background is provided for each primary (all spots in the same primary have the same background value - equal to the primary background value);

in the NameColumnsAtRow field (required) insert the index corresponding to the row in the MS-excel data file where data begin (first row : '0', second row : '1', third row : '2', ...);

in the Location field (required) insert the name of the column in the MS-excel raw data file containing spot location on the array; if the spot location is identified by more than one column insert in the Location field the column names separated by the symbol '$';

in the following fields it must be defined how spot and background intensities have been measured and coded in the raw dataset.

Case 1: spot and background intensities have been integrated on the spot area and the spot area (pixel or mm2); with the corresponding column names in the raw dataset fill in the following fields:
SumSpotIntensity
SpotArea
SumBkgrIntensity
BkgrArea
Case 2: spot and background intensities has been measured per unit area; with the corresponding column names in the raw dataset fill in the following fields:
MeanSpotIntensity
MeanBkgrIntensity
Case 3: spot intensities have been already subtracted by background intensity and in the raw dataset they are expressed per unit area; with the corresponding column name in the raw dataset fill in the field:
MeanSpotIntensity-MeanBkgrIntensity
Case 4: spot intensities have been already subtracted by background intensity and in the raw dataset they are expressed multiplied by the spot area; with the corresponding column name in the raw dataset fill in the field:
MeanSpotIntensity-MeanBkgrIntensityXSpotArea

The remaining fields in the InputStructure table are optional.

StdSpotIntensity (optional): if the raw dataset contains the standard deviations of spot pixel density values, fill in this field with the raw dataset column name containing these values;

SpotMedianDensityVariance (optional): if the raw dataset contains the median values of the absolute deviation of spot pixel density values (a measure of the variation around the median density value of the spot), fill in this field with the raw dataset column name containing these values;

PercSpotPixelExcluded (optional): if the raw dataset contains the percentages of the removed spot pixels with too low or too high intensities, fill in this field with the raw dataset column name containing these values;

Signal2NoiseRatio (optional) if the raw dataset contains signal to noise ratios - for example calculated as spot intensity (MeanSpotIntensity) minus background intensity (MeanBkgrIntensity) divided by standard deviation of background intensities - fill in this field with the raw dataset column name containing these ratios;

SpotExcluded (optional): if the raw dataset contains the flags that a user can manually setup to exclude spot values from data processing fill in this field with the raw dataset column name containing these flags.

Definition of a new label structure template for a gene expression dataset

With MS-Access open the database file MasterDB.mdb in the GAAS/SystemDB directory;

In the Tables view panel select and open (left mouse button - double click) the table named LabelStructure;

Select the table content by clicking (left mouse button - single click) the left upper grey square in the table; all table rows will be highlighted; then click the right button;

in the popup menu that appears select and click 'new record'; complete the fields of the new inserted row as follow:

in the LabelStructID field (required) insert an alphanumeric code starting with LS (8 characters); if you insert a code already present the MS-Access warns you;

in the DataTypeID field (required) insert 'D0000001' if the new label structure is for microarray data, else insert 'D0000000' if the new structure is for nylon filter data;

in the QueryName field (optional) insert the name of the query in the Label database to extract a LabelTable for data in exam; this need to be filled in only if the Label database contains more tables with all the data of all clone library from which have been taken the clone spotted on the array in exam; if the Label database already contains the LabelTable, leave this field empty;

in the TableName field (required) insert the name of the LabelTable; for microarray data this table is created by GAAS inside the database containing the pre-processed dataset, starting from the above cited query into the Label database;

in the CloneLocation field (required) insert the name of the column in LabelTable containing the spot location on the array; if the spot location is identified by more than one column insert in the Location field the column names separated by the symbol '$';

in the MasterIDClone field (required) insert the name of the column in the pre-processed dataset containing the unique clone location identifier;

in the CloneLabel field (required) insert the name of the column in the LabelTable containing the clone labels;

in the CloneAccession field (required) insert the name of the column in the LabelTable containing the clone accession number;

in the CloneID field (required) insert the name of the column in the LabelTable containing the clone main identifier;

in the CloneDescription field (required) insert the name of the column in the LabelTable containing the clone description;

in the CloneValidity field (optional) insert the name of the column in the LabelTable containing flag values for the clone validity;

in the FluorochromeRatioCalibration field (optional - used for microarray data) insert the name of the column in the LabelTable containing calibration ratios between two fluorochrome intensities;

in the FluorochromeRatioCalibration field (optional - used for microarray) insert the name of the column in the LabelTable containing the calibration values of single fluorochrome intensities;

in the CloneID1 field (optional) insert the name of the column in the LabelTable containing the first of secondary clone identifiers;

in the CloneID2 field (optional) insert the name of the column in the LabelTable containing the second of secondary clone identifiers;

in the CloneID3 field (optional) insert the name of the column in the LabelTable containing the third of secondary clone identifiers.

Creation of a new clone filter for a gene expression dataset

In the table FilterCloneType in the MasterDB system database are coded the queries used to filter clones according to their characteristics described in the label table that must be provided along with input datasets.
Using examples, following it is described how creating clone filters based on SQL queries. Two examples are given, the first based on a simple SQL query, can be coded on a row in the FilterCloneType MasterDB table; the second, based on a more articulated query, must be coded on more rows in the FilterCloneType MasterDB table.

Example 1: Create a clone filter for selecting clone labelled as 'DISCARD' in the Label table. This filter can be used to identify the clone values to be excluded a priory from the data analysis. The SQL query that must be codified in the FilterCloneType table of MasterDB is: SELECT * FROM [LabelTable] WHERE [CloneLabel] = 'DISCARD'

With MS-Access open the database file MasterDB.mdb in the GAAS/SystemDB directory;

In the Tables view panel select and open (left mouse button - double click) the table named FilterCloneType;

Select the table content by clicking (left mouse button - single click) the left upper grey square in the table; all rows will be highlighted; then click the right button;

in the popup menu that appears select and click 'new record'; complete the fields of the new inserted row as follow:

in the CloneTypeID field (required) insert an alphanumeric code starting with an A (8 characters); if you insert a code already present the MS-Access warns you;

in the DataName field (required) insert a name for the new filter is been creating (e.g. 'MyFilterDiscard');

in the UserID field (required) insert the ID number of the user creating the filter (find this number in Users table);

in the Mode field (optional) insert the string 'public' if you want the filter to be used also by other user besides the one creating it;

in the LabelStructID field (required) insert the alphanumeric code (find it in the LabelStructure table) of the label structure corresponding to the LabelTable containing the description of the clone characteristics used to filter out clones;

in the ColumnType field (required) insert the type of value describing the clone characteristic to be considered (in the example we want to filter clones according to their label, thus the label value type is a string (i.e. 'DISCARD') and it must be inserted in this field 'dbText'; if the clone characteristic was an integer value, it should be inserted 'dbInteger';

in the Order field (required) put '0' (first or single level condition) in case the row of the FilterCloneType table is been filling in codifies for a first level condition in the SQL query of the filter is been creating;

leave Condition field (optional) blank; this field is filled in case of articulated multi-conditional queries with the logical operator to be used in the query to joint two subsequent condition (e.g. 'AND', 'OR', ...);

in the ColumnName field (required) insert the column name (e.g. 'CloneLabel') in the table LabelTable containing the values to be considered to filter out the desired clones;

in the evaluation field (required) insert the matching operator to be used to compare clone characteristic values with wanted values (e.g. insert '=' for exact matching);

in the FilterValue field (required) insert the clone characteristic values of the clone to be filtered out (e.g. 'DISCARD' as the clone label value of the clone to be left out from the data analysis);

in the IDFiltername field (required) insert the alphanumeric code corresponding to the kind of filter is been defining; select this code from the table FilterName in MasterDB (e.g. select the code 'FN000000' (marker) when the defined filter is for discarding the filtered clones from the data processing).

Example 2: Create a clone filter for selecting clones labelled as 'Norm1' in the LabelTable and spotted from control plates identified with 'CONTROL' like labels in the LabelTable. This filter can be used to identify the clone values to be used for normalization. The SQL query that must be codified in the FilterCloneType table of MasterDB is: SELECT * FROM [LabelTable] WHERE ([CloneLabel] = 'Norm1' AND [PlateID] like '*CONTROL*')
In this case to codify this more articulated query, two rows must be added in the table FilterCloneType.

With MS-Access open the database file MasterDB.mdb in the GAAS/SystemDB directory;

In the Tables view panel select and open (left mouse button - double click) the table named FilterCloneType;

As follow add first row codifying the articulated query.

Select the table content by clicking (left mouse button - single click) the left upper grey square in the table; all rows will be highlighted; then click the right button;

in the popup menu that appears select and click 'new record'; complete the fields of the new inserted row as follow:

in the CloneTypeID field (required) insert an alphanumeric code starting with an A (8 characters); if you insert a code already present the MS-Access warns you;

in the DataName field (required) insert a name for the new filter is been creating (e.g. 'MyNormalizationClones');

in the UserID field (required) insert the ID number of the user creating the filter (find this number in Users table);

in the Mode field (optional) insert the string 'public' if you want the filter to be used also by other user besides the one creating it;

in the LabelStructID field (required) insert the alphanumeric code (find it in the LabelStructure table) of the label structure corresponding to the LabelTable containing the description of the clone characteristics used to filter out clones;

in the ColumnType field (required) insert the type of value describing the clone characteristic to be considered (in the example we want to filter clones according to their label, thus the label value type is a string (i.e. 'Norm1') and it must be inserted in this field 'dbText'; if the clone characteristic was an integer value, it should be inserted 'dbInteger';

in the Order field (required) put '0' (first or single level condition) in case the row of the FilterCloneType table is been filling in codifies for a first level condition in the SQL query of the filter is been creating;

leave Condition field (optional) blank; this field is filled in case of articulated multi-conditional queries with the logical operator to be used in the query to joint two subsequent condition (e.g. 'AND', 'OR', ...);

in the ColumnName field (required) insert the column name (e.g. 'CloneLabel') in the table LabelTable containing the first values to be considered to filter out the desired clones;

in the evaluation field (required) insert the matching operator to be used to compare the wanted values with the values of the first clone characteristic considered (e.g. insert '=' for exact matching);

in the FilterValue field (required) insert the clone characteristic values of the clone to be filtered out (e.g. 'Norm1' as the clone label value of the clones to be used in the data normalization processing);

in the IDFiltername field (required) insert the alphanumeric code corresponding to the kind of filter is been defining; select this code from the table FilterName in MasterDB (e.g. select the code 'FN000003' (normalization) when the defined filter is for selecting clones whose intensity values are to be used in the data normalization processing).

As follow add second row codifying the articulated query.

Select the table content by clicking (left mouse button - single click) the left upper grey square in the table; all rows will be highlighted; then click the right button;

in the popup menu that appears select and click 'new record'; complete the fields of the new inserted row as follow:

in the CloneTypeID field (required) insert an alphanumeric code starting with an A (8 characters); if you insert a code already present the MS-Access warns you;

in the DataName field (required) insert for the new filter is been creating the same name as in the first codifying row (i.e. 'MyNormalizationClones');

in the UserID field (required) insert for the user creating the filter the same ID number as in the previous codifying row;

in the Mode field (required) insert the same value as in the previous codifying row ('public' if you want the filter to be used also by other user besides the one creating it);

in the LabelStructID field (required) insert the same alphanumeric code as in the previous codifying row;

in the ColumnType field (required) insert the type of value describing the clone characteristic to be considered (in the example we want to filter clones according to the label of the plate from where they were spotted, thus the label value type is a string (e.g. 'CONTROL') and it must be inserted in this field 'dbText'; if the clone characteristic was an integer value, it should be inserted 'dbInteger';

in the Order field (required) put '1' (second level condition) in case the row of the FilterCloneType table is been filling in codifies for a second level condition in the SQL query of the filter is been creating;

in the Condition field (optional) place 'AND'; this field is filled in case of articulated multi-conditional queries with the logical operator to be used in the query to joint two subsequent condition (e.g. 'AND', 'OR', ...);

in the ColumnName field (required) insert the column name (e.g. 'PlateID') in the table LabelTable containing the second values to be considered to filter out the desired clones;

in the evaluation field (required) insert the matching operator to be used to compare the wanted values with the values of the second clone characteristic considered (e.g. insert 'like' for similar matching; like operator is used in conjunction with place holding characters - as '*' for 'whatever sequence of characters', thus 'val*' stays for 'what ever sequence of characters starting with "val"');

in the FilterValue field (required) insert the clone characteristic values of the clone to be filtered out (e.g. '*CONTROL*' as the plateID label value for the clones spotted from plates with a label containing the character string 'CONTROL');

in the IDFiltername field (required) insert the same alphanumeric code as in the previous codifying row, corresponding to the kind of filter is been defining.

© Marco Masseroli, PhD masseroli@biomed.polimi.it - Last update on .

!!Attention!!

This page contains JavaScript code that you cannot execute either because your browser does not allow it (version too old!!), or because it does not have enable the function to execute JavaScript. In the last case enable it to view more on this page.