Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Table Join Question

  1. #1
    Join Date
    Mar 2012

    Question Table Join Question

    I have two MySQL tables - category and product. Their primary keys are categoryID and productID, respectively. Every product has an associated categoryID as one of its fields. In essence, it is like a foreign key. *However, the categoryID field in the product table is not explicitly defined as a key in the table schema.

    My attempt at the join is shown below. I haven't been able to get the join to work though - the cube and dimensions do not show in Saiku. I tested with primitive dimensions that used the product and category tables independently and those worked so I know that connectivity between MySQL-Mondrian-Saiku is fine. Is it possible that, because categoryID was not explicitly defined as a foreign key in the category table schema, the Mondrian XML fails to validate? Or maybe my join is incorrect?

    <Cube name="Products">
       <Table name="product"/>
       <Dimension name="Category" foreignKey="productID">
          <Hierarchy hasAll="true" primaryKey="productID" primaryKeyTable="product">
             <Join leftKey="categoryID" rightKey="categoryID">
                <Table name="product"/>
                <Table name="category"/>
             <Level name="Category Name" table="category" column="category_name" uniqueMembers="true"/>

  2. #2


    Was this ever answered? Does Table Join work?

  3. #3
    Join Date
    Jan 2013


    It doesn't look like it was answered, but yes, table join does work, and there's no explicit requirement that primary/foreign keys be defined (although it's a really good idea to do so). There's an example in the [Product] dimension in the demo Foodmart.xml schema:

    <Dimension name="Product">
    <Hierarchy hasAll="true" primaryKey="product_id" primaryKeyTable="product">
    <Join leftKey="product_class_id" rightKey="product_class_id">
    <Table name="product"/>
    <Table name="product_class"/>

  4. #4
    Join Date
    Aug 2013


    I had the very same problem with an Oracle database. When I specified the foreign key relationship in the Oracle DB directly, everything worked out. Can you try that in your MySQL schema?

Tags for this Thread

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 - 2017 Pentaho Corporation. All Rights Reserved.