Load Testing Excel Services Report – Deriving Query String Parameters sr and wb

 LoadTest

Visual Studio Team System (VSTS) Test Edition is a valuable tool to load test web applications. This post is about using VSTS Test Edition to load test SharePoint Excel Services report. In particular, I explain the solution to handle session time-out issues encountered with Excel Services web test that could invalidate the load test. A basic load test in VSTS would consist of – Web Tests, Performance Monitors, Network and Browser mix and the mighty Virtual users as shown in figure 1.   

Figure1 - Components of Load Test
Figure1 - Components of Load Test

The test scenario for my web test recording was to logon to SharePoint site, then navigate to Excel Web service report, then change one of the filter values and finally refresh the Excel report.  

The web test was recorded and immediately after the recording, the web test ran perfectly fine as shown below.  

Screenshot 1 - Web Test
Screenshot 1 - Web Test

 Something weird happened when I ran the web test after an extended coffee break. The web test ran successfully but the response was throwing exception messages due to Session Timeout. Screenshot 2 shows the response from EwrFilter.aspx page displaying “Your session has timed out because of inactivity.”  

Screenshot 2 - Session Timeout
Screenshot 2 - Session Timeout

The default time-out value for Excel Services is 300 sec and obviously the session variables have changed since the web test was recorded. The best way to fix this is to pass session dependant values as variables instead of using the recorded values.  

The challenge is to identify the session dependant fields/query string parameters request from page SharePoint page EwrFilter.aspx. With a bit of educated guess it seems that the contents of query string parameters sr and wb does resemble like session values.  

Screenshot 3 - sr and wb in EwrFilter.aspx
Screenshot 3 - sr and wb in EwrFilter.aspx

Based on this assumption, it’s time to hunt down the page that sources values for query string parameters sr and wb. Usually this can be found in the response of one of the previous requests. In this case, the response of ExcelRenderer.aspx contains a hidden variable called m_excelWebRenderer$workbookIdElement which has the values of sr (highlighted in blue) AND wb (highlighted in green) .  

<input name=”m_excelWebRenderer$workbookIdElement” type=”hidden” id=”m_excelWebRenderer_workbookIdElement” wbid=”true” value=”64.7615be94-5d17-4948-967c-50f216223d25B1LOejYmeAPtpTJVKrpSZkzoNvo=118.22.16gypnvebMtEpqXBwBs1x090.5.en-US5.en-US73.-0600#0000-03-00-05T03:00:00:0000#+0000#0000-10-00-05T02:00:00:0000#-0060” />  

The solution to tackle the session time-out problem would be to extract the values for sr and wb from the response of ExcelRenderer.aspx and pass this value instead of the recorded value to the query string parameters sr and wb of EwrFilter.aspx.  

To extract sr and wb values from the response of ExcelRenderer.aspx, I used a Regular Expression based Extraction rule.  

Regular Expression for sr

(?&lt;=(&lt;input name="m_excelWebRenderer\$workbookIdElement.*value=\"\d+\.))([^=])*=  
Screenshot 4 - Extraction Rule for sr
Screenshot 4 - Extraction Rule for sr

Regular Expression for wb

(?&lt;=(&lt;input name="m_excelWebRenderer\$workbookIdElement.*value=\"\d+\..*?\=\d+\.))([^"]*)  
Screenshot 5 - Extraction Rule for wb
Screenshot 5 - Extraction Rule for wb

   The web test now looks like Screenshot 6

Screenshot 6 - Web Test with extracted sr and wb values
Screenshot 6 - Web Test with extracted sr and wb values

 Note that you need to change the URL Encode attribute of sr and wb from False to True, since the response of ExcelRenderer.aspx is already URL Encoded and so are the extracted values of sr and wb.

With all the hard work done, it’s time to run the Web Test again. And it’s a grand success – the EwrFilter.aspx page no longer times out and displays the filter values as expected.

Screenshot 7 - No more session time-out
Screenshot 7 - No more session time-out

And the response from the subsequent pages is as expected.

BENNY AUSTIN

One thought on “Load Testing Excel Services Report – Deriving Query String Parameters sr and wb

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s