Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: [Mondrian] Mondrian Schema Calculated Member Problem with PRD

  1. #1
    Diethard Steiner Guest

    Default [Mondrian] Mondrian Schema Calculated Member Problem with PRD

    Hi,
    My schema runs perfectly on the BI Server, in Schema Workbench and other
    tools [no error msgs there]. But Pentaho Report Designer doesn't like it for
    some reason (version 3.6.1; it works in 3.5). I've tried to figure out the
    problem for a while now, but today I actually came closer to finding the
    piece of code that causes the problem. It seems to be related to this
    section in the Schema XML:

    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>

    I get i.e. the error message in PDR 3.6.1: Caused by:
    mondrian.olap.MondrianException: Mondrian Error:MDX object '[Subscription
    Base Revenue Share Deals (Actual)]' not found in cube 'Global B2C Weekly KPI
    Cube'

    When I delete these calculated members then everything works fine, no error
    message in PDR 3.6.1. Can somebody please point out what the problem could
    be? As said, I am running MDX queries that used these calculated members in
    JPivot and get now error msg there.

    Please find below the whole Schema:

    <Schema name="Global B2C KPIs">
    <Dimension type="TimeDimension" highCardinality="false" name="Date
    Dimension">
    <Hierarchy name="Weekly Calendar" hasAll="true" allMemberName="Weekly
    Calendar All" allMemberCaption="Weekly Calendar All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Year" column="year" type="Integer" uniqueMembers="true"
    levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Week" column="week" type="Integer" uniqueMembers="false"
    levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="Date" hasAll="true" allMemberName="All Dates"
    allMemberCaption="All Dates" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Date" column="start_date" type="String"
    uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="YearWeek Calendar" hasAll="true"
    allMemberName="YearWeek Calendar All" allMemberCaption="YearWeek Calendar
    All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="YearWeek" column="year_week" type="String"
    uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Cube name="Global B2C Weekly KPI Cube" cache="true" enabled="true">
    <Table name="kpi_weekly_stats_act_bdgt_country">
    </Table>
    <Dimension type="StandardDimension" foreignKey="country_id"
    highCardinality="false" name="Country">
    <Hierarchy name="Country" hasAll="true" allMemberName="All Countries"
    allMemberCaption="All Countries" primaryKey="country_id">
    <Table name="dimension_countries">
    </Table>
    <Level name="Country" column="iso_country_code" type="String"
    uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <DimensionUsage source="Date Dimension" name="Date" caption="Date"
    foreignKey="date_id" highCardinality="false">
    </DimensionUsage>
    <Measure name="Billable Users (Actual)" column="act_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Actual)"
    column="act_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Actual)"
    column="act_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Actual)" column="act_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Actual)" column="act_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Actual)" column="act_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Billable Users (Budget)" column="bdgt_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Budget)"
    column="bdgt_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Budget)"
    column="bdgt_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Budget)" column="bdgt_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Ongoing Unsubscriptions (Budget)"
    column="bdgt_ongoing_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Budget)" column="bdgt_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Budget)" column="bdgt_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Subscription Base Revenue Share Deals (Actual)"
    column="act_sub_base_end_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions Revenue Share Deals (Actual)"
    column="act_new_subs_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues Revenue Share Deals (Actual)"
    column="act_revenues_rev_share" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <CalculatedMember name="CPA (Actual)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Actual)]=0 OR
    IsEmpty([Measures].[Marketing Spending (Actual)]),Null,[Measures].[Marketing
    Spending (Actual)]/([Measures].[New Subscriptions (Actual)]-[Measures].[New
    Subscriptions Revenue Share Deals (Actual)]))" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Actual)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Actual)]/[Measures].[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Actual)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Actual)]/[Measures].[Subscription Base
    (Actual)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="CPA (Budget)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Budget)] = 0 OR
    IsEmpty([Measures].[Marketing Spending (Budget)]),Null,[Measures].[Marketing
    Spending (Budget)]/[Measures].[New Subscriptions (Budget)])"
    dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Budget)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Budget)]/[Measures].[New Subscriptions (Budget)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Budget)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Budget)]/[Measures].[Subscription Base
    (Budget)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Revenues (Actual VS Budget)"
    formula="IIF([Measures].[Revenues (Budget)]=0 OR
    IsEmpty([Measures].[Revenues (Budget)]),Null,([Measures].[Revenues
    (Actual)]-[Measures].[Revenues (Budget)])/(Iif([Measures].[Revenues
    (Budget)]&#60;0,[Measures].[Revenues (Budget)]*-1,[Measures].[Revenues
    (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Revenues (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Revenues
    (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Subscription Base (Actual VS Budget)"
    formula="IIF([Measures].[Subscription Base (Budget)]=0 OR
    IsEmpty([Measures].[Subscription Base
    (Budget)]),Null,([Measures].[Subscription Base
    (Actual)]-[Measures].[Subscription Base
    (Budget)])/(Iif([Measures].[Subscription Base
    (Budget)]&#60;0,[Measures].[Subscription Base
    (Budget)]*-1,[Measures].[Subscription Base (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Subscription Base (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Subscription
    Base (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="New Subscriptions (Actual VS Budget)"
    formula="IIF([Measures].[New Subscriptions (Budget)]=0 OR
    IsEmpty([Measures].[New Subscriptions (Budget)]),Null,([Measures].[New
    Subscriptions (Actual)]-[Measures].[New Subscriptions
    (Budget)])/(Iif([Measures].[New Subscriptions
    (Budget)]&#60;0,[Measures].[New Subscriptions (Budget)]*-1,[Measures].[New
    Subscriptions (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[New Subscriptions (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[New
    Subscriptions (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Marketing Spending (Actual VS Budget)"
    formula="IIF([Measures].[Marketing Spending (Budget)]=0 OR
    IsEmpty([Measures].[Marketing Spending
    (Budget)]),Null,([Measures].[Marketing Spending
    (Actual)]-[Measures].[Marketing Spending
    (Budget)])/(Iif([Measures].[Marketing Spending
    (Budget)]&#60;0,[Measures].[Marketing Spending
    (Budget)]*-1,[Measures].[Marketing Spending (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Marketing Spending (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Marketing
    Spending (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)
    ">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- start problem -->
    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- end problem -->
    <CalculatedMember name="CPA (Actual VS Budget)" dimension="Measures"
    visible="true">
    <Formula>
    <![CDATA[IIF([Measures].[CPA (Budget)]=0 OR IsEmpty([Measures].[CPA
    (Budget)]),Null,([Measures].[CPA (Actual)]-[Measures].[CPA
    (Budget)])/(Iif([Measures].[CPA (Budget)]<0,[Measures].[CPA
    (Budget)]*-1,[Measures].[CPA (Budget)])))]]>
    </Formula>
    <CalculatedMemberProperty name="SOLVEORDER" value="2000">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[CPA (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=green|arrow=up&#39;,Iif(([Measures].[CPA (Actual
    VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=red|arrow=down&#39)
    ">
    </CalculatedMemberProperty>
    </CalculatedMember>
    </Cube>
    </Schema>


    Thanks a lot,
    Diddy

    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  2. #2
    Diethard Steiner Guest

    Default [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD

    Hi,
    I actually found the problem now. PDR seems to have a way stricter
    validation than Schema Workbench and other tools.
    I omitted the "[Measures]." part in these calculated members' formula and
    hence it didn't work in PDR. I would suggest having the same strict
    validation on all the tools, otherwise it is a bit difficult to find the
    problem.
    Best regards,
    Diddy

    ---------- Forwarded message ----------
    From: Diethard Steiner <diethard.steiner (AT) gmail (DOT) com>
    Date: Tue, Sep 28, 2010 at 10:34 AM
    Subject: Mondrian Schema Calculated Member Problem with PRD
    To: Mondrian developer mailing list <mondrian (AT) pentaho (DOT) org>


    Hi,
    My schema runs perfectly on the BI Server, in Schema Workbench and other
    tools [no error msgs there]. But Pentaho Report Designer doesn't like it for
    some reason (version 3.6.1; it works in 3.5). I've tried to figure out the
    problem for a while now, but today I actually came closer to finding the
    piece of code that causes the problem. It seems to be related to this
    section in the Schema XML:

    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>

    I get i.e. the error message in PDR 3.6.1: Caused by:
    mondrian.olap.MondrianException: Mondrian Error:MDX object '[Subscription
    Base Revenue Share Deals (Actual)]' not found in cube 'Global B2C Weekly KPI
    Cube'

    When I delete these calculated members then everything works fine, no error
    message in PDR 3.6.1. Can somebody please point out what the problem could
    be? As said, I am running MDX queries that used these calculated members in
    JPivot and get now error msg there.

    Please find below the whole Schema:

    <Schema name="Global B2C KPIs">
    <Dimension type="TimeDimension" highCardinality="false" name="Date
    Dimension">
    <Hierarchy name="Weekly Calendar" hasAll="true" allMemberName="Weekly
    Calendar All" allMemberCaption="Weekly Calendar All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Year" column="year" type="Integer" uniqueMembers="true"
    levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Week" column="week" type="Integer" uniqueMembers="false"
    levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="Date" hasAll="true" allMemberName="All Dates"
    allMemberCaption="All Dates" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Date" column="start_date" type="String"
    uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="YearWeek Calendar" hasAll="true"
    allMemberName="YearWeek Calendar All" allMemberCaption="YearWeek Calendar
    All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="YearWeek" column="year_week" type="String"
    uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Cube name="Global B2C Weekly KPI Cube" cache="true" enabled="true">
    <Table name="kpi_weekly_stats_act_bdgt_country">
    </Table>
    <Dimension type="StandardDimension" foreignKey="country_id"
    highCardinality="false" name="Country">
    <Hierarchy name="Country" hasAll="true" allMemberName="All Countries"
    allMemberCaption="All Countries" primaryKey="country_id">
    <Table name="dimension_countries">
    </Table>
    <Level name="Country" column="iso_country_code" type="String"
    uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <DimensionUsage source="Date Dimension" name="Date" caption="Date"
    foreignKey="date_id" highCardinality="false">
    </DimensionUsage>
    <Measure name="Billable Users (Actual)" column="act_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Actual)"
    column="act_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Actual)"
    column="act_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Actual)" column="act_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Actual)" column="act_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Actual)" column="act_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Billable Users (Budget)" column="bdgt_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Budget)"
    column="bdgt_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Budget)"
    column="bdgt_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Budget)" column="bdgt_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Ongoing Unsubscriptions (Budget)"
    column="bdgt_ongoing_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Budget)" column="bdgt_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Budget)" column="bdgt_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Subscription Base Revenue Share Deals (Actual)"
    column="act_sub_base_end_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions Revenue Share Deals (Actual)"
    column="act_new_subs_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues Revenue Share Deals (Actual)"
    column="act_revenues_rev_share" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <CalculatedMember name="CPA (Actual)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Actual)]=0 OR
    IsEmpty([Measures].[Marketing Spending (Actual)]),Null,[Measures].[Marketing
    Spending (Actual)]/([Measures].[New Subscriptions (Actual)]-[Measures].[New
    Subscriptions Revenue Share Deals (Actual)]))" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Actual)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Actual)]/[Measures].[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Actual)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Actual)]/[Measures].[Subscription Base
    (Actual)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="CPA (Budget)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Budget)] = 0 OR
    IsEmpty([Measures].[Marketing Spending (Budget)]),Null,[Measures].[Marketing
    Spending (Budget)]/[Measures].[New Subscriptions (Budget)])"
    dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Budget)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Budget)]/[Measures].[New Subscriptions (Budget)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Budget)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Budget)]/[Measures].[Subscription Base
    (Budget)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Revenues (Actual VS Budget)"
    formula="IIF([Measures].[Revenues (Budget)]=0 OR
    IsEmpty([Measures].[Revenues (Budget)]),Null,([Measures].[Revenues
    (Actual)]-[Measures].[Revenues (Budget)])/(Iif([Measures].[Revenues
    (Budget)]&#60;0,[Measures].[Revenues (Budget)]*-1,[Measures].[Revenues
    (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Revenues (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Revenues
    (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Subscription Base (Actual VS Budget)"
    formula="IIF([Measures].[Subscription Base (Budget)]=0 OR
    IsEmpty([Measures].[Subscription Base
    (Budget)]),Null,([Measures].[Subscription Base
    (Actual)]-[Measures].[Subscription Base
    (Budget)])/(Iif([Measures].[Subscription Base
    (Budget)]&#60;0,[Measures].[Subscription Base
    (Budget)]*-1,[Measures].[Subscription Base (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Subscription Base (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Subscription
    Base (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="New Subscriptions (Actual VS Budget)"
    formula="IIF([Measures].[New Subscriptions (Budget)]=0 OR
    IsEmpty([Measures].[New Subscriptions (Budget)]),Null,([Measures].[New
    Subscriptions (Actual)]-[Measures].[New Subscriptions
    (Budget)])/(Iif([Measures].[New Subscriptions
    (Budget)]&#60;0,[Measures].[New Subscriptions (Budget)]*-1,[Measures].[New
    Subscriptions (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[New Subscriptions (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[New
    Subscriptions (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Marketing Spending (Actual VS Budget)"
    formula="IIF([Measures].[Marketing Spending (Budget)]=0 OR
    IsEmpty([Measures].[Marketing Spending
    (Budget)]),Null,([Measures].[Marketing Spending
    (Actual)]-[Measures].[Marketing Spending
    (Budget)])/(Iif([Measures].[Marketing Spending
    (Budget)]&#60;0,[Measures].[Marketing Spending
    (Budget)]*-1,[Measures].[Marketing Spending (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Marketing Spending (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Marketing
    Spending (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)
    ">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- start problem -->
    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- end problem -->
    <CalculatedMember name="CPA (Actual VS Budget)" dimension="Measures"
    visible="true">
    <Formula>
    <![CDATA[IIF([Measures].[CPA (Budget)]=0 OR IsEmpty([Measures].[CPA
    (Budget)]),Null,([Measures].[CPA (Actual)]-[Measures].[CPA
    (Budget)])/(Iif([Measures].[CPA (Budget)]<0,[Measures].[CPA
    (Budget)]*-1,[Measures].[CPA (Budget)])))]]>
    </Formula>
    <CalculatedMemberProperty name="SOLVEORDER" value="2000">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[CPA (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=green|arrow=up&#39;,Iif(([Measures].[CPA (Actual
    VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=red|arrow=down&#39)
    ">
    </CalculatedMemberProperty>
    </CalculatedMember>
    </Cube>
    </Schema>


    Thanks a lot,
    Diddy

    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  3. #3
    Julian Hyde Guest

    Default RE: [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD

    It looks like PRD is using mondrian to validate formulas. I suspect that it
    is an earlier version of mondrian, which had weaker validation rules. I
    don't recall why we made the change, but people will log bugs that MDX
    succeeds in SSAS and fails in mondrian, and we will (rightly) change
    mondrian.

    Qualifying members with their dimension & hierarchy name is recommended.
    Mondrian can resolve members faster if you do.

    We can't give an error if people don't qualify member names. But should we
    emit a warning if someone writes [Store Sales] / [Store Cost] in a formula?
    I don't have a strong opinion either way. If you think we should, log a jira
    case, and others can +1 it if they agree.

    Julian


    _____

    From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org] On
    Behalf Of Diethard Steiner
    Sent: Tuesday, September 28, 2010 5:36 AM
    To: Mondrian developer mailing list
    Subject: [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD


    Hi,
    I actually found the problem now. PDR seems to have a way stricter
    validation than Schema Workbench and other tools.
    I omitted the "[Measures]." part in these calculated members' formula and
    hence it didn't work in PDR. I would suggest having the same strict
    validation on all the tools, otherwise it is a bit difficult to find the
    problem.
    Best regards,
    Diddy


    ---------- Forwarded message ----------
    From: Diethard Steiner <diethard.steiner (AT) gmail (DOT) com>
    Date: Tue, Sep 28, 2010 at 10:34 AM
    Subject: Mondrian Schema Calculated Member Problem with PRD
    To: Mondrian developer mailing list <mondrian (AT) pentaho (DOT) org>


    Hi,
    My schema runs perfectly on the BI Server, in Schema Workbench and other
    tools [no error msgs there]. But Pentaho Report Designer doesn't like it for
    some reason (version 3.6.1; it works in 3.5). I've tried to figure out the
    problem for a while now, but today I actually came closer to finding the
    piece of code that causes the problem. It seems to be related to this
    section in the Schema XML:

    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>

    I get i.e. the error message in PDR 3.6.1: Caused by:
    mondrian.olap.MondrianException: Mondrian Error:MDX object '[Subscription
    Base Revenue Share Deals (Actual)]' not found in cube 'Global B2C Weekly KPI
    Cube'

    When I delete these calculated members then everything works fine, no error
    message in PDR 3.6.1. Can somebody please point out what the problem could
    be? As said, I am running MDX queries that used these calculated members in
    JPivot and get now error msg there.

    Please find below the whole Schema:

    <Schema name="Global B2C KPIs">
    <Dimension type="TimeDimension" highCardinality="false" name="Date
    Dimension">
    <Hierarchy name="Weekly Calendar" hasAll="true" allMemberName="Weekly
    Calendar All" allMemberCaption="Weekly Calendar All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Year" column="year" type="Integer" uniqueMembers="true"
    levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Week" column="week" type="Integer" uniqueMembers="false"
    levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="Date" hasAll="true" allMemberName="All Dates"
    allMemberCaption="All Dates" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Date" column="start_date" type="String"
    uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="YearWeek Calendar" hasAll="true"
    allMemberName="YearWeek Calendar All" allMemberCaption="YearWeek Calendar
    All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="YearWeek" column="year_week" type="String"
    uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Cube name="Global B2C Weekly KPI Cube" cache="true" enabled="true">
    <Table name="kpi_weekly_stats_act_bdgt_country">
    </Table>
    <Dimension type="StandardDimension" foreignKey="country_id"
    highCardinality="false" name="Country">
    <Hierarchy name="Country" hasAll="true" allMemberName="All Countries"
    allMemberCaption="All Countries" primaryKey="country_id">
    <Table name="dimension_countries">
    </Table>
    <Level name="Country" column="iso_country_code" type="String"
    uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <DimensionUsage source="Date Dimension" name="Date" caption="Date"
    foreignKey="date_id" highCardinality="false">
    </DimensionUsage>
    <Measure name="Billable Users (Actual)" column="act_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Actual)"
    column="act_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Actual)"
    column="act_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Actual)" column="act_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Actual)" column="act_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Actual)" column="act_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Billable Users (Budget)" column="bdgt_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Budget)"
    column="bdgt_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Budget)"
    column="bdgt_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Budget)" column="bdgt_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Ongoing Unsubscriptions (Budget)"
    column="bdgt_ongoing_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Budget)" column="bdgt_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Budget)" column="bdgt_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Subscription Base Revenue Share Deals (Actual)"
    column="act_sub_base_end_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions Revenue Share Deals (Actual)"
    column="act_new_subs_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues Revenue Share Deals (Actual)"
    column="act_revenues_rev_share" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <CalculatedMember name="CPA (Actual)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Actual)]=0 OR
    IsEmpty([Measures].[Marketing Spending (Actual)]),Null,[Measures].[Marketing
    Spending (Actual)]/([Measures].[New Subscriptions (Actual)]-[Measures].[New
    Subscriptions Revenue Share Deals (Actual)]))" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Actual)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Actual)]/[Measures].[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Actual)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Actual)]/[Measures].[Subscription Base
    (Actual)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="CPA (Budget)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Budget)] = 0 OR
    IsEmpty([Measures].[Marketing Spending (Budget)]),Null,[Measures].[Marketing
    Spending (Budget)]/[Measures].[New Subscriptions (Budget)])"
    dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Budget)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Budget)]/[Measures].[New Subscriptions (Budget)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Budget)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Budget)]/[Measures].[Subscription Base
    (Budget)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Revenues (Actual VS Budget)"
    formula="IIF([Measures].[Revenues (Budget)]=0 OR
    IsEmpty([Measures].[Revenues (Budget)]),Null,([Measures].[Revenues
    (Actual)]-[Measures].[Revenues (Budget)])/(Iif([Measures].[Revenues
    (Budget)]&#60;0,[Measures].[Revenues (Budget)]*-1,[Measures].[Revenues
    (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Revenues (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Revenues
    (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Subscription Base (Actual VS Budget)"
    formula="IIF([Measures].[Subscription Base (Budget)]=0 OR
    IsEmpty([Measures].[Subscription Base
    (Budget)]),Null,([Measures].[Subscription Base
    (Actual)]-[Measures].[Subscription Base
    (Budget)])/(Iif([Measures].[Subscription Base
    (Budget)]&#60;0,[Measures].[Subscription Base
    (Budget)]*-1,[Measures].[Subscription Base (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Subscription Base (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Subscription
    Base (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="New Subscriptions (Actual VS Budget)"
    formula="IIF([Measures].[New Subscriptions (Budget)]=0 OR
    IsEmpty([Measures].[New Subscriptions (Budget)]),Null,([Measures].[New
    Subscriptions (Actual)]-[Measures].[New Subscriptions
    (Budget)])/(Iif([Measures].[New Subscriptions
    (Budget)]&#60;0,[Measures].[New Subscriptions (Budget)]*-1,[Measures].[New
    Subscriptions (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[New Subscriptions (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[New
    Subscriptions (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Marketing Spending (Actual VS Budget)"
    formula="IIF([Measures].[Marketing Spending (Budget)]=0 OR
    IsEmpty([Measures].[Marketing Spending
    (Budget)]),Null,([Measures].[Marketing Spending
    (Actual)]-[Measures].[Marketing Spending
    (Budget)])/(Iif([Measures].[Marketing Spending
    (Budget)]&#60;0,[Measures].[Marketing Spending
    (Budget)]*-1,[Measures].[Marketing Spending (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Marketing Spending (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Marketing
    Spending (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39) ">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- start problem -->
    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- end problem -->
    <CalculatedMember name="CPA (Actual VS Budget)" dimension="Measures"
    visible="true">
    <Formula>
    <![CDATA[IIF([Measures].[CPA (Budget)]=0 OR IsEmpty([Measures].[CPA
    (Budget)]),Null,([Measures].[CPA (Actual)]-[Measures].[CPA
    (Budget)])/(Iif([Measures].[CPA (Budget)]<0,[Measures].[CPA
    (Budget)]*-1,[Measures].[CPA (Budget)])))]]>
    </Formula>
    <CalculatedMemberProperty name="SOLVEORDER" value="2000">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[CPA (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=green|arrow=up&#39;,Iif(([Measures].[CPA (Actual
    VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=red|arro
    w=down&#39) ">
    </CalculatedMemberProperty>
    </CalculatedMember>
    </Cube>
    </Schema>


    Thanks a lot,
    Diddy








    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  4. #4
    Diethard Steiner Guest

    Default Re: [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD

    Hi Julian,
    Thanks a lot for your reply! I agree that properly defining the members with
    the dimension and hierarchy name should be the way to go. But the problem is
    that if one tool doesn't enforce it the end user will quite likely expect
    that another tool behaves exactly the same way.
    In any case, I am aware of it now and I'll stop being lazy and write proper
    MDX syntax from now onwards
    (I briefly describe this topic on my blog on
    http://diethardsteiner.blogspot.com/...idation.htmlin
    case somebody else gets stuck).
    Best regards,
    Diddy

    On Tue, Sep 28, 2010 at 7:56 PM, Julian Hyde <jhyde (AT) pentaho (DOT) com> wrote:

    > It looks like PRD is using mondrian to validate formulas. I suspect that
    > it is an earlier version of mondrian, which had weaker validation rules. I
    > don't recall why we made the change, but people will log bugs that MDX
    > succeeds in SSAS and fails in mondrian, and we will (rightly) change
    > mondrian.
    >
    > Qualifying members with their dimension & hierarchy name is recommended.
    > Mondrian can resolve members faster if you do.
    >
    > We can't give an error if people don't qualify member names. But should we
    > emit a warning if someone writes [Store Sales] / [Store Cost] in a formula?
    > I don't have a strong opinion either way. If you think we should, log a jira
    > case, and others can +1 it if they agree.
    >
    > Julian
    >
    > ------------------------------
    > *From:* mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org]
    > *On Behalf Of *Diethard Steiner
    > *Sent:* Tuesday, September 28, 2010 5:36 AM
    >
    > *To:* Mondrian developer mailing list
    > *Subject:* [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with
    > PRD
    >
    > Hi,
    > I actually found the problem now. PDR seems to have a way stricter
    > validation than Schema Workbench and other tools.
    > I omitted the "[Measures]." part in these calculated members' formula and
    > hence it didn't work in PDR. I would suggest having the same strict
    > validation on all the tools, otherwise it is a bit difficult to find the
    > problem.
    > Best regards,
    > Diddy
    >
    > ---------- Forwarded message ----------
    > From: Diethard Steiner <diethard.steiner (AT) gmail (DOT) com>
    > Date: Tue, Sep 28, 2010 at 10:34 AM
    > Subject: Mondrian Schema Calculated Member Problem with PRD
    > To: Mondrian developer mailing list <mondrian (AT) pentaho (DOT) org>
    >
    >
    > Hi,
    > My schema runs perfectly on the BI Server, in Schema Workbench and other
    > tools [no error msgs there]. But Pentaho Report Designer doesn't like it for
    > some reason (version 3.6.1; it works in 3.5). I've tried to figure out the
    > problem for a while now, but today I actually came closer to finding the
    > piece of code that causes the problem. It seems to be related to this
    > section in the Schema XML:
    >
    > <CalculatedMember name="% Subscription Base Revenue Share Deals
    > (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    > Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    > visible="true">
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <CalculatedMember name="% New Subscriptions Revenue Share Deals
    > (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    > Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    > visible="true">
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    > formatString="#0.00%" formula="[Revenues Revenue Share Deals
    > (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    >
    > I get i.e. the error message in PDR 3.6.1: Caused by:
    > mondrian.olap.MondrianException: Mondrian Error:MDX object '[Subscription
    > Base Revenue Share Deals (Actual)]' not found in cube 'Global B2C Weekly KPI
    > Cube'
    >
    > When I delete these calculated members then everything works fine, no error
    > message in PDR 3.6.1. Can somebody please point out what the problem could
    > be? As said, I am running MDX queries that used these calculated members in
    > JPivot and get now error msg there.
    >
    > Please find below the whole Schema:
    >
    > <Schema name="Global B2C KPIs">
    > <Dimension type="TimeDimension" highCardinality="false" name="Date
    > Dimension">
    > <Hierarchy name="Weekly Calendar" hasAll="true" allMemberName="Weekly
    > Calendar All" allMemberCaption="Weekly Calendar All" primaryKey="date_id">
    > <Table name="dimension_week">
    > </Table>
    > <Level name="Year" column="year" type="Integer" uniqueMembers="true"
    > levelType="TimeYears" hideMemberIf="Never">
    > </Level>
    > <Level name="Week" column="week" type="Integer" uniqueMembers="false"
    > levelType="TimeWeeks" hideMemberIf="Never">
    > </Level>
    > </Hierarchy>
    > <Hierarchy name="Date" hasAll="true" allMemberName="All Dates"
    > allMemberCaption="All Dates" primaryKey="date_id">
    > <Table name="dimension_week">
    > </Table>
    > <Level name="Date" column="start_date" type="String"
    > uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">
    > </Level>
    > </Hierarchy>
    > <Hierarchy name="YearWeek Calendar" hasAll="true"
    > allMemberName="YearWeek Calendar All" allMemberCaption="YearWeek Calendar
    > All" primaryKey="date_id">
    > <Table name="dimension_week">
    > </Table>
    > <Level name="YearWeek" column="year_week" type="String"
    > uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="Never">
    > </Level>
    > </Hierarchy>
    > </Dimension>
    > <Cube name="Global B2C Weekly KPI Cube" cache="true" enabled="true">
    > <Table name="kpi_weekly_stats_act_bdgt_country">
    > </Table>
    > <Dimension type="StandardDimension" foreignKey="country_id"
    > highCardinality="false" name="Country">
    > <Hierarchy name="Country" hasAll="true" allMemberName="All Countries"
    > allMemberCaption="All Countries" primaryKey="country_id">
    > <Table name="dimension_countries">
    > </Table>
    > <Level name="Country" column="iso_country_code" type="String"
    > uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    > </Level>
    > </Hierarchy>
    > </Dimension>
    > <DimensionUsage source="Date Dimension" name="Date" caption="Date"
    > foreignKey="date_id" highCardinality="false">
    > </DimensionUsage>
    > <Measure name="Billable Users (Actual)" column="act_billable_users"
    > datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Immediate Unsubscriptions (Actual)"
    > column="act_immediate_unsubs" datatype="Numeric" formatString="#,###"
    > aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Marketing Spending (Actual)"
    > column="act_marketing_spending" datatype="Numeric" formatString="€
    > #,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="New Subscriptions (Actual)" column="act_new_subs"
    > datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Revenues (Actual)" column="act_revenues"
    > datatype="Numeric" formatString="€ #,###" aggregator="sum"
    > visible="true">
    > </Measure>
    > <Measure name="Subscription Base (Actual)" column="act_sub_base_end"
    > datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Billable Users (Budget)" column="bdgt_billable_users"
    > datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Immediate Unsubscriptions (Budget)"
    > column="bdgt_immediate_unsubs" datatype="Numeric" formatString="#,###"
    > aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Marketing Spending (Budget)"
    > column="bdgt_marketing_spending" datatype="Numeric" formatString="€
    > #,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="New Subscriptions (Budget)" column="bdgt_new_subs"
    > datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Ongoing Unsubscriptions (Budget)"
    > column="bdgt_ongoing_unsubs" datatype="Numeric" formatString="#,###"
    > aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Revenues (Budget)" column="bdgt_revenues"
    > datatype="Numeric" formatString="€ #,###" aggregator="sum"
    > visible="true">
    > </Measure>
    > <Measure name="Subscription Base (Budget)" column="bdgt_sub_base_end"
    > datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Subscription Base Revenue Share Deals (Actual)"
    > column="act_sub_base_end_rev_share" datatype="Numeric" formatString="#,###"
    > aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="New Subscriptions Revenue Share Deals (Actual)"
    > column="act_new_subs_rev_share" datatype="Numeric" formatString="#,###"
    > aggregator="sum" visible="true">
    > </Measure>
    > <Measure name="Revenues Revenue Share Deals (Actual)"
    > column="act_revenues_rev_share" datatype="Numeric" formatString="€
    > #,###" aggregator="sum" visible="true">
    > </Measure>
    > <CalculatedMember name="CPA (Actual)" formatString="€ #0.0"
    > formula="Iif([Measures].[Marketing Spending (Actual)]=0 OR
    > IsEmpty([Measures].[Marketing Spending (Actual)]),Null,[Measures].[Marketing
    > Spending (Actual)]/([Measures].[New Subscriptions (Actual)]-[Measures].[New
    > Subscriptions Revenue Share Deals (Actual)]))" dimension="Measures"
    > visible="true">
    > </CalculatedMember>
    > <CalculatedMember name="Immediate Churn Rate (Actual)"
    > formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    > (Actual)]/[Measures].[New Subscriptions (Actual)]" dimension="Measures"
    > visible="true">
    > </CalculatedMember>
    > <CalculatedMember name="Billability (Actual)" formatString="#0.0%"
    > formula="[Measures].[Billable Users (Actual)]/[Measures].[Subscription Base
    > (Actual)]" dimension="Measures" visible="true">
    > </CalculatedMember>
    > <CalculatedMember name="CPA (Budget)" formatString="€ #0.0"
    > formula="Iif([Measures].[Marketing Spending (Budget)] = 0 OR
    > IsEmpty([Measures].[Marketing Spending (Budget)]),Null,[Measures].[Marketing
    > Spending (Budget)]/[Measures].[New Subscriptions (Budget)])"
    > dimension="Measures" visible="true">
    > </CalculatedMember>
    > <CalculatedMember name="Immediate Churn Rate (Budget)"
    > formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    > (Budget)]/[Measures].[New Subscriptions (Budget)]" dimension="Measures"
    > visible="true">
    > </CalculatedMember>
    > <CalculatedMember name="Billability (Budget)" formatString="#0.0%"
    > formula="[Measures].[Billable Users (Budget)]/[Measures].[Subscription Base
    > (Budget)]" dimension="Measures" visible="true">
    > </CalculatedMember>
    > <CalculatedMember name="Revenues (Actual VS Budget)"
    > formula="IIF([Measures].[Revenues (Budget)]=0 OR
    > IsEmpty([Measures].[Revenues (Budget)]),Null,([Measures].[Revenues
    > (Actual)]-[Measures].[Revenues (Budget)])/(Iif([Measures].[Revenues
    > (Budget)]&#60;0,[Measures].[Revenues (Budget)]*-1,[Measures].[Revenues
    > (Budget)])))" dimension="Measures" visible="true">
    > <CalculatedMemberProperty name="FORMAT_STRING"
    > expression="Iif(([Measures].[Revenues (Actual VS Budget)]&#60;
    > -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Revenues
    > (Actual VS Budget)]&#60;
    > 0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    > </CalculatedMemberProperty>
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <CalculatedMember name="Subscription Base (Actual VS Budget)"
    > formula="IIF([Measures].[Subscription Base (Budget)]=0 OR
    > IsEmpty([Measures].[Subscription Base
    > (Budget)]),Null,([Measures].[Subscription Base
    > (Actual)]-[Measures].[Subscription Base
    > (Budget)])/(Iif([Measures].[Subscription Base
    > (Budget)]&#60;0,[Measures].[Subscription Base
    > (Budget)]*-1,[Measures].[Subscription Base (Budget)])))"
    > dimension="Measures" visible="true">
    > <CalculatedMemberProperty name="FORMAT_STRING"
    > expression="Iif(([Measures].[Subscription Base (Actual VS Budget)]&#60;
    > -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Subscription
    > Base (Actual VS Budget)]&#60;
    > 0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    > </CalculatedMemberProperty>
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <CalculatedMember name="New Subscriptions (Actual VS Budget)"
    > formula="IIF([Measures].[New Subscriptions (Budget)]=0 OR
    > IsEmpty([Measures].[New Subscriptions (Budget)]),Null,([Measures].[New
    > Subscriptions (Actual)]-[Measures].[New Subscriptions
    > (Budget)])/(Iif([Measures].[New Subscriptions
    > (Budget)]&#60;0,[Measures].[New Subscriptions (Budget)]*-1,[Measures].[New
    > Subscriptions (Budget)])))" dimension="Measures" visible="true">
    > <CalculatedMemberProperty name="FORMAT_STRING"
    > expression="Iif(([Measures].[New Subscriptions (Actual VS Budget)]&#60;
    > -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[New
    > Subscriptions (Actual VS Budget)]&#60;
    > 0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)">
    > </CalculatedMemberProperty>
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <CalculatedMember name="Marketing Spending (Actual VS Budget)"
    > formula="IIF([Measures].[Marketing Spending (Budget)]=0 OR
    > IsEmpty([Measures].[Marketing Spending
    > (Budget)]),Null,([Measures].[Marketing Spending
    > (Actual)]-[Measures].[Marketing Spending
    > (Budget)])/(Iif([Measures].[Marketing Spending
    > (Budget)]&#60;0,[Measures].[Marketing Spending
    > (Budget)]*-1,[Measures].[Marketing Spending (Budget)])))"
    > dimension="Measures" visible="true">
    > <CalculatedMemberProperty name="FORMAT_STRING"
    > expression="Iif(([Measures].[Marketing Spending (Actual VS Budget)]&#60;
    > -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Marketing
    > Spending (Actual VS Budget)]&#60;
    > 0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|arrow=up&#39)
    > ">
    > </CalculatedMemberProperty>
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <!-- start problem -->
    > <CalculatedMember name="% Subscription Base Revenue Share Deals
    > (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    > Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    > visible="true">
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <CalculatedMember name="% New Subscriptions Revenue Share Deals
    > (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    > Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    > visible="true">
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    > formatString="#0.00%" formula="[Revenues Revenue Share Deals
    > (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    > <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > <!-- end problem -->
    > <CalculatedMember name="CPA (Actual VS Budget)" dimension="Measures"
    > visible="true">
    > <Formula>
    > <![CDATA[IIF([Measures].[CPA (Budget)]=0 OR IsEmpty([Measures].[CPA
    > (Budget)]),Null,([Measures].[CPA (Actual)]-[Measures].[CPA
    > (Budget)])/(Iif([Measures].[CPA (Budget)]<0,[Measures].[CPA
    > (Budget)]*-1,[Measures].[CPA (Budget)])))]]>
    > </Formula>
    > <CalculatedMemberProperty name="SOLVEORDER" value="2000">
    > </CalculatedMemberProperty>
    > <CalculatedMemberProperty name="FORMAT_STRING"
    > expression="Iif(([Measures].[CPA (Actual VS Budget)]&#60;
    > -0.01),&#39;|#0.00%|style=green|arrow=up&#39;,Iif(([Measures].[CPA (Actual
    > VS Budget)]&#60;
    > 0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=red|arrow=down&#39)
    > ">
    > </CalculatedMemberProperty>
    > </CalculatedMember>
    > </Cube>
    > </Schema>
    >
    >
    > Thanks a lot,
    > Diddy
    >
    >
    >
    >
    >
    >
    >
    > _______________________________________________
    > Mondrian mailing list
    > Mondrian (AT) pentaho (DOT) org
    > http://lists.pentaho.org/mailman/listinfo/mondrian
    >
    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  5. #5
    Julian Hyde Guest

    Default RE: [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD

    I agree we should have more validation. Some of the validations should
    produce warnings. And tools such as PRD should be able to validate queries
    and schemas without actually running them. Validation should produce
    multiple messages where possible. And lastly, error messages should describe
    precisely where the error occurred.

    These are all things we want to do in mondrian 4.0.

    I have added a comment to your blog. All, please log jira requests if you
    want additional validation rules.

    Julian


    _____

    From: Diethard Steiner [mailto:diethard.steiner (AT) gmail (DOT) com]
    Sent: Tuesday, September 28, 2010 12:31 PM
    To: jhyde (AT) pentaho (DOT) com; Mondrian developer mailing list
    Subject: Re: [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with
    PRD


    Hi Julian,
    Thanks a lot for your reply! I agree that properly defining the members with
    the dimension and hierarchy name should be the way to go. But the problem is
    that if one tool doesn't enforce it the end user will quite likely expect
    that another tool behaves exactly the same way.
    In any case, I am aware of it now and I'll stop being lazy and write proper
    MDX syntax from now onwards
    (I briefly describe this topic on my blog on
    http://diethardsteiner.blogspot.com/...chema-validati
    on.html in case somebody else gets stuck).
    Best regards,
    Diddy


    On Tue, Sep 28, 2010 at 7:56 PM, Julian Hyde <jhyde (AT) pentaho (DOT) com> wrote:


    It looks like PRD is using mondrian to validate formulas. I suspect that it
    is an earlier version of mondrian, which had weaker validation rules. I
    don't recall why we made the change, but people will log bugs that MDX
    succeeds in SSAS and fails in mondrian, and we will (rightly) change
    mondrian.

    Qualifying members with their dimension & hierarchy name is recommended.
    Mondrian can resolve members faster if you do.

    We can't give an error if people don't qualify member names. But should we
    emit a warning if someone writes [Store Sales] / [Store Cost] in a formula?
    I don't have a strong opinion either way. If you think we should, log a jira
    case, and others can +1 it if they agree.

    Julian


    _____

    From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org] On
    Behalf Of Diethard Steiner
    Sent: Tuesday, September 28, 2010 5:36 AM

    To: Mondrian developer mailing list

    Subject: [Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD


    Hi,
    I actually found the problem now. PDR seems to have a way stricter
    validation than Schema Workbench and other tools.
    I omitted the "[Measures]." part in these calculated members' formula and
    hence it didn't work in PDR. I would suggest having the same strict
    validation on all the tools, otherwise it is a bit difficult to find the
    problem.
    Best regards,
    Diddy


    ---------- Forwarded message ----------
    From: Diethard Steiner <diethard.steiner (AT) gmail (DOT) com>
    Date: Tue, Sep 28, 2010 at 10:34 AM
    Subject: Mondrian Schema Calculated Member Problem with PRD
    To: Mondrian developer mailing list <mondrian (AT) pentaho (DOT) org>


    Hi,
    My schema runs perfectly on the BI Server, in Schema Workbench and other
    tools [no error msgs there]. But Pentaho Report Designer doesn't like it for
    some reason (version 3.6.1; it works in 3.5). I've tried to figure out the
    problem for a while now, but today I actually came closer to finding the
    piece of code that causes the problem. It seems to be related to this
    section in the Schema XML:

    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>

    I get i.e. the error message in PDR 3.6.1: Caused by:
    mondrian.olap.MondrianException: Mondrian Error:MDX object '[Subscription
    Base Revenue Share Deals (Actual)]' not found in cube 'Global B2C Weekly KPI
    Cube'

    When I delete these calculated members then everything works fine, no error
    message in PDR 3.6.1. Can somebody please point out what the problem could
    be? As said, I am running MDX queries that used these calculated members in
    JPivot and get now error msg there.

    Please find below the whole Schema:

    <Schema name="Global B2C KPIs">
    <Dimension type="TimeDimension" highCardinality="false" name="Date
    Dimension">
    <Hierarchy name="Weekly Calendar" hasAll="true" allMemberName="Weekly
    Calendar All" allMemberCaption="Weekly Calendar All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Year" column="year" type="Integer" uniqueMembers="true"
    levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Week" column="week" type="Integer" uniqueMembers="false"
    levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="Date" hasAll="true" allMemberName="All Dates"
    allMemberCaption="All Dates" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="Date" column="start_date" type="String"
    uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    <Hierarchy name="YearWeek Calendar" hasAll="true"
    allMemberName="YearWeek Calendar All" allMemberCaption="YearWeek Calendar
    All" primaryKey="date_id">
    <Table name="dimension_week">
    </Table>
    <Level name="YearWeek" column="year_week" type="String"
    uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Cube name="Global B2C Weekly KPI Cube" cache="true" enabled="true">
    <Table name="kpi_weekly_stats_act_bdgt_country">
    </Table>
    <Dimension type="StandardDimension" foreignKey="country_id"
    highCardinality="false" name="Country">
    <Hierarchy name="Country" hasAll="true" allMemberName="All Countries"
    allMemberCaption="All Countries" primaryKey="country_id">
    <Table name="dimension_countries">
    </Table>
    <Level name="Country" column="iso_country_code" type="String"
    uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <DimensionUsage source="Date Dimension" name="Date" caption="Date"
    foreignKey="date_id" highCardinality="false">
    </DimensionUsage>
    <Measure name="Billable Users (Actual)" column="act_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Actual)"
    column="act_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Actual)"
    column="act_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Actual)" column="act_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Actual)" column="act_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Actual)" column="act_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Billable Users (Budget)" column="bdgt_billable_users"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Immediate Unsubscriptions (Budget)"
    column="bdgt_immediate_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Marketing Spending (Budget)"
    column="bdgt_marketing_spending" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions (Budget)" column="bdgt_new_subs"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Ongoing Unsubscriptions (Budget)"
    column="bdgt_ongoing_unsubs" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues (Budget)" column="bdgt_revenues"
    datatype="Numeric" formatString="€ #,###" aggregator="sum"
    visible="true">
    </Measure>
    <Measure name="Subscription Base (Budget)" column="bdgt_sub_base_end"
    datatype="Numeric" formatString="#,###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Subscription Base Revenue Share Deals (Actual)"
    column="act_sub_base_end_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="New Subscriptions Revenue Share Deals (Actual)"
    column="act_new_subs_rev_share" datatype="Numeric" formatString="#,###"
    aggregator="sum" visible="true">
    </Measure>
    <Measure name="Revenues Revenue Share Deals (Actual)"
    column="act_revenues_rev_share" datatype="Numeric" formatString="€
    #,###" aggregator="sum" visible="true">
    </Measure>
    <CalculatedMember name="CPA (Actual)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Actual)]=0 OR
    IsEmpty([Measures].[Marketing Spending (Actual)]),Null,[Measures].[Marketing
    Spending (Actual)]/([Measures].[New Subscriptions (Actual)]-[Measures].[New
    Subscriptions Revenue Share Deals (Actual)]))" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Actual)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Actual)]/[Measures].[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Actual)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Actual)]/[Measures].[Subscription Base
    (Actual)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="CPA (Budget)" formatString="€ #0.0"
    formula="Iif([Measures].[Marketing Spending (Budget)] = 0 OR
    IsEmpty([Measures].[Marketing Spending (Budget)]),Null,[Measures].[Marketing
    Spending (Budget)]/[Measures].[New Subscriptions (Budget)])"
    dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Immediate Churn Rate (Budget)"
    formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
    (Budget)]/[Measures].[New Subscriptions (Budget)]" dimension="Measures"
    visible="true">
    </CalculatedMember>
    <CalculatedMember name="Billability (Budget)" formatString="#0.0%"
    formula="[Measures].[Billable Users (Budget)]/[Measures].[Subscription Base
    (Budget)]" dimension="Measures" visible="true">
    </CalculatedMember>
    <CalculatedMember name="Revenues (Actual VS Budget)"
    formula="IIF([Measures].[Revenues (Budget)]=0 OR
    IsEmpty([Measures].[Revenues (Budget)]),Null,([Measures].[Revenues
    (Actual)]-[Measures].[Revenues (Budget)])/(Iif([Measures].[Revenues
    (Budget)]&#60;0,[Measures].[Revenues (Budget)]*-1,[Measures].[Revenues
    (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Revenues (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Revenues
    (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Subscription Base (Actual VS Budget)"
    formula="IIF([Measures].[Subscription Base (Budget)]=0 OR
    IsEmpty([Measures].[Subscription Base
    (Budget)]),Null,([Measures].[Subscription Base
    (Actual)]-[Measures].[Subscription Base
    (Budget)])/(Iif([Measures].[Subscription Base
    (Budget)]&#60;0,[Measures].[Subscription Base
    (Budget)]*-1,[Measures].[Subscription Base (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Subscription Base (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Subscription
    Base (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="New Subscriptions (Actual VS Budget)"
    formula="IIF([Measures].[New Subscriptions (Budget)]=0 OR
    IsEmpty([Measures].[New Subscriptions (Budget)]),Null,([Measures].[New
    Subscriptions (Actual)]-[Measures].[New Subscriptions
    (Budget)])/(Iif([Measures].[New Subscriptions
    (Budget)]&#60;0,[Measures].[New Subscriptions (Budget)]*-1,[Measures].[New
    Subscriptions (Budget)])))" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[New Subscriptions (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[New
    Subscriptions (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39)">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="Marketing Spending (Actual VS Budget)"
    formula="IIF([Measures].[Marketing Spending (Budget)]=0 OR
    IsEmpty([Measures].[Marketing Spending
    (Budget)]),Null,([Measures].[Marketing Spending
    (Actual)]-[Measures].[Marketing Spending
    (Budget)])/(Iif([Measures].[Marketing Spending
    (Budget)]&#60;0,[Measures].[Marketing Spending
    (Budget)]*-1,[Measures].[Marketing Spending (Budget)])))"
    dimension="Measures" visible="true">
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[Marketing Spending (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=red|arrow=down&#39;,Iif(([Measures].[Marketing
    Spending (Actual VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=green|ar
    row=up&#39) ">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- start problem -->
    <CalculatedMember name="% Subscription Base Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[Subscription Base Revenue Share
    Deals (Actual)]/[Subscription Base (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% New Subscriptions Revenue Share Deals
    (Actual)" formatString="#0.00%" formula="[New Subscriptions Revenue Share
    Deals (Actual)]/[New Subscriptions (Actual)]" dimension="Measures"
    visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <CalculatedMember name="% Revenues Revenue Share Deals (Actual)"
    formatString="#0.00%" formula="[Revenues Revenue Share Deals
    (Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true">
    <CalculatedMemberProperty name="DATATYPE" value="Numeric">
    </CalculatedMemberProperty>
    </CalculatedMember>
    <!-- end problem -->
    <CalculatedMember name="CPA (Actual VS Budget)" dimension="Measures"
    visible="true">
    <Formula>
    <![CDATA[IIF([Measures].[CPA (Budget)]=0 OR IsEmpty([Measures].[CPA
    (Budget)]),Null,([Measures].[CPA (Actual)]-[Measures].[CPA
    (Budget)])/(Iif([Measures].[CPA (Budget)]<0,[Measures].[CPA
    (Budget)]*-1,[Measures].[CPA (Budget)])))]]>
    </Formula>
    <CalculatedMemberProperty name="SOLVEORDER" value="2000">
    </CalculatedMemberProperty>
    <CalculatedMemberProperty name="FORMAT_STRING"
    expression="Iif(([Measures].[CPA (Actual VS Budget)]&#60;
    -0.01),&#39;|#0.00%|style=green|arrow=up&#39;,Iif(([Measures].[CPA (Actual
    VS Budget)]&#60;
    0.01),&#39;|#0.00%|style=yellow|arrow=blank&#39;,&#39;|#0.00%|style=red|arro
    w=down&#39) ">
    </CalculatedMemberProperty>
    </CalculatedMember>
    </Cube>
    </Schema>


    Thanks a lot,
    Diddy








    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian





    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

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.