Creating an OpenSTA response time report using an Excel pivot table

Creating an OpenSTA response time report using an Excel pivot table

Link for Downloading OpenSTAPivotTable

OpenSTAPivotTable.zip

Download the zipped spreadsheet, unzip, and open it. You will see two worksheets; RawDataFromOpenSTA, and ReportPage. Just so you know where we are headed and if it will be worth the trip for you, have a look at the ReportPage worksheet. This report is based on OpenSTA test data where I ramped up users from 35 to 140 users in batches of 35. The batch ramp time was set to 300 seconds and the interval between batches was set to 600 seconds. The columns Average, min, and max are response times statistics in seconds. Count is the number of times the event (TimerName) occurred for a given number of users (over a 10 minute period for this example), and Std Dev is Standard Deviation.

The report can be customized in 3 ways.

1. By clicking on the DownArrow Icon next to Timer Name (cell 4a), a list of all timer names will appear. You can check or uncheck the timers you would like to have included in the report.
2. By clicking the DownArrow icon next to Active Users (cell 4b), a list of users (from 1 to 140 in this example) will appear. The system is in a steady state at 35, 70, 105, and 140 users, so I’d like to focus my analysis on those data points. By unchecking all other users except 35,70,105, and 140, I reduce the data to a meaningful subset.
3. Lastly, by clicking on the DownArrow icon next to Data (cell 3c), I can control what columns are displayed (Average, count, std dev, min, and max).

To load OpenSTA timer data into the spreadsheet, start by exporting a timer list to a CSV file. In commander, open a test, navigate to the results tab, open a test and select Timer List. Right mouse inside the Timer List window and select Export. Create a file called testdata.csv (the name is not important). Open the OpenSTAPivotTable.xls spreadsheet. Open the .csv file you just created with Excel. Delete all columns except TimerName, ActiveUsers, and ElapsedTime. A fast way to do this is to Control+LeftMouseButton on Columns A-G, H, J, L-O, then right mouse and select delete. However you choose to do it, you should now have 3 colums, TimerName, ActiveUsers, and ElapsedTime.

Next we want to paste this data into the RawDataFromOpenSTA worksheet in the spreadsheet you downloaded. The fastest way to do this is click and drag across column identifiers A-C (all data should be highlighted), rightMouse->Copy, switch your focus to the RawDataFromOpenSTA worksheet, click and drag across column identifiers A-C (all data should be highlighted), rightMouse->Paste.

The final step is to update the pivot table (it is not dynamically regenerated when the source data is changed.) Switch to the ReportView worksheet, rightMouse over any cell in the report and select RefreshData. One last thing, if you want to chart data in the pivot table report, I find it easiest to select the entire pivot table report, edit->copy, then in another worksheet edit->pastespecial->values and edit->pasteSpecial->formats to get the data in a usable form for graphing or creating tables for reports.

Now you can do a few interesting things, like plot count vs users. Is it linear? If not, there is a bottleneck somewhere. How about response time vs users? Is there a point where a small increase in users results in a large increase in response time? Is so, look for correlations in hardware utilization (i.e. is CPU for example approaching 80%?). If you can’t find a hardware component that is saturated, start looking for software bottlenecks.

source : www.iPerformax.com