Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

SnowFlake Dim And Dim base on view.

Help
Marty
2004-10-07
2013-04-29
  • Marty
    Marty
    2004-10-07

    Hi all,

    (Sorry to post this question on two forums, I need the solution very urgently and it seems here got more people than mondrian forum :P)
    I have asked similar question before in thread:
    http://sourceforge.net/forum/forum.php?thread_id=1110157&forum_id=111376

    Andreas said that the order of the join elements is significant.

    After few weeks, I am working back to this problem.
    It seems that I can not figure out what should I do to solve this.

    Give an example:
    Assuming we have four tables: boxoffice, movie, movieclass, class.
    I defined a Dimension like this:
    <Dimension name="Dim" foreignKey="MovieID">
    <Hierarchy hasAll="true" allMemberName="All Members" primaryKey="ID" primaryKeyTable="movie">
    <Join rightKey="MovieID" rightAlias="movieclass" leftKey="ID">
    <Table name="movie" schema="MovieDB.dbo"/>
    <Join leftKey="ClassID" rightKey="ID">
    <Table name="movieclass" schema="MovieDB.dbo"/>
    <Table name="class" schema="MovieDB.dbo"/>
    </Join>
    </Join>
    <Level name="CName" column="ClassName" table="class"/>
    <Level name="MName" column="MovieName" table="movie"/>
    </Hierarchy></Dimension>

    It works fine. However as a normal user of a program, they may construct the join in a diff way like this:
    <Join leftKey="MovieID" leftAlias="movieclass" rightKey="ID">
    <Join leftKey="ClassID" rightKey="ID">
    <Table name="movieclass" schema="MovieDB.dbo"/>
    <Table name="class" schema="MovieDB.dbo"/>
    </Join>
    <Table name="movie" schema="MovieDB.dbo"/>
    </Join>

    OR

    <Join rightKey="MovieID" rightAlias="movieclass" leftKey="ID">
    <Table name="movie" schema="MovieDB.dbo"/>
    <Join leftKey="ID" rightKey="ClassID">
    <Table name="class" schema="MovieDB.dbo"/>
    <Table name="movieclass" schema="MovieDB.dbo"/>
    </Join>
    </Join>

    Both will lead to errors when you play with it. (for the latter one, error occurs when trying expand nodes);

    The reason is some sql statements generated are not completes. (lacking some table names).

    I have tried to trace some vars in ROlapStar.java: Table.addToFrom method. I just cannot understand
    the parent of a table very clearly. When building FROM Clause, it will add the table then the parent, then the parent of
    the parent till the top one. As the last sample above, error occurs when count the level "CName".
    RolapStar.Column.getCardinality: executing sql [select count(distinct "class"."Name") as "c0" from "MovieDB"."dbo"."class" as "class", "MovieDB"."dbo"."movie" as "movie", "MovieDB"."dbo"."boxoffice" as "boxoffice" where "boxoffice"."MovieID" = "movie"."ID" and "movie"."ID" = "movieclass"."MovieID"], failed (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The column prefix 'movieclass' does not match with a table name or alias name used in the query.)
    Table "movieclass" which is below table:class is missing.

    It is easy to write the right join sequence if only 2,3,4 tables. But if the dimension is built on 5,10, or more tables.
    I need to know the rules of mondrian. This is my point.
    I wrote a program to generate the xml after user doing a join by drag and drop.
    I need to write correct procedure to generate suitable Join tree for mondrian.
    For Example: User defined the join relationships of Table1 -- Table10. We can not ask them to
    build mondrian xml by themselves. I need to do that.
    Can somebody help me on this? Thanks.

    Actually, I think probably we can modify some mondrian code so that the order of joins could be ignored.
    Is there any suggestions?

    ===========================================================================================

    Besides, I tried to use view instead of Join to build dimension. Faild. always assert true failed (column names and table names)
    Any one can give me a correct example to do a dimension based on View?

    best regards
    Marty