[Mondrian] MDX results formatted as text

Peter Tran ptran at prospricing.com
Tue Apr 14 12:12:50 EDT 2009


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!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090414/f2d9f69b/attachment.html 


More information about the Mondrian mailing list