Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Two Levels possible when using Closure?

  1. #1
    Join Date
    Aug 2007
    Posts
    14

    Question Two Levels possible when using Closure?

    Hi!

    In my cube definition i use 2 levels in 1 hierarchy, the first one uses a closure table, the second one doesnt.
    I want the result look like this:

    Code:
    Building1
       Building1.Machine1
          Building1.Machine1.Part1
             ParameterA
             ParameterB
             ParameterC
       Building1.Machine2
          Building1.Machine2.Part1
             ParameterA
    ... and so on

    The problem is that either the closure is working and the Building/Machine/Part-Hierarchy is correctly displayed but there are no parameters and values shown,
    or the values are there, but the Hierarchy is flat - i.e. only the lowest level is shown.

    My cube looks like this at the moment:

    Code:
    <Cube name="AllesFlach" cache="true" enabled="true">
            <Table name="allesflach" />
            <Dimension foreignKey="siteEl_id" name="SiteElement">
                <Hierarchy name="SiteElement Hierarchy" hasAll="true" primaryKey="siteEl_id">
                    <Table name="param_site_el" />
                    <Level name="SiteElement" table="param_site_el" parent_column="parent_se_id" column="siteEl_id" nameColumn="siteEl_name" type="Numeric" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                        <Closure parentColumn="parent_id" childColumn="child_id">
                            <Table name="site_element_closure" />
                        </Closure>
                    </Level>
                    <Level name="Parameter" table="param_site_el" column="param_id" nameColumn="param_name" ordinalColumn="" type="Numeric" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                        <Property name="Param name" column="param_name" type="String" />
                    </Level>
                </Hierarchy>
            </Dimension>
            <Measure name="Min value" column="int_value" datatype="Integer" aggregator="min" visible="true" />
            <Measure name="Avg value" column="int_value" datatype="Integer" aggregator="avg" visible="true" />
            <Measure name="Max value" column="int_value" datatype="Integer" aggregator="max" visible="true" />
        </Cube>
    Does anyone have an idea how to get this hierarchy working correctly?

    thanks, Sebastian

  2. #2
    Join Date
    Aug 2007
    Posts
    14

    Default

    I managed to get the Hierarchy working but i have still no data visible in the cells. I checked the SQL statements that are sent to the database and they seem ok - when i run them manually i get some data back.
    Why doesnt that data show up in the table? The only row which has values is the "all"-row, any other rows are empty.
    Did anyone have a similar problem an can give me a hint how to resolve it?

    thanks, Sebastian

  3. #3
    Join Date
    Aug 2007
    Posts
    14

    Default

    Ok, i've got the values now. The problem was that the data type of the columns in the closure table (unsigned integer) did not match the data type of the columns in the dimension table (signed int).
    Now my only question is whether it is possible to use a second level after a level containing a closure or not?
    Any hint would be great.

    Sebastian

  4. #4
    Join Date
    Nov 2007
    Posts
    15

    Default

    I'd like to have an answer too.
    I have a "simple" hierarchy in which after several levels determined by the closure I'd like to show the detail levels but I can't figure out how to make mondrian work with theese levels.

    My dimension is defined as:
    Code:
    	<Dimension name="Uc">
    		<Hierarchy hasAll="true" allMemberName="All Ucs" primaryKeyTable="EMPLOYEE" primaryKey="ID">
    			<Join leftKey="ID_CDC" rightKey="ID" rightAlias="CDC">
    				<Table name="EMPLOYEE"/>
    				<Join leftKey="ID_UC" rightKey="ID">
    					<Table name="CDC"/>
    					<Table name="UC"/>
    				</Join>
    			</Join>
    			<Level name="Uc" table="UC" column="ID" nameColumn="CODE" parentColumn="ID_UC_FATHER" uniqueMembers="true">
    				<Closure parentColumn="ID_UC_FATHER" childColumn="ID_UC">
    					<Table name="UC_CLOSURE"/>
    				</Closure>
    			</Level>
    			<Level name="Cdc" table="CDC" column="ID" nameColumn="CODE"/>
    			<Level name="Employee" table="EMPLOYEE" column="ID" nameColumn="CODE">
    				<Property name="Partime" column="PARTIME"/>
    			</Level>
    		</Hierarchy>
    	</Dimension>
    But i can't access to the "Cdc" and "Employee" levels.

    If I flat down the "Uc" closure, as it follows:
    Code:
    	<Dimension name="Uc">
    		<Hierarchy hasAll="true" allMemberName="All Ucs" primaryKeyTable="EMPLOYEE" primaryKey="ID">
    			<Join leftKey="ID_CDC" rightKey="ID" rightAlias="CDC">
    				<Table name="EMPLOYEE"/>
    				<Join leftKey="ID_UC" rightKey="ID">
    					<Table name="CDC"/>
    					<Table name="UC"/>
    				</Join>
    			</Join>
    			<Level name="Uc" table="UC" column="ID" nameColumn="CODE" uniqueMembers="false"/>
    			<Level name="Cdc" table="CDC" column="ID" nameColumn="CODE"/>
    			<Level name="Employee" table="EMPLOYEE" column="ID" nameColumn="CODE">
    				<Property name="Partime" column="PARTIME"/>
    			</Level>
    		</Hierarchy>
    	</Dimension>
    I can analyze every level I have defined.

    Is this a limitation of the engine or I am doing something wrong?

    Thanks

  5. #5
    Join Date
    Aug 2007
    Posts
    14

    Default

    As far as i know it is still not possible to use multiple levels when one of them contains a closure

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.