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.
Developer
tab in Excel (in the top bar)Insert
Command Button (ActiveX Control)
under ActiveX Controls
View Code
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
ctrl
+ s
on the keyboard to save the code (or use the save button in the bar on the top)Developer
tab againDesign Mode
button by pressing on it (only do this when the button is selected)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.