<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi Andrea,<div>Please find attached a link to the xml files used to create 6 of the 7 Ensembl marts for release 61 (the sequence mart is created using a script so there is no xml to send). The xmls are for the four visible marts on the interface (Ensembl Gene, Ensembl Variation, Ensembl Regulation and Vega mart) as well as two marts that the user does not see on the mart interface, but which are accessed via the visible marts (ontology mart, genomic features mart). We are currently using Biomart version 0.7. As Arek mentioned, the Ensembl marts are quite complex and we have a few code hacks in place as well as pre and post build patches to run in order to create the databases. Therefore it is probably best if you use the martbuilder tool to see how the mart schemas are created from the core databases and then play with a simple schema and see how you get on. </div><div>Kind regards</div><div>Rhoda</div><div><br></div><div>The xml files can be found here:</div><div><a href="http://www.ebi.ac.uk/~rhoda/v61_xml/">http://www.ebi.ac.uk/~rhoda/v61_xml/</a></div><div><br></div><div><br></div><div><br></div><div><div>On 12 Mar 2011, at 19:45, Arek Kasprzyk wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size: 14px; font-family: Calibri, sans-serif; "><div><div><div><br></div><div>Hi Andrea,</div><div>Ok, I have a better idea now what you want. The situation is as follows: 0.8 rc5 has automated and integrated nicely a lot of workflows needed to create a new mart from a source schema. However the particular Ensembl core transformation is a very complex one and rc5 still has only a rudimentary support for that. If you just want to have an idea how the algorithm works it is better to start with a simpler use case not Ensembl mart . It will be difficult to recreate the exact Ensembl mart transformation from scratch for two reasons: rc5 has still a very rudimentary support for this pariticular schema so you will not get far. The 0.7 fully supports it but thereis a large number of 'tweaks aka hacks' to the transformation algorithm to get certain things to work so you will find difficult to recreate a lot of them.</div><div><div>I would advise you to play with any schema to get a few datasets to work (ensembl core schema is fine to play with too). If you want to build new marts and integrate them with ensembl definitely go for rc5 and treat the existing ensembl mart as a black box, the software will provide the means to integrate it nicely through a backwards compatibility mechanism with your newly created mart.This is much easier in 0.8 rc5. If you however just want to see how the ensembl mart transformation is achieved exactly you will need a 0.7 XML transformation file from the Ensembl team.</div><div><br></div><div>FYI: A short description of the basic transformation algorithm below:</div><div><br></div><div>starting from one or more input “candidate” table, the software finds the largest set of table joins it can perform using only 1:1 and many-to-one (M:1) relations, and merges these tables together to create the main table. Multiple candidate tables can be given as input, in which case the algorithm creates main tables out of each selected candidate table and if unable to do so will create several separate datasets. Once the main tables are completed, if there is a 1:M relation between them they become main and sub-main tables. If there is now 1:M relation between them, they are split into separate datasets. Any tables that have a 1:M or many-to-many (M:N) relation with the newly-created main table or sub-main table are made into independent dimension tables. </div><div><br></div></div><div><br></div><div>Please let us know if you have any more questions,</div><div>a</div><div><div><!--StartFragment--><p class="MsoNormal" style="tab-stops:18.0pt"><b><span lang="EN-CA" style="font-size:9.0pt;color:#5B6F8C">Arek Kasprzyk</span></b><b><span lang="EN-CA" style="font-size:10.0pt;color:#5B6F8C"><br></span></b><span lang="EN-CA" style="font-size:8.0pt">Director, Bioinformatics Operations and Principal Investigator<br></span><span lang="EN-CA" style="font-size:6.0pt"><br></span><b><span lang="EN-CA" style="font-size:9.0pt;color:#5C708D">Ontario Institute for Cancer Research</span></b><span lang="EN-CA" style="font-size:9.0pt"><br></span><span lang="EN-CA" style="font-size:8.0pt">MaRS Centre, South Tower<br> 101 College Street, Suite 800<br> Toronto, Ontario, Canada M5G 0A3<br><span style="mso-tab-count:1"> </span><br> Tel:<span style="mso-tab-count:1"> </span><span style="mso-tab-count:1"> </span>416-673-8559<br> Toll-free: <span style="mso-tab-count:1"> </span>1-866-678-6427<br> <a href="http://www.oicr.on.ca">www.oicr.on.ca</a></span></p><p class="MsoNormal" style="tab-stops:18.0pt"><span lang="EN-CA" style="font-size:8.0pt"><br></span></p><div class="MsoNormal"><span lang="EN-CA"><span class="Apple-style-span" style="font-family: Verdana; "><font class="Apple-style-span" size="3"><span class="Apple-style-span" style="font-size: 11px;">Administrative Assistant: <a href="mailto:Natasha.Lander@oicr.on.ca">Natasha.Lander@oicr.on.ca</a></span></font></span></span></div><div class="MsoNormal"><span lang="EN-CA" style="font-size:8.0pt"><o:p> </o:p></span></div><div class="MsoNormal"><span lang="EN-CA" style="font-size:8.0pt;font-family:Arial; color:black">This message and any attachments may contain confidential and/or privileged information for the sole use of the intended recipient. Any review or distribution by anyone other than the person for whom it was originally intended is strictly prohibited. If you have received this message in error, please contact the sender and delete all copies. Opinions, conclusions or other information contained in this message may not be that of the organization.<o:p></o:p></span></div><!--EndFragment--></div></div></div></div><div><br></div><span id="OLK_SRC_BODY_SECTION"><div style="font-family:Calibri; font-size:11pt; text-align:left; color:black; BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt"><span style="font-weight:bold">From: </span> Andrea Edwards <<a href="mailto:edwardsa@cs.man.ac.uk">edwardsa@cs.man.ac.uk</a>><br><span style="font-weight:bold">Date: </span> Sat, 12 Mar 2011 14:13:24 -0500<br><span style="font-weight:bold">To: </span> "<a href="mailto:users@biomart.org">users@biomart.org</a>" <<a href="mailto:users@biomart.org">users@biomart.org</a>><br><span style="font-weight:bold">Subject: </span> Re: [BioMart Users] how to get from ensembl main database schema to ensembl mart schema<br></div><div><br></div><div><div><div>Hi</div><div><br></div><div>I am not concerned whether I use biomart 0.7 or 0.8 - whichever is </div><div>easiest for what I would like to do. I havent done anything yet and I'm </div><div>starting from scratch.</div><div><br></div><div>All i want to do is have a go at re-creating the ensembl mart from the </div><div>ensembl core databases. I wanted to do this because ensembl is an </div><div>example of a database whose schema I am familiar with and whose mart I </div><div>have used. I wanted to do this for 2 reasons:</div><div>a) to get some practice</div><div>b) to get an intuitition of what type of mart I can create from my own </div><div>database schema and what types of query I can run and what the </div><div>filters/attributes will be</div><div>c) get an idea of how i could integrate my database with ensembl as I </div><div>believe they only need to share ids or underlying assembly to b integrated</div><div><br></div><div>Will i be able to recreate the ensembl mart in biomart 0.8? I presume </div><div>the ensembl xml files are available for 0.7 and I won;t be able to read </div><div>them in 0.8? Without these files how will i know the exact steps ensembl </div><div>used to specify their mart structure? How will i know what main tables </div><div>they chose or how for example they created the PRINTS dimension table </div><div>mentioned in my original query?</div><div><br></div><div>Thanks a lot</div><div><br></div><div><br></div><div>On 12/03/2011 18:59, Arek Kasprzyk wrote:</div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;" type="cite"><div> Putting this back on the list to keep everyone else in the loop</div><div><br></div><div> a</div><div><br></div><div><br></div><div><br></div><div> On 2011-03-12, at 13:56, "Arek Kasprzyk"<<a href="mailto:Arek.Kasprzyk@oicr.on.ca">Arek.Kasprzyk@oicr.on.ca</a>></div><div> wrote:</div><div><br></div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;" type="cite"><div> If you are starting from scratch it would be much better to start with</div><div> 0.8 rc5. Creating new mart is as simple as choosing one or more main</div><div> tables in the source schema. You can choose different tables and</div><div> create different datasets. There is some documentation about it in</div><div> rc5. If you want to know how the transformation algorithm works I can</div><div> describe that to you too</div><div><br></div><div><br></div><div> a</div><div><br></div><div><br></div><div><br></div><div> On 2011-03-12, at 12:53, "Andrea Edwards"<<a href="mailto:edwardsa@cs.man.ac.uk">edwardsa@cs.man.ac.uk</a>></div><div> wrote:</div><div><br></div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;" type="cite"><div> ok - thanks</div><div><br></div><div> i don't know much about biomart as you can probably tell but i was</div><div> told</div><div> there are quite significant differences between 0.7 and 0.8.</div><div> If i am interested in understanding how the schema transformations</div><div> take</div><div> place so that I can design my own mart and integrate it with existing</div><div> marts, would i be better dropping back to 0.7? I'm keen to get a</div><div> mart up</div><div> and running very soon.</div><div><br></div><div> On 12/03/2011 17:41, Arek Kasprzyk wrote:</div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;" type="cite"><div> 0.8 rc 5 has still only rudimentary support for the MBuilder</div><div> component. You will not be able to read 0.7 mbuilder XML with it.</div><div> (ccing junjun who has just taken over the coordination of the</div><div> BioMart</div><div> development to let him know that such discussions are taking place)</div><div><br></div><div> a</div><div><br></div><div><br></div><div><br></div><div> On 2011-03-12, at 12:28, "Andrea Edwards"<<a href="mailto:edwardsa@cs.man.ac.uk">edwardsa@cs.man.ac.uk</a>></div><div> wrote:</div><div><br></div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;" type="cite"><div> Brilliant - thanks for such a prompt reply.</div><div><br></div><div> I note that you say MBuilder (0.7) whereas i have checked out the</div><div> code</div><div> for biomart 0.8 rc4</div><div><br></div><div><br></div><div> On 12/03/2011 16:39, Arek Kasprzyk wrote:</div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;" type="cite"><div> Hi Andrea</div><div> All the transformation information is stored in the XML file that</div><div> MBuilder (0.7) uses to compile it's DDL for Ensembl core</div><div> databases. I</div><div> am sure the ensembl mart team will be happy to provide you the</div><div> latest</div><div> version</div><div><br></div><div> a</div><div><br></div><div><br></div><div><br></div><div> On 2011-03-12, at 11:15, "Andrea Edwards"<<a href="mailto:edwardsa@cs.man.ac.uk">edwardsa@cs.man.ac.uk</a>></div><div> wrote:</div><div><br></div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;" type="cite"><div> Hello</div><div><br></div><div> I was wondering if there were any documents showing how the</div><div> ensembl</div><div> marts were created from the main ensembl databases.</div><div> Specifically i</div><div> was</div><div> hoping there were documents describing what tables were selected</div><div> as</div><div> main</div><div> tables for the marts and how the dimension tables were mapped to</div><div> the</div><div> main tables.</div><div><br></div><div> As an example the ensembl_mart_61 contains a main table for human</div><div> named</div><div> translation_main (this is an abbreviation of the name but its</div><div> obvious</div><div> which one i mean) and this has a field called</div><div> protein_feature_prints_bool which is essentially a boolean field</div><div> indicating whether a protein translation is assocated with a row</div><div> in</div><div> the</div><div> PRINTS dimension table protein_feature_prints_dm. If the</div><div> translation</div><div> does have a row in this dimension table then I am guessing it</div><div> has a</div><div> PRINTS domain in it!</div><div><br></div><div> The core database itself however has a table called translation</div><div> which</div><div> represents, well, a translation. Translations are linked to rows</div><div> in a</div><div> table called 'protein_feature' which in turn has a foreign key</div><div> called</div><div> analysis_id which links to an 'analysis' table with fields</div><div> 'database'</div><div> and 'program'. So in this schema, a translation is associated</div><div> with a</div><div> PRINTS annotation if it is linked to a 'protein_feature' record</div><div> which is</div><div> in turn linked to an 'analysis' record with the text 'PRINTS'</div><div> somewhere</div><div> in both/either the database/program fields.</div><div><br></div><div> I am interested in how the biomart software is configured with</div><div> 'rules'</div><div> to create the mart schema from the database schema. Is there a</div><div> configuration file with these rules in that I could look at? Is</div><div> there a</div><div> worked example? As an academic exercise I'd like to recreate the</div><div> ensembl</div><div> marts. I have the biomart user manual but even with that document</div><div> I do</div><div> not know how to recreate the ensembl marts</div><div><br></div><div> I am NOT specifically interested in protein domains. I used the</div><div> PRINTS</div><div> example purely for illustrative purposes as I thought it was a</div><div> straightforward example. I am interested in how you specify the</div><div> 'rules'</div><div> to get from a schema to a mart.</div><div><br></div><div> thanks a lot</div><div><br></div><div> _______________________________________________</div><div> Users mailing list</div><div> <a href="mailto:Users@biomart.org">Users@biomart.org</a></div><div> <a href="https://lists.biomart.org/mailman/listinfo/users">https://lists.biomart.org/mailman/listinfo/users</a></div></blockquote></blockquote></blockquote></blockquote></blockquote></blockquote></blockquote><div><br></div><div>_______________________________________________</div><div>Users mailing list</div><div><a href="mailto:Users@biomart.org">Users@biomart.org</a></div><div><a href="https://lists.biomart.org/mailman/listinfo/users">https://lists.biomart.org/mailman/listinfo/users</a></div><div><br></div></div></div></span></div> _______________________________________________<br>Users mailing list<br><a href="mailto:Users@biomart.org">Users@biomart.org</a><br>https://lists.biomart.org/mailman/listinfo/users<br></blockquote></div><br><div> <span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>Rhoda Kinsella Ph.D.</div><div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; ">Ensembl Bioinformatician,</div></div><div>European Bioinformatics Institute (EMBL-EBI),<br>Wellcome Trust Genome Campus, </div><div>Hinxton<br>Cambridge CB10 1SD,</div><div>UK.</div></div></span></div></span> </div><br></body></html>