Hi all,

I am finding that the DSN requested counter in the Last
Query group of performance counters in returning incorrect
results. When I look at the Query log, I find the correct
DSN.

Does anyone know why this would happen? I am trying to
compare the DSN Requested to the DSN Used and figure out
why it is not using aggregates.

Any help would be greatly appreciated.

Thanks,
Amir

Hi Amir,

The fact that the DSN Requested value is not equal the DSN Used value tells
us some important things.

First, it indicates that Analysis Services did not have the exact data set
available in memory or on disk, meaning that no exact aggregation exists
for this query.
To verify that this indeed the case, you can use a couple of different
tools. George Spofford from DSSLab.com has produced a Partition Explorer
Utility that will list all aggregations on a partition by partition basis.
This tool also allows you to create, edit, and delete aggregations. The
Partition Manager that ships with the SQL Server Resource Kit can also be
used to achieve the same purpose. This tool was updated to support the SQL
Server Accelerator for Business Intelligence and is available for free from
the Microsoft web site.

Secondly, The DSN Used value indicates that Analysis Services did not need
to retrieve fact level data to return the result set. Instead, the server
was able to use the results of a supporting aggregation

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

Bas

Thanks for the reply. Actually I am aware that if the DSN used and requested is not the same, it means that anal. services is having to go down some levels and aggregate data in order to slve the query

My questions actually was that the DSN requested is displaying incorrectly. eg. in the Query log I have a DSN of 4217622963692000000 and in the DSN requested counter I get something like -7335632952598440000. Something which I know is wrong because I don't have such deep dimesions

But I think I have figured out the problem. I think it has to do with the number of dimensions. My cube had 21 dimensions when this behaviour was occuring. I did some testing and found that if the cube has more than 19 dimensions, the DSN requested and used counters display bizzarre stuff. Currently my cube has 19 dimesions and the counters are working fine
Just wanted to post this out in case someone else sees something similar

Thanks for your help
Ami