[Mondrian] MDX results formatted as text

Brian Vandenberg phantall at gmail.com
Tue Apr 14 17:29:43 EDT 2009


  If I don't have a straightforward way to use OLAP4j (or something
similar), and I'm relying on another utility to output the results of a
query to a text file (or read directly from stdout), I want to make sure I
can parse it easily.  While ugly, the 'crappy' format was relatively
straightforward to parse.

-Brian

On 4/14/09, Julian Hyde <jhyde at pentaho.com> wrote:
>
>  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
>>
>>
>
> _______________________________________________
> 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/55ef5896/attachment.html 


More information about the Mondrian mailing list