Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Severe Severe
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Mondrian Backlog
    • Component/s: None
    • Labels:
      None
    • Customer Case:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      Mondrian incorrectly treats a member key ([x].[y].&[z]) as a member name ([x].[y].[z]) when it appears in the WHERE clause. As a result the member cannot be found.

      The following forum post gives details:

      http://forums.pentaho.org/showthread.php?p=203451

        Activity

        Hide
        Mondrian Importer User added a comment -
        {jhyde}

        , 12/17/2008: IP, Artifact Created: 66.92.12.96

        Show
        Mondrian Importer User added a comment - {jhyde} , 12/17/2008: IP, Artifact Created: 66.92.12.96
        Hide
        Paul Smith added a comment -

        oooh, we're looking at integrating Mondrian and other Pentaho components into a SaaS solution, and part of where we thought we could go is to dynamically create MDX and query a remote Mondrian server, using the "ID" property of some of the dimensions.

        In our dataset, only portions of the Cube can be seen by an individual based on their organization. I don't believe the Role concept is going to work for us, so right now my only solution was to try the WHERE using the ID and the ampersand.

        Is there a workaround that can be done using FILTER? My MDX skillz are pretty weak (a couple of days), so I'm struggling to find a solution that can support:

        SELECT
            NON EMPTY {
                
                [Activity].MEMBERS
                
                
            } on columns, 
            NON EMPTY {
                [Time].[Year].MEMBERS   
            } on rows
        FROM
            ProjectActivity
        
        WHERE [Organization].[organizationId].&[9146]
        
        ...
        (Schema snippet)
            <Dimension name="Organization">
                <Hierarchy hasAll="true" primaryKey="organizationId"
                    allMemberName="All Organizations">
                    <Table name="DimOrganization" />
                    <Level name="Organization" column="organizationId" nameColumn="organizationName"
                        uniqueMembers="true" />
                </Hierarchy>
            </Dimension>
        ...
        
            <Cube name="ProjectActivity" defaultMeasure="ActivityMetric">
                <Table name="FactProjectActivity" />
                <DimensionUsage name="Project" source="Project"
                    foreignKey="projectId" />
        
                <DimensionUsage name="Organization" source="Organization"
                    foreignKey="organizationId" />
        
                <DimensionUsage name="Activity" source="Activity"
                    foreignKey="activityId" />
        
                <DimensionUsage name="Time" source="Time"
                    foreignKey="datekey" />
                    
                <Measure name="ActivityMetric" column="ActivityMetric"
                    aggregator="sum" formatString="#,###" />
            </Cube>
        
        
        

        (this is just a test cube, it's the WHERE clause that's the important bit here)

        Any workaround, hack, trick etc ?

        Show
        Paul Smith added a comment - oooh, we're looking at integrating Mondrian and other Pentaho components into a SaaS solution, and part of where we thought we could go is to dynamically create MDX and query a remote Mondrian server, using the "ID" property of some of the dimensions. In our dataset, only portions of the Cube can be seen by an individual based on their organization. I don't believe the Role concept is going to work for us, so right now my only solution was to try the WHERE using the ID and the ampersand. Is there a workaround that can be done using FILTER? My MDX skillz are pretty weak (a couple of days), so I'm struggling to find a solution that can support: SELECT NON EMPTY { [Activity].MEMBERS } on columns, NON EMPTY { [Time].[Year].MEMBERS } on rows FROM ProjectActivity WHERE [Organization].[organizationId].&[9146] ... (Schema snippet) <Dimension name="Organization"> <Hierarchy hasAll="true" primaryKey="organizationId" allMemberName="All Organizations"> <Table name="DimOrganization" /> <Level name="Organization" column="organizationId" nameColumn="organizationName" uniqueMembers="true" /> </Hierarchy> </Dimension> ... <Cube name="ProjectActivity" defaultMeasure="ActivityMetric"> <Table name="FactProjectActivity" /> <DimensionUsage name="Project" source="Project" foreignKey="projectId" /> <DimensionUsage name="Organization" source="Organization" foreignKey="organizationId" /> <DimensionUsage name="Activity" source="Activity" foreignKey="activityId" /> <DimensionUsage name="Time" source="Time" foreignKey="datekey" /> <Measure name="ActivityMetric" column="ActivityMetric" aggregator="sum" formatString="#,###" /> </Cube> (this is just a test cube, it's the WHERE clause that's the important bit here) Any workaround, hack, trick etc ?
        Hide
        Carlos Esteban García added a comment -

        Please verify also that one can create a member grant inside a role using the keycolumn, something like this:

        <HierarchyGrant hierarchy="producto" rollupPolicy="partial" access="custom">
        <MemberGrant member="[producto].&[242]" access="all">
        </MemberGrant>
        </HierarchyGrant>

        Thx

        Show
        Carlos Esteban García added a comment - Please verify also that one can create a member grant inside a role using the keycolumn, something like this: <HierarchyGrant hierarchy="producto" rollupPolicy="partial" access="custom"> <MemberGrant member=" [producto] .& [242] " access="all"> </MemberGrant> </HierarchyGrant> Thx
        Hide
        Julian Hyde added a comment -

        Also, we should check that StrToMember, StrToSet and olap4j's PreparedStatement.setString(int) can handle such members.

        Show
        Julian Hyde added a comment - Also, we should check that StrToMember, StrToSet and olap4j's PreparedStatement.setString(int) can handle such members.
        Hide
        Carlos Esteban García added a comment -

        I did not want to create a new jira case for this improvement that would be great to have cause is really associated with this bug. (But if you guys decide should be in a diferent case it's good)

        Would be great if one could decide in the schema the way querys will be build in MDX, lets say one can choose for a specific member if the query is going to be generate using NameColumn or KeyColumn. In this way when the query for level user will be always generate like [user].[GROUP1].&[code_user]

        Could be something like this:

        <Dimension type="StandardDimension" name="user" caption="User">
        <Hierarchy name="user" hasAll="true" allMemberCaption="Total" primaryKey="id_user" caption="User">
        <Table name="dim_users" schema="public">
        </Table>
        <Level name="group_user" column="group_user" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Group (User)">
        </Level>
        <Level name="user" column="code_user" nameColumn="user" <Unable to render embedded object: File (-----------> MDXColumn = "column" <) not found.-----Can have only two options column or nameColumn------------> type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="User">
        </Level>
        </Hierarchy>
        </Dimension>

        Having this would be great cause when a user saves a analysis view and a name of a member has changed in DW, when he tries to open the view again he will get an error.

        Thanks

        Show
        Carlos Esteban García added a comment - I did not want to create a new jira case for this improvement that would be great to have cause is really associated with this bug. (But if you guys decide should be in a diferent case it's good) Would be great if one could decide in the schema the way querys will be build in MDX, lets say one can choose for a specific member if the query is going to be generate using NameColumn or KeyColumn. In this way when the query for level user will be always generate like [user] . [GROUP1] .& [code_user] Could be something like this: <Dimension type="StandardDimension" name="user" caption="User"> <Hierarchy name="user" hasAll="true" allMemberCaption="Total" primaryKey="id_user" caption="User"> <Table name="dim_users" schema="public"> </Table> <Level name="group_user" column="group_user" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Group (User)"> </Level> <Level name="user" column="code_user" nameColumn="user" < Unable to render embedded object: File (-----------> MDXColumn = "column" <) not found. ----- Can have only two options column or nameColumn ------------> type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="User"> </Level> </Hierarchy> </Dimension> Having this would be great cause when a user saves a analysis view and a name of a member has changed in DW, when he tries to open the view again he will get an error. Thanks
        Hide
        Stephen Morring added a comment -

        Carlos hit the nail on the head.

        If a user has saved a query as "select ... on columns,

        {[100 Vehicles].[110 Cars].[112 Some Car Name Here]}

        on rows from [My Cube]" any change in a name along the hierarchy will break the user's query. For example, if the warehouse picks up 110 as "Autos" going forward, rather than "Cars", then the saved query will break.

        I've considered using just the "key" in the cube definition and then programmatically looking up the "name" from the dimension tables anywhere that I output the results of the MDX queries, but that seems like a pretty harsh workaround.

        Thanks

        Show
        Stephen Morring added a comment - Carlos hit the nail on the head. If a user has saved a query as "select ... on columns, {[100 Vehicles].[110 Cars].[112 Some Car Name Here]} on rows from [My Cube] " any change in a name along the hierarchy will break the user's query. For example, if the warehouse picks up 110 as "Autos" going forward, rather than "Cars", then the saved query will break. I've considered using just the "key" in the cube definition and then programmatically looking up the "name" from the dimension tables anywhere that I output the results of the MDX queries, but that seems like a pretty harsh workaround. Thanks
        Hide
        Julian Hyde added a comment -

        Partially fixed in change 15004 (on main line, not in mondrian-3.4 release, but integrated to lagunitas branch (proto mondrian-4.0)).

        Still need to check all the use cases above: use in MemberGrant element, use in StrToMember, StrToSet, and setParameter().

        As of this change, we cannot do what Carlos is asking for in his 2009/10/15 comment. It could be achieved with a special mode in Analyzer, where it exports members in query models using the <level>.&key format rather than <dimension>.<m1>.<m2>...<mn> format. Or we could have a database-wide preference for the format of member unique names (this is what SSAS did... but it broke applications). Please log a jira case for this functionality.

        Show
        Julian Hyde added a comment - Partially fixed in change 15004 (on main line, not in mondrian-3.4 release, but integrated to lagunitas branch (proto mondrian-4.0)). Still need to check all the use cases above: use in MemberGrant element, use in StrToMember, StrToSet, and setParameter(). As of this change, we cannot do what Carlos is asking for in his 2009/10/15 comment. It could be achieved with a special mode in Analyzer, where it exports members in query models using the <level>.&key format rather than <dimension>.<m1>.<m2>...<mn> format. Or we could have a database-wide preference for the format of member unique names (this is what SSAS did... but it broke applications). Please log a jira case for this functionality.
        Hide
        Benny Chow added a comment -

        This feature request to use member IDs instead of member names in Analyzer's report XML definition makes sense. There will probably be significant changes to make this work because Analyzer has never dealt with members keyed by ID instead of name.

        Show
        Benny Chow added a comment - This feature request to use member IDs instead of member names in Analyzer's report XML definition makes sense. There will probably be significant changes to make this work because Analyzer has never dealt with members keyed by ID instead of name.

          People

          • Assignee:
            Unassigned User
            Reporter:
            Julian Hyde
          • Votes:
            7 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

            • Created:
              Updated: