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

Diethard Steiner diethard.steiner at gmail.com
Tue Sep 28 15:31:13 EDT 2010


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/2010/09/mondrian-mdx-and-schema-validation.htmlin
case somebody else gets stuck).
Best regards,
Diddy

On Tue, Sep 28, 2010 at 7:56 PM, Julian Hyde <jhyde at pentaho.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.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="&#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
>
>
>
>
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100928/0e308255/attachment.html 


More information about the Mondrian mailing list