PDA

View Full Version : q: join does not work



janheise
11-23-2002, 12:29 AM
i try to use <join> in a dimension but it does not work as expected. i enabled logging of the sql-statements and the following sql statement makes the db whine:
select "store_hierarchy"."toplevel" as "c0", "store_hierarchy"."intermediate" as "c1", "store"."kdnr" as "c2" from "store" as "store" order by "store_hierarchy"."toplevel", "store_hierarchy"."intermediate", "store"."kdnr"
i changed that to include the join:
select "store_hierarchy"."toplevel" as "c0", "store_hierarchy"."intermediate" as "c1", "store"."kdnr" as "c2" from "store" as "store" where "store_hierarchy"."store_id" = "store"."store_id" order by "store_hierarchy"."toplevel", "store_hierarchy"."intermediate", "store"."kdnr"
this runs fine from the command-line, but where do i have to look in mondrian to check for misuse or a probable bug?
thx, jan
<Dimension name="Store">
<Hierarchy hasAll="true" primaryKey="store_id" primaryKeyTable="store">
<!--
<Query>
<SQL dialect="generic">
SELECT kdnr, toplevel, intermediate
FROM "store", "store_hierarchy"
WHERE "store"."store_id" = "store_hierarchy"."store_id"
</SQL>
</Query>
<Level name="Handelskette" column="toplevel" uniqueMembers="true"/>
<Level name="Mittelding" column="intermediate" uniqueMembers="true"/>
<Level name="Name" column="kdnr" uniqueMembers="true"/>
-->
<Join leftKey="store_id" rightKey="store_id" type="inner">
<Table name="store"/>
<Table name="store_hierarchy"/>
</Join>
<Level name="Handelskette" table="store_hierarchy" column="toplevel" uniqueMembers="true"/>
<Level name="Mittelding" table="store_hierarchy" column="intermediate" uniqueMembers="true"/>
<Level name="Name" table="store" column="kdnr" uniqueMembers="true"/>
</Hierarchy>
</Dimension>

janheise
11-23-2002, 12:46 AM
using the <query> (see below) creates an empty sql-statement which throws an exception in executeQuery.
at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:156)
at mondrian.rolap.SqlMemberSource.getMembers(SqlMemberSource.java:213)
at mondrian.rolap.CacheMemberReader.<init>(CacheMemberReader.java:41)
at mondrian.rolap.RolapSchema.createMemberReader(RolapSchema.java:226)
at mondrian.rolap.RolapSchema.createMemberReader(RolapSchema.java:161)
at mondrian.rolap.RolapHierarchy.init(RolapHierarchy.java:160)
at mondrian.rolap.RolapDimension.init(RolapDimension.java:109)
at mondrian.rolap.RolapCube.init(RolapCube.java:222)
at mondrian.rolap.RolapCube.<init>(RolapCube.java:78)
at mondrian.rolap.RolapSchema.load(RolapSchema.java:84)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:72)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:33)
at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:113)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:68)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:42)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:38)
at mondrian.web.servlet.MDXQueryServlet.processRequest(MDXQueryServlet.java:81)
at mondrian.web.servlet.MDXQueryServlet.doGet(MDXQueryServlet.java:168)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:126)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:103)
here comes the complete query:
<Query>
<SQL dialect="generic">
SELECT "store_hierarchy"."toplevel" as "c0", "store_hierarchy"."intermediate" as "c1", "store"."kdnr" as "c2"
FROM "store" as "store"
WHERE "store"."store_id" = "store_hierarchy"."store_id"
ORDER BY "store_hierarchy"."toplevel", "store_hierarchy"."intermediate", "store"."kdnr"
</SQL>
</Query>
any suggestions?
jan

jhyde
11-23-2002, 01:58 PM
Regarding <Query>. I changed the syntax a while ago, and forgot to change the example in FoodMart.xml (because the code is commented out!). The <Query> tag is now <View>:
<View>
<SQL dialect="generic">
SELECT "store_hierarchy"."toplevel" as "c0", "store_hierarchy"."intermediate" as "c1", "store"."kdnr" as "c2"
FROM "store" as "store"
WHERE "store"."store_id" = "store_hierarchy"."store_id"
ORDER BY "store_hierarchy"."toplevel", "store_hierarchy"."intermediate", "store"."kdnr"
</SQL>
</View>
If the query happens to be a join query, say "select * from emp join dept on emp.deptno = dept.deptno", note that mondrian can't see the tables. Therefore you should not use table names in your <Hierarchy> and <Level> elements.
I don't know what happens if you do -- probably it just ignores them. We should validate this, but we don't currently.

jhyde
11-23-2002, 02:13 PM
Regarding <Join>. I think this is a bug I introduced fairly recently, when I added the ORDER BY clause to ensure that members came back in deterministic order. Looks like it's sorting on columns from the "store_hierarchy", but forgetting to add that table into the FROM clause.
I still need to do more investigation of this, but try this workaround. Set uniqueMembers="true" for each level, then it should (well, might) stop walking up the hierarchy and adding columns to ORDER BY.
The code, incidentally, is in class mondrian.rolap.SqlMemberSource, probably the method 'String makeKeysSql()'.

janheise
11-24-2002, 08:01 AM
i changed from query to view and removed the table-attributes (they are not ignored, but i forgot to copy the exception...) and this is the exception i get:
21:00:13,737 DEBUG SqlQuery:312 - addFrom(MondrianDef.Relation relation, boolean failIfExists)
mondrian.resource.ChainableRuntimeException: Internal error: assert failed
at mondrian.olap.MondrianResource.newInternal(MondrianResource.java:42)
at mondrian.olap.Util.assertTrue(Util.java:277)
at mondrian.rolap.sql.SqlQuery.addFromQuery(SqlQuery.java:258)
at mondrian.rolap.sql.SqlQuery.addFrom(SqlQuery.java:318)
at mondrian.rolap.RolapHierarchy.addToFrom(RolapHierarchy.java:427)
at mondrian.rolap.SqlMemberSource.makeLevelMemberCountSql(SqlMemberSource.java:197)
at mondrian.rolap.SqlMemberSource.getLevelMemberCount(SqlMemberSource.java:94)
at mondrian.rolap.SqlMemberSource.getMemberCount(SqlMemberSource.java:78)
at mondrian.rolap.RolapSchema.createMemberReader(RolapSchema.java:222)
at mondrian.rolap.RolapSchema.createMemberReader(RolapSchema.java:161)
at mondrian.rolap.RolapHierarchy.init(RolapHierarchy.java:164)
at mondrian.rolap.RolapDimension.init(RolapDimension.java:109)
at mondrian.rolap.RolapCube.init(RolapCube.java:222)
at mondrian.rolap.RolapCube.<init>(RolapCube.java:78)
at mondrian.rolap.RolapSchema.load(RolapSchema.java:84)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:72)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:33)
at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:113)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:68)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:42)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:38)
at mondrian.web.servlet.MDXQueryServlet.processRequest(MDXQueryServlet.java:81)
at mondrian.web.servlet.MDXQueryServlet.doGet(MDXQueryServlet.java:168)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:126)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:103)
at com.caucho.server.http.FilterChainServlet.doFilter(FilterChainServlet.java:96)
at com.caucho.server.http.Invocation.service(Invocation.java:312)
at com.caucho.server.http.CacheInvocation.service(CacheInvocation.java:135)
at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:221)
at com.caucho.server.http.HttpRequest.handleConnection(HttpRequest.java:163)
at com.caucho.server.TcpConnection.run(TcpConnection.java:137)
at java.lang.Thread.run(Thread.java:536)

jhyde
11-24-2002, 10:02 AM
I know you're just trying to solve one problem, but we're running into at least 2 separate bugs here (with Query and Join), and I am having trouble figuring out what's going on.
Can you please log a bug for each problem you are experiencing. In the bug, describe which version of mondrian are you using, and include a full test-case: (a) all necessary CREATE TABLE commands, (b) any necessary data, (c) your schema.xml, (d) the query you are running, if any.

jhyde
11-24-2002, 01:04 PM
I fixed some problems relating to <Hierarchy> based upon <Join> in change 237.

jhyde
11-24-2002, 01:13 PM
By the way, I just checked in the beginnings of a document on how to create a schema.
http://apoptosis.dyndns.org:8080/open/mondrian/doc/schema.html
PLEASE CONTRIBUTE TO THIS DOCUMENT!! All hints, tips and scholarly contributions will be welcome.