Is it possible to change the timeout from Analysis Services on a drill through ?

I am drilling through to a SQL Server database but the fact table is quite large and therefore needs time to return the query and it is not doing so at all levels in all hierarchies.
There is a limit of 5000 records returned, but the query can time out on as little as 3 records due to the large number of records.
I realise that this could tie up the server for a while but there are few users for this system and it is important to return the required records.

Depends on the MS OLAP client front-end, since an OLE DB property has to
be changed. This ADO example may help:

http://groups.google.com/groups?hl=e...Li6XkuDHA.972%
40cpmsftngxa07.phx.gbl
Subject: RE: Datatype issue on Drillthrough
View: Complete Thread (9 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-12-04 01:09:32 PST

Hi Rui,

Thanks for your post. As I understand, when you try to drill though the
data in the cube using Analysis Manager, the process always times out.
If I
have misunderstood, please feel free to let me know.

When you drill through the data in the cube, the Analysis Services will
generate a query, which is accepted by the data source, and will be
waiting
for the data source returning the Dataset. The duration of the drill
through depends on the time of the query execution on Sybase. Since I am
not familiar with Sybase's detailed information, what I can do is very
limited. At this point, I think it would be most beneficial for you to
talk
to Sybase support regarding this issue.

Also, on the Analysis Services side, increasing the value of the timeout
option would be best. From what I know, by default, the value of the
timeout option in Analysis Manager is 30 seconds. Unfortunately, the
timeout option cannot be set using Analysis Manager. You have to set the
option and drill through the data using codes. The CommandTimeout method
will be used to set the value of the timeout option.

Sample codes:

/////////////////////

dim cst, cmd
Set cst = server.CreateObject("ADODB.recordSet")
Set cmd = server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "Data Source=shasql92;Initial Catalog=foodmart
2000;Provider=msolap.2;"
cmd.CommandTimeout = 60
dim strSource
'strSource = strSource & "DRILLTHROUGH maxrows 1000 "
'strSource = strSource & "SELECT {[Time].[1997]} ON columns, "
'strSource = strSource & "{[measures].[supply time]} ON rows, "
'strSource = strSource & "{[Warehouse].[All Warehouses]} ON pages "
'strSource = strSource & "FROM [warehouse] "
strSource = strSource & "drillthrough "
strSource = strSource & "select "
strSource = strSource & "{[Measures].[Unit Sales]} on rows, "
strSource = strSource & "{[Promotions].[All Promotions].[Best Savings]}
on
columns "
strSource = strSource & "from sales "
cmd.CommandText = strSource
set cst = cmd.execute()
Response.Write cst.fields.item(0)
response.write " "
Response.Write cst.fields.item(1)
response.write " "
Response.Write cst.fields.item(2)
response.write " "
Response.Write cst.fields.item(3)
response.write " "
Response.Write cst.fields.item(4)
...

- Deepak

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!