On 13/08/08 7:37 AM, "Henrikki Almusa" <[EMAIL PROTECTED]> wrote:
> I have 'subject' table with multiple phenotype tables which all have
> foreing key link to the subject table. Subject table has
>
> subject_id (PK)
> birth_date
> gender
> time_stamp
> ...
>
> For example of phenotype tables, I have 'weight' with
>
> subject_id (FK)
> weight_kg
> date_measured
> ...
>
> We have 7 different database instances located on different sites. Data
> is different but database schemas are same but not all things are
> measured in each so they might lack some phenotype tables or have no
> data in them. However since these are all their own instances, their
> subject_id values are not unique over all the sites. They are unique
> within their own site. When I combined the data to create one biomart
> instance, I replaced the subject_id columns for all table to a running
> number with the script which combined them.
>
> For the mart the 'subject' table would be main table and phenotype
> tables dm tables. I would like to get one subject table from all N
> locations subject tables. Same applies to the phenotype tables so that
> there would be only one weight table (and not N weight tables).
>
>> As far as views are concerned - this is possible assuming that the
>> conversion logic is not too complex. However I would advise you to use
>> materialized schemas as otherwise you would loosing the benefits of query
>> optimizations
>
> I did assume that it would be slower to access.
Hi Henrikki,
I think we understand your problem now. You have basically three options
there:
1. Leave data where it is on all 7 instances and create a dataset per each
one. Then deploy a portal connecting to those 7 instances and presenting a
separate dataset for each. (you would have to run each source scheme ->
dataset conversion on each instance) - you can use Mbuilder to run schema
transformation.
2. Move all data to one instance and and create a one transformation file
with seven partitions.
(you need to choose one of the schemas and then provide a regular expression
for detecting all your 7 source schemas. To try it out:
1. Add new schema and provide a regular expression so that Mbuilder can
identify all 7 schemas)
You can run all transformation in as a single run. However this will still
leave you with 7 different datasets.
3. Move all data to one instance and manually merge the data into one mart
schema taking care of all the conflicting keys (Mbuilder will not help you
with that because we do not support merges yet - it is in our to do list)
I guess (3) is what you are already doing so we could not be of a much help
to you this time. Sorry.
A.
