<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;"><?xml version="1.0"?></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"><Schema name="QueryVsTable"></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Cube name="Sales" defaultMeasure="Unit Sales"></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Table name="sales_fact_1997"/></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;"> <Dimension name="ProductQuery"
foreignKey="product_id"></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Hierarchy hasAll="true" primaryKey="product_id"></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Query></span><span style="font-family: arial,helvetica,sans-serif;"> <SQL dialect="generic"> SELECT * FROM "product_class" </SQL></span><span style="font-family: arial,helvetica,sans-serif;"> </Query></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Level name="Product Family" column="product_family"/></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
arial,helvetica,sans-serif;"> </Hierarchy></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> </Dimension></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;"> <Dimension name="ProductTable" foreignKey="product_id"></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Hierarchy hasAll="true" primaryKey="product_id"></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Table name="product_class"/></span><br
style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> <Level name="Product Family" column="product_family"/></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> </Hierarchy></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> </Dimension></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;"> <Measure name="Unit Sales" column="unit_sales" aggregator="sum"/> <br> </span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
arial,helvetica,sans-serif;"> </Cube></span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"></Schema></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;"> 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;"> OlapConnection
connection = DriverManager.getConnection(</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> "jdbc:mondrian:Jdbc=jdbc:postgresql:" +</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> "//localhost/foodmartdb?user=foodmart&password=foodmart;" +</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> "Catalog=FoodMart.xml; " +</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
arial,helvetica,sans-serif;"> "JdbcDrivers=org.postgresql.Driver")</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> .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;"> int levelsInTable = connection.getSchema()</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
arial,helvetica,sans-serif;"> .getCubes() .get( "Sales" )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> .getDimensions() .get( "ProductTable" )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> .getHierarchies().get(0)</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
arial,helvetica,sans-serif;"> .getLevels() .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;"> int levelsInQuery = connection.getSchema()</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> .getCubes() .get( "Sales" )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family:
arial,helvetica,sans-serif;"> .getDimensions() .get( "ProductQuery" )</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> .getHierarchies().get(0)</span><br style="font-family: arial,helvetica,sans-serif;"><span style="font-family: arial,helvetica,sans-serif;"> .getLevels() .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;"> 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;"> }</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
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>