<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman,new york,times,serif;font-size:12pt"><div>Hello, <br><br><span>I know there is another thread related to this one at <a rel="nofollow" target="_blank" href="http://forums.pentaho.org/showthread.php?t=61848">http://forums.pentaho.org/showthread.php?t=61848</a>, but i was unable to subscribe to that thread so sending out this one instead. </span><br><br>Here is a simple example that demonstrates the issue:<br>Given
the following simple schema, were i am defining two identical
dimensions one using query element and other using standard table
element<br><br style="color: rgb(0, 0, 255);"><font style="color: rgb(0, 0, 255);" size="2"><span style="font-family: arial,helvetica,sans-serif;">&lt;?xml version="1.0"?&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&lt;Schema name="QueryVsTable"&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp; &lt;Cube name="Sales" defaultMeasure="Unit Sales"&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp; &lt;Table name="sales_fact_1997"/&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Dimension name="ProductQuery"
 foreignKey="product_id"&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Hierarchy hasAll="true" primaryKey="product_id"&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Query&gt;</span><span style="font-family: arial,helvetica,sans-serif;"> &lt;SQL dialect="generic"&gt; SELECT * FROM "product_class" &lt;/SQL&gt;</span><span style="font-family: arial,helvetica,sans-serif;"> &lt;/Query&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Level name="Product Family" column="product_family"/&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
 arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/Hierarchy&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/Dimension&gt;</span><span style="font-family: arial,helvetica,sans-serif;"><br><br style="font-family: arial,helvetica,sans-serif;"></span><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Dimension name="ProductTable" foreignKey="product_id"&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Hierarchy hasAll="true" primaryKey="product_id"&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Table name="product_class"/&gt;</span><br
 style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Level name="Product Family" column="product_family"/&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/Hierarchy&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/Dimension&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Measure name="Unit Sales" column="unit_sales" aggregator="sum"/&gt; <br>&nbsp;&nbsp; </span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
 arial,helvetica,sans-serif;">&nbsp; &lt;/Cube&gt;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&lt;/Schema&gt;</span></font><br><br>and then running the snippet:<br><br><font style="color: rgb(0, 0, 255);" size="2"><span style="font-family: arial,helvetica,sans-serif;">import java.sql.DriverManager;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">import java.sql.SQLException;</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">import org.olap4j.OlapConnection;</span><br style="font-family: arial,helvetica,sans-serif;"><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">public class TestQuery {</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
 arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp; public static void main(String[] args) throws SQLException{</span><br style="font-family: arial,helvetica,sans-serif;"><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OlapConnection
 connection = DriverManager.getConnection(</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "jdbc:mondrian:Jdbc=jdbc:postgresql:" +</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "//localhost/foodmartdb?user=foodmart&amp;password=foodmart;" +</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Catalog=FoodMart.xml; " +</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
 arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "JdbcDrivers=org.postgresql.Driver")</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .unwrap(OlapConnection.class);</span><br style="font-family: arial,helvetica,sans-serif;"><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; int levelsInTable = connection.getSchema()</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
 arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getCubes()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .get( "Sales"&nbsp;&nbsp; )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getDimensions() .get( "ProductTable" )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getHierarchies().get(0)</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
 arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getLevels()&nbsp;&nbsp;&nbsp;&nbsp; .size();</span><br style="font-family: arial,helvetica,sans-serif;"><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; int levelsInQuery = connection.getSchema()</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getCubes()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .get( "Sales"&nbsp;&nbsp; )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
 arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getDimensions() .get( "ProductQuery" )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getHierarchies().get(0)</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getLevels()&nbsp;&nbsp;&nbsp;&nbsp; .size();</span><br style="font-family: arial,helvetica,sans-serif;"><br style="font-family:
 arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.out.format("Levels in Table = %d, Levels in Query = %d\n", levelsInTable, levelsInQuery );</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">&nbsp;&nbsp;&nbsp; }</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;">}</span></font><br><br>I am getting the output: <span style="color: rgb(0, 0, 255);">Levels in Table = 2, Levels in Query = 1</span><span>.
So it seems that there is problem with hierarchy construction when
using a Query element. This is then causing the problem highlighted in <a rel="nofollow" target="_blank" href="http://forums.pentaho.org/showthread.php?t=61848">http://forums.pentaho.org/showthread.php?t=61848</a> where Mondrian throws "[Dimension].[Level1].[Level2] not found in cube" error. I&nbsp;
 tryed to debug it through, but quickly got lost in the code. Any help would be
 greatly appreciated. Currently I am using "mondrian-3.0.4.11371"</span><br><br>Thanks, <br>ikusha<br></div></div><br>

      </body></html>