[Mondrian] MDX results formatted as text

[Brian Vandenberg]

  I would prefer this be a non-default option that may be set in your
config.  tables formatted as in the example given are harder to parse in
languages that don't have native support for OLAP/mdx (R, SAS, Mathematica,
Processing, Python, etc)

-Brian

On Tue, Apr 14, 2009 at 10:12 AM, Peter Tran <ptran at prospricing.com> wrote:

>  Either new format is better than the "old crappy format".  :)  Can you
> make this new format the default?
>
> Thanks!
> -Peter
>
>  ------------------------------
> *From:* mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
> *On Behalf Of *Julian Hyde
> *Sent:* Friday, April 10, 2009 7:51 PM
> *To:* 'Mondrian developer mailing list'
> *Subject:* [Mondrian] MDX results formatted as text
>
>  When Mondrian tools output MDX results as text, such as in the cmdRunner
> utility, we've been using the same old crappy format for years. For example,
> the query
>
> select
>   crossjoin(
>     {[Time].[1997].[Q1], [Time].[1997].[Q2].[4]},
>     {[Measures].[Unit Sales], [Measures].[Store Sales]}) on 0,
>   {[USA].[CA].[Los Angeles],
>    [USA].[WA].[Seattle],
>    [USA].[CA].[San Francisco]} on 1
> FROM [Sales]
> is formatted as
>
> Axis #0:
> {}
> Axis #1:
> {[Time].[1997].[Q1], [Measures].[Unit Sales]}
> {[Time].[1997].[Q1], [Measures].[Store Sales]}
> {[Time].[1997].[Q2].[4], [Measures].[Unit Sales]}
> {[Time].[1997].[Q2].[4], [Measures].[Store Sales]}
> Axis #2:
> {[Store].[All Stores].[USA].[CA].[Los Angeles]}
> {[Store].[All Stores].[USA].[WA].[Seattle]}
> {[Store].[All Stores].[USA].[CA].[San Francisco]}
> Row #0: 6,373
> Row #0: 13,736.97
> Row #0: 1,865
> Row #0: 3,917.49
> Row #1: 6,098
> Row #1: 12,760.64
> Row #1: 2,121
> Row #1: 4,444.06
> Row #2: 439
> Row #2: 936.51
> Row #2: 149
> Row #2: 327.33
>
> I've just checked in an alternative formatter that makes the result look
> more like a pivot table. The same query would come out like this:
>
>                      1997       1997        1997       1997
>                      Q1         Q1          Q2         Q2
>                                             4          4
>                      Unit Sales Store Sales Unit Sales Store Sales
> === == ============= ========== =========== ========== ===========
> USA CA Los Angeles   6,373      13,736.97   1,865      3,917.49
> USA WA Seattle       6,098      12,760.64   2,121      4,444.06
> USA CA San Francisco 439        936.51      149        327.33
>
> Two questions:
>
> 1. Should we move this code to olap4j? (It would seem to make sense because
> it doesn't require any mondrian internals to do the job, and the processing
> requires a 'grid model' similar to query models already part of olap4j.)
>
> 2. What do people feel is the ideal format for formatting MDX results as
> text? A couple of possible formats are below.
>
> "Oracle" format:
>                      1997
>                      Q1                     Q2
>                                             4
>                      Unit Sales Store Sales Unit Sales Store Sales
> === == ============= ========== =========== ========== ===========
> USA CA Los Angeles        6,373   13,736.97      1,865    3,917.49
>     WA Seattle            6,098   12,760.64      2,121    4,444.06
>     CA San Francisco        439      936.51        149      327.33
>
> "MySQL" format:
>
> |                          |
> 1997                                                |
> |                          | Q1                       |
> Q2                       |
> |                          |                          |
> 4                        |
> |                          | Unit Sales | Store Sales | Unit Sales | Store
> Sales |
>
> +-----+----+---------------+------------+-------------+------------+-------------+
> | USA | CA | Los Angeles   |      6,373 |   13,736.97 |      1,865 |
> 3,917.49 |
> |     | WA | Seattle       |      6,098 |   12,760.64 |      2,121
> |    4,444.06 |
> |     | CA | San Francisco |        439 |      936.51 |        149
> |      327.33 |
>
>
> Julian
>
> PS I also posted this question to my blog<http://julianhyde.blogspot.com/2009/04/formatting-mdx-as-plain-text.html>,
> but it's more readable in email format!
>
> _______________________________________________
> 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/20090414/96e5e394/attachment.html