h

IO

mon

™

  The I/O Performance Monitor

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

Big Picture

hIOmon Add-On
Microsoft® Office Excel Template/Macros

Product Info

A Microsoft Office Excel template and accompanying macros are also included with the hIOmon "Device and File I/O Performance Analysis" Add-On.  This Excel template can be used to directly import the "summary" file (and device or process) I/O operation performance metrics from the "hIOmon Manager Export File" that was created/activated through the use of the configuration script (e.g., the configuration script option used with the "Device and File I/O Performance Analysis Add-On").

Features

Architecture

Screen Shots

Documentation

Download

In addition, this hIOmon Excel template includes several hIOmon Excel macros.  One macro can be used to easily generate a variety of “Pivot Table” graphs/charts based upon the metrics saved within the hIOmon Manager Export File.  For example, you can graph upon an individual file (and/or device or process) basis over a period of time the total amount of data transferred by read I/O operations and by write I/O operations, the number of random and sequential accesses, etc. as observed by the hIOmon I/O Monitor.

The hIOmon Excel template/macros along with the "Device and File I/O Performance Analysis" option of the "hIOmon Automated Monitoring Configuration Setup" script provide a quick and easy way to help you establish, evaluate and understand the particular I/O operation performance characterizations associated with your specific files (and devices) based upon empirical metrics directly related to the individual files that you are actually using.

NOTE that the hIOmon Excel template/macros can also be used with the hIOmon "Process I/O Performance Analysis Add-On" and with  the hIOmon "Fragmented File I/O Operation Metrics Analysis Add-On".  For example, the hIOmon Excel template can be used to directly import the "fragmented file I/O operation" performance metrics from the "hIOmon Manager Export File" that was created/activated through the use of the "Fragmented File I/O Performance Metrics" option of the configuration script.  In addition, the hIOmon Excel macros can be used to easily generate a variety of graphs/charts based upon the metrics saved within the hIOmon Manager Export File.  For example, you can graph upon a logical disk basis over a period of time the total number of file I/O operations that were associated with Physical Device "split I/O" operations.

Additional information about the hIOmon Excel template/macros is provided below (with complete information provided within the "hIOmon Add-On User Guide" document).  An online tutorial is also available that demonstrates the use of the hIOmon Excel Template and associated macros.

Excel Template/Macros Overview

The hIOmon Excel template provided with the hIOmon "Device and File I/O Performance Analysis Add-On" is the "hIOmonDevFilePerfAnalysisExcelWorksheet.xlt" file.  This Excel template include several hIOmon macros, which are Visual Basic macro files that have already been incorporated (i.e., loaded/imported) into the Excel template file.

The hIOmon Excel macros provided include:

1. The "CoalesceSummaryMetrics" macro, which can be used to coalesce file (and device and process) summary I/O operation performance metrics that have been collected upon a periodic time interval basis.
   
   That is, the macro can be used upon an Excel worksheet (which contains summary I/O operation performance metrics that have been collected and exported by the hIOmon software into a CSV-based "hIOmon Manager Export File") to consolidate into a single row all of those rows of summary periodic metrics for a particular file (or device or process) that are within the same summary time interval period.
   
   This macro can also be used to consolidate the rows of summary I/O operation performance metrics into a single overall total for each individual file (or device or process).
   

2. The "AddCalcMetrics" macro, which can be used to add specific metrics (which require calculations based upon other hIOmon metric values already present within the worksheet) to the worksheet.  Such calculated metrics include "Overall Read Avg Response Time" (read I/O operation overall average response time), "Period Read Avg IOPS" (read I/O operations per second rate observed upon the periodic time interval summary collection basis), and "Overall Read Avg MBs" (overall read megabytes per second transfer rate).
   
   

3. The "BuildPivotTableChart" macro, which can be used to generate Excel Pivot Table/Charts based upon the summary I/O operation performance metrics that have been loaded into the worksheet template. 

The "hIOmon Manager Export File" is a CSV-based export file containing I/O operation performance metrics that have been collected by the hIOmon I/O Monitor.  The "configuration" script file (e.g., see the "Device and File I/O Performance Analysis" script option) can be used to easily and quickly configure the hIOmon software so that I/O operation performance metrics for the desired individual files (and their associated logical and optionally physical disk volume) are collected and exported to a hIOmon Manager Export File for subsequent use with the hIOmon Excel template file and its associated macros.

The sections below provide brief information about how to import the hIOmon Manager Export File using the hIOmon Excel template and how to use the "BuildPivotTableChart" macro to chart/graph the summary I/O operation performance metrics loaded into the worksheet template. 

Specific "step-by-step" instructions for loading the hIOmon Manager Export File contents (i.e., the exported I/O operation performance metrics) into the hIOmon Excel template file and for using the accompanying macros are described within the "hIOmon Add-On User Guide" document).

The Excel template file and its associated macros have been developed by hyperI/O LLC for use with the Microsoft Office Excel spreadsheet software program.  Using these macros requires that the "use of macros" be enabled within the Excel template.

If you choose to "Disable Macros" within Excel, then you will not be able to run the macros (and so will not be able to use them as an easy and quick means, for instance, of automatically generating Pivot Table/Charts for selected metrics contained within the imported hIOmon Manager Export File).  Note that the Visual Basic Editor (if available under Excel) can be used to display the contents of the macros.

In addition, the macros require that the column headings within the imported hIOmon Manager Export File (which represent the types of I/O operation performance metrics that were saved into the export file) not be altered from those that were automatically included by the hIOmon software when the export file was created.

The "hIOmonDevFilePerfAnalysisExcelWorksheet.xlt" Excel template file can be found in the "AddOns" folder within the "..\hIOmon\Support\" directory where the hIOmon software was installed.  You can also open this hIOmon Excel template file by means of the "Start -> Programs -> hIOmon -> AddOns -> hIOmon Device-File Perf Analysis Excel Worksheet" shortcut (that was added to the Start Menu by the Windows Installer as part of the hIOmon software installation process).

The hIOmon Manager Export File to be imported can be found in the "\hIOmonGr\ExportFiles" folder within the "..\hIOmon\Support\" directory where the hIOmon software was installed.

The hIOmon Manager Export File is a standard "Comma Separated Values (CSV)" delimited file.  As such, it can be imported simply by opening the export file within the Excel template.  See the "Fragmented File I/O Metrics Analysis Add-On" description for an example of how to import the hIOmon Manager Export File within the Excel template. Also refer to the "hIOmon Add-On User Guide" document for complete details about using the hIOmon Excel template and the accompanying macros.

The hIOmon Excel template includes a hIOmon Excel "Build Pivot Table/Chart" macro that can be used to easily and quickly generate Excel Pivot Table/Charts based upon the summary I/O operation performance metrics that have been loaded into the template worksheet.  See the "Fragmented File I/O Metrics Analysis Add-On" description for an example of how this macro can be used.

The basic charting/graphing and data (e.g., sort and autofilter) functions already included within Excel can also be used upon the hIOmon Excel template worksheet; see the "Using the Base Excel Chart/Graphing Functions" page for more information.

Please refer to the "hIOmon Add-On User Guide" document for complete details about charting and graphing the file and device I/O performance metrics collected by hIOmon..

Back to Top

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