Tutorial on GRID Computing EMBnet Conference 2008 The

Tutorial on "GRID Computing“ EMBnet Conference 2008 The LIBI Federated database “GRID Computing” Tutorial 17 September 2008 CNR - ITB http: //www. libi. it

Agenda • Bioinformatic Data integration issues in grid environments • Data Federation in the LIBI platform • Tutorial goals: – a simple case study for querying the federated DB – Designing a data abstraction model on bioinformatic information: the DDQB application Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

Issues Concerning Data Integration • In the bioinformatics’ domain, an increasing number of grid applications manage data at very large scales of both size and distribution. • The complexity of data management on a grid arises from the scale, dynamism, autonomy, heterogeneity and distribution of data sources. Mission to accomplish: • The goal is providing an IT layer that allows grid applications to access data without taking into account issues such as those exposed before (large scale distribution, dynamism, heterogeneity, etc. ) Viable approaches: • Data Federation: data are logically integrated • Data Warehouse: data are physically integrated Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

Data Federation vs Data Warehouse DF DW • Data warehousing ‘cleaning up’ data and placing it into a centralized repository works well in situations where data are relatively static and data types are not too different • Moving data into a warehouse can limit the specialized search capabilities available with (through) the original data source. • Building and maintaining enterprise wide warehouse on the scale required by most large research organizations with hundreds of data sources can be both costly and risky to implement. • Data warehouse centralization clash with the basic grid-concepts of data replication and distribution according to monitored statistics • Data federation allow to access current data from multiple, heterogeneous, dislocated data sources simultaneously, with a single query For bioinformatic problems Data Federation seems the most promising solution Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

Data Federation Layer Name LIBI Federated Database Institution IBM Innovation Lab – Bari Service IBM-DB 2@213. 26. 249. 183 Content Relational DB federating Mito. Res, UTRef, UTRSite, Pubmed, Gen. Bank, OMIM, Uniprot, Hmt. DB, EMBL_CDS Description This database federates local and remote resources to provide a uniform, standard interface to access data. This federated DB consist of a rationalized composition of both ITB-owned DBs (Mito. Res, UTRef, UTRSite) stored locally, of the resource Hmt. DB owned by Biology Dept. of Bari University, and public NCBI DBs accessed remotely (Pubmed, Gen. Bank, OMIM). The Uniprot DB replicated locally has been federated as well. Federation provides unique inter-database relationship features that enable users to discover and extract relevant pieces of information with a single query, even if they would be originated from different DBs. Besides this prominent advantage for the LIBI-platform and its end-users, federated DB provides developers and service consumers with a homogeneous, standard interface (the SQL language) to access data stored locally and/or remotely. Availability For LIBI users and services Access DB 2 DRDA service available at Main components IBM DB 2, Web. Sphere Federator Server (with relational & nonrelational wrappers) Custom components EMBL/FASTA wrapper Average user access Tens of concurrent users DBMS IBM DB 2 9. 1 Query language SQL Local DBs Mito. Res, UTRef, UTRSite, Uniprot, EMBL_CDS 213. 26. 249. 183 Remote DBs Pubmed, Gen. Bank, OMIM, Hmt. DB Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

Accessing Federated Data: Web. Sphere Federator Server and related activities Web. Sphere Federator Server enables federated databases to access heterogeneous and distributed (local and remote) data sources. It provides a unified data-management interface (for query and insert): the SQL language Other features of the federated DB: § DB user-defined functions have been built to accomplish bioinformatics-specific data handling and analyses § A web graphical interface to access federated DB information has been made available (DDQB) § We are developing new interfaces to expose Federated DB capabilities as Web Services Collaborations: § SPACI developed a wrapper to the DB 2 for GRel. C, to access the federated DB from Grid environments Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

Data sources integrated in the Federated LIBI Schema DBName Data source location Data source connection/type Gen. Bank Web Service s Pub. Med Web Service s Mitores My. SQL OMIM Web Service s UTRef My. SQL UTRSite My. SQL Hmt. DB Species 2000 DB 2 Uniprot LEGENDA relational database flat file web services Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008 Web Service s

Overall Federated DB Schema UTRef Mito. Res UTRSite Gen. Bank OMIM PUBMED Hmt. DB Uniprot EMBL_CDS Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

TUTORIAL: a simple case study involving federated DB (1/4) Need Studying the regulation of the expression of the topoisomerase I at m. RNA level. Biologist needs to retrieve information about regulation of expression of the topoisomerase I. To this end she decides to investigate if there are UTR sequences responsible for the regulation of the expression of the corresponding gene and retrieve pieces of information about the regulatory motif Pieces of information to retrieve • Mitores Entry Accession number • Mitores Entry product description • Mitores Gene name • UTRef Accession number • UTRef UTR Type • UTRSite accession number • UTRSite Standard name Involved Databases Mito. Res UTRef Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008 UTRSite

TUTORIAL: a simple case study involving federated DB (2/4) UTRef Mito. Res UTRSite Gen. Bank OMIM PUBMED Hmt. DB Uniprot EMBL_CDS Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

TUTORIAL: a simple case study involving federated DB (3/4) UTRef Mito. Res UTRSite Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

TUTORIAL: a simple case study involving federated DB (4/4) Need Studying the regulation of the expression of the topoisomerase I at m. RNA level. Biologist needs to retrieve information about regulation of expression of the topoisomerase I. To this end she decides to investigate if there are UTR sequences responsible for the regulation of the expression of the corresponding gene and retrieve pieces of information about the regulatory motif Pieces of information to retrieve • Mitores Entry Accession number • Mitores Entry product description • UTRef Accession number Involved Databases Mito. Res • UTRef Type • UTRSite accession number • UTRSite Standard name Under the cover UTRef UTRSite SQL Query SELECT DISTINCT "t 1". "DESCRIPTION" AS "Description", "t 2". "MITONUC_ID" AS "ID", "t 3". "UTRDB_ID" AS "UTRef ID", "t 3". "TYPE" AS "UTR type", "t 4". "NAME" AS "Gene name", "t 5". "UTRSITEID" AS "UTRSite ID", "t 5". "STANDARDNAME" AS "Standard name" FROM "LIBI". "MITONUC_GENE" "t 2" LEFT JOIN "LIBI". "MITONUC_GENE_PRODUCT" "t 6" ON "t 2". " GENE_ID" = "t 6". "GENE_ID" RIGHT JOIN "DDQB". "MITONUC_PRODUCT_DDQB" "t 1" ON "t 6". " PRODUCT_ID" = "t 1". "PRODUCT_ID" LEFT JOIN "LIBI". "MITONUC_GENE_MRNA" "t 7" ON "t 2". "GENE_ID" = "t 7". "GENE_ID" RIGHT JOIN "DDQB". "MITONUC_MRNA_DDQB" "t 8" ON "t 7". " MRNA_ID" = "t 8". "MRNA_ID" LEFT JOIN "DDQB". "MITONUC_UTR_VIEW" "t 3" ON "t 8". "MRNA_ID" = "t 3". "MRNA_ID" LEFT JOIN "LIBI". "UTREF_UTR" "t 9" ON "t 3". "UTRDB_ID" = "t 9". "ACCESSION" LEFT JOIN "LIBI". " UTREF_SIGNAL" "t 10" ON "t 9". "ACCESSION" = "t 10". "ACCESSION" LEFT JOIN "DDQB". "UTRSITE_DDQB" "t 5" ON "t 10". "UTRSITEID" = "t 5". "UTRSITEID" LEFT JOIN "LIBI". "MITONUC_GENE_NAME" "t 11" ON "t 2". " GENE_ID" = "t 11". "GENE_ID" RIGHT JOIN "LIBI". "MITONUC_GENE_NAME" "t 4" ON "t 11". "GENE_NAME_ID" = "t 4". "GENE_NAME_ID" WHERE UPPER("t 1". "DESCRIPTION") LIKE ' %TOPOISOMERASE I%' Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008

Modeling a data abstraction in bioinformatics: DDQB IBM Data Discovery and Query Builder (DDQB) is a powerful search tool with a graphical interface that enables users with various levels of expertise to easily configure queries and leverage the full spectrum of information assets. By means of DDQB researchers can query the federated DB not in term of its physical fields, but in term of more abstract entities arranged into taxonomies that have been specifically developed for LIBI users, so queries become tasks closer to their research subjects than to informatics activities. For the DDQB tutorial: http: //213. 26. 249. 183/ Tutorial on "GRID Computing“, EMBnet Conference 2008, 17 September 2008