help with optimiser columns

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

help with optimiser columns

Leandro Hermida
Hi BioMart team,

I haven't used MartBuilder for a mart I've built because there isn't a source schema and I was wondering if you could help me with how I should add optimiser columns manually to my mart?  Just a few questions:
  • Do optimiser columns need to have a certain naming scheme?
  • If you put optimiser columns not in the main table but a separate table what should the table name be?
  • What are index optimiser columns?  What do they produce when checked in MartBuilder so that I can add them manually?
thank you,
Leandro



Reply | Threaded
Open this post in threaded view
|

Re: help with optimiser columns

Yong Liang



On 09/06/10 9:11 AM, "Leandro Hermida" <[hidden email]> wrote:

> Hi BioMart team,
>
> I haven't used MartBuilder for a mart I've built because there isn't a source
> schema and I was wondering if you could help me with how I should add
> optimiser columns manually to my mart?  Just a few questions:
A simple table has the naming schema like dataset__content__type

> * Do optimiser columns need to have a certain naming scheme?
An optimiser column has the pattern like content_bool
> * If you put optimiser columns not in the main table but a separate table what
> should the table name be?
The table name should look like dataset__content_bool__dm, which includes
the key column in the main table, and content_bool columns for each
dimension table.
> * What are index optimiser columns?  What do they produce when checked in
> MartBuilder so that I can add them manually?
The optimiser columns are for sub main table, and automatically checked in
MartBuilder. It will automatically generate a sub main content_bool column
in main table.
 
> thank you,
> Leandro
>
Hope that help. Let me know if you have any questions.
>


Reply | Threaded
Open this post in threaded view
|

Re: help with optimiser columns

Leandro Hermida
Hi Yong,



> * Do optimiser columns need to have a certain naming scheme?
An optimiser column has the pattern like content_bool
> * If you put optimiser columns not in the main table but a separate table what
> should the table name be?
The table name should look like dataset__content_bool__dm, which includes
the key column in the main table, and content_bool columns for each
dimension table.

Sorry some additional questions... could we try with a real example?  I'm particularly interest in split optimiser count columns. If you were to just put them in the main table how would they look? Let's say I have a "model" dimension table with the following columns, "provider" and "organism", and I wanted to make split optimiser count columns for them.  Would their column names in the main table be provider_count and organism_count?  How does BioMart then know to use them properly and that they are not regular (non-split) count columns of the "model" dimension?

> * What are index optimiser columns?  What do they produce when checked in
> MartBuilder so that I can add them manually?
The optimiser columns are for sub main table, and automatically checked in
MartBuilder. It will automatically generate a sub main content_bool column
in main table.

I cannot use MartBuilder because I don't have a source schema, so what does the Index Optimiser Columns produce SQL-wise?  Does it produce nothing if you only have a main table (and of course dimensions) but no subclass tables?

thanks,
Leandro

On Wed, Jun 9, 2010 at 4:32 PM, Yong Liang <[hidden email]> wrote:



On 09/06/10 9:11 AM, "Leandro Hermida" <[hidden email]> wrote:

> Hi BioMart team,
>
> I haven't used MartBuilder for a mart I've built because there isn't a source
> schema and I was wondering if you could help me with how I should add
> optimiser columns manually to my mart?  Just a few questions:
A simple table has the naming schema like dataset__content__type

> * Do optimiser columns need to have a certain naming scheme?
An optimiser column has the pattern like content_bool
> * If you put optimiser columns not in the main table but a separate table what
> should the table name be?
The table name should look like dataset__content_bool__dm, which includes
the key column in the main table, and content_bool columns for each
dimension table.
> * What are index optimiser columns?  What do they produce when checked in
> MartBuilder so that I can add them manually?
The optimiser columns are for sub main table, and automatically checked in
MartBuilder. It will automatically generate a sub main content_bool column
in main table.

> thank you,
> Leandro
>
Hope that help. Let me know if you have any questions.
>



Reply | Threaded
Open this post in threaded view
|

Re: help with optimiser columns

Yong Liang



On 09/06/10 12:38 PM, "Leandro Hermida" <[hidden email]> wrote:

> Hi Yong,
>
>
>
>>>> * Do optimiser columns need to have a certain naming scheme?
>> An optimiser column has the pattern like content_bool
>>>> * If you put optimiser columns not in the main table but a separate table
>>> what
>>>> should the table name be?
>> The table name should look like dataset__content_bool__dm, which includes
>> the key column in the main table, and content_bool columns for each
>> dimension table.
>
> Sorry some additional questions... could we try with a real example?  I'm
> particularly interest in split optimiser count columns. If you were to just
> put them in the main table how would they look? Let's say I have a "model"
> dimension table with the following columns, "provider" and "organism", and I
> wanted to make split optimiser count columns for them.  Would their column
> names in the main table be provider_count and organism_count? 
Yes, there will have two column in the main table called provider_count and
organism_count. The count column is the number of rows in the dimension
table.

>How does
> BioMart then know to use them properly and that they are not regular
> (non-split) count columns of the "model" dimension?
>
>>>> * What are index optimiser columns?  What do they produce when checked in
>>>> MartBuilder so that I can add them manually?
>> The optimiser columns are for sub main table, and automatically checked in
>> MartBuilder. It will automatically generate a sub main content_bool column
>> in main table.
>
> I cannot use MartBuilder because I don't have a source schema, so what does
> the Index Optimiser Columns produce SQL-wise? 
It will create index in all tables, the produced SQL looks like:
create index I_1 on table (key);

>Does it produce nothing if you
> only have a main table (and of course dimensions) but no subclass tables?


> thanks,
> Leandro
>
> On Wed, Jun 9, 2010 at 4:32 PM, Yong Liang <[hidden email]> wrote:
>>
>>
>>
>> On 09/06/10 9:11 AM, "Leandro Hermida" <[hidden email]> wrote:
>>
>>>> Hi BioMart team,
>>>>
>>>> I haven't used MartBuilder for a mart I've built because there isn't a
>>> source
>>>> schema and I was wondering if you could help me with how I should add
>>>> optimiser columns manually to my mart?  Just a few questions:
>> A simple table has the naming schema like dataset__content__type
>>
>>>> * Do optimiser columns need to have a certain naming scheme?
>> An optimiser column has the pattern like content_bool
>>>> * If you put optimiser columns not in the main table but a separate table
>>> what
>>>> should the table name be?
>> The table name should look like dataset__content_bool__dm, which includes
>> the key column in the main table, and content_bool columns for each
>> dimension table.
>>>> * What are index optimiser columns?  What do they produce when checked in
>>>> MartBuilder so that I can add them manually?
>> The optimiser columns are for sub main table, and automatically checked in
>> MartBuilder. It will automatically generate a sub main content_bool column
>> in main table.
>>
>>>> thank you,
>>>> Leandro
>>>>
>> Hope that help. Let me know if you have any questions.
>>>>
>>
>>
>

Yong Liang
Database/Application Programmer

Ontario Institute for Cancer Research
MaRS Centre, South Tower
101 College Street, Suite 800
Toronto, Ontario, Canada M5G 0A3
           
Tel:                        647-260-7951
Toll-free:             1-866-678-6427
www.oicr.on.ca
 
This message and any attachments may contain confidential and/or privileged
information for the sole use of the intended recipient. Any review or
distribution by anyone other than the person for whom it was originally
intended is strictly prohibited. If you have received this message in error,
please contact the sender and delete all copies. Opinions, conclusions or
other information contained in this message may not be that of the
organization.