[Mondrian] Mondrian Schema Calculated Member Problem with PRD

Diethard Steiner diethard.steiner at gmail.com
Tue Sep 28 05:34:47 EDT 2010


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="&#8364;
#,###" 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="&#8364; #,###" 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="&#8364;
#,###" 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="&#8364; #,###" 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="&#8364;
#,###" aggregator="sum" visible="true">
    </Measure>
    <CalculatedMember name="CPA (Actual)" formatString="&#8364; #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="&#8364; #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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100928/971d4a43/attachment.html 


More information about the Mondrian mailing list