Luca Bonuccelli - 17 abril, 2020 - 7 ’ read

Spring Release 2020: Call Analytics top performances and scalability.

Call Analytics Database, R&D, Spring Release 2020, Updates

We had announced it, ladies and gentlemen. Spring Release 2020 was truly an incredible flowering. After talking about the introduction of Full-text search capabilities for contacts search on the Imagicle apps, the new reports available for the Call Recording solution, and the creation of an OWASP Web Security Test Guide, today we are going to tell you about another of the great new features of this release: the tremendous improvement we have made to the Call Analytics solution by optimizing the use of the database and reports. Developers from all over the world, supervisors, operators used to fighting database saturation, spread the sails! Let’s go!

Imagicle Call Analytics: much more than a simple reporting tool.

With Spring 2020, we have worked to further simplify the use of our Call Analytics, the easiest and most advanced solution for monitoring and analyzing telephone traffic.

The name, Call Analytics, might suggest a simple tool for calculating call costs, but it is much more than that.

Thanks to its reports, it tells us how many calls our salespeople made this month for the telemarketing campaign, how long our customers have to wait before being served or, as happened in these emergency days, it allows us to monitor in detail the activity of our call center and size it appropriately also based on traffic at peak times, identifying how many and which calls we have not been able to manage.

Billing is an incredible tool for analyzing the activity of companies of any size.

In addition, from Spring 2020, the database space employed by the Billing have been reduced by up to 70% and the reports will be up to twice as fast.

We are Italo and Luca, from the R&D team, and we are here to tell you how we achieved this increase in Call Analytics performance.

Increase in the database capabilities.

To achieve this performance increase, we completely re-engineered the structure of the database on which the Call Analytics works.

The new schema was designed to reduce data duplication, which took up more memory space and resulted in lower performance efficiency.

In addition, the new schema was made following the principles of the data warehouse.

Let’s take a small step back in time. The first to define the data warehouse concept was William Inmon in 1990: «A data warehouse is a subject-oriented, integrated, nonvolatile, and time-varying collection of data in support of management’s decisions».

Without going into too much detail of this definition, it’s interesting to mention it because Inmon defines decision support as the primary function of a data warehouse: therefore, it must be designed to answer business questions.

There are three categories of decision support that can be provided:

Reports: even if reports are considered the lowest level of decision support, a reporting system capable of generating information reports is of the utmost importance for the proper functioning of any commercial activity;
On-Line Analytic Processing (OLAP): this type of data analysis is performed interactively and quickly on large quantities of data. This interactivity and speed can be obtained by calculating the result of the aggregations of some possible combinations.
Data mining: this type of analysis is very different from the previous ones. In fact, data mining helps in decision support by identifying information of various kinds, which are not known a priori. A classic example of the use of Data Mining is that of the supermarket chain, which analyzing the receipts found that people who bought diapers often bought beer as well. Therefore, by placing the more expensive beers near diapers, they could increase sales.

Currently, Call Analytics provides only the first of these decision support categories, but with this new data warehouse structure, we have laid the basis for providing our customers with the other two categories in the future.

We did tests with real databases to measure the reduction of disk space occupied by the Call Analytics tables. We used two databases, one with around 38 million calls, the other being our production DB, which has around 1 million calls:

Spring Release 2020: Call Analytics top performances and scalability.

But how will customers who already have Call Analytics, but with the old database structure, benefit from this increase in performance?

Well, they will simply have to update their Imagicle UCX Suite to the Spring 2020 version!

Easy, right?

During this update, the billing calls will be migrated from the old database to the new data warehouse. Migration duration depend on the number of calls to be migrated. For our customers who have less than a million calls, the migration will be imperceptible, while for customers who have tens of millions of calls, the migration will take a few hours.

And what if I have a cluster? Will call migration be done from all nodes?

The answer is no

During the update, only one node will migrate the calls. Thanks to the replication service, the other nodes will receive calls from the node that made the migration.

Reports optimization.

We all know that trying to optimize one part of the system often worsens another. As the saying goes: the blanket is short, if you pull on one side, you will leave the other uncovered.

In this case, having reduced the space occupied by the database on the disk, someone (reasonably) might think that we will pay this benefit with more time spent in the extraction of the reports.

Indeed this was a very concrete risk. To avoid it, we have analyzed and optimized the queries of each report, sometimes filing the details, sometimes rewriting them from scratch. We found that in most cases, we were able to reduce execution times drastically.

To evaluate the extent of the improvements, we used a Windows Server 2012 virtual machine with 4 vCPU and 8 GB of ram on Cisco BE6K host (UCSC-C220-M4S: 8 CPU Xeon E5-2630 v3 @ 2.4 GHz, 32 GB of ram).

The database ran on an instance of SQL Server 2017 with around 37 million total calls.

Below, the execution times of the reports for 1 month (about 3.3 million calls):

as you can see most of the reports have had an excellent improvement, with the exception of those in which the details of the individual calls are returned, but all remain in the order of ten seconds (or slightly more ); also, on a smaller number of filtered calls things can go even better :).

A bunch of important tech-specs 🤓

To better understand the steps that led to the optimization of the database, some details must be specified.

some additional fields supplied directly from the data warehouse have been introduced in the view on which the reports operate, making it possible to use less computationally heavy comparison operations. For example, a Boolean (true/false) field was used to indicate whether a call was answered or not, without having to calculate its duration.
the typology of some fields has been changed to facilitate the aggregation/filtering phase. For example, the field containing the call duration was expressed with an integer corresponding to the seconds of actual conversation, in order to speed up the aggregation operations, which at that point translate into a sum of integers
SQL Window functions such as ROW_NUMBER or RANK have been used to take advantage of the new capabilities of SQL Server.
Just to give a concrete example, in the Group Cost By Department report, we used the ROW_NUMBER () OVER (PARTITION BY …) function to speed up the construction of the ranking charts, identifying directly in the query the records that will enhance the ‘Top 10 chart costs’ without having to do subsequent processing.

And customers who have custom reports will still be able to use it?

This time the answer is yes! 👍

Don’t worry, in order not to create problems for customers who have customized reports, we have maintained the same accessibility to the data we had with the old database structure, creating views where previously there were tables. The best SQL Server capabilities in managing views (already introduced by SQL Server 2008 R2) will do the rest.

Conclusion.

With this release, the Call Analytics application becomes even smarter, easier and faster.

By reducing the required disk space, new configuration and sizing possibilities open up:

large customers (with tens of millions of calls per year) will be able to use smaller HD and will have more responsive reports;
small customers will be able to continue using their SQL Server Express, and decide to extend their retention to remain online for a longer period;
medium-sized customers who were previously forced to purchase a commercial version of SQL Server will now have the same benefits by using the Express edition included in AppSuite without additional costs .

What else to say? All you have to do is updating your IAS and start experiencing the benefits of a lean and fast database that this Spring 2020 is bringing to you!

Ps. Did you miss the first post on the news included in the Spring Release 2020?
Get back on track!

Click here and discover the full-text search engine included in Spring 2020: ready to optimize the contacts search for the Imagicle directories solution and the search fields of all Imagicle apps and gadgets, including Cisco Jabber and Cisco Finesse.
Here there’s all you have to know about the new reporting system for Call Recording, to monitor and find all the recording activities in a blink.
And here find out Imagicle first steps for the creation of our personal framework for the development of secure software and how we got started with the OWASP Web Security Test Guide.