[Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD
Diethard Steiner
diethard.steiner at gmail.com
Tue Sep 28 08:35:48 EDT 2010
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.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.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)]<0,[Measures].[Revenues (Budget)]*-1,[Measures].[Revenues
(Budget)])))" dimension="Measures" visible="true">
<CalculatedMemberProperty name="FORMAT_STRING"
expression="Iif(([Measures].[Revenues (Actual VS Budget)]<
-0.01),'|#0.00%|style=red|arrow=down',Iif(([Measures].[Revenues
(Actual VS Budget)]<
0.01),'|#0.00%|style=yellow|arrow=blank','|#0.00%|style=green|arrow=up'))">
</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)]<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)]<
-0.01),'|#0.00%|style=red|arrow=down',Iif(([Measures].[Subscription
Base (Actual VS Budget)]<
0.01),'|#0.00%|style=yellow|arrow=blank','|#0.00%|style=green|arrow=up'))">
</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)]<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)]<
-0.01),'|#0.00%|style=red|arrow=down',Iif(([Measures].[New
Subscriptions (Actual VS Budget)]<
0.01),'|#0.00%|style=yellow|arrow=blank','|#0.00%|style=green|arrow=up'))">
</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)]<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)]<
-0.01),'|#0.00%|style=red|arrow=down',Iif(([Measures].[Marketing
Spending (Actual VS Budget)]<
0.01),'|#0.00%|style=yellow|arrow=blank','|#0.00%|style=green|arrow=up'))
">
</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)]<
-0.01),'|#0.00%|style=green|arrow=up',Iif(([Measures].[CPA (Actual
VS Budget)]<
0.01),'|#0.00%|style=yellow|arrow=blank','|#0.00%|style=red|arrow=down'))
">
</CalculatedMemberProperty>
</CalculatedMember>
</Cube>
</Schema>
Thanks a lot,
Diddy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100928/885da28c/attachment.html
More information about the Mondrian
mailing list