The goal of this article is to demonstrate how a rather common reporting issue can be easily addressed using Talend and Excel power pivot. Power pivot has been chosen as reporting tool, since if you are already using Excel, there is no need for additional cost. In any case the same results can be achieved with any reporting tool.
A common problem that enterprises face with the use of any call center is usage reporting. It is necessary to be able to track incoming and outgoing calls per department for a number of reasons. The best approach would be to be able to connect directly to call center Db and download the appropriate data from there. Another approach is to extract the data from the call center and work with these files.
Using Talend it is possible to address the problem with either solution, but in this scenario the second approach (extract files), was chosen. Talend has very powerful capabilities in terms of file management, thus it is very easy to create a job for this purpose.
The designed process is shown in Figure 1 and is the following:
- Cisco call manager extracts daily cdr files in a specific directory.
- Talend will look at the specified directory for filenames starting with cdr.
- If there are no files, Talend will create an exception and send an email stating that no file was found.
- If there are files, it will process them one by one and send the data in a specified database, MySQL, for the specific scenario. If the database rejects any data, again an email is sent to inform the user with the rejected rows.
- If the process finishes successfully, then the processed files are moved to another folder (processed), so as to be able to keep track of all records at any given point of time.
Since the information is stored in a database, it is quite easy to design the required reporting. Still another issue that has to be resolved is the appropriate access to specific information. Every department should have access to specific records, but that is a trivial issue for any reporting tool and thus not presented in the specific scenario.
In order to fully utilize Excel power pivot functionality, the only thing that is required is a database to store (and read obviously) the data. Power pivot is very powerful and can provide really good results with almost no extra cost at all. It uses native excel and therefore it is easy to use for anyone already using excel.
In figure 2 you can see an excel sample of such a report. The specific report layout aims to demonstrate power pivot capabilities and is by no means exhausting. In terms of reporting every organization has its requirements and specifications that would need to be followed in any such implementation.
Power pivot provides some nice tools such as filters as shown in figure 2. You can see that date filters and Initial caller are applied as buttons. It is very easy to apply any kind filter combination just by selecting the appropriate filters and have the results filtered either in table format or graphically as shown in figure 3.
Two month data (January, February) are selected in the selection. You can see that all dates are also selected both in the table and in the graph accordingly. The graph shows calling parties sum of duration in two pies, the smaller one representing data less than 10% for each party.
