[Mondrian] MDX results formatted as text

Julian Hyde jhyde at pentaho.com
Fri Apr 10 20:50:39 EDT 2009


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
<http://julianhyde.blogspot.com/2009/04/formatting-mdx-as-plain-text.html>
my blog, but it's more readable in email format!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090410/3afe03e5/attachment.html 


More information about the Mondrian mailing list