[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