Microsoft Excel – The Capacity Planners’ Tool of Choice
People who participate in the disciplines of Capacity and Performance Management (CPM) have a broad range of software tools to choose from to help them in their daily grind. Typically the CPM practitioner will need software to:
When one attends IT industry user groups, e.g. UKCMG, the choice of software tools can be bewildering and the costs associated with them potentially prohibitive. Fortunately Microsoft Excel can be used for the analytical, automation and presentation tasks that make up the majority of the CPM practitioner's working day.
There are few organisations which do not now have Microsoft Excel (or similar spreadsheet based functionality) loaded onto the majority of their IT staff’s desktops. This wide availability means that:
There are sound economic reasons for using Microsoft Excel for CPM activities and these reasons are supported by a number of technical features.
Summarisation and Consolidation
CPM data can come from many sources. This means that there is great potential for related data to be measured over different time intervals or even over the same interval but at offset start and end times. An example of the former might be where a server’s CPU utilisation is reported at 15min intervals but the business throughput is only measured at hourly intervals. Even if they are measured over the same interval (e.g. hourly) the CPU utilisation measurement might be time stamped, say, 2 seconds after the hour while the business throughput measurements are measured anything up to a minute either side of the hour.
Another issue is that the measurement period for one or more sources of data is not contiguous, e.g. business volumes are only measured where the value is greater than 0.
These situations are easily dealt with by using Excel’s SECOND, MINUTE, HOUR and INT (integer) functions combined with the summarisation functionality of Pivot Tables. The user determines the granularity required, say hourly, and applies the HOUR function (or whichever is appropriate) to the timestamp. This then allows a pivot table to summarise or aggregate (using average, minimum, maximum, sum, etc) to the interval chosen. This even works with event records like CICS type 110 or IIS logs as well as interval records.
Modelling
CPM practitioners need to create models of computer systems, the most prevalent technique being based on analytical queuing theory. The formulae used in these models are intrinsically parameterised and are ideally suited to Excel spreadsheets. By supplying measurements or forecasts of service time, inter-arrival rate and simple configuration data, Excel can produce expected response times and utilisation of devices.
Models of batch systems are valuable resources in CPM. These can be built within Excel using the job dependencies and measured or forecasted elapsed times for the jobs. Although undocumented, Excel can produce Gantt charts to illustrate the results.
Forecasts
In an ideal world business or user representatives will provide capacity planners with business forecasts with which predictions of resource utilisation and service will be made. When this is the case Excel has multiple ways of carrying out linear regression analysis to enable the relationship between business driver and resource use to be determined (through scatter graph trend lines, regression wizard, SLOPE and INTERCEPT functions, etc).
Where no forecast has been given then the capacity planner typically uses a trending technique to produce a forecast that the business can sign-off against. Excel has many functions to do this, FORECAST, GROWTH, TREND, Moving Average wizard etc).
Reports
As discussed CPM involves much analysis however, that analysis loses its usefulness if the results aren’t made available or understood. Excel offers a number of different ways to offer reports to the user or customer. These include:
The last of these can be done simply by saving a workbook using the “Save as Web Page” option from the “File” menu.
Automation
Many of the analysis and reporting functions that have been described here can be complex and time consuming. Resources and time would be saved if the repetitive tasks could be carried out automatically. Excel can accomplish this automation through VBA macros (Visual Basic for Applications). VBA is an object oriented language that allows Excel functionality to be programmable and repeatable. For those that are not willing to write program code there is a keyboard macro recording feature that allows the user to record the results of various actions in an Excel session. The recording session results in VBA code that can then be customised later.
The Excel VBA macros may be used in conjunction with scheduling packages so that they can run at appropriate times without manual intervention.
Conclusion
Microsoft Excel is an essential part of the CPM practitioner’s toolkit as it is widely available, inexpensive and can satisfy our wide range of analysis and reporting requirements all with one product.
Access to Capacitas articles is unrestricted although research is restricted to registered users of this website; registration is free and available to all. Click here to sign up now. Subscribers will be informed via email when new research is published.