[Mondrian] Fwd: Mondrian Schema Calculated Member Problem with PRD
Julian Hyde
jhyde at pentaho.com
Tue Sep 28 14:56:08 EDT 2010
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.org [mailto:mondrian-bounces at pentaho.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.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|ar
row=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|ar
row=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|ar
row=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|ar
row=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|arro
w=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/7e9c1ab0/attachment.html
More information about the Mondrian
mailing list