until recently , data mining required expensive and cumbersome data 
 mining software or a database expert who could accurately 
 translate a request for information into a functional , preferably 
 efficient , query .
database warehouses and online analytical 
 processing ( olap ) offer an attractive and readily available alternative .
as compared to a database , a data warehouse has faster retrieval 
 time , internally consistent data , and a construction   that allows 
 users to slice and dice ( ie , extract a single item ( slice ) and 
 compare items in a cross - tabulated table ( dice ) ) .
the primary 
 difference between a data warehouse and a traditional transaction 
 database lies in the volatility of the data .
the information in a 
 transaction database is constantly changing , whereas data in a 
 data warehouse is stable ; its information is updated at standard 
 intervals ( monthly or weekly ) .
a perfect data warehouse would be 
 updated to add values for the new time period only , without 
 changing values previously stored in the warehouse .
thus , 
 microarray databases can be data warehouses , because the data in 
 them is consistent and stable .
gene expression values in any given 
 experiment remain the same and usually only new data from new 
 experiments is added .
data warehousing software is incorporated in 
 most of the major relational database management systems such as 
 sqlserver2000 and oracle 9i .
olap represents a class of software that enables decision support 
 and reporting based upon a data warehouse
. a schematic 
 view of how olap software interacts with the data warehouse is 
 shown in figure 1 .
it is fast because most 
 system responses to users are delivered within 5 seconds , with the 
 simplest analysis taking no more than 1 second and very few taking 
 more than 20 seconds .
the key feature of olap is that it provides a 
 multidimensional , conceptual view of the data , including full 
 support for hierarchies and multiple hierarchies .
a cube is 
 defined by any number of data dimensions ; it is not limited to 
 three ; and sometimes an olap cube may have fewer than three 
 dimensions .
the data dimensions describe an olap cube just as 
 width , height , and depth describe a geometrical cube . 

where it is appropriate , dimensions can be organized into any 
 number of levels ( hierarchies ) . in relational database systems , olap cubes are constructed from a 
 fact table and one or more   dimension tables .
a fact table is the 
 relational table in the warehouse that stores the detailed values 
 for measures ( the thing you are measuring ) .
for example , this 
 could be the values for the relative change in gene expression . 

the dimension tables however are more abstract , containing only 
 one row for each leaf ( lower ) member in the fact table .
they are 
 used to create summaries and aggregates of the data in the fact 
 table .
ad hoc calculations and statistical analysis can also be 
 achieved , but are vendor specific .
analysis services 2000 ( used 
 here ) is capable of such ad hoc calculations on complex data .
the relationship between two dimensions can be modeled using a 
 grid as shown in table 1 .
dimensions are the labels 
 along the axes of the grid and each of the cells is a fact .
a cube is 
 designed to aggregate , analyze , and find trends in the measures . 

for example , if the cube describes relative gene induction , the 
 measure is the average relative expression level of a gene under 
 experimental conditions compared to control conditions , and the 
 cube is used to compute this average for the dimensions chosen . in 
 other words , the measure is the number that you would find in the grid cell .
levels may also contain other levels , 
 depending on how they are configured in the cube .
for example , in 
 table 1 which represents a two - dimensional cube from 
 our data warehouse designed to identify soybean cyst nematode 
 ( scn ) resistance - associated genes in soybean cultivars peking ( p ) 
 and kent ( k ) , the biosamples are considered one level under 
 k+/k 
 ( kent infected with scn versus uninfected ) , which in turn 
 is another level ( along with p+/p ; peking infected with scn 
 versus uninfected ) under the dimension probe combination .
a fact 
 describes the combination of the various dimensions , for example , 
 probe combination = p+/p , 
 gene = a01a10 , time = 6 hours yields a specific fact about the induction 
 of gene a01a10 in p+/p 6 hours after scn infection ( assuming 
 we added a third dimension of time ) .
this representation is just 
 like the ( x , y , z ) coordinate system in mathematics .
depending on 
 the way   the cube is being used , the fact may show a measure of 
 the induction of a gene at a specific biosample or the result of 
 the t test or some other differential gene expression test .
the mathematical 
 operations of count and sum are the primary reason why data 
 warehouses are useful .
calculated measures , such as average , can 
 be calculated from those two basic measures .
once dimensions are organized and a cube is being 
 processed , the aggregations are calculated .
generally , 
 aggregations are calculated immediately after the cube is 
 initially populated or when there is a change in the content of the cube .
dzeroski et al   used olap 
 on a database of patients with y chromosome deletions and found 
 correlations between deletion patterns and patient populations , as 
 well as clinical phenotype severity .
for example , silver et al 
  used olap to make business decisions that improved 
 operational efficiency of hospitals while maintaining high levels 
 of patient care .
hristovski et al   found olap to be a 
 suitable data mining tool for public health .
however , to the best 
 of our knowledge , olap has not been applied to gene expression databases .
we applied olap technology to our microarray warehouse , the 
 soybean genomics and microarray database ( sgmd ) , to mine 
 a time - course experiment aiming at discovering genes expressed in 
 soybean roots upon infection by the scn .
scn is the major pest of 
 soybean and causes an estimated loss of 1 $ billion in the 
 united states per year
. the discovery of genes expressed under 
 these conditions will provide scientists with information and 
 tools to develop soybean cultivars that are resistant to scn . 
 using olap
we identified numerous candidate genes and associated 
 pathways in a susceptible soybean cultivar ( kent ) after infection 
 with scn [ 7 , 
 8 ] . in comparison to traditional gene expression 
 data mining methods , such as k - means and self - organizing maps 
 ( som ) clustering ,
we used analysis services 2000 ( microsoft , redmond , 
 wash ) , a product that comes with sqlserver2000 , to build a 
 multidimensional cube of gene expression experiments conducted 
 over time ( figure 2 ) .
our fact table contained rows of 
 data describing clones and their fold induction at each time point 
 for each biosample and probe combination ( p+/p , k+/k ) .
the 
 measures from this fact table were the normalized log ratio from 
 lowess print - tip normalization , called norm , and the 
 count of unique clones printed ( called countelements ) .   a 
 calculated measure , named avgnorm ,
was created to represent the 
 average normalized log ratio from the two measures mentioned 
 above .
the first was experiments 
 ( exps ) , which had four levels , exp , biosample , time point , and 
 probe combination .
a second dimension , called cloneinfo , had two 
 levels , the clones i d 's and their names .
the third ( t test ) and 
 fourth ( timepoints ) dimensions had one level each , 
 induced char ( which refers to the results of the t test ) and 
 time point , respectively ( figure 2 ) .
cubes are very 
 flexible , new dimensions and measures can be added and removed to 
 customize the data analysis process , that is , the cube can be 
 configured to answer the scientific question at hand .
gene expression levels of approximately 6000 soybean genes were 
 measured at seven time points after scn infection .
the reference ( control ) sample was rna extracted from 
 soybean ( cultivar kent ) roots which is scn susceptible , not 
 infected with scn , and our treatment samples were rna extracted 
 from kent cultivar   6 hours , 12 hours , 24 hours , 2 days , 4 days , 6 
 days , and 8 days after infection with scn .
reverse labeling of 
 probes was conducted because the two dyes ( cy3 and cy5 ) may 
 not have the same labeling efficiencies and do not have exactly 
 the same correspondence between mrna concentration and fluorescent 
 intensities .
two replicated slides ( one of which is the dye swap ) were used for 
 each time point .
two biological samples were also used to account 
 for biological variation and inherent variation in the extraction 
 of mrna , generating a total of 7  2  2 = 28 slides 
 and 12 data points for each gene .
t tests were used to determine 
 differentially expressed genes at each of the time points 
 .
details on slide printing , hybridization , and scanning 
 protocols are described in alkharouf et al .
olap was used 
 to produce lists of common significantly induced / suppressed genes 
 at the early ( 6 , 12 , and 24 hours ) , mid ( 2 and 4 days ) , and late 
 ( 6 and 8 days ) time points .
we used results of the t test to 
 determine significance ( p  .05 ) and chose a cutoff value of 1.5 
 fold for extra stringency .
in addition , k - means and 2d som 
 clustering were applied on the time series data set .
k - means was 
 done using j - express version 2.0 ( molmine ; 
 http://www.molmine.com ) setting 
 k = 20 , initialization method to forgy , and distance metric to 
 euclidean .
som was done using the 2d som algorithm from genesight 
 version 3.5.2 ( biodiscovery ; http://www.biodiscovery.com ) , setting the number of horizontal 
 clusters to 5 , the number of vertical clusters to 5 , distance 
 metric to euclidean and clustering by genes only .
we used analysis services 2000 ( microsoft , redmond , 
 wash ) , a product that comes with sqlserver2000 , to build a 
 multidimensional cube of gene expression experiments conducted 
 over time ( figure 2 ) .
our fact table contained rows of 
 data describing clones and their fold induction at each time point 
 for each biosample and probe combination ( p+/p , k+/k ) .
the 
 measures from this fact table were the normalized log ratio from 
 lowess print - tip normalization , called norm , and the 
 count of unique clones printed ( called countelements ) .   a 
 calculated measure , named avgnorm ,
was created to represent the 
 average normalized log ratio from the two measures mentioned 
 above .
the first was experiments 
 ( exps ) , which had four levels , exp , biosample , time point , and 
 probe combination .
a second dimension , called cloneinfo , had two 
 levels , the clones i d 's and their names .
the third ( t test ) and 
 fourth ( timepoints ) dimensions had one level each , 
 induced char ( which refers to the results of the t test ) and 
 time point , respectively ( figure 2 ) .
cubes are very 
 flexible , new dimensions and measures can be added and removed to 
 customize the data analysis process , that is , the cube can be 
 configured to answer the scientific question at hand .
gene expression levels of approximately 6000 soybean genes were 
 measured at seven time points after scn infection .
the reference ( control ) sample was rna extracted from 
 soybean ( cultivar kent ) roots which is scn susceptible , not 
 infected with scn , and our treatment samples were rna extracted 
 from kent cultivar   6 hours , 12 hours , 24 hours , 2 days , 4 days , 6 
 days , and 8 days after infection with scn .
reverse labeling of 
 probes was conducted because the two dyes ( cy3 and cy5 ) may 
 not have the same labeling efficiencies and do not have exactly 
 the same correspondence between mrna concentration and fluorescent 
 intensities .
two replicated slides ( one of which is the dye swap ) were used for 
 each time point .
two biological samples were also used to account 
 for biological variation and inherent variation in the extraction 
 of mrna , generating a total of 7  2  2 = 28 slides 
 and 12 data points for each gene .
t tests were used to determine 
 differentially expressed genes at each of the time points 
 .
details on slide printing , hybridization , and scanning 
 protocols are described in alkharouf et al .
olap was used 
 to produce lists of common significantly induced / suppressed genes 
 at the early ( 6 , 12 , and 24 hours ) , mid ( 2 and 4 days ) , and late 
 ( 6 and 8 days ) time points .
we used results of the t test to 
 determine significance ( p  .05 ) and chose a cutoff value of 1.5 
 fold for extra stringency .
in addition , k - means and 2d som 
 clustering were applied on the time series data set .
k - means was 
 done using j - express version 2.0 ( molmine ; 
 http://www.molmine.com ) setting 
 k = 20 , initialization method to forgy , and distance metric to 
 euclidean .
som was done using the 2d som algorithm from genesight 
 version 3.5.2 ( biodiscovery ; http://www.biodiscovery.com ) , setting the number of horizontal 
 clusters to 5 , the number of vertical clusters to 5 , distance 
 metric to euclidean and clustering by genes only .
olap was used to drill down , slice , and dice the time series data 
 and find lists of genes induced and suppressed in each of the 
 specified time intervals ( table 2 ) .
olap was used to 
 find commonly induced or suppressed genes at two or more time 
 points and in one or more biosamples .
olap only took 2 
 to 5 seconds to return a result of a query after the cube was 
 constructed ( running on a 1.8  ghz pentium 4 workstation with 
 1  gb ram ) .
this is a fraction of the time needed to produce 
 similar reports from complex sql queries and multiple - table joins . 
 for instance selecting   statistically induced genes common to the 
 6- , 12- , and 24-hour time points , which requires 3-table joins , 
 took almost 25 seconds to achieve , whereas the same report took 
 only 1 second with olap running on the same system .
a common technique for viewing multidimensional output is to view 
 the output as a two - dimensional
this is a simple and informative technique to view the reports in 
 a spreadsheet - like manner .
multidimensional extensions ( mdx )   can 
 also be used to query cubes instead of using the user interface 
 mentioned above .
mdx is a syntax designed for querying 
 multidimensional objects and data and is more flexible than the 
 user interface .
it was used to query the cube and obtain the 
 results shown in table 2 .
mdx has a similar syntax to 
 sql , but is designed to work with multidimensional cubes instead 
 of   relational tables .
the olap reports highlighted a number of genes and defense 
 pathways that were triggered in soybean in response to scn 
 infection ( table 2 ) .
the key findings in the study were that the nematodes 
 elicit the activation of a transcription factor ( wrky ) that shuts 
 down a defense pathway known as the salicylic acid inducible 
 pathway , thereby rendering the plants more susceptible to nematode infection .
olap found a number of candidate resistance genes that k - means 
 and som did   not ( table 2 ) , whereas cluster analysis 
 did not reveal any new   information that olap did not identify by 
 mdx queries .
for instance olap found trehalose-6-phosphate 
 synthase ( tps ) induced at the mid time points , whereas cluster 
 analysis did not .
tps is a key enzyme of sugar metabolism and its 
 induction at the mid time points , where the nematode has formed 
 the syncytium ( feeding site ) , may be an indicator of the 
 parasite 's success in utilizing the plants metabolite synthesis 
 apparatus for its own sustenance .
metabolic profiling experiments 
  conducted in collaboration with the noble foundation also show increased levels of trehalose 
 in kent 48 hours after infection with scn ( unpublished data ) .
olap 
 also found jasmonic acid ( ja ) inducible genes , such as 
 pathogenesis - related protein pr-6 and chalcone synthase , induced 
 at the early and mid time points   whereas cluster analysis did 
 not .
the ja signaling pathway is known to be induced in plants 
 after wound damage or parasitic infection . generally , we found olap a lot more powerful for determining genes 
 induced at specific time intervals but not at other time points . 

this was hard to do using cluster analysis , because the algorithms 
 are designed to group genes with similar profiles , not 
 necessarily to identify genes induced uniquely at one time point , 
 but not at others .
this explains why none of the genes found 
 uniquely induced at the early , mid , or late time points were 
 identified by cluster analysis ( table 2 ) . finding 
 these genes is important for the dissection of the metabolic 
 effects of the nematode invasion across time . in terms of speed , olap took approximately 1.2 minutes to generate 
 all the reports summarized in table 2 and are shown in 
 their entirety on 
 http://psi081.ba.ars.usda.gov / sgmd / publications / olap/. in contrast , it took 5 times longer ( approximately 6.5 minutes ) to 
 do one of the cluster analysis methods ( including the time it 
 takes to export the data from the database to the respective 
 clustering software in the required format ) .
if one were to also 
 measure the time it takes to interpret the olap reports versus the 
 clustering results , olap would be even at a more advantage point , 
 because it makes the results easier to interpret
. results of the 
 cluster analysis can also be accessed from the web site mentioned above .
gene expression data is valuable for the understanding of gene 
 regulation and biological networks .
a main goal of gene expression 
 data analysis is to determine what genes are expressed as a result 
 of a certain cellular state , that is , what genes are expressed in 
 diseased cells that are not expressed in healthy cells .
microarray 
 experiments profile hundreds to thousands of genes at a time 
 generating large data sets that are only getting bigger as more 
 advances in genomics and microtechnologies are made .
as these data 
 sets become larger , however , the need for fast and effective 
 database mining tools becomes more obvious and necessary .
data 
 warehouses and olap provide tools to construct , populate , view , 
 and access microarray data in an efficient and fast manner .
the 
 fundamental unit of olap software is the cube , which is a 
 repository of integrated information from the existing data sources . in our cube design
the data sources were the relational tables in 
 sgmd , a gene expression database .
microarray databases 
 are in fact data warehouses because of their consistent and stable 
 data , and little if any modifications to the database model need 
 to be made to use olap .
olap proved to be more efficient than 
 standard relational database queries that rely on time - consuming 
 multitable joins .
although the results obtained from olap and 
 these standard sql queries are the same , the time it takes to 
 execute an olap query was found to be 25 times greater than standard sql queries .
olap provides a different view of the data compared to cluster 
 analysis and provides additional insights into the data as shown 
 in table 2 .
one reason is the 
 large number of genes of an unknown function that makes such 
 cluster analysis difficult to interpret .
olap avoids this issue 
 because it allows for the categorization of genes into categories 
 of known and unknown functions , thereby reducing the complexity of 
 the problem by allowing investigators to analyze genes with a 
 known function first .
another benefit of olap is that the values 
 of the clustered elements do not all have to be the same unit , as 
 they are in cluster analysis .
this is useful when searching for 
 trends across a heterogeneous data set . in olap
, you can set any 
 type or number of dimensions to drill your data with , thereby 
 identifying trends that can not be identified using cluster analysis .
olap 's main advantage is that it is flexible and can be customized 
 to answer the scientific question at hand if some prior knowledge 
 is known about the data sets , whereas cluster analysis is mostly 
 used as an initial data mining tool with no prior knowledge and is 
 used mainly for grouping genes based on similar expression 
 profiles .
the genes that are clustered together however can vary 
 considerably because of the different similarity metrics that are 
 used .
another issue with clustering is that a gene can be 
 characterized in more than one way , while it can belong to only 
 one cluster .
olap allows scientists , especially those not trained 
 in the computational sciences , to mine their data sets to not only 
 group genes based on their expression profiles but to also ask 
 specific scientific questions such as  give me the genes induced 
 at a certain time point , that is , not induced at all other time 
 points , or the genes induced at time point a that are also 
 induced at time points b , and c ,  for instance .
the answers 
 to these questions can provide valuable insights into the 
 relationships between genes and pathways that cluster analysis can not answer . in the case of our data set , for instance , seeking resistance 
 genes induced at specific time points yielded a number of 
 candidate resistance genes and gave us insights into the metabolic 
 changes in soybean when infected with scn .
thus olap is an 
 automation of the manual analysis that most biologists would 
 always perform rather than relying on visually appealing but 
 scientifically uninformative cluster analysis .
we are not 
 suggesting that olap is better than cluster analysis , but only 
 that the two methods are useful and quite different .
we are 
 suggesting however that olap can be considered as a supplement or 
 even an alternative to cluster analysis when clustering methods 
 are not suitable to analyze a data set , such as small time - course data sets as ours .
the implementation of olap technology to gene expression analysis 
 is not difficult given the right tools .
olap can be applied to any 
 gene expression database built on any of the major relational 
 database management systems ( oracle , sybase , mysql , or even 
 access ) , through the use of ole db ( an industry standard 
 technology for database connectivity ) .
olap reports can also be 
 obtained using excel 's ( microsoft , redmond , wash ) pivot tables , a 
 feature that allows one to cross - tabulate columns in excel .
olap 's ability to drill 
 through the data and find common / unique genes given different 
 criteria , along with its flexibility , make it an important data 
 mining tool in gene expression analysis , one that holds great 
 promise in our view .
this study also demonstrates that databases and database 
 applications may not be used solely for the storage and retrieval 
 of expression data but that they can act as tools for doing 
 exploratory data analysis as well .
in fact databases   can 
 eliminate the need for third - party software , because most of the 
 analysis , even time series analysis , and can be done within the database itself .
olap provides efficient and easy - to - use reporting tools 
 and graphical interface , to enable users to mine a data warehouse 
 for hidden information . a snapshot of a multidimensional cube of gene expression 
 data constructed in microsoft 's analysis services 2000 ( shipped 
 with sqlserver2000 ) .
( a ) shows the dimensions of the cube and 
 their associated levels , ( b ) is the fact table , and ( c ) shows the dimension tables .
example , probe combination and genes are dimensions ; p+/p , 
 k+/k , biosample 1 , biosample 2 , a01a10 , ssh1b07 , d09h12 , and 
 b03c02 are levels of the respective dimension . the cells containing various figures are facts . individual data in the fact 
 cells are the values of the measures . in this example , there are two measures used in the cube , one is the fold induction , the second is the result of the t test ( 1 significantly induced , 1 significantly suppressed , 0 unchanged ) .
genes found to be induced at different 
 time intervals using olap , k - means , and som clustering .
many of 
 the key candidate resistance genes were identified by olap and not 
 cluster analysis , in particular those genes induced at specific 
 time intervals and not others .  
 gene expression databases contain a wealth of information , but 
 current data mining tools are limited in their speed and 
 effectiveness in extracting meaningful biological knowledge from 
 them . 
 online analytical processing ( olap ) can be used as a 
 supplement to cluster analysis for fast and effective data mining 
 of gene expression databases . 
 we used analysis services 2000 , a 
 product that ships with sqlserver2000 , to construct an olap cube 
 that was used to mine a time series experiment designed to 
 identify genes associated with resistance of soybean to the 
 soybean cyst nematode , a devastating pest of soybean . 
 the data for 
 these experiments is stored in the soybean genomics and microarray 
 database ( sgmd ) . 
 a number of candidate resistance genes and 
 pathways were found . 
 compared to traditional cluster analysis of 
 gene expression data , olap was more effective and faster in 
 finding biologically meaningful information . 
 olap is available 
 from a number of vendors and can work with any relational database 
 management system through ole db . 
INTRODUCTION
METHODS
Cube construction
Microarray data
RESULTS
DISCUSSION
Figures and Tables