I have a cube with a distinct count measure.
Response times seem pretty good on the cube data browser as well as the
front end charting tool

But when I use actions to run reports from reporting services, passing the
dimension values to query the cube for details, it seems quite slow. Is
there anything specific that we need to keep in mind while building the
cube to resolve this issue?


thank you

--
Message posted via http://www.sqlmonster.com

How is the response when you run the MDX used by your RS report in MDX Sample
application? And the response when you look at the report using Report
Manager? I'm guessing that it will be roughly the same as running the query
through an action. Remember also that it can only be a fair comparison if you
run exactly the same query in RS as in your ad-hoc query tool.

I can think of several reasons why your response times are worse in RS:
- RS has to open a connection each time it runs a query, because it doesn't
do any connection pooling (I'm assuming you've not implemented your own
custom data provider for AS and you're using the OLEDB Provider). Opening a
connection to an AS cube can sometimes take a few seconds, and if your report
involves running several queries (for example for populating lists of valid
values for parameters - do you do this?) this can all add up.
- Because of the above, you don't get the effect of client-side caching as
you do in other AS client tools. Remember also that in most ad-hoc query
tools the act of building a query involves running queries: think of what
happens when you drag and drop dimensions and measures onto an Excel pivot
table, or drill down from higher levels to get to the data you want. Every
step involves running a query, and since these queries also populate the
client side cache they make the 'final' query seem quicker.
- RS has the overhead of rendering the report as well as running the
queries, and this in itself can take a few seconds.

Overall, there's nothing particularly special about running queries via RS
and I would recommend you follow the normal procedures for tuning an AS cube.
Mosha has a good list of the resources available on the web on this topic
here:
http://www.mosha.com/msolap/tech.htm#Performance
You have got your distinct count measure in a cube separate from all your
other measures, haven't you? See the section entitled 'Use Distinct Count
measures appropriately' here
http://www.microsoft.com/technet/pro...cspg.mspx#EKAA

HTH,

Chris
--------------------------------------------------------
Blog: http://spaces.msn.com/members/cwebbbi/

"brian via SQLMonster.com" wrote: