Optimized CellSet data Retrieval

Hi,

I have customized report for displaying OLAP CellSet data on the web. It is using ADOMD.NET, and showing everything in pivot table format. I am not using OWC or any other tool, it is my company's tool.

I notice Cognos PowerPlay has this feature, "GetDataLater", which means, when user presses this button, all data will be suppressed and only member names on column and rows will be shown with correct dimensionality. It is very fast to drill down, nest/crossjoin with other dimension after data is being suppressd.

I researched in ADOMD.NET specification for such behavior, but could not find. I found that if i get two Axes spearately, Row and Column, not putting them together is faster than getting both Axes in same CellSet. But still performace of such work around is almost unnoticeable once user puts more nested/crossjoin dimensions.

So, is there way to achieve it in ADOMD.NET or some feature in SSAS 2005, that suppresses the data whenever needed thus improving performance of CellSet dramatically.

Regards,

Bakhodir Makhamadov

There are several techniques how this can be done - probably the best technique is through using BeginRange/EndRange properties to define empty cell, because it allows NON EMPTY clause to work correctly (although if you are using NON EMPTY, you are probably not winning any time by not bringing the cells back).

(For more ideas please check the last section of this blog. It is not directly related to your question, but the ideas are applicable here as well: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/11/query_dimensions_mdx.aspx)

|||

Hi Mosha,

Thanks for information on BeginRange/EndRange properties, looks like i have missed it on XMLA Specifications.

The suggestion with BeginRange/EndRange on the XMLA restricting the number of CellData being returned, is the solution i am looking for. It satisfies my requirement in terms of functionality, since i am able to control the number of celldata being returned. I did some simulaiton code with these properties on XMLA, it works.

I am curious, if these properties are used does SSAS processes my MDX script faster. Do i get huge performace gain by restricting the celldata and retrieving only metadata of Column and Row Axes.

As part of the SSAS internal team, i believe you should have good idea of what exactly happens in SSAS when these properties are used.

I am enclosing one of the MDX scripts used in OLAP report designing:

select

{

NonEmpty

(

{[Network Product].[Network Product].[All].children}

*

{

[Estimated TT Impact].[Estimated TT Impact].[All].[0.5 day],

[Estimated TT Impact].[Estimated TT Impact].[All].[1.5 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[2.0 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[0.0 day]

}

*

{

[Measures].[Total Shipments],

[Measures].[% of Shipments Delivered]

}

)

}

on 0,

{

NonEmpty

(

{[Account].[Account].[All].children}

*

{[Shipment Weight].[Shipment Weight].[All].children}

)

}

on 1

from [Expected Delivery Standard]

The above MDX query generates more than 1000 row members as [Account] dimension has many children, and as you can see it is being crossjoined with other dimensions.

So, aside from functionality i need, restricting/suppressing data, do I get big performace gain when using BeginRang/EndRange properties?

Thank you in advance,

Regards,

Bakhodir Makhamadov

sql