One advantage of working with an organized and integrated modules - such as the methodology suggested by Hunter - is the gain that this scenario provides. Let’s begin to understand how this works with the module ‘Hunter Updater’.
Currently we have the main databases with our information (Network, Parameters, and Performance).
And we also have a number of other external tools we use in every way, in daily work, and the data update for them is crucial to the outcome of the analysis. Some examples are Prediction Tools, Drive Test Tools, Frequency Planning Tools, Workspaces in Mapinfo and Google Earth - with the configuration of current Network, or even simply Specific File Formats/Reports, which should be generated periodically.
All these external tools have a specific format for its data input/update, as simple as ‘TEXT’, ‘CSV’, ‘XLS’, ‘XML’ to even more complex shapes, such as database tables, or ‘DAT’ files type.
The good news is that Regardless of which is the format expected by such software, we can always generate them from our Hunter System: Once we have the data from our network, we simply work it properly and export it in the desired format!
And let’s start today with a tool that will be available in the next tutorial, "Hunter Mapbasic Tool’ - a tool that helps a lot in the tasks of quick inspection of the Neighbours and Frequency Plan for GSM Network.
This tool reads a file: expects a specific format (Mapinfo TAB). Although it is not difficult to generate this file manually, this repetitive task becomes unnecessary - as well as any updates for other tools that require only manual labor.
So now let’s see how we generate the final table in Mapinfo (including with the points already created) using the data from our system (Access/Excel).
Note: this subject has been explained in the previous tutorial Mapinfo Automation. Now however, the approach is more practical, and also involves the use of more than one table as a source of final data.
‘Hunter Mapbasic Tool’
As we generate the input file for our Hunter Mapbasic Tool, let’s talk a little about it.
This tool is fully created using Mapbasic - Mapinfo Programming Language, and serves as the Auxiliary for GSM Frequency Plan Analysis (BCCH, BSIC, HSN, MAY) and also of for Generic Neighbors (Neighbor List).
Very soon we will talk specifically about it, so let’s only worry now about generating its input file. Of course it must contain all the information that will be worked such as Latitude, Longitude, BCCH, Neighbors, etc. … That is, an overview table with all the information as shown below.
As we said, the tool reads an input file of type ‘TAB’, a MapInfo table. A file of type ‘TAB’ can be obtained in various ways, eg by opening an Excel spreadsheet in Mapinfo (in this case is also generated a file of type ‘DAT’). Beyond these two types of files, the tool needs two more formats (‘ID’ and ‘MAP’), created when the points are created, from the Latitude/Longitude fields.
So, we can manually create an Excel spreadsheet (1), and open it through the Mapinfo Interface (Menus) - generating the ‘TAB’ and ‘DAT’ files (2). And finally, again via Mapinfo Menu, create points for it - generating the ‘ID’ and ‘MAP’ files (3). Then we can start working.
Okay. What if you could generate all needed Data Sheet Info, and also all the input files (TAB, DAT, ID and MAP) automatically? Yes, it would be better, right?
Well that’s what we’ll show you how to do today.
Starting from our Initial Data…
Let’s get the data we already have available in the tables - Hunter modules.
Network : a database containing tables with Physical Information for our Network, such as Latitude, Longitude, Azimuth, etc …
- In our case, a database with table ‘tbl_Network’.
Parameters : another database with configuration parameters - BCCH, BSIC, TCH, Neighbors, etc. …
- In our case, another database with some tables with the required parameters for the demonstration today. Note: For convenience, all tables will be available in one database, but in practice are located in different places, according to what we always teach in Hunter Methodology.
The task of this module can then be divided into two steps:
- Generate a single table (or query) with all the necessary data;
- Format the data in this table (query) in its final expected form (files).
… Generating a Single Table from Several Others …
To generate a Single Final Query, with all the data from other tables and/or queries, the procedure is simple. Just create a query with the tables/queries involved, tying the corresponding fields (in this example ‘cellname’) and then simply choosing the output fields.
But in real life, the data usually DO NOT come in the format you desire. In fact, usually come in a variety of possible ways (rows, columns, partially treated or untreated). And in separate tables, depending on the content - for example, one (or more) to Neighbors, others to TCH and so on.
But this is not a problem for us. Once we’ve created queries that put the data in the right format, our procedure becomes extremely simple!
We will see soon.
… Export this Table to Final Expected Format
After treating the data and bring them together in a single query, we can now export the final files.
And this is an easy part because it has already been learned before, the tutorial on Mapinfo Automation remember?
We have a few extra details, but we will see next, with the explanation of our application today.
Present the solution to the module 'Hunter Updater - Mapinfo Network Cells (Mapbasic Tool). "
From tables with data from our Network (Latitude, Longitude, etc …) and Parameters (Neighbors, BCCH, etc …), generate updated files for our Tool ‘Hunter Mapbasic Tool’.
The file structure is as shown below, and you should already be familiar with how to get it from the received file via email.
We then get the first part of our application, which is to generate a final query with all the necessary data.
Warning: here we show the solution according to data format in our sample files. Depending on your ‘vendor’, formats can be a little different, and you should make some slight adjustment. Anyway, our sample files cover the main possibilities of data presentation from the majority of existing ‘vendors’.
This table is the simplest of all. It has the ID field (‘cellname’), and other physical fields that we use as ‘Latitude’ and ‘Longitude’.
There is no need to do anything with this table, the data is already as we expect.
One possibility is the presentation of data with one ID per line, but the data in columns - such as data TCH (FREQn) in the columns below.
In this case, we transform the data into a single field, just create a calculated field, concatenated the desired data.
But we can do this at the final query, and we’ll also not do anything with this table as well.
A third type of data comes from custom tables. In the future we will talk about them: they are control tables with the parameters of each technology more accessible - generated by us (and not derived from OSS) based on some other tables. It is like a summary table with the main parameters of the network.
So when we need fast access to main basic parameters, we can acces this table directly. It may also have calculated fields, such as a TRX Quantity. But let’s talk about this approach later. For now, just assume that you have this custom table already available, and we can use it to fetch the data from ‘BCCH’, ‘BSIC’, among some others.
Table tbl_NEIGHBOR and tbl_MAIO
Finally, another possibility, pretty common: data arranged in any number of rows for each ID (in our case, ‘cellname’).
This is what happens for example with the Neighbors.
We need to ‘transpose’ the data, and get something like shown below. But see that we can have from 1 to 31 Neighbors!
We can consider this as part of ‘less simple’ throughout this tutorial today. But even so it’s not hard to understand.
A possibility to ‘translate’ data in Access is to use a crosstab query, as explained in the ‘Access Tips’ Tips Serie.
However, if the data is in a format that hinder this type of query, or better, if we need a little more standard or customized result, we can use VBA.
So to resolve this problem, we create a little VBA code (less than 30 lines), which has as parameters or arguments the table/query with the data to be transposed, and also the table where the data should be stored.
Note: Notice that we use a query as input argument, and not the table. This is to ensure that data is sorted, so the macro to work properly.
The same procedure applies to MAIO - which has a table for TRX, and need to be grouped in Cell granularity.
But in this case, for MAIO, we don’t want each one in one column, but we want only one field, grouping them all. To do this simply create a new query, grouping the data properly.
Ok, we have set our tables at the level of ID ‘cellname’. Now, we simply gather all in a new query, and then tie all of the common field (ID).
After we define our basis query, the rest is pretty simple. The code is exactly the same as you learned in the tutorial for Mapinfo Automation via VBA. If you have questions about how to do this, please read that tutorial.
Note: the code in this tutorial, including the part that calls the routine to transpose a table in Access is very simple, and written in a few dozen lines (not counting repetitions). Anyway, if you have questions that are not explicit here, contact us by posting your question to the exclusive forum for Hunter Users.
The result this time, is better seen in the program for which this ‘Updater’ module was created to.
Note that the same methodology used here to generate the input of our tool ‘Hunter Mapbasic Tool’ can be used to generate input files and/or updates for any tool you need. In the future, we will publish more ‘Hunter Updater’ modules, with different examples, as well as generating various formats.
Good guys, for today, that’s it. We hope you enjoyed this tutorial. Several tutorials are ready, and we will publish soon, keep watching, and grow with us. Your visit is always welcome.
Today we saw another Hunter Module, now intended to Automate the Generation of Input Files for Update Other Tools and Databases.
Using sample data, we show how simple and quick is to get data in the format we need. And better yet, create a set procedure for this update - which will be done much faster from the adoption of these practices.
This ‘Hunter Updater’ Module Tutorial applies to Mapinfo, but several other formats will be demonstrated in due course.
Thank you for visiting and we hope that the information presented continue to serve as a starting point for your solutions and macros.
As always, we thank the telecomHall Donors. The tutorial files have already been sent, please check. If you have had a problem upon receipt, please inform.
New tutorials are ready, and will be published soon. We recommend that you take all the doubts before continuing.
Remember, your acquired knowledge can be your biggest difference, just depends on your will.
Download Source Code: Blog_031_Hunter_Updater_Mapinfo_Network_Cells(Mapbasic_Tool).zip (226.6 KB)