I use Pentaho 1.6GA with Oracle 10G. When I defined a Cube with Dimensions it works fine. When I move these dimension so that they can be shared across multiple fact table I get "Error Occurred While getting Resultset" when expand one of the dimensions on the JPivot UI. I looked at log file but does not give me any more details. The OLAP engine is breaking down before generating SQL query.

The orginal working version:

<Schema name="InventoryCube">
<Cube name="InventoryCube">
<Table name="INV"/>
<Dimension name="Locations" foreignKey="DDCKEY">
<Hierarchy name="Locations" hasAll="true" allMemberName="All locations" primaryKey="BUSNLOCKEY" primaryKeyTable="DIMBUSINESSLOCATIONS">
<Join leftKey="ACCOUNTKEY" rightKey="ACCOUNTKEY">
<Table name="DIMBUSINESSLOCATIONS"/>
<Table name="DIMACCOUNTS"/>
</Join>
<Level name="ACCOUNTNAME" table="DIMACCOUNTS" column="ACCOUNTNAME" uniqueMembers="true"/>
<Level name="BUSNLOCNAME" table="DIMBUSINESSLOCATIONS" column="BUSNLOCNAME" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="Stores" foreignKey="STOREKEY">
<Hierarchy name="Stores" hasAll="true" allMemberName="All stores" primaryKey="STOREKEY">
<Table name="NEWDIMSTORES"/>
<Level name="STORENAME" table="NEWDIMSTORES" column="STORENAME" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="Products" foreignKey="PRODUCTKEY">
<Hierarchy name="Products" hasAll="true" allMemberName="All products" primaryKey="PRODUCTKEY" primaryKeyTable="DIMPRODUCTS">
<Join leftKey="SUBCATEGORYKEY" rightKey="SUBCATEGORYKEY">
<Table name="DIMPRODUCTS"/>
<Table name="DIMPRODSUBCATEGORY"/>
</Join>
<Level name="SUBCATEGORYNAME" table="DIMPRODSUBCATEGORY" column="SUBCATEGORYNAME" uniqueMembers="false"/>
<Level name="PRODUCTNAME" table="DIMPRODUCTS" column="PRODUCTNAME" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="Periods" foreignKey="DATEKEY">
<Hierarchy name="Periods" hasAll="true" allMemberName="All periods" primaryKey="DATEKEY">
<Table name="DIMTIME"/>
<Level name="CALENDARYEAR" table="DIMTIME" column="CALENDARYEAR" uniqueMembers="false"/>
<Level name="CALENDARQUARTER" table="DIMTIME" column="CALENDARQUARTER" uniqueMembers="true"/>
<Level name="CALENDARMONTHNAME" table="DIMTIME" column="CALENDARMONTHNAME" ordinalColumn="CALENDARMONTHNOOFYEAR"
uniqueMembers="false"/>
<Level name="WEEKCODE" table="DIMTIME" column="WEEKCODE" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Measure name="MAX of NETINV" column="NETINV" aggregator="max" datatype="Numeric" formatString="#,##0.###"/>
</Cube>
</Schema>

The following shared version that gives error:

<?xml version="1.0" encoding="UTF-8"?>
<Schema name="Panasonic">
<!-- Shared dimensions -->
<Dimension name="Locations">
<Hierarchy name="Locations" hasAll="true" allMemberName="All locations" primaryKey="BUSNLOCKEY" primaryKeyTable="DIMBUSINESSLOCATIONS">
<Join leftKey="ACCOUNTKEY" rightKey="ACCOUNTKEY">
<Table name="DIMBUSINESSLOCATIONS"/>
<Table name="DIMACCOUNTS"/>
</Join>
<Level name="ACCOUNTNAME" table="DIMACCOUNTS" column="ACCOUNTNAME" uniqueMembers="true"/>
<Level name="BUSNLOCNAME" table="DIMBUSINESSLOCATIONS" column="BUSNLOCNAME" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="Stores">
<Hierarchy name="Stores" hasAll="true" allMemberName="All stores" primaryKey="STOREKEY">
<Table name="NEWDIMSTORES"/>
<Level name="STORENAME" table="NEWDIMSTORES" column="STORENAME" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="Products">
<Hierarchy name="Products" hasAll="true" allMemberName="All products" primaryKey="PRODUCTKEY" primaryKeyTable="DIMPRODUCTS">
<Join leftKey="SUBCATEGORYKEY" rightKey="SUBCATEGORYKEY">
<Table name="DIMPRODUCTS"/>
<Table name="DIMPRODSUBCATEGORY"/>
</Join>
<Level name="SUBCATEGORYNAME" table="DIMPRODSUBCATEGORY" column="SUBCATEGORYNAME" uniqueMembers="false"/>
<Level name="PRODUCTNAME" table="DIMPRODUCTS" column="PRODUCTNAME" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="Periods">
<Hierarchy name="Periods" hasAll="true" allMemberName="All periods" primaryKey="DATEKEY">
<Table name="DIMTIME"/>
<Level name="CALENDARYEAR" table="DIMTIME" column="CALENDARYEAR" uniqueMembers="false"/>
<Level name="CALENDARQUARTER" table="DIMTIME" column="CALENDARQUARTER" uniqueMembers="true"/>
<Level name="CALENDARMONTHNAME" table="DIMTIME" column="CALENDARMONTHNAME" ordinalColumn="CALENDARMONTHNOOFYEAR"
uniqueMembers="false"/>
<Level name="WEEKCODE" table="DIMTIME" column="WEEKCODE" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Cube name="InventoryCube">
<Table name="INV"/>
<DimensionUsage name="Products" source="Products" foreignKey="ProductKey"></DimensionUsage>
<DimensionUsage name="Locations" source="Locations" foreignKey="DDCKey"></DimensionUsage>
<DimensionUsage name="Stores" source="Stores" foreignKey="StoreKey"></DimensionUsage>
<DimensionUsage name="Periods" source="Periods" foreignKey="DateKey"></DimensionUsage>
<Measure name="MAX of NETINV" column="NETINV" aggregator="max" datatype="Numeric" formatString="#,##0.###"/>
</Cube>
</Schema>

I will very much appreciate any help on this.

Regards,

Ronak