Showing posts with label request. Show all posts
Showing posts with label request. Show all posts

Friday, March 30, 2012

How can I connect to SQL Server 2005 analysis services server programmatically?

Hi, all here,

Would please anyone here give me any idea about how can I connect to SQL Server 2005 analysis services server and send XML request to it programmatically (with Business intelligence development studio in SQL Server 2005)? Thanks a lot.

With best regards,

Yours sincerely,

That's a big topic - you can start here

http://msdn2.microsoft.com/en-us/library/ms186654.aspx

For the XMLA ThinMiner sample look here

http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/124.aspx

Here's a blog entry of someone who's figured it out as well

http://geekswithblogs.net/darrengosbell/archive/2006/05/25/xmlaClient.aspx

|||Hi, Jamie, thanks a lot.

Wednesday, March 7, 2012

How "NOT" to expire the ReportSession and ControlID in the request

This question is related to VSTS and above parameters in the request. Question is:

A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.

You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)

You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.

|||

Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.

Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!

|||

I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)

|||

It won't. The expiration time is computed when the session is created.

Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?

|||

Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like

GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007

Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).

With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.

<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>

Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.

Thanks for your input.

|||can some one reply to this please|||

The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1

How "NOT" to expire the ReportSession and ControlID in the request

This question is related to VSTS and above parameters in the request. Question is:

A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.

You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)

You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.

|||

Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.

Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!

|||

I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)

|||

It won't. The expiration time is computed when the session is created.

Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?

|||

Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like

GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007

Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).

With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.

<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>

Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.

Thanks for your input.

|||can some one reply to this please|||

The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1