[Mondrian] MDX results formatted as text
Brian Vandenberg
phantall at gmail.com
Tue Apr 14 18:47:44 EDT 2009
Julian,
Oops. I thought reply would go to the list.
I like the way Kettle represents it. Perhaps an elegant way would be one
or more columns for labeling rows by dimension, and column labels for each
measure, as in JPivot. I'm unsure how to extend that for more than 2 axes,
though.
-Brian
On Tue, Apr 14, 2009 at 4:38 PM, Julian Hyde <jhyde at pentaho.com> wrote:
> If you need MDX output to be parseble, please propose a format for that.
> I would imagine that a multidimensional version of CSV format would be good.
> Would you prefer a line-per-cell (as in the current format) or a
> line-per-row (as in the proposed 'MySQL' or 'Oracle' formats)? Would you
> like member properties printed as well as members? Do you need to see what
> is on the slicer? Does the format need to handle escaped characters, like
> the CSV format does?
>
> (I have replied to you personally because you emailed me personally. But by
> all means forward to the list.)
>
> ------------------------------
> *From:* Brian Vandenberg [mailto:phantall at gmail.com]
> *Sent:* Tuesday, April 14, 2009 3:20 PM
> *To:* jhyde at pentaho.com
>
> *Subject:* Re: [Mondrian] MDX results formatted as text
>
> Ya, for the stuff I write in Java I just use olap4j and for the work I do
> I tend to either just use Java or output to a text format I can control
> easily. I'm more or less trying to ensure there's an alternative for users
> that may be using a tool like R, Processing, etc (though in those cases
> there's a way to make use of OLAP4j) for generating visualizations, or doing
> other analytical work.
>
> -Brian
>
> On Tue, Apr 14, 2009 at 3:38 PM, Julian Hyde <jhyde at pentaho.com> wrote:
>
>> If you write Java code, then using olap4j is straightforward. I know
>> that not everyone writes Java code.
>>
>> If you are using another utility, you need to tell us what that utility
>> is, so that we can influence its roadmap.
>>
>> Julian
>>
>> ------------------------------
>> *From:* Brian Vandenberg [mailto:phantall at gmail.com]
>> *Sent:* Tuesday, April 14, 2009 2:30 PM
>> *To:* jhyde at pentaho.com; Mondrian developer mailing list
>>
>> *Subject:* Re: [Mondrian] MDX results formatted as text
>>
>> 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/c09b419c/attachment.html
More information about the Mondrian
mailing list