Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: [Mondrian] schema question

  1. #1
    Macros Black Guest

    Default [Mondrian] schema question

    Hi guys,
    I am new to Mondrian, and first let me say, really sweet concept, leveraging
    this whole hypercube model for analytics. Also, nice, apt name. So I have
    a question that I hope someone can help me with. I have a fact table of
    product sales which I'm extracting dimensions from. These products belong to
    categories, which is represented in a separate table product_categories
    which has two columns, product_id and category_id.

    I would like to have 'categories' as a dimension in the cube as well, but
    I'm not sure how to achieve this since it's not in the fact table (only
    product_id is in the fact table, along with the sales, prices, dates etc.).
    Also since it is a "dependent" dimension in the sense that a product id
    uniquely determines the values of the "category" axis, it doesn't seem right
    to modify the fact table to include category_id. I think I am looking for
    something like this:

    <dimension>
    <table = ?>
    use key product_id in sales_fact_table and maybe do some kind of join or use
    the corresponding category_id's in the product_categories table
    </table>
    </dimension>

    Help would be appreciated!

    Thanks,
    -m

    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  2. #2
    Luc Boudreau Guest

    Default Re: [Mondrian] schema question

    Macros,

    You will find all your answers, and much more, in our online documentation
    about Mondrian schemas. It is available at:

    http://mondrian.pentaho.com/documentation/schema.php

    You can also use our test schema, FoodMart, as a reference. It is available
    here:

    http://perforce.eigenbase.org:8080/@...o/FoodMart.xml

    Good luck!

    Luc




    On Fri, Jul 15, 2011 at 8:37 PM, Macros Black <scarletred10 (AT) gmail (DOT) com>wrote:

    > Hi guys,
    > I am new to Mondrian, and first let me say, really sweet concept,
    > leveraging this whole hypercube model for analytics. Also, nice, apt name.
    > So I have a question that I hope someone can help me with. I have a fact
    > table of product sales which I'm extracting dimensions from. These products
    > belong to categories, which is represented in a separate table
    > product_categories which has two columns, product_id and category_id.
    >
    > I would like to have 'categories' as a dimension in the cube as well, but
    > I'm not sure how to achieve this since it's not in the fact table (only
    > product_id is in the fact table, along with the sales, prices, dates etc.).
    > Also since it is a "dependent" dimension in the sense that a product id
    > uniquely determines the values of the "category" axis, it doesn't seem right
    > to modify the fact table to include category_id. I think I am looking for
    > something like this:
    >
    > <dimension>
    > <table = ?>
    > use key product_id in sales_fact_table and maybe do some kind of join or
    > use the corresponding category_id's in the product_categories table
    > </table>
    > </dimension>
    >
    > Help would be appreciated!
    >
    > Thanks,
    > -m
    >
    >
    > _______________________________________________
    > Mondrian mailing list
    > Mondrian (AT) pentaho (DOT) org
    > http://lists.pentaho.org/mailman/listinfo/mondrian
    >
    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  3. #3
    Macros Black Guest

    Default Re: [Mondrian] schema question

    Hi Luc,
    Thanks for the quick response! Could you point me at the specific section
    that talks about this? I did look at the documentation on schemas and also
    the sample FoodMart schema, but it wasn't clear to me how to handle this
    specific case that I described. Also it didn't look like the FoodMart schema
    had an equivalent example.

    Thanks,
    -Macros


    On Sat, Jul 16, 2011 at 7:01 AM, Luc Boudreau <lucboudreau (AT) gmail (DOT) com> wrote:

    >
    > Macros,
    >
    > You will find all your answers, and much more, in our online documentation
    > about Mondrian schemas. It is available at:
    >
    > http://mondrian.pentaho.com/documentation/schema.php
    >
    > You can also use our test schema, FoodMart, as a reference. It is available
    > here:
    >
    >
    > http://perforce.eigenbase.org:8080/@...o/FoodMart.xml
    >
    > Good luck!
    >
    > Luc
    >
    >
    >
    >
    > On Fri, Jul 15, 2011 at 8:37 PM, Macros Black <scarletred10 (AT) gmail (DOT) com>wrote:
    >
    >> Hi guys,
    >> I am new to Mondrian, and first let me say, really sweet concept,
    >> leveraging this whole hypercube model for analytics. Also, nice, apt name.
    >> So I have a question that I hope someone can help me with. I have a fact
    >> table of product sales which I'm extracting dimensions from. These products
    >> belong to categories, which is represented in a separate table
    >> product_categories which has two columns, product_id and category_id.
    >>
    >> I would like to have 'categories' as a dimension in the cube as well, but
    >> I'm not sure how to achieve this since it's not in the fact table (only
    >> product_id is in the fact table, along with the sales, prices, dates etc.).
    >> Also since it is a "dependent" dimension in the sense that a product id
    >> uniquely determines the values of the "category" axis, it doesn't seem right
    >> to modify the fact table to include category_id. I think I am looking for
    >> something like this:
    >>
    >> <dimension>
    >> <table = ?>
    >> use key product_id in sales_fact_table and maybe do some kind of join or
    >> use the corresponding category_id's in the product_categories table
    >> </table>
    >> </dimension>
    >>
    >> Help would be appreciated!
    >>
    >> Thanks,
    >> -m
    >>
    >>
    >> _______________________________________________
    >> Mondrian mailing list
    >> Mondrian (AT) pentaho (DOT) org
    >> http://lists.pentaho.org/mailman/listinfo/mondrian
    >>
    >>

    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  4. #4
    Venkatesh U Guest

    Default Re: [Mondrian] schema question

    You have got various options
    1. Design the product dimension as a snowflake dimension, using join table
    syntax in the schema XML(You may use schema workbench to easily edit the
    schema xml files)
    2. Create a view joining Product dimension and Product categories and use it
    as a single dimension with 2 levels Category and Product
    3. Join the tale product_categories with the Fact and create a view, use
    this view as your fact table.

    I would go with the second option due to its simplicity
    Hope this helps.

    Venki

    On Sat, Jul 16, 2011 at 6:07 AM, Macros Black <scarletred10 (AT) gmail (DOT) com>wrote:

    > Hi guys,
    > I am new to Mondrian, and first let me say, really sweet concept,
    > leveraging this whole hypercube model for analytics. Also, nice, apt name.
    > So I have a question that I hope someone can help me with. I have a fact
    > table of product sales which I'm extracting dimensions from. These products
    > belong to categories, which is represented in a separate table
    > product_categories which has two columns, product_id and category_id.
    >
    > I would like to have 'categories' as a dimension in the cube as well, but
    > I'm not sure how to achieve this since it's not in the fact table (only
    > product_id is in the fact table, along with the sales, prices, dates etc.).
    > Also since it is a "dependent" dimension in the sense that a product id
    > uniquely determines the values of the "category" axis, it doesn't seem right
    > to modify the fact table to include category_id. I think I am looking for
    > something like this:
    >
    > <dimension>
    > <table = ?>
    > use key product_id in sales_fact_table and maybe do some kind of join or
    > use the corresponding category_id's in the product_categories table
    > </table>
    > </dimension>
    >
    > Help would be appreciated!
    >
    > Thanks,
    > -m
    >
    >
    > _______________________________________________
    > Mondrian mailing list
    > Mondrian (AT) pentaho (DOT) org
    > http://lists.pentaho.org/mailman/listinfo/mondrian
    >
    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.