Performance measurements data analysis

authors:Joost Baars
date:June 2020

Description

This page describes how the data of the matrix board performance measurement application can easily be analyzed (see application: Matrixboard performance application). This page contains a method that can easily calculate the average of each configuration, the only part that needs to be done manually is creating graphs (if wanted).

Requirements

This page is tested on Microsoft Excel for Office 365. The code shown on this page should also work for other versions of Excel, but the steps will probably be different.

  • The CSV results of the performance application
  • Microsoft Excel

Analyzing the data

Follow the steps below for easy calculating the average values of each configuration. Also, the maximum deviation is calculated using these steps. This method is only useful if each configuration is executed at least twice.

1. Open the CSV results with Excel.
2. Go to the Developer tab in Excel (in the top bar)
3. Click on Insert
4. Select Command Button (ActiveX Control) under ActiveX Controls
5. Click somewhere on the Excel spreadsheet to create the command button
6. Right click the command button and select View Code
7. Copy the code below
8. Paste the code between Private Sub CommandButton1_Click() and End Sub
Dim i As Integer
Dim Copyrange As String
Dim String1 As String
Dim Name As String
Dim Connect As String
Dim Disconnect As String
Dim CPU As String
Dim Phys As String
Dim Virt As String
Dim start As Integer
Dim sizeMeasurement As Integer
Dim storeY As Integer
Dim FirstFind As Integer
MsgBox ActiveSheet.UsedRange.Rows.Count

FirstFind = 0
start = 1
sizeMeasurement = 1
storeY = 2

Cells(1, 10) = "Configuration"
Cells(1, 11) = "Connect duration average"
Cells(1, 12) = "Disconnect duration average"
Cells(1, 13) = "CPU usage average"
Cells(1, 14) = "Physical memory usage average"
Cells(1, 15) = "Virtual memory usage average"
Cells(1, 17) = "Connect duration maximum deviation"
Cells(1, 18) = "Disconnect duration maximum deviation"
Cells(1, 19) = "CPU usage maximum deviation"
Cells(1, 20) = "Physical memory usage maximum deviation"
Cells(1, 21) = "Virtual memory usage maximum deviation"


For i = 1 To ActiveSheet.UsedRange.Rows.Count
    Let Copyrange = "A" & i
    If IsNumeric(Cells(i, 1).Value) = False Or i = ActiveSheet.UsedRange.Rows.Count Then
        Let String1 = "J" & storeMeasurement
        If FirstFind = 1 Then
            Cells(storeY, 10) = Cells(start, 1)
            Cells(storeY, 11) = Application.Average(Range(Cells(start, 2), Cells(i - 1, 2)).Value)
            Cells(storeY, 12) = Application.Average(Range(Cells(start, 5), Cells(i - 1, 5)).Value)
            Cells(storeY, 13) = Application.Average(Range(Cells(start, 7), Cells(i - 1, 7)).Value)
            Cells(storeY, 14) = Application.Average(Range(Cells(start, 8), Cells(i - 1, 8)).Value)
            Cells(storeY, 15) = Application.Average(Range(Cells(start, 9), Cells(i - 1, 9)).Value)
            Cells(storeY, 17) = Application.Max(Range(Cells(start, 2), Cells(i - 1, 2)).Value) - Application.Min(Range(Cells(start, 2), Cells(i - 1, 2)).Value)
            Cells(storeY, 18) = Application.Max(Range(Cells(start, 5), Cells(i - 1, 5)).Value) - Application.Min(Range(Cells(start, 5), Cells(i - 1, 5)).Value)
            Cells(storeY, 19) = Application.Max(Range(Cells(start, 7), Cells(i - 1, 7)).Value) - Application.Min(Range(Cells(start, 7), Cells(i - 1, 7)).Value)
            Cells(storeY, 20) = Application.Max(Range(Cells(start, 8), Cells(i - 1, 8)).Value) - Application.Min(Range(Cells(start, 8), Cells(i - 1, 8)).Value)
            Cells(storeY, 21) = Application.Max(Range(Cells(start, 9), Cells(i - 1, 9)).Value) - Application.Min(Range(Cells(start, 9), Cells(i - 1, 9)).Value)

            storeY = storeY + 1
        End If
        start = i
        FirstFind = 1
    End If
Next i
8. Click ctrl + s on the keyboard to save the code (or use the save button in the bar on the top)
9. Close the code window
10. Within the Excel spreadsheet, go to the Developer tab again
11. Deselect the Design Mode button by pressing on it (only do this when the button is selected)
12. Click on the created button within the Excel spreadsheet

Data should be generated by clicking on the button.

The average data should now appear on the top of the Excel spreadsheet (on the right of the csv results). If the button is placed on the data, the button can be moved by selecting the Design Mode within the Developer tab.

The meaning of the data is also generated using this script. The maximum deviation is the deviation between the lowest and highest value within the measurements.