Hello folks, today we see another great tip of practical use of MS Access in a common activity: Analysis in Telecom and IT.
And so we'll know a new type of SQL Query - the Crosstab Query. Although the name may seem complicated, this query can help us a lot. So let's see how to do?
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.
To download files from this tutorial, click here.
Note: The Hunter SDK is a set of tools designed and demonstrated in telecomHall - including Macros and Codes. The Hunter SDK is only sent to telecomHall Donors, as a thank you for the contribution they made and recognition of our dedicated effort. Click here if you want to know more.
First, we define our scenario, or problem: From a table with Performance Data, display data in two dimensions!
Oops, was even more complicated? Calm, in the examples you will find it interesting.
Then suppose the example table with Network Performance grouped by BSC, as shown in the tutorial 'Hunter Performance', available for download above. Let's check the 'Greatness of Traffic' in each of our four BSCs (Which BSC has more traffic, which the behavior of each one, etc. ...).
In 'normal' tabular display of data from table 'PERF_G_BSCRNC' fields are 'Date' (varDateTime), 'BSC' (BSCRNC) and 'Traffic' (TRAF) of each BSC, in each period.
We can reorder the table, for example by 'Date' (varDateTime) and 'BSC' (BSCRNC). Still, our data are grouped in only one direction - now you can see separate data for each BSC, per period.
Okay, it is best to view. But what if we could put a shaft in the Horizontal and the other in Vertical, would not be better?
For it is quite simple to do with Access. Note: we are demonstrating here with little data, and the advantages are not as noticeable. But the higher the amount of data, that kind of query helps a lot in the analysis. Let's see it better soon.
Let's do it! Access the Menu: 'Create' (1) -> 'Query Wizard' (2) and choose 'Crosstab Query Wizard' (3). Click the 'OK' button (4).
On the first screen of the Wizard, you can choose the Table (or Query) from where our data we'll come from. In our case, we still have only one table, and therefore it is already selected (1). Click 'Next' (2) to continue.
On the next screen, define which field should appear in the 'Y axis', or 'lines' of our Query. Let's choose the 'BSCRNC' (1). Note that we can choose more than one field, but let's leave it at that for now. Click 'Next' (2) to continue.
On the next screen we'll define the field that should appear in 'X Axis', or 'Columns' of our Query. Choose the 'varDateTime' (1). Click 'Next' (2) to continue.
On the next screen, we have the option to choose some sort of grouping headers. We choose as 'Date/Time' (1), because we do not want clustering now. Click 'Next' (2).
Now, concluding, it gives the values to be presented. In 'Fields' choose 'TRAF' (1), and in 'Functions' choose 'Sum' (2) to add the amount of traffic grouped by Row/Column. Uncheck the option with Totals for Lines (3) - for now we do not want that. To finish, click 'Next' (4).
On the last screen you have the option to save this query with any name you want (1). Let as it is. Leave selected the option to view the Query (2), and click 'Finish' (3).
And then our result.
Okay, maybe you're still not getting to see the benefits that simply viewing a Cross 'Rows x Columns' can bring us.
But then, let's do this. Let's 'move' the data from this table (CTRL + C, CTRL + V) into Excel, and apply some Conditional Formatting.
In a very intuitive way - don't even need to see the 'values' - we can quickly see that the BSC 'BSCC' (1) always has the lowest traffic carried. And that BSC 'BSCB' (2) is the one that has the highest traffic routed, followed closely by 'BCSD' (3).
By simply applying a new Conditional Formatting we can immediately take other conclusions: the behavior of traffic 'BSCC' is different on Saturday (1).
Sure, we learn to create Crosstab Queries. But do you want to see another example, even more practical and interesting?
Consider our sample data for 10 sites (GAAA1 to GAAK1). Again we'll use sample data tables from module 'Hunter Performance' accumulated by CELL. Later we will show various forms of analysis of these data, but now let's see a simple way, using only the direct assistance of the type of query that we've learned today.
Consider also that we have data (example) for these sites from day 13 to 30 (October) - now you can run some quick analysis.
To demonstrate, let's look at the rate of dropped calls for these cell sites.
Ok, we have a table with the data, don't we? Of course you can do some calculations, so set the rate in Ascending and Descending order. But the question remains: What's is the 'conclusions'?
The fact is that data in tabular form, without adequate treatment, are not of much use.
But look what happens if we simply follow the above steps and create a new crosstab query using:
- Base Table: 'G_PERF_CELL'
- Header Line: Field 'cellname '
- Column Heading: Field 'varDateTime'
- Intersection Value: Field 'DROP_P' (% dropped calls, which we are analyzing).
Running the query, the data is much simpler to be analyzed. For example, we see that the site GAAE1 (1) began to show data only from day 20 - or the site was out of service, or was activated on that date!
Continuing a bit longer to complete.
See what happens if you just pasted the data into a spreadsheet and apply an Excel Conditional Formatting.
Several conclusions can already be made on our network, based on these single-formatted data - and realize that not need to worry about numerical values for that!
- The site GAAC1 (1) is the worst or most offender in terms of rate of dropped calls (greater number of Red records)
- The site GAAE1 (2) only presents data from day 20 - or was out of service, or has been activated recently, as we have already noticed even without the formatting.
- GAAH1 (3) is getting worse, increasing its rate of decay (getting more Red records at the end of line)
- The site GAAI1 (4) is the best site in our network (virtually all records are Green). It can be used for example as reference parameters to other sites with trouble.
Well, that's it. Of course, the analysis here was very quick, but it served to demonstrate how you can, with some small 'tricks' to facilitate and improve the quality of your overall analysis.
This sample was shown to Call Drops, but can be applied to any other indicator. In addition, there are several other ways to further improve the arduous task of searching for the offenders in our Network, the first step to trigger actions to solve it!
This is the Hunter Philosophy, whose development you should be following and practicing, and should be able to smoothly apply the most modern techniques in your network, always in an integrated, organized and simple way.
This another tip on how to use the Access Software, from Microsoft, applied to IT and Telecom.
We learned today how to run a simple and quick analysis, using Crosstab Queries. Depending on your needs, this type of query can be quite interesting.
Also, these queries are auxiliary and can for example be created/accessed by VBA code, greatly expanding universe of possibilities and actions.
Although shown as an example to the area of Telecom, the scope is not limited to this area only. Once you understand the concepts, will be much simpler to apply it in any other area.
That's our goal, always looking for to present the best content, always being practical and going straight to the point - only to be seen what matters.
Thanks for the visit, and until our next meeting!