[Mondrian] MDX results formatted as text

Julian Hyde jhyde at pentaho.com
Tue Apr 14 17:02:51 EDT 2009


Mondrian only formats results if you call Result.print(PrintWriter). This
API gets called as follows:

*	The API is not called in core mondrian.
*	Mondrian's test suite calls is a lot. The test suite will continue
to use the crappy old format. Much too much effort to change.
*	cmdrunner calls it, when printing results to stdout. cmdrunner could
have an 'outputformat' option. Contributions welcome.
*	The workbench calls it, although I'm not exactly sure where. The UI
could use one of the new options, perhaps as a preference. Contributions
welcome.

Which of these are you concerned about, Brian? Or are you calling the API
directly? If the API allows you to choose the format (including 'crappy'
format) would that be sufficient?
 
By the way, I am going to move the code to olap4j, provisionally as the
org.olap4j.query.CellSetFormatter class. Since olap4j.jar is necessary for
mondrian to run, it doesn't affect the above discussion.
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Brian Vandenberg
Sent: Tuesday, April 14, 2009 9:46 AM
To: Mondrian developer mailing list
Subject: Re: [Mondrian] MDX results formatted as text


  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/59d78665/attachment.html 


More information about the Mondrian mailing list