[Mondrian] RE: Conditional Formatting (Diethard Steiner)

Julian Hyde jhyde at pentaho.com
Tue Aug 24 21:28:49 EDT 2010


I agree, CalculatedMemberProperty is a more convenient way to specify it.

But contrary to what Benny says, the format string is interpreted as an MDX expression. (Of course, that expression can be string literal if you happen to want the same format string for all cells.)

Julian


> -----Original Message-----
> From: mondrian-bounces at pentaho.org 
> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Benny Chow
> Sent: Tuesday, August 24, 2010 6:19 PM
> To: mondrian at pentaho.org
> Subject: [Mondrian] RE: Conditional Formatting (Diethard Steiner)
> 
> 
>   <CalculatedMember name="Arrow Unit Sales" dimension="Measures">
>     <Formula>[Measures].[Unit Sales]</Formula>
>     <CalculatedMemberProperty name="FORMAT_STRING" 
> expression="IIf([Measures].[Unit Sales] > 
> 10000,'|#,###|arrow=up',IIf([Measures].[Unit Sales] > 
> 5000,'|#,###|arrow=down','|#,###|arrow=none'))" />
>   </CalculatedMember>
> 
> Since you are using an MDX expression to do conditional 
> formatting, you should specify the expression via a member 
> property as shown above.  I think the formatString is treated 
> as a string literal and not actually evaluated as an MDX expression.
> 
> Benny
> 
> -----Original Message-----
> From: mondrian-bounces at pentaho.org 
> [mailto:mondrian-bounces at pentaho.org] On Behalf Of 
> mondrian-request at pentaho.org
> Sent: Wednesday, August 25, 2010 1:00 AM
> To: mondrian at pentaho.org
> Subject: Mondrian Digest, Vol 45, Issue 14
> 
> Send Mondrian mailing list submissions to
> 	mondrian at pentaho.org
> 
> To subscribe or unsubscribe via the World Wide Web, visit
> 	http://lists.pentaho.org/mailman/listinfo/mondrian
> or, via email, send a message with subject or body 'help' to
> 	mondrian-request at pentaho.org
> 
> You can reach the person managing the list at
> 	mondrian-owner at pentaho.org
> 
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of Mondrian digest..."
> 
> 
> Today's Topics:
> 
>    1. Re: Mondrian Performance Test Harness (Nicholas Goodman)
>    2. Re: -----SPAM----- Mondrian + Hadoop Hive Research 
> (Calum Miller)
>    3. Conditional Formatting (Diethard Steiner)
> 
> 
> ----------------------------------------------------------------------
> 
> Message: 1
> Date: Mon, 23 Aug 2010 12:59:57 -0700
> From: Nicholas Goodman <ngoodman at bayontechnologies.com>
> Subject: Re: [Mondrian] Mondrian Performance Test Harness
> To: Mondrian developer mailing list <mondrian at pentaho.org>
> Message-ID:
> 	<8B758210-9B0F-472A-BD95-D136A4794BEA at bayontechnologies.com>
> Content-Type: text/plain; charset="windows-1252"
> 
> 
> On Aug 23, 2010, at 12:37 PM, 
> <jeff.s.wright at thomsonreuters.com> wrote:
> 
> > Good references, I’ve seen those. SSB is a snowflake model 
> with a single fact table. I’d prefer to be able to test 
> Virtual cubes. I came down on the side of TPC-DS because it 
> seemed more meaty as a data model and there was code to 
> support it, even if it wasn’t finished or actively used as a 
> TPC benchmark.
> SSB is based on a modified TPC-H benchmark.  In general, it 
> generates the TPC-H data denormalized.  I'd think that the 
> SSB could easily be extended to make a separate Orders fact 
> (a level above line items) that should zero balance with 
> lineitems but allow for virtual cube testing thru conformed 
> dimensions.
> 
> >  >A scalable database generator is essential
> >  
> > Unfortunately both SSB and TPC-DS use data generators 
> written in C, but we may be able to take on porting that to 
> Java as scope for this semester or a follow on project.
> I vaguely recall, but just googled and couldn't find a java 
> based version of TPCH from the OSU OSL lab @ sourceforge.  
> But, can't find anything on any search so I might be losing 
> some marbles.
> 
> LucidDB (and firewater) would LOVE to have a java based 
> version of the SSB generator!  In particular, we'd love to be 
> able to access the generation via straight java because that 
> could allow us to wrap it in Java and load directly from Java 
> into LucidDB without generating/persisting intermediate huge files.
> 
> If you're genuinely interested in this, contact me and we can 
> discuss if we can also add some guidance/help for your team.
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: 
> http://lists.pentaho.org/pipermail/mondrian/attachments/201008
23/a3c14a82/attachment-0001.html

------------------------------

Message: 2
Date: Mon, 23 Aug 2010 22:23:13 +0100
From: Calum Miller <calum at millersoft.ltd.uk>
Subject: [Mondrian] Re: -----SPAM----- Mondrian + Hadoop Hive Research
To: Jordan Ganoff <jganoff at pentaho.com>
Cc: "mondrian at pentaho.org" <mondrian at pentaho.org>
Message-ID: <1F7DDBAF-BB1D-49DE-BFBC-9BA28F2AD725 at millersoft.ltd.uk>
Content-Type: text/plain; charset="utf-8"

Hi Jordan,

Many thanks for the feedback, which confirms my suspicions that a Hive Dialect for Mondrian is at least possible. I'm going to start by getting foodmart into hadoop to support the Mondrian test  suite via hive then progress onto the necessary changes to the Sql join statement.  I'll post updates to the specified jira.

Calum


On 23 Aug 2010, at 16:30, Jordan Ganoff <jganoff at pentaho.com> wrote:

> Calum,
> 
> The PDI team recently did some research for using Apache's Hive database with Mondrian.  You tweeted "Thinking of adding Hive dialect to Mondrian, too slow for add-hoc queries but would enable mdx reports against hadoop" on 11:21 AM Aug 22nd. This is an effort to give you a bit of heads up to some of the issues you'll encounter and the JIRA case with patches to Mondrian to get Hive working at a remedial level.  Research done by James Dixon, Sean Flatley and myself found the following issues when integrating Hive with Mondrian 3.1.6.13364.  We used our custom built Hive JDBC Driver[1] for testing:
> 
> - Prepared Statements are mostly unimplemented in the JDBC driver from Apache in versions 0.5.0 and trunk (0.7.0).  We are working on implementing more functionality as needed to get Report Designer and Metadata Editor working well.  Our changes are being contributed back to the official Hive project but until they are accepted we're building our own version[1][2].
> 
> - Join syntax generated by Mondrian is not compatible with Hive.  In short, Hive does not support multiple items in the from clause.[2]
> 
> See the related JIRA case with attached patches to get Mondrian 3.1.6.13364 to work with Hive and associated test results: http://jira.pentaho.com/browse/PDI-4355
> 
> [1]: http://ci.pentaho.com/view/Data%20Integration/job/apache-hive-0.5.0/
> [2]: http://forums.pentaho.com/showthread.php?77826-Hive-amp-Hadoop
> [3]: http://wiki.apache.org/hadoop/Hive/LanguageManual/Joins
> 
> Hope this helps,
> 
> Jordan Ganoff
> Software Engineer
> <pentaho-email-logo.gif>
> Pentaho: The Commercial Open Source Alternative for Business Intelligence
> 5950 Hazeltine National Drive, Suite 340 • Orlando, FL 32822, USA
> +1 407 812-OPEN (6736) • 407 517-6206 • 321 848-8207
> Get your free download today at http://www.pentaho.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100823/931035df/attachment-0001.html

------------------------------

Message: 3
Date: Tue, 24 Aug 2010 11:28:59 +0100
From: Diethard Steiner <diethard.steiner at gmail.com>
Subject: [Mondrian] Conditional Formatting
To: mondrian at pentaho.org
Message-ID:
	<AANLkTim5Tu9D2bbrKHYMEJqNibZNOEj-ihH1E-7whDF9 at mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"

Hi,
I created some calculated members and want to apply conditional formatting.
To test my formatting, I wrote an MDX query and created a member with the
formatting string and all worked fine.
In Schema Workbench, I apply the formatString to the calculated member ...
and then when I check the results in JPivot online, I can see the
formatString instead of the values (but period signs etc is missing). I
opened the XML schema file in an editor and saw that workbench URL encodes
the formatstring.

If I just use a simple format like #0.00% all works fine. So I am wondering
as to why the iif statement is not working. What exactly am I doing wrong?

Here the code:
Iif(([Measures].[Index Revenues]<
-0.01),"|#0.00%|style='red'|arrow='down'",Iif(([Measures].[Index Revenues]<
0.01),"|#0.00%|style='yellow'|arrow='blank'","|#0.00%|style='green'|arrow='up'"))

Looks like this in the Schema:
<CalculatedMember name="Index Revenues" formatString="Iif(([Measures].[Index
Revenues]&#60;
-0.01),&#34;|#0.00%|style=&#39;red&#39;|arrow=&#39;down&#39;&#34;,Iif(([Measures].[Index
Revenues]&#60;
0.01),&#34;|#0.00%|style=&#39;yellow&#39;|arrow=&#39;blank&#39;&#34;,&#34;|#0.00%|style=&#39;green&#39;|arrow=&#39;up&#39;&#34;))"
formula="IIF([Date.Weekly Calendar].CurrentMember Is
Null,Null,([Measures].[act_revenues]-[Measures].[bdgt_revenues])/(Iif([Measures].[bdgt_revenues]&#60;0,[Measures].[bdgt_revenues]*-1,[Measures].[bdgt_revenues])))"
dimension="Measures" visible="true">
</CalculatedMember>

Thanks,
Diddy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100824/610f6e5d/attachment-0001.html

------------------------------

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian


End of Mondrian Digest, Vol 45, Issue 14
****************************************
_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian




More information about the Mondrian mailing list