PDA

View Full Version : how to define a dimension that is based on



raul12
12-10-2002, 02:32 AM
a table with 'where' clause?
e.g. (FoodMart) Time dimension where year>1995

jhyde
12-10-2002, 03:12 AM
1. Define a view (if your database supports them).
2. Use the <Query> element instead of <Table>, for example
<Dimension name="Time" foreignKey="time_id">
<Hierarchy hasAll="false" primaryKey="time_id">
<Quey dialect="generic">
<SQL><![CDATA[select * from time_by_day where year > 1995]]></SQL>
</Query>
<Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/>
<Level name="Quarter" column="quarter" uniqueMembers="false"/>
<Level name="Month" column="month_of_year" uniqueMembers="false" type="Numeric"/>
</Hierarchy>
</Dimension>
(the <Query> element used to be called <View>, which is why the commented-out text in FoodMart.xml is misleading).
For more information see
http://apoptosis.dyndns.org:8080/open/mondrian/doc/index.html

jhyde
12-10-2002, 03:17 AM
Oops, I meant
http://apoptosis.dyndns.org:8080/open/mondrian/doc/schema.html

raul12
12-10-2002, 08:00 PM
Gracias
FoodMartTestCase.java works fine with original FoodMart.xml but after modification (Table->Query):
testSample0(mondrian.test.FoodMartTestCase)
mondrian.resource.ChainableRuntimeException: Internal error: while building member cache; sql=[]
at mondrian.olap.MondrianResource.newInternal(MondrianResource.java:45)
at mondrian.rolap.SqlMemberSource.getMembers(SqlMemberSource.java:259)
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.test.TestContext.getFoodMartConnection(TestContext.java:131)
at mondrian.test.FoodMartTestCase.getConnection(FoodMartTestCase.java:45)
at mondrian.test.FoodMartTestCase.runQuery(FoodMartTestCase.java:39)
at mondrian.test.FoodMartTestCase.runQueryCheckResult(FoodMartTestCase.java:121)
at mondrian.test.FoodMartTestCase.runQueryCheckResult(FoodMartTestCase.java:114)
at mondrian.test.FoodMartTestCase.testSample0(FoodMartTestCase.java:609)

jhyde
12-21-2002, 01:46 PM
I was wrong: what used to be <Query> is now <View>. It also has a mandatory 'alias' attribute. And I recommend that you quote the SQL using <![CDATA[ ... ]]>. So, this works:
<Dimension name="Gender2" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Gender" primaryKey="customer_id">
<View alias="gender2">
<SQL dialect="generic">
<![CDATA[SELECT * FROM customer]]>
</SQL>
</View>
<Level name="Gender" column="gender" uniqueMembers="true"/>
</Hierarchy>
</Dimension>

raul12
01-05-2003, 09:37 PM
SQL query for time dim: SELECT * FROM "time_by_day" WHERE "the_year" BETWEEN 1995 AND 1997
Result:
mondrian.resource.ChainableRuntimeException: Internal error: bad relation type SELECT * FROM "time_by_day" WHERE "the_year" BETWEEN 1995 AND 1997
at mondrian.olap.MondrianResource.newInternal(MondrianResource.java:42)
at mondrian.olap.Util.newInternal(Util.java:293)
at mondrian.rolap.RolapStar$Table.addJoin(RolapStar.java:350)
at mondrian.rolap.RolapCube.register(RolapCube.java:288)
at mondrian.rolap.RolapCube.init(RolapCube.java:239)
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.test.TestContext.getFoodMartConnection(TestContext.java:131)
at mondrian.test.FoodMartTestCase.getConnection(FoodMartTestCase.java:45)
at mondrian.test.FoodMartTestCase.runQuery(FoodMartTestCase.java:39)
at mondrian.test.FoodMartTestCase.runQueryCheckResult(FoodMartTestCase.java:121)
at mondrian.test.FoodMartTestCase.runQueryCheckResult(FoodMartTestCase.java:114)
at mondrian.test.FoodMartTestCase$1.run(FoodMartTestCase.java:1724)
at mondrian.test.TestCaseForker$1.run(FoodMartTestCase.java:1779)

jhyde
01-05-2003, 10:00 PM
Looks like a bug: the piece of code which joins a dimension to the fact table can't handle arbitrary SQL statements.
I can fix this, but the fix will only work if your database supports SELECT in the FROM clause (aka inline views). What database are you using?

jhyde
01-06-2003, 04:51 PM
Fixed in change 262.