Tuesday, July 28, 2009

Scalability in a Spreadsheet - Google Style

Speaking of spreadsheets, it's always nice when someone, who uses your ideas, takes the time to write to you about it. Case in point, Scott Roberts sent me the following email, telling me how he'd set up my USL model in Google Docs spreadsheets.
Subject: USL Capacity Planning Spreadsheet - Google Docs
Date: Sun, 26 Jul 2009 19:46:44 -0400
From: Scott Roberts
To: me

Dr. Gunther,

I've used your scalability model quite frequently over the past few years, and I've recently converted them over to Google Spreadsheets. Since the various Google-supplied graphing facilities do not provide trend lines (much less the polynomial variety), I had to automate the regression calculation for each deviation point for the chart. The result is less smooth than an Excel trend line but it gets the job done. The upside of all this is that the USL parameter coefficients are now automatically calculated. Simply plug in the number of virtual users, transaction rate, and response time, and let the spreadsheet handle the rest.
...
Thank you,
--Scott
Since I don't use Google Docs myself, I wasn't sure how the regression analysis of the inverted USL transform (2nd degree polynomial) was done. The Google tool does not have the full equivalent of the Excel AnalysisPak. Scott explained that he was able to hack it with LINEST(), just like Excel. Cool!

Scott has kindly provided 3 examples, which you can also use:
  1. Spreadsheet derived from the Guerrilla Capacity Planning textbook.
  2. Load Generator example taken from "Benchmarking Blunders and Things That Go Bump in the Night," CMG MeasureIT, 2006.
  3. Conversion of the GCaP class Excel sscalc-class.xls spreadsheet.
You'll need a Google account to access them fully. So, with all the usual caveats about numerical precision (or lack thereof) in spreadsheets (which Scott fully understands), this looks like a very nice way to share your USL performance analysis with colleagues and managers.

Thank you, Scott!

3 comments:

Ramya RM said...
This comment has been removed by a blog administrator.
Ramya RM said...

Hi Dr.Gunther,
I am not able to access the sscalc-class.xls spreadsheet mentioned in this blog.
Can you plz do the needful.

Thanks
Ramya

Neil Gunther said...

I think that might be due to a name change for that file since this blog entry was posted in 2009. If you click on this USL tools section link, it's available as USLcalc.xls. I just downloaded it.