Microsoft Access for Telecom - Part 4 (Using Different Data Sources)

Thursday, October 27, 2011 2:04:00 PM Categories: Microsoft Access
Rate this Content 1 Votes

Hello, we will continue our series on Access Tips. But our approach is slightly different today: we will demonstrate step by step how to integrate different formats of input data with the Hunter System.

 

 

In other words, we will see how to use data from our network in the Hunter Sample Modules.

Note: Obviously, we believe that everyone has read all the previous tutorials, since they have necessary considerations that have been demonstrated and explained in detail. The tutorials are written sequentially, always presenting new concepts, especially for those who do not have experience with Access. If you skip one or more tutorials - maybe because you might think that this does not apply to what you want at the moment - you will have problems for sure!

So come and see how simple it is to adjust the actual data from your network to the Hunter Sample Applications.

Note: All telecomHall articles are originally written in Portuguese. Following we translate to English and Spanish. As our time is short, maybe you find some typos (sometimes we just use the automatic translator, with only a final and 'quick' review). We apologize and we have an understanding of our effort. If you want to contribute translating / correcting of these languages, or even creating and publishing your tutorials, please contact us: contact.

 

Download

To download this free tutorial files, click here.

To learn how to get all the files and codes of Hunter System, click here.

 

Scenario

From our current data files, create a way to use it at the Hunter System, in the provided Example Modules.

For this, we choose a module: 'Hunter Network'. This is the module that contains the network physical data, stored in a standard table 'tbl_Network' in standard database 'Hunter_Network_DB.mdb' in the standard local directory 'C:\Hunter\Network\Database\'.

In our examples, we present data from a fictitious GSM and UMTS network.

These data are already available in the cited table, with the standard names of fields as well!

For example, this table contains a field called 'LATITUDE', which contains the given Latitude of each cell. It also has a field called 'LONGITUDE', with the given Longitude of each cell. It has a field called 'ANT_AZIM' with the data of Antenna Azimuth of each cell, and so on, as the fields shown below.

 

This is a very simple table with only a few fields needed for all the modules presented till today.

 

For example, in module 'Hunter GE Network', the data in this table are treated, and a KML file is generated. Each module was developed as a complete 'example', and uses these sample data to obtain the final result. For example, the direction of plotted cells is determined by our standard field 'ANT_AZIM', ie the azimuth of the antenna, and the opening of the symbol is in turn defined by the value of field 'ANT_HBW'.

We know that such modules do not yet cover all your needs. But it is very important to realize that serves to know and learn how to obtain such results.

For example, we still have no data 'Frequency' in this table. This is because we have not published a specific example module for this purpose yet!

Anyway, you can play all existing modules, simply adjust its data, that is, put your data in the standard Hunter format (mainly namings).

Lets see how to do this, using our module 'Hunter Network' as example.

To begin, let's assume that the physical data ('Network') of your network are available in an Excel spreadsheet, with our specific format - for example with different names. For this, we use the data (also ficticious) as shown below - in file 'MyCustomNetworkTable.xls'.

 

Note that the network data has the 'same' fields, with only the names of fields a bit different (for example, in our sample table Latitude is given by the 'Lat' field, while our standard table uses 'LATITUDE').

Our sample table still has some less fields (eg missing field 'ANT_HBW'), and a few more fields (eg 'FREQUENCY').

Fields in red have not yet been demonstrated and / or used in example modules, and there is nothing to do with them at this time.

Fields in blue are the most important, and will be used in our applications. For this, let's see how the fit.

 

Note on Integration (Modularity) of the System

At this point, it is worth remembering why it is important the modularity and standardization of the system. The iamge below illustrates this importance.

 

The same standardized table (and therefore the same fields and terminology) is used by several other modules. In the specific case of our example, all Hunter Modules using network physical data fetch data from this particular table.

This is done in a very simple way by Access - simply create a link for this table in the modules where it is needed. This ensures, among other advantages, that all the output data/report is always up to date, simply keeping updated a single table with data from our network! In addition, whenever there is a change in the original - and only - table, such as adding a new field to a new purpose / module, all links are already automatically updated, sinbce our linked tables already reflect the changes carried out.

 

Ways to Use and Integrate Hunter

So to use the modularized system - and thereby obtain all the advantages that this form gives us - we need to follow this standardization. That is, we need to have a clear definition of a single table with the physical data.

Option 1: The best way to do this, especially for those just starting a system from scratch, is using the offered standardization - in other words, simply fill the base table 'tbl_Network' with the data on your network.

Option 2: Another way, however, is what we're seeing today. Continuing our example, and assuming you have the data on your network according to the spreadsheet 'MyCustomNetworkTable.xls', we need to generate (update) our table 'tbl_Network' with its data. But nothing prevents you start using this option, and in the future decide to use the first option.

Option 3: Another way is of course possible. You can learn from all example modules, and create your own system with your own nomenclature, etc. .. However, note that this way you'll have greater difficulty, or be unable to use the modules without significant changes to all the queries, codes and scripts that expect our standardized nomenclature. Anyway, you have total freedom to use it the way want.

The great advantage of using the first option is that everyone wins with the standardization that with time and with the publication of new modules, it is becoming even greater.

The number of worldwide Hunter Users grows every day, and increasingly we seek to unify and standardize all the best practices used in all areas of Telecommunications and IT.

 

Using data from a different source

Let's see an example of how to integrate our Hunter System to our Excel Sheet. In other words, we'll 'transform' our original spreadsheet in the base table 'tbl_Network'.

For this, we start by linking our original spreadsheet in our default database 'Hunter_Network_DB.mdb'. To do this we access the Menu: 'External Data' (1) -> 'Excel' (2).

 

We point to the file name where to find our spreadsheet (in the case of this example, we store the original worksheet in the same place where our underlying database is) (1). And we chose the option of 'Link' (2).

 

After completing the Wizard, we have linked our spreadsheet. It's now like a 'table'.

 

In our database, rename the original table 'tbl_Network' to any one other name, eg 'tbl_Network (Original Expected Format used in HUNTER)'. This table is now NOT accessible to all other modules. They expect a table called 'tbl_Network', and that is what we are creating now.

For this, we'll create a query based on our Excel spreadsheet - that in turn, is linked with our real data.

Note: Remember that all terms used have already been demonstrated in other tutorials, and always keep repeating it would be redundant and unnecessary. If you have doubts, read previous tutorials.

At this point then we need to define each calculated field, as appropriate. Let's do some step by step, as an example.

We begin with the first field in our table: 'PLMN'.

This field does not exist in our Excel spreadsheet (although there should be). We have two options: assign a value ('NA' or '') or the actual value ('MyPLMNName').

We chose to use the value 'NA' (1), even because we can change this later. Thus, we insert the first field in the query. Running it (2), we have our 'table' with just the default field 'PLMN' (3).

 

Our next expected fields are 'MSC' and 'BSCRNC' - important ones, but also lacking in the original spreadsheet.

Thus, we follow the same procedure, and assign 'NA' for them.

 

Note that our 'table' pattern is already taking shape - even if the field values are not the best way possible - as they are not also present in our spreadsheet.

Note: The correct thing is to update these data in our original spreadsheet, and update those references - instead of assigning the value 'NA' for example, apportioned the corresponding field.

Let's continue. Next required field is 'sitePROP'. This is the field with the value of the property, the physical location of our site.

In our original spreadsheet, this particular field does not exist, but the same is inserted in its 'Site' field, the second character onwards. Then, simply create a calculated field 'sitePROP', extracting the values properly.

 

Now yes, our table is getting more as we hope.

Continuing, the next required field: 'BTS'. This case is simpler. This field is available in our original table, named 'SITE'. So, just create the calculated field 'BTS' and associate the field 'SITE' to it.

 

We could continue with this show until the end of the required fields (not many). But you should have understood the procedure. It follows then, our query with all existing and required fields in our standard.

 

That done, now we only need to put data from this query into a - 'new' - table 'tbl_Network'.

 

Create new table with the Real Current Data

It is very simple to insert data from a query into an Access table. Simply create a new query - now of type 'Create Table' - and insert the name 'tbl_Network' in the name of the table to be created. That is, when this query is run, our expected table is created with the names and the appropriate fields values.

 

So, when executed, our table is created! (For ease of implementation, we create a new macro 'Update_Network_with_MyData_RUN').

 

And done! All our other modules now have an updated table - with the actual data from our network - from where they can fetch its data.

Remember that the modules were written as an example, and has the standard results/outputs. These results are always being improved, as we demonstrate new modules as well as we're updating existing ones.

The modules of the Roadmap, as well as modifications of its results are discussed by all Hunter Users, and increasingly with the participation of all we will create all new modules that can suit everyone.

Many other modules are being published gradually. This week for example, we began the series of modules and algorithms for Performance / KPI Analysis. Do not miss out, contributing at least with suggestions, so you help in the evolution of the system and the benefits apply directly to you!

 

Other types of Data Sources

It may be however, that your physical data is not an Excel worksheet, but instead a Text or CVS file. Or in another Access database. Or even in any other available format such as XML.

 

No problem, the procedure for adjustment is exactly the same: to bind its real data format, and change the query that matches the fields. That's it.

 

Integrating Other Modules Base

The example shown today was to make the integration of your physical data (real data) with the Base Module 'Hunter Network' - allowing all other modules that use this information start to work automatically, without any problems.

However, there are other standard modules, such as 'Hunter Performance', with Performance information such as Call Drop rate, and 'Hunter Parameters' with information on configuration parameters, such as Frequencies.

The integration of these modules can also be done in a similar way as we have seen here.

Anyway, it's good you're back to work with the methodology as organized as possible. Regardless of your network vendors, the results can be standardized, so getting all the benefits mentioned above.

 

Standardization of other Fields - Future

As we observe it in our spreadsheet example, some fields were not used. This is because they are not so necessary (used) in current modules.

Gradually, we add new fields, such as 'BTS_Model'. Of course, nothing stops you even add in your system, and replicate to the appropriate modules.

But the best option is to participate - in the Forums. So we all can go molding system, standardizing the way that suits everyone.

 

Do not mix things up!

Another learning that we can draw from our sample spreadsheet, with our physical data, is a very common mistake when we work in a modularized / organized.

Note that this worksheet is 'mixing' physical data (in blue) with Performance Data (1) and Parameters Data (2).

 

This is wrong because it is contrary to the essence of our system.

These data should be stored in different locations, and this worksheet should actually be the output of a specific module: an application to fetch data of the corresponding bases, and create this 'report'.

 

Working with Multiple Data Sources

Just to conclude, remember that you can take advantage of the Access power, for example, when your data is in different sources.

Returning to our example (Network), it may be that our data is stored in more than one file or data source.

For example, we have a spreadsheet with 'CELLNAME' and 'ANT_MODEL'. But without the 'ANT_HBW' field, which indicates the horizontal opening of the antenna, used for example in the modules of Google Earth (GE).

As you already know, just join these tables properly, and the desired result is achieved.

So you get the final table with the fields and ÇELLNAME', 'ANT_MODEL' from a table, and the corresponding 'ANT_HBW' from another table (eg an auxiliary table with data for antennas, including gain).

 

Conclusion

This was more a tutorial on the use of Access, now more focused on allowing the integration of real databases on your network with the Hunter Modules examples, both present and future.

Remember that we are always working towards a more standardized system, and appropriate scenario of all. So it is very important the participation of everyone, especially you who are Hunter User and receives all the files and code regularly.

We invite you to become more active, especially through the User Forum - there's on Exclusive Forum to Hunter Users.

The participation of everyone is also welcome in Groups and Communities.

Thanks for the visit, and until our next meeting!