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 <holl...@eaglegenomics.com>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: holl...@eaglegenomics.com > 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
