It happens all the time: equipment in a Telecoms network stops reporting traffic statistics. It’s often a problem, though not always the one you think.
The problem can be caused by a failing component somewhere along the data delivery path. You might think that’s the problem.
More likely, the device is under excessive load, and it stopped reporting stats to dedicate all its processing power to providing service.
Of course, it is up to you to figure out which it is. Fault Management systems might help by alerting you to the failing component. But if the issue is excessive load, the solution lies on a different path.
Many telecoms components are designed to provide service as their number one priority over all others. When that priority is threatened, low-priority tasks are discontinued. From the perspective of the equipment, we say it’s designed to shed low-priority load when under stress.
Stress is often caused by an anomalously high load, or by a capacity shortfall. An anomalous load could be triggered by a natural disaster, weather, a civil disturbance, or anything where large numbers of people begin using the network at the same time.
A capacity shortfall means you best start preparing a Capital budget request to fund a capacity augment.
But those aren’t the problems I wanted to discuss. I want to discuss how you can design your Excel Dashboards and recurring reports to handle errors and data dropouts. The types of data dropouts I described above happen all the time. Having an Error Correction strategy built-in will save you tons of time and avoid long explanations.
Follow Along
I’ve created a workbook which shows you exactly how to do what I’ve written about. I’ll refer to it a lot during this article. You can download it to follow along with the article. The workbook will help you understand what I’ve written, and also allow you to copy/paste into your own workbooks.
I do ask for your email address so that we can keep in touch. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe at any time.
Approach
In this article, I’ll consider a general case: a full month of missing data. Actually, though, this is a little artificial. Rarely will the missing data conform exactly to a full month. This will probably only happen when some defect causes data to go missing for this month and part of the previous month. However, the approach is more broadly applicable and simplifies my task to explain the technique.
The source throughput data I use in my example produces a KPI which is the averaged over the whole month. As an average, it doesn’t matter if part of the month is missing, the value is still the average for the month. But if the data gets summed or has errors, having a block of it missing will be a bigger problem. You’ll have to proactively handle it. I’ve mocked up throughput data to demonstrate missing data.
If you’ve read my other #ExcelTelecomTricks articles, you may recognize the data. Throughput data is generated by a Cacti server monitoring a combined multi-technology Mobile network, and a Fixed Broadband network. A custom Perl script queries the Cacti server daily to collect and collate the Cacti data. The Perl script writes the data to a CSV file. That file is imported monthly into a purpose-built Excel workbook. The workbook populates the Dashboard.
Requirements
Before I start describing the solution, let’s consider our requirements. As always, I want the solution to be as automated as possible.
In this approach, you ‘ll have to identify the missing data manually. It might be possible for that to be automated.
For example, by comparing the monthly count of data points in the current month with previous months. But it is also possible that the number of elements can change month by month, which the count of data points unreliable. So I’ll sidestep that for now, and not require to automatic problem identification.
Certainly, you should not have to mess about with the report or dashboard template. I still adhere to the principle of “separating content from presentation.” Dashboards are simply one of many possible views of the source data.
We use the source data, modify it in an intermediate step, then configure the Dashboard to display the modified data. The source and the Dashboard remain unchanged: error-correction is implemented in the intermediate step. You never overwrite or destroy any raw data.
The same argument as above applies equally here. You never know when you might want to revisit this data, create another view of it. So always retain all source data in its pristine state.
To summarize these requirements as bullets,
- The error correction is as automated as possible
- No manual tweaking of the dashboard or report template.
- The raw, underlying data remains unmodified.
Solution
To state the problem simply, when an expected value is missing or erroneous, substitute a different value. Fundamentally, this is an example of abstraction. I’ve written before about abstraction. The simplest case, the one we’re considering, is when an entire interval is missing.
An important unknown is how you want to determine the correct value to substitute for the missing data. Will you simply repeat the value from the previous month? How about taking the average of several previous months? Is there a long-term trend or seasonality you should to consider?
It’s easy to overthink this. I’ll sidestep the debate by manually entering a replacement value. After you’re comfortable using this technique in your own Dashboards, you might think of an automated way to generate an error correction value. That should be easy to add to your workbook.
A monthly Dashboard needs access to the Trailing Twelve Months. I wrote about TTM Dates in an earlier Excel Telecom Trick. I’ve calculated the TTM dates on the Lookups worksheet.
Next, I summarize the throughput data with a Pivot Table on worksheet Dashboard. This determines the average throughput per month for each group type.
So far, these steps have been done many times in my previous articles. Here is the new part, the part where we add some abstraction. The abstraction tests if the Error Correction data exists. If so, that data is used. If not, the source data is used as normal.
Wait. What Error Correction data? Right, I’ve not described that, yet. Let’s talk about the Error Correction data, and how to align it with the TTM dates in your Dashboard.
Error Correction Worksheet
On the Error Correction worksheet, I’ve created a simple, 3-column table. Whenever you determine that error correction is required, enter a row in this table.
Enter the date for the month needing to be corrected. Use the first day of this month, because that’s how the Synthetic Field “Monthly Date” was defined on the Source Data worksheet.
Enter the group which needs correcting. If data is missing for more than one group, add a row for each group needing error correction. If your Dashboard only reports on a single item, this field might not be necessary in your case.
Lastly, select the value to use for error correction. How you choose this is up to you. I discussed some considerations earlier in the article. Typically, I want to avoid a dramatic change from the previous month, unless that kind of variability is typical for this data set.
Once you entered the data, refresh the Pivot table and your error correction value should now be displayed in the chart. You can see on the Dashboard worksheet where data is missing for each group type.
Inserting Error Correction
Now that the Error Correction worksheet is set up and you’ve added data to the error correction table, how does that data get included in our results? I want the worksheet to test for the existence of Error Correction Data. If that data is present, use it. If it is not present, use the source data. This test can be implemented using Excel’s IFERROR() function. IFERROR() has 2 arguments. The first argument is executed if there is no error. If the first argument generates an error, the second argument is executed. This is exactly what I described. If I use a GETPIVOTDATA() function to display the Error Correction data, it will cause an error if that data is missing.
Putting it All Together
On The Dashboard worksheet, I’ve added a data table. The row headers are the TTM dates. The Columns show the Groups for which Throughput data is displayed. For each value, I’ve entered the IFERROR() function. It displays the Error Correction value if one is present, otherwise, it displays the value from the source data. I’ve added 2 more columns to the right of the data table.
The first column uses another IFERROR() function which is nearly identical. If there is Error Correction data, display it. Otherwise, display a zero.
The second added column creates a new label for the horizontal axis of the chart. This label appends 2 asterisks (“**”) to the TTM date if Error Correction data is present. This is a way to automatically flag to the audience that Error Correction has been applied.
The chart title also calls out the 2 asterisks. Here is the chart with June clearly flagged. I’ve only corrected for groups “Cache” and “DSL”. The chart shows the dramatic dip to zero for the other group types.
Conclusion
My requirements for this trick were 1) to be as automated as possible, 2) no messing with the Dashboard or report template, and 3) don’t alter the source data. Although the amount of automation is a little subjective, I think the requirements have been achieved. In summary, Error Correction using this approach is a 2-step process:
- Add a row to the Error Correction table for the missing or erroneous data.
- Refresh the Pivot Table.
It’s hard to imagine it being any easier than that. Your missing or erroneous data has been corrected, and you can print or share your Dashboard.
To implement this trick in your own projects simply add a worksheet to hold your error correction table and associated Pivot Table.
Then, where your existing Pivot Table displays your source data, wrap an IFERROR() function around it and display.
In this example, I’ve shown Error Correction in the case of missing data. But this technique works just the same when there are actual erroneous values. Simply add a row to the table on the Error Correction worksheet, refresh the Pivot Table, et voilà, the error is corrected!
Give it a try, enter a comment to let me know what you think.
Once again, here is the link to download the workbook.
If you’d like to see more of my Telecoms Tools and Career-Building tips, connect with me on LinkedIn
or follow me on Twitter