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

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

Opposite of TopN

Is there a way to get "the rest" of a list? I have a TopN filter
working on one table showing the top 3 results. Now, I have another
table that I want to show everything but the top 3. This table will be
hidden by default, but that shouldn't matter. Is there a way to get
"not TopN =3"?
Also, how would I get the first 3, instead of top 3? My results would
be pre-sorted coming from the datasource and I just want to show the
first few rows.
ThanksAre you using a stored procedure? You could use a correlated subquery. For
example: select bookid from books where bookid not in (select top 3 bookid
from books).
"Steven" wrote:
> Is there a way to get "the rest" of a list? I have a TopN filter
> working on one table showing the top 3 results. Now, I have another
> table that I want to show everything but the top 3. This table will be
> hidden by default, but that shouldn't matter. Is there a way to get
> "not TopN =3"?
> Also, how would I get the first 3, instead of top 3? My results would
> be pre-sorted coming from the datasource and I just want to show the
> first few rows.
> Thanks
>

Operation not allowed in this context

This error message is showing up when I execute the following code (called in a function):

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
With rs
For j = 0 To .RecordCount - 1
rs(0).Value = j + 1
If j < .RecordCount - 1 Then .MoveNext
Next j
.Close
End With
Set rs = Nothing

Any idea what causes this to fire?what line is giving the error?

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...rs.Close.|||I have no idea. But try this one:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...what I have noticed is that if I comment out the rs.Close it runs just fine. That's not to say that it is ok to not close out your recordsets but, since I am new to ADO (always worked in DAO before) I am a little uncertain as to when that is/isn't necessary.

Any ideas on that?|||I suggest that you close your connection to the database and set the recordset to nothig:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & _
" AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
conn.Close 'Close the connection
Set conn = Nothing
Set rs = Nothing|||open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!|||Originally posted by Bobba Buoy
open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!

What should I comment?|||You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.|||Originally posted by Bobba Buoy
You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.

If your application is used by many users at same time, it's a good idea to close the database connection. One open connection takes several kilobytes in memory.