MBuilder follows all 1:1 and M:1 relations it can find, until there are no more. It will never follow 1:M or N:M relations except in the case where a 1:M (never N:M) relation leads off the initial table or a table that is reached purely by a chain of 1:1 relations, in which case they become dimensions, which have new relation chains of their own starting at the point they branch off the main table.
In MBuilder 0.5 you basically could like it or lump it! But in MBuilder 0.6 there is a 'force relation' feature which you can use to make it follow a relation beyond the point where it would normally have stopped. I won't go as far as Arek and call the lack of this option in 0.5 a 'bug in the algorithm'... I'd prefer to call the ability to include additional relations beyond those covered by the default algorithm a 'new feature' instead. :) After all, the algorithm must have a defined and predictable stopping point else it would end up merging the entire schema into a single table! By forcing a relation you make MBuilder treat it as though it were another 1:1 relation in the chain (even if logically it is not), and it will continue merging relations past that point until it reaches the next logical stopping point. So you will usually end up with more relations than the one you just clicked on included in the final table. You can then use masking to remove the ones you don't want. Because the forced relation is treated as a 1:1 relation this also means that you can potentially end up with new dimensions popping up as well from 1:M relations it finds beyond the forced relation, if the forced relation comes after an unbroken chain of 1:1 relations from the starting point. There is no limit to the number of relations you can force. Also new in MBuilder 0.6 is the ability to alter the transformation behaviour on a per-table or per-dataset basis, allowing you to make it use different choices for each separate main or dimension table should you so desire. Combined with the new compound relation feature which allows you to follow one relation multiple times and apply different restrictions to each iteration, this can allow MBuilder to gather much more of the schema into a single dataset table, or even generate multiple dimensions from the same 1:M relation, with each one including different information. cheers, Richard On Fri, June 1, 2007 10:13 pm, Arek Kasprzyk wrote: > > On 1 Jun 2007, at 16:06, Guðmundur Árni Þórisson wrote: > >> I'm starting to use MartBuilder for real now and slowly getting the >> hang of things. I've managed to create some simple datasets without >> problems (just works!), but when going into more complex things I am >> stumbling. Specifically, I do not see how to tell MBuilder to do >> certain complex multi-table joins to pull various pieces of data >> together. >> Here's an example of this sort of thing is genotype frequency data >> I want to pull out of my database. Some tables hold data for filters >> and/or attributes, others are merely linking tables: >> >> Study->Experiment->Usedmarkerset->GenotypeFrequencyCluster- >> >GenotypeFrequency->[couple more tables] >> >> I see some indication of how MartBuilder is doing things when it has >> created a dimension table out of two tables, and click 'Explain >> table'. It's also doing something clever when I select multiple tables >> in the 'Create datasets' dialog. But is this procedure entirely >> automatic, offering no control beyond masking dimension tables *after* >> MBuilder has created the dataset? How deep does it go, in terms of >> following the PK-FK trail? >> > > the algorithm follows the PK/FK and derived cardinalities. whenever it > hits 1:n or n:m it stops and creates a dm table > or in case of a dm table just stops. > > >> I guess I'm hunting for a clue as to whether I can add tables to a >> dataset semi-manually, one by one? > > you can override the algorithm and make MBuilder to merge table > according to you wishes. > Not sure if available in 0.5 but definitely possible in 0.6 Richard > will tell you more about it > > >> Or is it entirely a matter of telling MBuilder to do X number of >> tables, and then sorting out the mess afterwards via mask/hide and so >> on? >> > > you can force it to merge tables regardless the algorithm. > > > a. > > > >> >> >> Mummi >> >> PS I tried the 'Add restriction to table' and it's just wicked, >> extremely useful! My particular use case is that I have an Experiment >> table with different types of experiment-thingies: frequency >> determination, association analysis etc. This function let's me easily >> create seperate datasets for each! >> >> >> >> ----------------------------------------------------------- >> Gudmundur A. Thorisson, PhD student, Brookes lab >> Department of Genetics >> University of Leicester >> University Road >> Leicester, LE1 7RH, UK >> E-mail: [EMAIL PROTECTED] >> Tel: +44 (0)116 252-3055 >> >> >> >> > > > ------------------------------------------------------------------------ > ------- > Arek Kasprzyk > EMBL-European Bioinformatics Institute. > Wellcome Trust Genome Campus, Hinxton, > Cambridge CB10 1SD, UK. > Tel: +44-(0)1223-494606 > Fax: +44-(0)1223-494468 > ------------------------------------------------------------------------ > ------- > > > -- Richard Holland BioMart (http://www.biomart.org/) EMBL-EBI Hinxton, Cambridgeshire CB10 1SD, UK
