Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: how to define a dimension that is based on

  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Default how to define a dimension that is based on

    a table with 'where' clause?
    e.g. (FoodMart) Time dimension where year>1995

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default RE: how to define a dimension that is based on

    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/ope...doc/index.html

  3. #3
    Join Date
    Nov 1999
    Posts
    1,618

    Default RE: how to define a dimension that is based on


  4. #4
    Join Date
    Dec 2002
    Posts
    5

    Default RE: how to define a dimension that is based on

    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)

  5. #5
    Join Date
    Nov 1999
    Posts
    1,618

    Default RE: how to define a dimension that is based on

    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>

  6. #6
    Join Date
    Dec 2002
    Posts
    5

    Default RE: how to define a dimension that is based on

    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)

  7. #7
    Join Date
    Nov 1999
    Posts
    1,618

    Default RE: how to define a dimension that is based on

    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?

  8. #8
    Join Date
    Nov 1999
    Posts
    1,618

    Default RE: how to define a dimension that is based on

    Fixed in change 262.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.