September, 2008
by Peg McMahon
For the past five years, Peg has been part of the Capacity and Performance Management team at Sprint. Her primary focus is on performance, concentrating on best practices, modeling, pre-production application tuning and system sizing. Before joining Sprint, Peg consulted in Disaster Recovery for Sprint and for AT&T. As a coder, she developed web-based applications for the United States Department of Agriculture. She survived Y2K as a COBOL programmer for Payless Cashways. Her mainframe expertise came from more than 15 years as a Large Systems Customer Engineer with I.B.M. She is the newly elected Chair of the Kansas City Computer Measurement Group.
About the Author
Peg McMahon
There are times when an Excel pivot chart can make your work easier. Unfortunately, people tend to avoid them because Excel makes constructing a pivot chart seem complicated. At the least, it is perceived to be harder than it really is.
In this short tutorial, I will take you through the most common pivot chart tasks that I do. It might take some of the mystery out of it for you & make you bold enough to strike out on your own.
I usually use pivot charts to visualize process details. Typically about a half hour after an application experiences a slow-down, I’ll get a phone call asking what happened. I can pull the process details off the server using a MeasureWare extract. (We use HP OpenView for metrics but this capability is found in all major tools.)
However, as you can see below, the extract is less than helpful.
Figure 1 - Extract Results
This pull results in minute-by-minute process details. But there are over 270 entries just for the 17:00 minute alone. Other than a pivot chart, I can’t think of any method to visualize this amount of data over time.
You probably will not need to do this first step -- but I did. The amount of data was too large for Excel to handle so I had to delete all processes with zero CPU utilization. That got me down to a table with a mere 160 entries (or so) for the 17:00 minute. After I pruned the data, I tidied up the headers. At last, something I could work with.
Figure 2 - Pruned and Formatted
We are now ready for the "Made Easy" part. Select one and only one cell on the spreadsheet. Then go to the Data menu and left click on PivotTable and PivotChart Report.
Figure 3 - Pivot First Step
The PivotTable and PivotChart Wizard will appear. Here is where most beginners go terribly wrong. They actually read this and try to use the wizard. No. No. No. Don’t do it. Take my advice here, fellow beginners. Resist the urge to read. Take your mouse and hit Finish. Don’t think. Just do it.
Figure 4 - Wizard: Just hit finish!
This is the result of hitting finish and it is much easier to work with. The pivot chart will be done in four easy steps.
Figure 5 - Wizard Result
First, since you want the results to be time-based, go over to the PivotTable Field List and left click on Time. Holding down the left mouse button, drag it over to Column A where it says Drop Row Fields Here. Let go of the left mouse button to drop Time into the box.
Figure 6 - Dragging the Time field
Figure 7 - Dropping the Time field
Figure 8 - Result of dropping Time field
Since you want to use the process names as column headers, use the same technique to take Name out of the PivotTable Field List and drag it over to Drop Column Fields Here. And drop it there.
Figure 9 - Result of dropping Name field
The next step is to drop a data item into the Drop Data Items Here field. I am interested in CPU usage per process so I will take hold of CPU % in the PivotTable Field List and drag it to the center of the sheet – and drop it there.
Figure 10 - CPU Data dropped in place
Now we are ready to do something. I hate to reveal how simple this is. It will destroy my carefully crafted mystique as an expert. But, oh well.
Go up to the Chart Wizard icon and press it.
Figure 11 - Click the Chart Wizard
This is the result.
Figure 12 - Chart Wizard results
That’s it. Three drag-&-drops and one icon click. Don’t tell anyone how easy this is.
I guess there are still a couple of small expert things to know here. (So I won’t look like a complete wonk.)
NOTE: At this point we are leaving EASY PivotChart and going into REALLY BASIC BEGINNER PivotChart.
To change the graph representation from Sum of CPU % to, say, average of CPU%, hit the grey button at the upper left that says Sum of CPU%. The other choices will appear in a drop down menu. Sometimes Average is good to know. Honestly, though, for this purpose Sum of CPU % works just fine.
Figure 13 - Changing Calculation of CPU time
I’ll show you how to find what is NOT there. But first, you have to have a hunch. (Hey, if it were easy, anybody could do it.)
Let’s look at the graph for a minute. On the left, there is a pink process that is kind of small at 17:00, 17:01 and 17:02 but grows large at 17:03 and then seems to disappear at 17:04. Hovering over it, we find that the process is java and that java is using over 300 percent CPU at the 17:04 minute. That’s a lot of CPU for java. We may be on to something.
Figure 14 - Inspect suspicious consumers by hovering over them
Let’s look at java by itself. On the right side of the grey Name box is a small drop down arrow. If you press it, all the names used in the chart are displayed.
Figure 15 - Click dropdown for Name
I am going to de-select the (Show All) check box and select only java. Check the java box and hit OK.
Figure 16 - Selecting just java
Now, this makes a little more sense. Java balloons up at 17:04 and does not reappear until 17:29. No wonder people had a hard time accessing the app. We now know WHAT happened and we can go find the right people to tell us WHY. The forces of good again triumph over the forces of chaos and evil!
Figure 17 - Java utilization over time
From another system, here is our last example. In this case, I am trying to define workloads and I need to find the most common processes running on the server. I quickly do the drag-and-drop as above to generate the preliminary graph.
Figure 18 - Default bar chart generated by wizard
The default bar chart in this case is hard for me to read. Because the time frame is longer, the bars are much skinnier. The easiest way for me to fix that – this is just me – is to right click on any white area around the chart and select Chart Type.
Figure 19 - Right click on right area to access Chart Type menu
Changing the chart from a bar chart to an area chart makes a lot more sense to me.
Figure 20 - Select Area chart
There. That’s better – at least for my middle-aged eyes.
Figure 21 - Area chart result
That’s it. You can get a lot of mileage out of these few basic techniques. A mainframe capacity manager might use Pivot Charts to show the comparative usage of LPARS over time. You could use the same technique to look at memory by process. Or CPU use by user. Or CPU by server in a sysplex. Or to search for hot disks using a disk extract. Whatever.
There are a few differences when using Excel 2007, click here to see them.
Once you know how easy it is, let your imagination take over. You do not need to hesitate any longer. But, just to preserve the mystery that surrounds our profession, act like pivot charts are really difficult, obscure and arcane. I won’t tell if you don’t.