Become a pivot table god

Our friend Bob runs a very good software development company. And one talent he’s proud of is being a god of Microsoft Excel pivot tables.

This might not seem like a big deal to you, but the ability to analyze data and turn it into information is crucial when you are running a business, particularly when you’re dealing with software development, which tends to generate all sorts of curious statistics and data sets.The ability to perform a fast, ad hoc analysis makes life much easier.

Now like many people, you might have heard of pivot tables, but like most, you might not have figured out how to use them. So this week, we’ll explain how they work and how you might use them.

A pivot table is a tool that creates cross-tabulations. This means you can ask questions such as how many times a data item occurs (for example, how many Web access attempts to each unique Web site are in the log?) or how many times that data item occurs in relation to other data items (how many times did each employee access each individual Web site?).

Let’s say you have a log file (we talked about syslog in this column some time ago) of SNMP traps captured by Kiwi Syslog Daemon  from a LinkSys EtherFast DSL router.

With a little judicious configuring of Syslog Daemon’s filters, you can create a capture file of all Internet requests by selecting only SNMP messages that originate from the IP address of the DSL router. After a bit of massaging (replacing all spaces with tabs and adding headings to each column), you can open the file in Excel.

You should now have your data in columns and those columns should include date, time, destination IP address, source IP address, destination port and source port.

Next, under Excel’s Data menu option you click on PivotTable and PivotChart Report. This invokes a wizard that asks from where you are going to get your data. You accept the default of “Microsoft Excel list or database” and click Next.

Then you select the data range, the destination for the pivot table (a new sheet) and click finish. What you get is a table on the new sheet with a dialog box labeled PivotTable Field List and a tool palette labeled PivotTable.

The table at this point has no contents. It does, however, have regions outlined in blue that sport the labels Drop Row Fields Here, Drop Column Fields Here, Drop Data Items Here and Drop Page Fields Here. By dragging and dropping the fields from the PivotTable Field List onto the various regions, you can create different analyses of the data.

For example, using the data you have, drag the To IP Address field to the Drop Row Fields Here region, From IP Address field to the Drop Column Fields Here region, and the Time field to the Drop Data Items Here region and voilá.

You now have a table that tabulates how many times each source IP address has attempted to access each destination IP address, complete with totals for each row and column, and a grand total.

Now drag the Date field to the Drop Page Fields Here region. You’ll notice that if the field has multiple values when you drop a field on a region, there will be a triangle to the right of the title. Clicking on the title produces a list of the data items so you select what you want included.

In the row and column regions you can select which values are displayed, while selections in the page region control which groups of field and row items are used. In our example, selecting dates in the page region will restrict which “from” and “to” IP addresses are included in the table, letting you, in effect, sort by date.

Grief saver

The value of pivot tables lies in ad hoc analysis. Where you don’t need them is where there’s a ready-made tool for analysis. For example, Web logs usually are better analyzed with specifically designed analysis tools. But when you run up against the limits of an analysis tool or wind up (as seems all too common) with a data set for which no tool exists, pivot tables can save you from all sorts of grief writing custom code to handle the job.

There’s a lot more to pivot tables and no end of compendious tomes on the subject. With a little work, you can learn how to use them effectively and become, like Bob, a god of pivot tables.

Tabulate to gearhead@gibbs.com.