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?
<Dimension name="Category" foreignKey="productID">
<Hierarchy hasAll="true" primaryKey="productID" primaryKeyTable="product">
<Join leftKey="categoryID" rightKey="categoryID">
<Level name="Category Name" table="category" column="category_name" uniqueMembers="true"/>