[Mondrian] Re: drillThrough

Paul Stoellberger paul.stoellberger at aschauer-edv.at
Mon Apr 12 09:36:54 EDT 2010

You're absolutely right, the extended context is mondrian specific and your suggestion sounds like a great solution that is more generic and OLAP system independent.
It would allow us to select just the number of rows / columns that we need.

I'm just not sure if we have any access to the metadata of available columns already to build such a request.
The columns for measures is clear, but the others would be KEY() and NAME() of level's !?

André (itxpander on irc) said it would be nice to include some other columns, that are not part of the cube per se, in the drillthrough as well.
So for example you would want an ID column in the resultset so you can identify the data / rows in your source system.

I guess you could do this right now with a helper dimension / property that would just be used for that. 

The drillthrough brings up, once again, the need to have another abstraction layer with different implementations for the OLAP systems olap4j wants to support.
Right now we only differentiate between datasources (Mondrian / XMLA) but i think olap4j should know about MDX syntax differences between SSAS / Mondrian / ... as well. (This would help to implement a query validator.... e.g members on just 1 axis is not supported by mondrian but on SSAS it is. Right now I just hope that the generated MDX is somehow standard and hope that the execution doesn't fail)

Any thoughts on that?

Right now the XML/A driver doesn't support drillthrough at all (it throws a unsupported operation exception), but I've seen in the code somewhere that xml/a datasources' meta information can be queried for drill through support.

On 9 Apr 2010, at 20:12, Julian Hyde wrote:

>> Paul wrote:
>> So what I would suggest is having a method drillThrough() - 
>> that uses extended context = false by default, and the one 
>> that lets me choose: drillThrough(boolean extendedContext)
> There are a couple of problems with the extendedContext parameter. First, it
> gives coarse-grained control over which columns come back, but some folks
> have been asking to specify the columns more precisely.
> Second, it is not easy to implement the parameter on other OLAP servers
> (e.g. the olap4j driver for XMLA talking to SSAS).
> SQL Server 2008's DRILL THROUGH syntax gives fine-grained control. E.g.
>   ([Date].[Calendar].[Month].[July 2003])
> ON 0 
> FROM [Adventure Works]
> WHERE [Geography].[Country].[Australia]
>  [$Date].[Date]
>  ,KEY([$Product].[Model Name])
>  ,NAME([$Employee].[Employee])
>  ,[Reseller Sales].[Reseller Sales Amount]
>  ,[Reseller Sales].[Reseller Tax Amount]
>  ,[Reseller Sales].[Reseller Standard Product Cost]
> http://technet.microsoft.com/en-us/library/ms145964.aspx
> I think that olap4j should implement that specification:
> Cell.drillThrough(
>  int maxRowCount, // < 0 means no limit
>  String columnList // comma-separated list of columns, or null to return
> default
> );
> The above example would be
> Cell cell; // cell pointing at ([July 2003], [Australia])
> ResultSet rset = cell.drillThrough(
>    100,
>    "[$Date].[Date]"
>    + ",KEY([$Product].[Model Name])"
>    + ",NAME([$Employee].[Employee])"
>    + ",[Reseller Sales].[Reseller Sales Amount]"
>    + ",[Reseller Sales].[Reseller Tax Amount]"
>    + ",[Reseller Sales].[Reseller Standard Product Cost]");
> You can of course have an 'extended context' check box in PAT, if you wish.
> If checked, you would generate a columnList including all levels of all
> dimensions.
> This would of course require a change to mondrian. But it's a change we've
> been intending to make for some time. I'd rather this than to bake the
> mondrian-specific 'extendedContext' into olap4j.
> Julian

More information about the Mondrian mailing list