h

IO

mon

™

  The I/O Performance Monitor

Home  |  Products & Services  |  Contact  |  About hyperI/O^sm

Big Picture

hIOmon Fragmented File I/O Metrics Analysis Add-On 
Build Pivot Table/Chart Macro

Product Info

The hIOmon "Device and File I/O Performance Analysis Add-On" provides a Microsoft® Office Excel macro that can be used to easily and quickly generate a variety of charts and graphs using the Excel Pivot Table/Chart feature.  This "Build Pivot Table/Chart" macro can also be used by the hIOmon "Process I/O Performance Analysis Add-On" and also by the hIOmon "Fragmented File I/O Operation Metrics Analysis Add-On" (where the charts and graphs generated by the macro are based upon the "fragmented file I/O operation" performance metrics that have been imported from a hIOmon Manager Export File into the Excel worksheet using the hIOmon Excel template file).

Features

Architecture

Screen Shots

Documentation

Download

How to create PivotTable/Charts using the hIOmon "BuildPivotTableChart" Excel macro

The hIOmon Excel template file incorporates a Visual Basic module (named "hIOmonBuildPivotTableChart") that includes the hIOmon "BuildPivotTableChart" Excel macro.  This macro can be used to automatically generate Pivot Table/Charts based upon the metric values contained within the hIOmon Excel template worksheet.

Moreover, you can create a variety of different charts/graphs based upon the particular metric types that you select.  The particular metric types to be used in creating a Pivot Table/Chart are chosen by selecting one (or more) of the Column Headings within the first row of the worksheet.

For example, to generate a Pivot Table/Chart based upon the "Read IOP Count" and "Write IOP Count" metrics, first select both of the associated Column Headings as shown below.  Note that as a result of being selected, the "Read IOP Count" cell is highlighted by a dark color background and the "Write IOP Count" cell is highlighted by a black perimeter box (since it was the second cell selected).   

Next use the Excel "Alt+F8" shortcut (or alternatively go to the Excel "Tools" standard toolbar option, select "Macro" within the menu drop-down list, and finally the "Macros..." sub-menu option) to display the Excel "Macro List" as shown below:

Select the "BuildPivotTableChart" macro, and then click on the "Run" button.  Based upon the selected column headings, the macro will automatically create a Pivot Table (named "hIOmonIOstatsSheetx") along with a Pivot Chart (named "hIOmonIOstatsChartx").  The generated Pivot Table is shown below with the "E:" device (within the "File Name" drop-down list box) and the "5" date (within the "Date" drop-down list box) selected for display:  

Note again that each row of the displayed Pivot Table ("hIOmonIOstatsSheet1") contains the current total accumulated by the hIOmon I/O Monitor as of the particular hour for the selected device (in this case, the "E:" drive as shown in the drop-down box alongside the "File Name" button in row 3).  In other words, at hour 9 (on date 5) there had been a total of 70803 read I/O operations observed by the hIOmon I/O Monitor; one hour later (at hour 10), the accumulated total had risen by 59947 to a cumulative total of 130750 read I/O operations observed.  

The associated Pivot Chart ("hIOmonIOstatsChart1") generated by the macro is similarly based upon the same selected "Read IOP Count" and "Write IOP Count" column headings as shown below:   

As illustrated above, the macro provides a easy and quick means for creating Pivot Tables and Pivots Charts, moreover based upon the particular metrics of interest to you.  See the "Building an Excel Pivot Table/Chart for Specific-File Fragmentation I/O Metrics" tutorial for another example that makes use of the hIOmon "BuildPivotTableChart" Excel macro.   

Please note the following regarding the BuildPivotTableChart macro support for generating Pivot Tables/Charts:

• A Pivot Table/Chart can be created based upon one or more metric types.  Simply select the column headings representing the particular metrics of interest to you, and then run the macro.
  
  Note that by default, the macro generates the Pivot Table/Chart using the "Hour" metric as the basis for the "Category" axis (i.e., "X-axis").  To have some other time metric (e.g., the “Minute” metric) serve as the basis for the Category/X-axis, simply select the column heading of the desired time metric along with the other particular metrics of interest to you, and then run the macro. 
  
  

• The macro can be used to generate one or more Pivot Tables/Charts for the same worksheet.  Simply select the column headings of interest before running the macro for each Pivot Table/Chart that you wish to create.  Each generated Pivot Table and associated Pivot Chart will occupy a new separate worksheet.

• The Excel "Pivot Tables/Charts" feature provides great flexibility for dynamically altering not only the display itself (e.g., the chart type) but also the displayed contents (e.g., you can individually select a device within the "File Name" drop-down list box to show the metrics for the respective device).
  
  Please be aware, however, that the "(All)" option must be used with caution, since it may not provide valid overall summarized values (e.g., the overall "grand total" sum of all the individual devices might not be valid due to overlaps and/or missing values, such as no recorded I/O activity for some hours).
  
  In addition, if you manually drag/move a Pivot Table "metric" field button from the PivotTable Toolbar to the Pivot Table/Chart "Data" area, then you must change this "metric" (using the "Field Setting" or "Format PivotChart Field" menu selection) to be summarized by "Max" (rather than the default "Sum").       

• To save a generated Pivot Table or Pivot Chart worksheet, open another existing Excel workbook and then copy the worksheet to this other Excel workbook (e.g., by right-clicking on the worksheet name tab at the bottom of the Excel window display, then selecting the displayed "Move or Copy ..." menu option).

In addition as an extended feature, the macro can optionally generate a Pivot Table/Chart based upon user-specified period groupings.

Back to Top

hyperI/O^sm -  improving the performance of storage I/O in computer servers