<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18943"></HEAD>
<BODY>
<DIV><SPAN class=317225118-28092010><FONT color=#000080 size=2
face="Lucida Sans">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.</FONT></SPAN></DIV>
<DIV><SPAN class=317225118-28092010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=317225118-28092010><FONT color=#000080 size=2
face="Lucida Sans">Qualifying members with their dimension & hierarchy name
is recommended. Mondrian can resolve members faster if you
do.</FONT></SPAN></DIV>
<DIV><SPAN class=317225118-28092010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=317225118-28092010><FONT color=#000080 size=2
face="Lucida Sans">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.</FONT></SPAN></DIV>
<DIV><SPAN class=317225118-28092010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=317225118-28092010><FONT color=#000080 size=2
face="Lucida Sans">Julian</FONT></SPAN></DIV><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #000080 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px"
dir=ltr>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> mondrian-bounces@pentaho.org
[mailto:mondrian-bounces@pentaho.org] <B>On Behalf Of </B>Diethard
Steiner<BR><B>Sent:</B> Tuesday, September 28, 2010 5:36 AM<BR><B>To:</B>
Mondrian developer mailing list<BR><B>Subject:</B> [Mondrian] Fwd: Mondrian
Schema Calculated Member Problem with PRD<BR></FONT><BR></DIV>
<DIV></DIV>Hi,
<DIV>I actually found the problem now. PDR seems to have a way stricter
validation than Schema Workbench and other tools. </DIV>
<DIV>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.</DIV>
<DIV>Best regards,</DIV>
<DIV>Diddy<BR><BR>
<DIV class=gmail_quote>---------- Forwarded message ----------<BR>From: <B
class=gmail_sendername>Diethard Steiner</B> <SPAN dir=ltr><<A
href="mailto:diethard.steiner@gmail.com">diethard.steiner@gmail.com</A>></SPAN><BR>Date:
Tue, Sep 28, 2010 at 10:34 AM<BR>Subject: Mondrian Schema Calculated Member
Problem with PRD<BR>To: Mondrian developer mailing list <<A
href="mailto:mondrian@pentaho.org">mondrian@pentaho.org</A>><BR><BR><BR>Hi,
<DIV>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:</DIV>
<DIV>
<DIV><BR></DIV>
<DIV>
<DIV> <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"></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <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"></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <CalculatedMember name="% Revenues Revenue Share
Deals (Actual)" formatString="#0.00%" formula="[Revenues Revenue Share Deals
(Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true"></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV></DIV></DIV>
<DIV><BR></DIV>
<DIV>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'</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>Please find below the whole Schema:</DIV>
<DIV><BR></DIV>
<DIV>
<DIV><Schema name="Global B2C KPIs"></DIV>
<DIV> <Dimension type="TimeDimension" highCardinality="false"
name="Date Dimension"></DIV>
<DIV> <Hierarchy name="Weekly Calendar" hasAll="true"
allMemberName="Weekly Calendar All" allMemberCaption="Weekly Calendar All"
primaryKey="date_id"></DIV>
<DIV> <Table name="dimension_week"></DIV>
<DIV> </Table></DIV>
<DIV> <Level name="Year" column="year"
type="Integer" uniqueMembers="true" levelType="TimeYears"
hideMemberIf="Never"></DIV>
<DIV> </Level></DIV>
<DIV> <Level name="Week" column="week"
type="Integer" uniqueMembers="false" levelType="TimeWeeks"
hideMemberIf="Never"></DIV>
<DIV> </Level></DIV>
<DIV> </Hierarchy></DIV>
<DIV> <Hierarchy name="Date" hasAll="true"
allMemberName="All Dates" allMemberCaption="All Dates"
primaryKey="date_id"></DIV>
<DIV> <Table name="dimension_week"></DIV>
<DIV> </Table></DIV>
<DIV> <Level name="Date" column="start_date"
type="String" uniqueMembers="true" levelType="TimeDays"
hideMemberIf="Never"></DIV>
<DIV> </Level></DIV>
<DIV> </Hierarchy></DIV>
<DIV> <Hierarchy name="YearWeek Calendar" hasAll="true"
allMemberName="YearWeek Calendar All" allMemberCaption="YearWeek Calendar All"
primaryKey="date_id"></DIV>
<DIV> <Table name="dimension_week"></DIV>
<DIV> </Table></DIV>
<DIV> <Level name="YearWeek" column="year_week"
type="String" uniqueMembers="true" levelType="TimeWeeks"
hideMemberIf="Never"></DIV>
<DIV> </Level></DIV>
<DIV> </Hierarchy></DIV>
<DIV> </Dimension></DIV>
<DIV> <Cube name="Global B2C Weekly KPI Cube" cache="true"
enabled="true"></DIV>
<DIV> <Table
name="kpi_weekly_stats_act_bdgt_country"></DIV>
<DIV> </Table></DIV>
<DIV> <Dimension type="StandardDimension"
foreignKey="country_id" highCardinality="false" name="Country"></DIV>
<DIV> <Hierarchy name="Country" hasAll="true"
allMemberName="All Countries" allMemberCaption="All Countries"
primaryKey="country_id"></DIV>
<DIV> <Table
name="dimension_countries"></DIV>
<DIV> </Table></DIV>
<DIV> <Level name="Country"
column="iso_country_code" type="String" uniqueMembers="false"
levelType="Regular" hideMemberIf="Never"></DIV>
<DIV> </Level></DIV>
<DIV> </Hierarchy></DIV>
<DIV> </Dimension></DIV>
<DIV> <DimensionUsage source="Date Dimension" name="Date"
caption="Date" foreignKey="date_id" highCardinality="false"></DIV>
<DIV> </DimensionUsage></DIV>
<DIV> <Measure name="Billable Users (Actual)"
column="act_billable_users" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Immediate Unsubscriptions (Actual)"
column="act_immediate_unsubs" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Marketing Spending (Actual)"
column="act_marketing_spending" datatype="Numeric" formatString="&#8364;
#,###" aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="New Subscriptions (Actual)"
column="act_new_subs" datatype="Numeric" formatString="#,###" aggregator="sum"
visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Revenues (Actual)"
column="act_revenues" datatype="Numeric" formatString="&#8364; #,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Subscription Base (Actual)"
column="act_sub_base_end" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Billable Users (Budget)"
column="bdgt_billable_users" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Immediate Unsubscriptions (Budget)"
column="bdgt_immediate_unsubs" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Marketing Spending (Budget)"
column="bdgt_marketing_spending" datatype="Numeric" formatString="&#8364;
#,###" aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="New Subscriptions (Budget)"
column="bdgt_new_subs" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Ongoing Unsubscriptions (Budget)"
column="bdgt_ongoing_unsubs" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Revenues (Budget)"
column="bdgt_revenues" datatype="Numeric" formatString="&#8364; #,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Subscription Base (Budget)"
column="bdgt_sub_base_end" datatype="Numeric" formatString="#,###"
aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Subscription Base Revenue Share
Deals (Actual)" column="act_sub_base_end_rev_share" datatype="Numeric"
formatString="#,###" aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="New Subscriptions Revenue Share
Deals (Actual)" column="act_new_subs_rev_share" datatype="Numeric"
formatString="#,###" aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <Measure name="Revenues Revenue Share Deals
(Actual)" column="act_revenues_rev_share" datatype="Numeric"
formatString="&#8364; #,###" aggregator="sum" visible="true"></DIV>
<DIV> </Measure></DIV>
<DIV> <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"></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <CalculatedMember name="Immediate Churn Rate
(Actual)" formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
(Actual)]/[Measures].[New Subscriptions (Actual)]" dimension="Measures"
visible="true"></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <CalculatedMember name="Billability (Actual)"
formatString="#0.0%" formula="[Measures].[Billable Users
(Actual)]/[Measures].[Subscription Base (Actual)]" dimension="Measures"
visible="true"></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <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"></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <CalculatedMember name="Immediate Churn Rate
(Budget)" formatString="#0.0%" formula="[Measures].[Immediate Unsubscriptions
(Budget)]/[Measures].[New Subscriptions (Budget)]" dimension="Measures"
visible="true"></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <CalculatedMember name="Billability (Budget)"
formatString="#0.0%" formula="[Measures].[Billable Users
(Budget)]/[Measures].[Subscription Base (Budget)]" dimension="Measures"
visible="true"></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <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"></DIV>
<DIV> <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;))"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <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"></DIV>
<DIV> <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;))"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <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"></DIV>
<DIV> <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;))"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <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"></DIV>
<DIV> <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;))
"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV><SPAN style="WHITE-SPACE: pre-wrap"></SPAN><!-- start problem
--></DIV>
<DIV> <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"></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <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"></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> <CalculatedMember name="% Revenues Revenue Share
Deals (Actual)" formatString="#0.00%" formula="[Revenues Revenue Share Deals
(Actual)]/[Revenues (Actual)]" dimension="Measures" visible="true"></DIV>
<DIV> <CalculatedMemberProperty name="DATATYPE"
value="Numeric"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV><SPAN style="WHITE-SPACE: pre-wrap"></SPAN><!-- end problem
--></DIV>
<DIV> <CalculatedMember name="CPA (Actual VS Budget)"
dimension="Measures" visible="true"></DIV>
<DIV> <Formula></DIV>
<DIV> <![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)])))]]></DIV>
<DIV> </Formula></DIV>
<DIV> <CalculatedMemberProperty name="SOLVEORDER"
value="2000"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> <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;))
"></DIV>
<DIV> </CalculatedMemberProperty></DIV>
<DIV> </CalculatedMember></DIV>
<DIV> </Cube></DIV>
<DIV></Schema></DIV></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>Thanks a lot,</DIV>
<DIV>Diddy</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV></DIV><BR></DIV></BLOCKQUOTE></BODY></HTML>