MDX Query/Caching/Performance Questions

i having trouble trying improve processing speed mdx query.  this 1 driving me bonkers i’m hoping can me.

 

here mdx query:

 

select crossjoin([year],{[measures].[quick ratio]}) on columns, crossjoin(descendants([department]),descendants([gl company])) on rows from [glratios]

 

year count: 9

department count: 33

gl company count: 41

gl summary category id.category count: 25 

quick ratio calculated member calculated follows:

 

'iif(([gl summary category id].[category].&[current liabilities],[measures].[balance]) <> 0,(

(([gl summary category id].[category].&[cash],[measures].[balance])

+([gl summary category id].[category].&[accounts receivable],[measures].[balance]))

/([gl summary category id].[category].&[current liabilities],[measures].[balance])) ,0)

'

 

the result cell set consists of 1430 rows , 11 columns.

 

storage method molap.  i have designed aggregations.

 

1) when run query sql server management studio, takes 23 seconds run first time.  the second time run mdx query sql server management studio, takes 2 seconds run.    i assume result of caching.  the first time run query after cube has been processed, see on 73,000 query subcube entries in sql profiler this:

 

eventclass                  query subcube

eventsubclass            1 - cache data

textdata         0000000000000000000000000000000000000001000000000,1,10,1

 

i on 99% of of entries in sql profiler contain same textdata.  why mdx query generating these entries, , why generating many entries appear identical textdata values?  is caused have done when defining dimensions, or design flaw made while defining data source view?  is caused using calculated member measure?

 

2) have c# web service uses executexmlreader method of microsoft.analysisservices.adomdclient.adomdcommand class execute same mdx query on server , return client xml.  in other words rolling own web service instead of calling xmla web service client.

 

when web service call executes mdx query, uses aspnet user. each time mdx query executed via web service generates >73,000 query subcube entries in sql profiler , takes 23 seconds run.  the mdx query generates same sql profiler entries each time executed web service.  it not cache.

 

is there use of aspnet user prevents caching of data on server? case of each aspnet session having its’ own cached data?

 

thanks in advance help.

 

wendell g.

sometimes server chooses cell cell calculation plan on bulk evaluation plan.  setting cache policy=9 forces server use bulk evaluation plan if 1 available.  workaround in rare cases decision hurts performance.

 

warning:

generally speaking, setting cache policy=9 cause severe performance degradation.  users should use diagnosis purpose or occassionally boost performance of individual queries.  microsoft not recommend users change connection string property unless explicitly recommended customer support after full disgnose of customer database , exhaust other means of improving performance.

SQL Server  >  SQL Server Analysis Services