Introduction
Hello. We will continue the series of Hunter Tool modules, showing today the module responsible for the plot of Network Performance (KPI) in Google Earth.
Needless to say: the analysis of Performance indicators (KPI) is an essential task to evaluate any system.
And in terms of user experience, i.e. to facilitate the analysis as much as possible, Google Earth is becoming increasingly used in virtually all areas, not just in Telecommunications and IT.
As one of its major advantages, we point out that Google Earth is available for those who want to get it. That is, you can send its reports to other people, they simply need to have Google Earth installed (or install it for free) to also do the same analysis.
Another advantage comes from the nature of the program, which provides a large amount of resources (aerial images always updated, support tools as ruler and others that allow editing and interaction with the data, etc.). In addition, it has been continuously improved, mainly in the 3D field - a new dimension that allows us to further extend our analysis.
And one interesting benefit, we use quite a lot: the files are text type, and can be written via any programming language, such as VBA Excel (macros). In other words, through the manipulation of our tabular data, we can build almost any kind of visualization – including polygons, colors, and as we speak, 3D!
In today’s tutorial, we will view a complete application, which handles the data from two tables (Network data as antennae, sites, etc. And also Performance data such as traffic and dropped calls).
Important Note
But before we continue, we have important information for you that has been viewing all telecomHall tutorials.
Each tutorial always presents a series of new concepts, always with the goal of increasing your range of possibilities. Knowing these new possibilities, you can decide how to act.
But to do this, each new tutorial demands a great effort, especially when we try to describe here all processes, all codes, etc.
On one hand it is good for beginners; but it’s time consuming! Time that could be dedicated simply to provide numerous modules still exist, and are waiting to be published/made available. That is, everyone ends up losing if the pace of tutorials decreases.
Another important point that we would like to reinforce is that currently the Hunter is used in almost every country on the planet. And in each of these countries has its peculiarities, their specific needs. The diversity is much wider in every sense of the word. Even if we wanted to, we could no support everyone individually.
And our goal is not, nor ever was, to meet specific needs! Our goal is to ‘teach’. And for that, we always create modules that can be used as examples.
And to create examples, we need to follow a methodology. Hence the need to adopt standard names, either to the structure of the directories, filenames, indicators, etc.
From the available data, you can first: practice. See the module in action. Then you can, using the same structure, replace the data for your own, and again: practice - but now with your Network data. Finally, you have the possibility to extend as you like or want, according to your interests or needs. Of course to do this you need to have a minimum knowledge of macros, and have read all the tutorials.
And we have another very important point: If you want to make the most of what Hunter can offer, you should really read all available tutorials, without exception! This is because each tutorial, especially earlier tutorials, shows step by step how each module is created. Even if you, for example, never run a Test Drive, at least read the tutorial about it, and follow the tips and tricks that were shared, and typically used in subsequent tutorials.
Finally, the issue of support. With a large amount of users currently using the tool, it is virtually impossible to give individual support, directly answer all emails. So, for everyone keep learning, and consequently we have more time to publish new modules, we ask (please) that in case of any doubts, ask the question in the this forum.
Note: the emails received will only be answered if directly related to problems with the implementation of modules with the sample files that is sent together with each one. If you have any problem with your particular developments, please use the mentioned Forum. So, the answer will be given by there, and will be available for ALL those who may have the same question or problem.
Following this line, we will be able to publish new modules every week. Not just involving Google Earth, but also new modules for Mapinfo, Mapbasic, Dashboards, Access, Graphics, etc. You can be sure: everybody wins, and you’ll like a lot what’s about to come.
Hunter Google Earth Performance (KPI) Plot
Back to our today’s tutorial: today we will provide a complete application using Access that combines data from two tables, and generates the plot in Google Earth.
User Interface
As always, let’s start from the main Interface of this module.
Note: to run this application, simply open the file ‘Hunter_GE_Performance_RUN_KPI_1.0.0.mdb’ located in the directory: ‘C:\Hunter\GE\Performance\Scripts’.
The options are as always very intuitive, and basically follow the same explanations of other tutorials/published modules.
Following the most direct way of demonstration, at ‘Main’ tab you have the main plot options.
From the corresponding choice (‘checkboxes’), you can generate indicators plot files like ‘Traffic’, ‘Call Drop Rate’, ‘Call Blocking Rate’, ‘Efficiency Rate’ and ‘Handover Efficiency Rate’.
These indicators are the key indicators to whatever the system, and we’ve also already talked about them in previous tutorials.
All this however will be easier to be understood when you practice. That is, after receiving the files, simply click on the ‘RUN’ button (the button with Google Earth icon), and the files will be generated in the corresponding directory of this module. Also: If you click on the button with the icon of a Windows Folder, the program opens this directory for you.
Opening one of these files, you can see the result. Let’s see some examples, along with some of the features.
In any layer, as the ‘Call Drop Rate’ layer, when you hover over the edge of a cell (1), you see the result (2) without having to click.
Also in any layer, such as the ‘Handover Efficiency Rate’, by clicking on the edge of the cell you have access to a message box (1), with some extra information, as in the example: the Traffic for that cell.
In the example of ‘Call Blocking Rate’ layer, we can easily identify the areas where the call blocking is more expressive (1). In addition, we can see for example an unbalance between different sites, in the same location.
In the left side menu, we can see that the data files are grouped into ‘directories’, which allow the application to ‘filters’. For example to make the ‘drill down’ for some particular indicator or cell.
In the above example, we’ve unchecked all points. Then checked only what we want to show/see. In this case, we are analyzing ‘SUCC_P’ (1), the Efficiency Rate, which is the ‘root’ directory for this Indicator. Then we filter only the ‘UMTS_850_1’ folder (2), i.e. cells UMTS, band 850 MHz and with 1 Carrier. And finally we do a ‘drill down’ even deeper, choosing only those cells with Efficiency between ‘0 and 75%’ (3) - checking the corresponding folder that contains these cells.
We can then see that we have only one cell in this ‘bad’ condition: ‘UADI_850_1’ cell.
These were some examples, or demonstration of the possibility of analysis that the data allow us to do. You can use according to your needs, both for analysis and also management reports and presentations.
Note: the definition of all properties that we saw is automatic, without the need for code changes. To do this, of course, must be followed some minimum requirements in the input data.
These data come from auxiliary tables, as we shall see in more detail below.
Auxiliary Tables
Auxiliary tables contains the information necessary to generate the output data, for this and other modules that use the information in common.
Better Explaining: in one of the tables, we use constants, as the Earth curvature radius, used in calculations of distance between coordinates. These constants are used by other modules - some we’ve seen, and others that we will see soon. So, it is desirable that the keep this data in a common place, for two reasons. First, to avoid redundant information, since we have only one location providing the common information. Second, because when we do some editing or changing in these common data, it will be already updated to be used in other applications.
This information can be provided in tables in the Access Databases, but for convenience, we saved it in Excel tables. And the common directory that we use for this control is the ‘C:\Hunter\Common\Database’.
Each of these tables is already properly linked to our application, and we have already seen that this is the same as having these existing tables in the application database.
Starting with the simplest table - ‘tbl_Aux_Constants’, we have the constants. We see that here we can define constants, and then use the data from wherever we need.
Another auxiliary table that this module has is ‘tbl_Aux_GE_Styles’. This table is already known from other tutorials, and contains the Google Earth styles information.
That is, we define a particular style ‘strStyle’, and for this style, we define colors, lines, sizes, etc. For example, style ‘color003’ has the ‘color003.png’ image as its icon, the icon size equal to ‘0.2’, etc.
When changed, or even included a new style here, it shall be made available to the applications that use it. To do this, the application uses a query, filtering the field ‘Hunter_Module’. This prevents the use of all styles in all applications. (This would not be a problem, would be just use much more styles when then needed).
Continuing, we have the auxiliary table ‘tbl_Aux_KPI_Thematic’. This table contains the ranges and colors that will be used for formatting, or KPI Thematic Maps.
In above example, we define for example, for the ‘TRAF’ - Traffic, ranges 0 to 0.1 as light blue (LightBlue), 0.1 to 10 as light green (LightGreen), etc.
You can include new ranges, delete existing ranges, or simply change the values.
Note: always remember to check if the ranges defined are consistent! For example, if you set the first range of 0.1 to 0, and the second of 5 to 10, the cells that have Traffic between 0.1 and 5 will not be plotted!
Take advantage of this same table to create our Legends. Just use a ‘Print Screen’, and in an image editor, like ‘Paint’ cut the excess edges.
A legend that covers our current range is:
Remember that legends should be stored as ‘Legend_INDICATOR.png’ in the icons directory Hunter, where ‘INDICATOR’ is the name of the corresponding indicator. (But of course, you can change this ‘format’ in your code).
The last table is the ‘tbl_Aux_TECHNOLOGY_BAND_CARR’. This table is important because it defines the characteristics of every type of cell that we have on the network. And when the ‘USE’ field of its row is marked, it will be created a sub-folder on Google Earth, grouping this information.
These considerations are important because in a same map we will analyze our entire network. For example, if all our cells have the same distance (radius), they would be overlapping, and consequently the analysis lose sense.
The distance for each cell is defined by the field ‘Distance’. And the cell height is given by the ‘Height’ field. Remember to always use the higher height to smaller cells distance, and smaller height for higher cells distance. Mainly in modules where will be 3D plotting this information will be essential in order to obtain a better result.
The fields ‘intNumSteps’ and ‘floAngleStep’ are used to indicate the number of points of the semicircle of each cell, and the variation, in degrees, of the central angle.
Note: do not confuse here the distance, and especially the height of the cell with the values of the network. Remember that here we are talking of the properties we plot in Google Earth, and serve only to make the distinctions between different cells (by technology, Carrier and Bandwidth) as talked about earlier.
The field that is used to group the data is ‘TECHNOLOGY_BAND_CARR’. This field contains a formula, and you only need to fill in the data ‘Carrier’, ‘Technology’ and ‘Band’ of each cell type that you have in your Network.
To help the filling of the data in that table, and especially avoid that cells with some kind of ‘TECHNOLOGY_BAND_CARR’ don’t be plotted, you can do a check using the auxiliary query ‘Audit_Fill_TECHNOLOGY_BAND_CARR’.
This simple query checks whether the data that is present in the table with the data from the network (‘tbl_Network’) have its respective cell types declared. If one is missing, you can include in the auxiliary table.
Note: remember that the editing of the auxiliary tables is not something you will be doing always. Once defined the fields and values of your network, you hardly need to make edits here. Both for this and other modules that use these data.
Data tables
Once defined the auxiliary tables, we can now see the data tables.
We have two data tables, one with Network information, and other with Performance information.
Tables with Network Data
In our example, the table with network data comes from a linked Excel file.
This is a simple table, but it neeeds at least the main fields (Latitude, Longitude, etc.), as indicated in yellow.
Tables with Performance Data
And the table with Performance Data comes from another linked Excel file.
This table contains the main network indicators, as we saw earlier.
To get the data in its final form, as shown in some images above, simply run the tool. And of course: the values must match! If you have a cellname ‘XYZ’ with traffic information on the Performance table, to allow this cellname to be plotted, this cell must exist – exactly with this name ‘XYZ’ - in the Network Table.
To use data from your network, replace the ‘values’, but keep the same ‘structure’ (or fields names).
If you want to customize the application, turn on debug mode tool (View the code in VBE Editor). The code is fully commented, so you can easily include your own codes/needs.
As already mentioned, the best way to get support is posting your question on the Forum for users of the tool, here.
Full Code
As always, the VBA code is already available.
Conclusion
We have seen today the Hunter module responsible for plotting the Network and Performance Indicators Data in Google Earth, a great way to expand your universe of analysis, as well as reports.
We are Increasingly focused to provide new modules, and if you still need any further explanation, feel free to ask.
Thank you for visiting, and once again we thank to those who recognize our effort.
Always remember: the knowledge you acquire with each tutorial can represent your differential!
Download
Download Source Code:Blog_038_Hunter_Google_Earth_Performance_KPI_Plot_(Application).zip (1.5 MB)
