The best thing about 'restrict relation' is the way you can ask it to do
things like join table A to B where some function of a combination of
columns from A and B is true, e.g.:
WHERE (...PK/FK join included by default: a.id = b.a_id...)
AND ( a.weight < b.weight
OR a.height > b.height
OR length(b.name)>6 )
which would give you all records from B that are related to A and are
'heavier', 'shorter', or have a 'name' longer than 6 characters.
cheers,
Richard
On Sun, June 3, 2007 8:14 pm, Guðmundur Árni Þórisson wrote:
> Very nice. Thanks for the info Richard. I'll give 'force relation' a
> try and get back to you if I run into any trouble. sounds like it
> would do the trick for me.
>
> The 'restriction to relation' you explain in your other mail is
> something I'd seen but didn't see obviously how it was different from
> the other kind (which is no longer in 0.6 as you say). But it seems
> very useful and I'll probably have use for the 'hard restriction'
> feature as well. And/or the compound relation feature. The The
> 'Explain ...' is extremely powerful from what I've seen so far. Lots
> of goodies in MBuilder to play with!!
>
>
> Mummi
>
> On 3 Jun 2007, at 19:46, Richard Holland wrote:
>
>> 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
>>
>
--
Richard Holland
BioMart (http://www.biomart.org/)
EMBL-EBI
Hinxton, Cambridgeshire CB10 1SD, UK
