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.<br>
<br>-Brian<br>
<br><div><span class="gmail_quote">On 4/14/09, <b class="gmail_sendername">Julian Hyde</b> <<a href="mailto:jhyde@pentaho.com" target="_blank">jhyde@pentaho.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div><span><font size="2" color="#000080" face="Lucida Sans">Mondrian only formats results if you call Result.print(PrintWriter). This
API gets called as follows:</font></span></div>
<ul>
<li><span><font size="2" color="#000080" face="Lucida Sans">The API is not called in core mondrian.</font></span></li>
<li><span><font size="2" color="#000080" face="Lucida Sans">Mondrian's test suite calls is a lot. <span><font size="2" color="#000080" face="Lucida Sans">The
test suite will continue to use the crappy old format. Much too much effort to
change.</font></span></font></span></li>
<li><span><font size="2" color="#000080" face="Lucida Sans">cmdrunner calls it, when printing results to stdout. <span><font size="2" color="#000080" face="Lucida Sans">cmdrunner could have an 'outputformat' option. Contributions
welcome.</font></span></font></span></li>
<li><span><font size="2" color="#000080" face="Lucida Sans"><span>The </span>workbench calls it, although
I'm not exactly sure where. <span><font size="2" color="#000080" face="Lucida Sans">The UI could use one of the new
options, perhaps as a preference. Contributions
welcome.</font></span></font></span></li></ul>
<div><span><font size="2" color="#000080" face="Lucida Sans">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?</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">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.</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">Julian</font></span></div><br>
<blockquote style="border-left: 2px solid rgb(0, 0, 128); padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div dir="ltr" align="left" lang="en-us">
<hr>
<font size="2" face="Tahoma"><b>From:</b> <a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a>
[mailto:<a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a>] <b>On Behalf Of </b>Brian
Vandenberg<br><b>Sent:</b> Tuesday, April 14, 2009 9:46 AM<span><br><b>To:</b>
Mondrian developer mailing list<br></span><b>Subject:</b> Re: [Mondrian] MDX results
formatted as text<br></font><br></div><div><span>
<div></div> 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)<br><br>-Brian<br><br>
<div class="gmail_quote">On Tue, Apr 14, 2009 at 10:12 AM, Peter Tran <span dir="ltr"><<a href="mailto:ptran@prospricing.com" target="_blank">ptran@prospricing.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">Either
new format is better than the "old crappy format". :) Can you
make this new format the default?</font></span></div><span>
<div dir="ltr" align="left"><font size="2" color="#0000ff" face="Arial"></font><br><font size="2" color="#0000ff" face="Arial">Thanks!</font></div>
<div dir="ltr" align="left"></div></span><span><font size="2" color="#0000ff" face="Arial">-Peter</font></span></div><br>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font size="2" face="Tahoma"><b>From:</b> <a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a> [mailto:<a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a>] <b>On Behalf Of </b>Julian
Hyde<br><b>Sent:</b> Friday, April 10, 2009 7:51 PM<br><b>To:</b> 'Mondrian
developer mailing list'<br><b>Subject:</b> [Mondrian] MDX results formatted
as text<br></font><br></div>
<div></div>
<div><span><font size="2" color="#000080" face="Lucida Sans">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</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans"><font face="Courier">select<br> crossjoin(<br>
{[Time].[1997].[Q1], [Time].[1997].[Q2].[4]},<br>
{[Measures].[Unit Sales], [Measures].[Store Sales]}) on 0,<br>
{[USA].[CA].[Los Angeles],<br>
[USA].[WA].[Seattle],<br> [USA].[CA].[San Francisco]} on
1<br>FROM [Sales]</font><br></font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans">is formatted
as</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Courier">Axis #0:<br>{}<br>Axis
#1:<br>{[Time].[1997].[Q1], [Measures].[Unit Sales]}<br>{[Time].[1997].[Q1],
[Measures].[Store Sales]}<br>{[Time].[1997].[Q2].[4], [Measures].[Unit
Sales]}<br>{[Time].[1997].[Q2].[4], [Measures].[Store Sales]}<br>Axis
#2:<br>{[Store].[All Stores].[USA].[CA].[Los Angeles]}<br>{[Store].[All
Stores].[USA].[WA].[Seattle]}<br>{[Store].[All Stores].[USA].[CA].[San
Francisco]}<br>Row #0: 6,373<br>Row #0: 13,736.97<br>Row #0: 1,865<br>Row
#0: 3,917.49<br>Row #1: 6,098<br>Row #1: 12,760.64<br>Row #1: 2,121<br>Row
#1: 4,444.06<br>Row #2: 439<br>Row #2: 936.51<br>Row #2: 149<br>Row #2:
327.33</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">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:</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Courier">
1997
1997
1997
1997<br>
Q1
Q1
Q2
Q2<br>
4
4<br>
Unit Sales Store Sales Unit Sales Store Sales<br>=== == =============
========== =========== ========== ===========<br>USA CA Los
Angeles 6,373
13,736.97 1,865 3,917.49<br>USA WA
Seattle
6,098 12,760.64
2,121 4,444.06<br>USA CA San Francisco
439
936.51
149 327.33</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">Two
questions:</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">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.)</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">2. What do people
feel is the ideal format for formatting MDX results as text? A couple of
possible formats are below.</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">"Oracle"
format:</font></span></div>
<div><span><span><font size="2" color="#000080" face="Courier">
1997<br>
Q1
Q2<br>
4<br>
Unit Sales Store Sales Unit Sales Store Sales<br>=== == =============
========== =========== ========== ===========<br>USA CA Los
Angeles
6,373 13,736.97
1,865 3,917.49<br> WA
Seattle
6,098 12,760.64
2,121 4,444.06<br> CA San
Francisco
439
936.51
149 327.33</font></span></span></div>
<div><span><span><font size="2" color="#000080" face="Courier"></font></span></span> </div>
<div><span><span><font size="2" color="#000080" face="Lucida Sans">"MySQL"
format:</font></span></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Courier">|
|
1997
|<br>|
|
Q1
|
Q2
|<br>|
|
|
4
|<br>|
| Unit Sales | Store Sales | Unit Sales | Store Sales
|<br>+-----+----+---------------+------------+-------------+------------+-------------+<br>|
USA | CA | Los Angeles
| 6,373 | 13,736.97
| 1,865 | 3,917.49
|<br>| | WA | Seattle
| 6,098 | 12,760.64
| 2,121 | 4,444.06
|<br>| | CA | San Francisco
| 439
| 936.51
| 149
| 327.33 |</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">Julian</font></span></div>
<div><span><font size="2" color="#000080" face="Lucida Sans"></font></span> </div>
<div><span><font size="2" color="#000080" face="Lucida Sans">PS I also posted
this question <a href="http://julianhyde.blogspot.com/2009/04/formatting-mdx-as-plain-text.html" target="_blank">to my blog</a>, but it's more readable in email
format!</font></span></div><br>_______________________________________________<br>Mondrian
mailing list<br><a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br></span></div></blockquote></div>
<br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br>