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/
