martbuild and marteditor

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

martbuild and marteditor

Andreu Alibés
Hi,

I'm sorry if the question is a bit long, but I need to describe the set up.

I have to following type of database in mysql that I would like to convert to a mart:

------------------------
A "gene table" with the following columns:
- gene id
- gene name
- sequence
...
------------------------
A "rna_gene table" with:
- rna_gene_id
- rna_id
- gene_id
...

A "rna table" with
- rna_id
- description
...
------------------------

A "reaction_gene table" with:
- reaction_gene_id
- reaction_id
- gene_id
...

A "reaction table" with
- reaction_id
- description
...
------------------------

If, when using martbuider I select only the 'gene' table to build the dataset, the corresponding mart does not include data from the "rna" and "reaction" tables.
If, I select the tables "gene", "rna" and "reaction" tables to build the dataset, I get two datasets: one where I could filter by gene and rna and the other that I can filter by gene and reaction

So, is there a way to build a single dataset so I can filter by gene, rna and reaction?

This is a made up example, in the real database there are many more tables, but all follow this structure.

Thanks,

   Andreu


--
--------------------------------------------
Andreu Alibés, PhD
Bioinformatics Core & EMBL-CRG Systems Biology Unit
Center for Genomic Regulation

C/ Dr. Aiguader 88, 08003 Barcelona, Spain
Phone: +34 93 316 0202

http://sites.google.com/site/aalibes

Reply | Threaded
Open this post in threaded view
|

Re: martbuild and marteditor

Richard Holland
Before doing anything, check that your Schema diagram in MartBuilder is showing any keys and relations at all in the various tables. If it is not, you'll need to create them by using the right-click menus, or by turning on 'guess relations' in the database connection dialog. You should have a PK of gene id on the Gene table, a PK of rna id on the Rna table, and a PK of reaction id on the Reaction table. Then the gene and rna and reaction id columns in rna_gene and reaction_gene should be FKs, and all the PKs and FKs should be linked by 1:M relations. Once you are sure that the keys and relations are showing and linking the tables correctly then continue below.

Your database looks like it is many-many Gene-Rna, and many-many Gene-Reaction. I expect when you create the mart by choosing Gene only, you are getting two dimensions called RnaGene and ReactionGene? If you explore those you'll find that they do include the Rna and Reaction data, as they are combinations of the data from all the tables.

If it really isn't doing it, you can 'trick' it by right-clicking the relations between Rna and RnaGene, and Reaction and ReactionGene, and making them 1:1. Then check the relations between Gene and RnaGene and ReactionGene are 1:M, and try again (still using the Gene table as the starting point).

cheers,
Richard

On 20 Oct 2010, at 15:35, Andreu Alibés wrote:

> Hi,
>
> I'm sorry if the question is a bit long, but I need to describe the set up.
>
> I have to following type of database in mysql that I would like to convert to a mart:
>
> ------------------------
> A "gene table" with the following columns:
> - gene id
> - gene name
> - sequence
> ...
> ------------------------
> A "rna_gene table" with:
> - rna_gene_id
> - rna_id
> - gene_id
> ...
>
> A "rna table" with
> - rna_id
> - description
> ...
> ------------------------
>
> A "reaction_gene table" with:
> - reaction_gene_id
> - reaction_id
> - gene_id
> ...
>
> A "reaction table" with
> - reaction_id
> - description
> ...
> ------------------------
>
> If, when using martbuider I select only the 'gene' table to build the dataset, the corresponding mart does not include data from the "rna" and "reaction" tables.
> If, I select the tables "gene", "rna" and "reaction" tables to build the dataset, I get two datasets: one where I could filter by gene and rna and the other that I can filter by gene and reaction
>
> So, is there a way to build a single dataset so I can filter by gene, rna and reaction?
>
> This is a made up example, in the real database there are many more tables, but all follow this structure.
>
> Thanks,
>
>    Andreu
>
>
> --
> --------------------------------------------
> Andreu Alibés, PhD
> Bioinformatics Core & EMBL-CRG Systems Biology Unit
> Center for Genomic Regulation
> C/ Dr. Aiguader 88, 08003 Barcelona, Spain
> Phone: +34 93 316 0202
> http://sites.google.com/site/aalibes
>

--
Richard Holland, BSc MBCS
Operations and Delivery Director, Eagle Genomics Ltd
T: +44 (0)1223 654481 ext 3 | E: [hidden email]
http://www.eaglegenomics.com/

Reply | Threaded
Open this post in threaded view
|

Re: martbuild and marteditor

Andreu Alibés
Thanks Richard,

Does that main that, when using martbuilder, I should select only the gene table to create the dataset?

A

On Wed, Oct 20, 2010 at 4:57 PM, Richard Holland <[hidden email]> wrote:
Before doing anything, check that your Schema diagram in MartBuilder is showing any keys and relations at all in the various tables. If it is not, you'll need to create them by using the right-click menus, or by turning on 'guess relations' in the database connection dialog. You should have a PK of gene id on the Gene table, a PK of rna id on the Rna table, and a PK of reaction id on the Reaction table. Then the gene and rna and reaction id columns in rna_gene and reaction_gene should be FKs, and all the PKs and FKs should be linked by 1:M relations. Once you are sure that the keys and relations are showing and linking the tables correctly then continue below.

Your database looks like it is many-many Gene-Rna, and many-many Gene-Reaction. I expect when you create the mart by choosing Gene only, you are getting two dimensions called RnaGene and ReactionGene? If you explore those you'll find that they do include the Rna and Reaction data, as they are combinations of the data from all the tables.

If it really isn't doing it, you can 'trick' it by right-clicking the relations between Rna and RnaGene, and Reaction and ReactionGene, and making them 1:1. Then check the relations between Gene and RnaGene and ReactionGene are 1:M, and try again (still using the Gene table as the starting point).

cheers,
Richard

On 20 Oct 2010, at 15:35, Andreu Alibés wrote:

> Hi,
>
> I'm sorry if the question is a bit long, but I need to describe the set up.
>
> I have to following type of database in mysql that I would like to convert to a mart:
>
> ------------------------
> A "gene table" with the following columns:
> - gene id
> - gene name
> - sequence
> ...
> ------------------------
> A "rna_gene table" with:
> - rna_gene_id
> - rna_id
> - gene_id
> ...
>
> A "rna table" with
> - rna_id
> - description
> ...
> ------------------------
>
> A "reaction_gene table" with:
> - reaction_gene_id
> - reaction_id
> - gene_id
> ...
>
> A "reaction table" with
> - reaction_id
> - description
> ...
> ------------------------
>
> If, when using martbuider I select only the 'gene' table to build the dataset, the corresponding mart does not include data from the "rna" and "reaction" tables.
> If, I select the tables "gene", "rna" and "reaction" tables to build the dataset, I get two datasets: one where I could filter by gene and rna and the other that I can filter by gene and reaction
>
> So, is there a way to build a single dataset so I can filter by gene, rna and reaction?
>
> This is a made up example, in the real database there are many more tables, but all follow this structure.
>
> Thanks,
>
>    Andreu
>
>
> --
> --------------------------------------------
> Andreu Alibés, PhD
> Bioinformatics Core & EMBL-CRG Systems Biology Unit
> Center for Genomic Regulation
> C/ Dr. Aiguader 88, 08003 Barcelona, Spain
> Phone: +34 93 316 0202
> http://sites.google.com/site/aalibes
>

--
Richard Holland, BSc MBCS
Operations and Delivery Director, Eagle Genomics Ltd
T: +44 (0)1223 654481 ext 3 | E: [hidden email]
http://www.eaglegenomics.com/




--
--------------------------------------------
Andreu Alibés, PhD
Bioinformatics Core & EMBL-CRG Systems Biology Unit
Center for Genomic Regulation

C/ Dr. Aiguader 88, 08003 Barcelona, Spain
Phone: +34 93 316 0202

http://sites.google.com/site/aalibes

Reply | Threaded
Open this post in threaded view
|

Re: martbuild and marteditor

Richard Holland
yes

On 20 Oct 2010, at 17:29, Andreu Alibés wrote:

> Thanks Richard,
>
> Does that main that, when using martbuilder, I should select only the gene table to create the dataset?
>
> A
>
> On Wed, Oct 20, 2010 at 4:57 PM, Richard Holland <[hidden email]> wrote:
> Before doing anything, check that your Schema diagram in MartBuilder is showing any keys and relations at all in the various tables. If it is not, you'll need to create them by using the right-click menus, or by turning on 'guess relations' in the database connection dialog. You should have a PK of gene id on the Gene table, a PK of rna id on the Rna table, and a PK of reaction id on the Reaction table. Then the gene and rna and reaction id columns in rna_gene and reaction_gene should be FKs, and all the PKs and FKs should be linked by 1:M relations. Once you are sure that the keys and relations are showing and linking the tables correctly then continue below.
>
> Your database looks like it is many-many Gene-Rna, and many-many Gene-Reaction. I expect when you create the mart by choosing Gene only, you are getting two dimensions called RnaGene and ReactionGene? If you explore those you'll find that they do include the Rna and Reaction data, as they are combinations of the data from all the tables.
>
> If it really isn't doing it, you can 'trick' it by right-clicking the relations between Rna and RnaGene, and Reaction and ReactionGene, and making them 1:1. Then check the relations between Gene and RnaGene and ReactionGene are 1:M, and try again (still using the Gene table as the starting point).
>
> cheers,
> Richard
>
> On 20 Oct 2010, at 15:35, Andreu Alibés wrote:
>
> > Hi,
> >
> > I'm sorry if the question is a bit long, but I need to describe the set up.
> >
> > I have to following type of database in mysql that I would like to convert to a mart:
> >
> > ------------------------
> > A "gene table" with the following columns:
> > - gene id
> > - gene name
> > - sequence
> > ...
> > ------------------------
> > A "rna_gene table" with:
> > - rna_gene_id
> > - rna_id
> > - gene_id
> > ...
> >
> > A "rna table" with
> > - rna_id
> > - description
> > ...
> > ------------------------
> >
> > A "reaction_gene table" with:
> > - reaction_gene_id
> > - reaction_id
> > - gene_id
> > ...
> >
> > A "reaction table" with
> > - reaction_id
> > - description
> > ...
> > ------------------------
> >
> > If, when using martbuider I select only the 'gene' table to build the dataset, the corresponding mart does not include data from the "rna" and "reaction" tables.
> > If, I select the tables "gene", "rna" and "reaction" tables to build the dataset, I get two datasets: one where I could filter by gene and rna and the other that I can filter by gene and reaction
> >
> > So, is there a way to build a single dataset so I can filter by gene, rna and reaction?
> >
> > This is a made up example, in the real database there are many more tables, but all follow this structure.
> >
> > Thanks,
> >
> >    Andreu
> >
> >
> > --
> > --------------------------------------------
> > Andreu Alibés, PhD
> > Bioinformatics Core & EMBL-CRG Systems Biology Unit
> > Center for Genomic Regulation
> > C/ Dr. Aiguader 88, 08003 Barcelona, Spain
> > Phone: +34 93 316 0202
> > http://sites.google.com/site/aalibes
> >
>
> --
> Richard Holland, BSc MBCS
> Operations and Delivery Director, Eagle Genomics Ltd
> T: +44 (0)1223 654481 ext 3 | E: [hidden email]
> http://www.eaglegenomics.com/
>
>
>
>
> --
> --------------------------------------------
> Andreu Alibés, PhD
> Bioinformatics Core & EMBL-CRG Systems Biology Unit
> Center for Genomic Regulation
> C/ Dr. Aiguader 88, 08003 Barcelona, Spain
> Phone: +34 93 316 0202
> http://sites.google.com/site/aalibes
>

--
Richard Holland, BSc MBCS
Operations and Delivery Director, Eagle Genomics Ltd
T: +44 (0)1223 654481 ext 3 | E: [hidden email]
http://www.eaglegenomics.com/