Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-1227

Properties function does not implicitly convert dimension to member; has documentation typos

    Details

    • QA Validation Status:
      Validated by QA

      Description

      Go to http://mondrian.pentaho.com/documentation/schema.php#Calculated_members

      Note the following example:

      <CalculatedMember name="Store Sqft" dimension="Measures" visible="false">
      <Formula>[Store].Properties("Sqft")</Formula>

      According to the MDX specification for the Properties function, the syntax is the following:

      Member_Expression.Properties(Property_Name [, TYPED])

      See http://msdn.microsoft.com/en-us/library/ms144821.aspx

      If you attempt to use Dimension.Properties(Property_Name), you will receive the following error in your logs:

      mondrian.olap.MondrianException: Mondrian Error:No function matches signature '<Dimension>.Properties(<String>)'

      Also note that in the example, the CalculatedMember is not closed.

        Activity

        Hide
        Tiago Gomes Ferreira added a comment - - edited
        In FoodMart the "Store" dimension used in Sales cube has "Store Name" as its last level, which is where the "Store Sqft" property is defined:

        <Dimension name="Store">
          <Hierarchy hasAll="true" primaryKey="store_id">
            <Table name="store"/>
            ...
            <Level name="Store Name" column="store_name" uniqueMembers="true">
              ...
              <Property name="Store Sqft" column="store_sqft" type="Numeric"/>
              ...
            </Level>
          </Hierarchy>
        </Dimension>

        When using this on members at the "Store Name" level, eg [Store].[USA].[CA].[Beverly Hills].[Store 6], the property works ok.
        For any other level the property is undefined, so using the measure in a query that has only [Store].[USA] will yield an error
        as the property does not exist at the "Store Country" level.

        From a purely functional standpoint this seems to me the right behavior for mondrian.
        Admittedly it isn't very friendly to get that error on an introductory example, so maybe the measure definition should be
        changed to something more permissive.
        I see two options:

          1) just yielding null:

            <CalculatedMember name="Store Sqft" dimension="Measures" visible="false">
              <Formula>
                  IIF([Store].CurrentMember.Level.Name = "Store Name",
                    [Store].Properties("Store Sqft"),
                    NULL)
              </Formula>
            </CalculatedMember>

          2) or aggregating:

            <Formula>
              IIF([Store].CurrentMember.Level.Name = "Store Name",
                    [Store].Properties("Store Sqft"),
                    SUM(Descendants([Store].CurrentMember, [Store].Levels("Store Name")), [Store].Properties("Store Sqft")) )
            </Formula>

        Should we change Calculated Members example to one of these?

        Also Analyzer's message, «Your last operation involves a calculated measure that uses an unsupported total type» seems more confusing than the one given by mondrian «Property 'Store Sqft' is not valid for member '[Store].[USA]'».
        Should this be an analyzer jira issue?
        Show
        Tiago Gomes Ferreira added a comment - - edited In FoodMart the "Store" dimension used in Sales cube has "Store Name" as its last level, which is where the "Store Sqft" property is defined: <Dimension name="Store">   <Hierarchy hasAll="true" primaryKey="store_id">     <Table name="store"/>     ...     <Level name="Store Name" column="store_name" uniqueMembers="true">       ...       <Property name="Store Sqft" column="store_sqft" type="Numeric"/>       ...     </Level>   </Hierarchy> </Dimension> When using this on members at the "Store Name" level, eg [Store].[USA].[CA].[Beverly Hills].[Store 6], the property works ok. For any other level the property is undefined, so using the measure in a query that has only [Store].[USA] will yield an error as the property does not exist at the "Store Country" level. From a purely functional standpoint this seems to me the right behavior for mondrian. Admittedly it isn't very friendly to get that error on an introductory example, so maybe the measure definition should be changed to something more permissive. I see two options:   1) just yielding null:     <CalculatedMember name="Store Sqft" dimension="Measures" visible="false">       <Formula>           IIF([Store].CurrentMember.Level.Name = "Store Name",             [Store].Properties("Store Sqft"),             NULL)       </Formula>     </CalculatedMember>   2) or aggregating:     <Formula>       IIF([Store].CurrentMember.Level.Name = "Store Name",             [Store].Properties("Store Sqft"),             SUM(Descendants([Store].CurrentMember, [Store].Levels("Store Name")), [Store].Properties("Store Sqft")) )     </Formula> Should we change Calculated Members example to one of these? Also Analyzer's message, «Your last operation involves a calculated measure that uses an unsupported total type» seems more confusing than the one given by mondrian «Property 'Store Sqft' is not valid for member '[Store].[USA]'». Should this be an analyzer jira issue?
        Hide
        Julian Hyde added a comment -
        Let's do option #1.

        There may also be an analyzer issue (unhelpful error message) but let's log that as a separate issue.
        Show
        Julian Hyde added a comment - Let's do option #1. There may also be an analyzer issue (unhelpful error message) but let's log that as a separate issue.
        Hide
        Pedro Vale added a comment -
        Solved according to comments on the JIRA.

        Assigning to Julian for validation/integration of pull request

        https://github.com/pentaho/mondrian/pull/25
        Show
        Pedro Vale added a comment - Solved according to comments on the JIRA. Assigning to Julian for validation/integration of pull request https://github.com/pentaho/mondrian/pull/25
        Hide
        Julian Hyde added a comment -
        Merged pull request.
        Show
        Julian Hyde added a comment - Merged pull request.
        Hide
        Brandon Bruce added a comment -
        I the same schema with the calculated member and store name. It returned results.
        Show
        Brandon Bruce added a comment - I the same schema with the calculated member and store name. It returned results.

          People

          • Assignee:
            Brandon Bruce
            Reporter:
            Brian Hagan (Inactive)
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: