:- Option to control what columns are returned
:- Support for cursors on the ResultSet object so the user is able to request chunks of the result instead of all at once
What kind of control do you need? Is it sufficient to supply a list of column names? That could be implemented easily. But could also be implemented easily by the application, putting a wrapper on top of the result set.
Or let me know how you would like the columns to be specified.
Support for cursors on the ResultSet object so the user is able to request chunks
of the result instead of all at once.
The ResultSet API is already a cursor, in the sense that you can skip forwards any number of rows and, for some providers, also skip backwards. Does it work to keep a count in the session context (or the URL of the page) and skip forward by calling ResultSet.absolute(int rows) and ResultSet.relative(int rows) methods.
I am guessing that you want to open a cursor in one web round trip and refer to the same cursor in a subsequent web round trip. In that mode of operation, you would use a connection pool, and open a connection for each round trip to the server. This is difficult for a typical olap4j provider to do because it requires keeping a cursor open across sessions.
It might be easier for the XMLA driver to achieve (because XMLA allows paged access to drillthrough results), but the XMLA driver could expose that functionality through the ResultSet API.
Sorry to be difficult here, but I don't want to take olap4j beyond the scope of JDBC otherwise it will be very difficult to implement an olap4j provider. This functionality is much better implemented as a layer on top of olap4j, analogous to connection pools.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Regarding columns returned, I'm thinking just an array/list of strings specifying column names. The purpose behind both features would be to improve efficiency over very large result-sets. If I only need one column out of 60, and there's say, 10,000 rows, I don't need 60*10000 cells, I just need 10000 cells.
As far as the cursor suggestion goes, suppose my drillthrough request is going to return a few million rows, and I want to process rows in batches. I'm only speculating here, since I won't have time to play with DrillThrough until the weekend or next week, but with normal queries I generally need to wait until the entire resultset is received before I can start working. If I need to wait 'til the XML/A servlet serves up all of the data as with normal queries, it could get annoying. For that particular aspect of the feature request I probably should've waited 'til I'd played with the API a little, but I figured while I was adding the other I might as well throw it in just in case.
Thank you for your input.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Specifically, when the XMLA driver implements Cell.drillThrough(), the ResultSet.absolute(int) and .relative(int) methods should do something intelligent. If XMLA supports the syntax "DRILLTHROUGH FIRSTROW x MAXROWS y SELECT ...", and if the first call to the cursor is absolute(500), then it could defer submitting the XMLA query until the first row is asked for. (The standard XMLA drillthrough command does not support the FIRSTROW keyword but we could easily support it in mondrian.)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Re "DRILLTHROUGH FIRSTROW x MAXROWS y SELECT ...". I was wrong about the "FIRSTROWS" keyword. The correct syntax uses the "FIRSTROWSET" keyword. It is already implemented by SSAS2000 (and perhaps later versions) and by mondrian.
I hope I could know the number of rows before I retrieve the entire rows.
To render big drill through data effectively, one needs to implement some kind of lazy loading mechanism. For that, it is required to know the total row count beforehand.
Though, as the ResultSet returned from Cell.drillThrough() is created with scroll insensitive option, I can't just skip to the last row to read the row count first then go back to the first row to start rendering.
I'd like there's some way to read the total row count or at least an option to get scrollable resultset instead.
Also, support for sorting and filtering the result (can't do that after the query execution in case of lazy loading the result) would be nice to have for usability.
Last edit: Xavier Cho 2013-01-05
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
What kind of control do you need? Is it sufficient to supply a list of column names? That could be implemented easily. But could also be implemented easily by the application, putting a wrapper on top of the result set.
Or let me know how you would like the columns to be specified.
The ResultSet API is already a cursor, in the sense that you can skip forwards any number of rows and, for some providers, also skip backwards. Does it work to keep a count in the session context (or the URL of the page) and skip forward by calling ResultSet.absolute(int rows) and ResultSet.relative(int rows) methods.
I am guessing that you want to open a cursor in one web round trip and refer to the same cursor in a subsequent web round trip. In that mode of operation, you would use a connection pool, and open a connection for each round trip to the server. This is difficult for a typical olap4j provider to do because it requires keeping a cursor open across sessions.
It might be easier for the XMLA driver to achieve (because XMLA allows paged access to drillthrough results), but the XMLA driver could expose that functionality through the ResultSet API.
Sorry to be difficult here, but I don't want to take olap4j beyond the scope of JDBC otherwise it will be very difficult to implement an olap4j provider. This functionality is much better implemented as a layer on top of olap4j, analogous to connection pools.
Regarding columns returned, I'm thinking just an array/list of strings specifying column names. The purpose behind both features would be to improve efficiency over very large result-sets. If I only need one column out of 60, and there's say, 10,000 rows, I don't need 60*10000 cells, I just need 10000 cells.
As far as the cursor suggestion goes, suppose my drillthrough request is going to return a few million rows, and I want to process rows in batches. I'm only speculating here, since I won't have time to play with DrillThrough until the weekend or next week, but with normal queries I generally need to wait until the entire resultset is received before I can start working. If I need to wait 'til the XML/A servlet serves up all of the data as with normal queries, it could get annoying. For that particular aspect of the feature request I probably should've waited 'til I'd played with the API a little, but I figured while I was adding the other I might as well throw it in just in case.
Thank you for your input.
See also the following thread: http://sourceforge.net/forum/forum.php?thread_id=2554237&forum_id=577988
Specifically, when the XMLA driver implements Cell.drillThrough(), the ResultSet.absolute(int) and .relative(int) methods should do something intelligent. If XMLA supports the syntax "DRILLTHROUGH FIRSTROW x MAXROWS y SELECT ...", and if the first call to the cursor is absolute(500), then it could defer submitting the XMLA query until the first row is asked for. (The standard XMLA drillthrough command does not support the FIRSTROW keyword but we could easily support it in mondrian.)
Re "DRILLTHROUGH FIRSTROW x MAXROWS y SELECT ...". I was wrong about the "FIRSTROWS" keyword. The correct syntax uses the "FIRSTROWSET" keyword. It is already implemented by SSAS2000 (and perhaps later versions) and by mondrian.
From http://msdn.microsoft.com/en-us/library/aa216740(SQL.80).aspx :
<drillthrough> := DRILLTHROUGH [<Max_Rows>] [<First_Rowset>] <MDX select="">
< Max_Rows> := MAXROWS <positive number="">
<First_Rowset> := FIRSTROWSET <positive number="">
I hope I could know the number of rows before I retrieve the entire rows.
To render big drill through data effectively, one needs to implement some kind of lazy loading mechanism. For that, it is required to know the total row count beforehand.
Though, as the ResultSet returned from Cell.drillThrough() is created with scroll insensitive option, I can't just skip to the last row to read the row count first then go back to the first row to start rendering.
I'd like there's some way to read the total row count or at least an option to get scrollable resultset instead.
Also, support for sorting and filtering the result (can't do that after the query execution in case of lazy loading the result) would be nice to have for usability.
Last edit: Xavier Cho 2013-01-05