Ben Clifford

Visualizing HCP Chargeback Reports

Blog Post created by Ben Clifford Employee on Feb 16, 2018

HCP chargeback reports contain valuable information that is useful toward understanding HCP utilization and workloads. The problem is that the data can be overwhelming. Trying to understand this data in it's tabular form is not humanly possible. What we need to understand this data is visual representation, but building charts and graphs is time consuming isn't it? Actually no, you can visualize chargeback report data in under 5 minutes using the PivotChart features in Excel. Read on to find out how.

 

In the HCP System Management Console go to the Monitoring => Chargeback page. Select the range of dates you would like to report and choose Hour or Day reporting interval. Hour is recommended if you would like to be able to identify time of day peaks in application activity.

Click the Download Report button and open the downloaded report.csv file in Excel. The example shown uses Microsoft Excel 2016, but the pivot chart features have existed since at least 2008.  Save the file as an xls/xlsx file, csv format is not capable of saving the changes you will be making.

 

Click anywhere inside the data in you spreadsheet and then, in the Insert menu, select PivotChart.

On the Create PivotChart dialog, your data has automatically been fully selected, and it will default to placing your PivotChart in a new sheet. Accept the defaults and click OK.

Excel will create a new sheet in your workbook, into which it will insert a new PivotTable (green) and a new PivotChart (red). Both Pivot objects are empty because we have not added anything to them just yet. If you do not see a dialog window titled "PivotChart Fields" just click anywhere inside the PivotChart area1.
1If you still do not see it right click in the PivotChart area and select "Show field list".

In the PivotChart Fields dialog you will select the fields from the data that you want to visualize, and how you want to represent and filter that data. Typically you will want to be able to filter by tenantName and namespaceName, so go ahead and drag those fields into the 'Filters' section. We also typically like to display the activity in these reports over time, so go ahead and drag startTime into the 'Axis (Categories)' section. Finally you need to decide what data values you want to visualize, for our first chart lets visualize the number of read and write operations, so we will drag reads and writes into the 'Values' section.

pivot_select.gif

Notice that after we drop the fields in the Values section that the text changes to "Sum of <field>", this is because we have to specify field aggregation rules. We need these rules if, for example, we decide to roll our results up by month instead of day or hour. In some cases we may wish to show the sum of the values (default), or the average, or perhaps the min or the max. To change aggregation rules for the field click the down arrow next to the field in the Values section and click "Value Field Settings...", select your aggregation rule. For now we are going to leave our aggregation at Sum.

If you have been following along you should now see something like what you see below. Your PivotTable (green) now has data in it as does your PivotChart (red). The first thing you may notice is that your nice daily or hourly chargeback data has been rolled  up by month. And you may also notice that the numbers shown are much higher than what the system is actually doing. Let's start by addressing the inflated numbers.

Because of the way chargeback reports roll up the data by tenant (tenant not blank, namespace blank) and by system (tenant and namespace blank), if you aggregate the whole report you will be triple counting values: once in the namespace, once in the tenant rollup, and a third time in the system rollup. For our example let's just look at system numbers, so we will filter on tenantName=blank. In the upper left corner of your PivotChart click on tenantName.

And in the selection dialog select '(blank)' and click OK.

Now that we are not double counting data, let's fix the time rollup issue. In the PivotTable right click on any month value, 'Nov' in the example below.

Select 'Group' from the context menu, and in the Grouping dialog box select Hours, Days, and Months and click OK.

Now you will see your data reported by hour.

It may be difficult to digest as a bar chart, you can convert to a different chart type. In the PivotChart Tools Design menu click "Change Chart Type".

In the Change Chart Type dialog box, choose line and click OK.

 

You now know enough to get started and to quickly visualize HCP workload information. You can see from the examples above that you can quickly choose different field values to visualize, filter the data by tenant or namespace, and choose the right chart type to graphically represent your data.

Outcomes