Antenna Distribution is a project that shows how to run business analysis tools on a set of a data. Data is consistent of several information:
- positions of 5G antennas on island of Krk, Croatia (location data is scrambled)
- information about each antenna (capacity, radius, traffic etc.)
- list of every region on island, and how many antennas does it cover
- list of connected customers and their static locations (measuring home-box internet)
Data is stored in CSV files in data directory here.
Data will firstly be loaded to the relation database, called AntennaDistribution_STAGE, using the ETL generator. Extract, transform, load (ETL) is the general procedure of copying data from one or more sources into a destination system which represents the data differently from the source or in a different context than the source. After that, ETL will transform data to dimensions and fact tables to another database called AntennaDistribution_DWH. Those tables will be used in OLAP (Online analytical processing) and also as a ground for showing graphical BI reports.
DDL for STAGE and DWH tables is located in ddl directory here.
A graphical representation of tables is shown below.
This project use MS SQL database to store the data and Visual Studio for transforming the data. Also, graphical diagrams are done in Power Bi application.
- MS SQL with Management studio
- Visual Studio, 2019 or newer, with Integration services and Analysis service for multidimensional and data mining
- Power BI
Before any process is started, DDL scripts for STAGE and DWH (mentioned above) should be created. DDL-s does not contain database creation, so this step should be done manually.
AntennaDistributionETL directory contain Visual Studio solution which defines all ETL tasks. After opening the solution, connection sources should be updated. Pay attention to use the same encoding as I did:
Run Stage, Dimensions and Fact tasks in mentioned order.
Some stages are graphically shown below:
After ETL is done with filling required data to DWH database, OLAP analysis can be done. Directory AntennaDistributionOLAP contain Visual Studio solution which defines all dimension and fact tables and their relationships.
Star schema for given tables is show on the picture below:
By using these definitions, various reports could be easily done, ex:
Analysis 1: For given date-time period, show capacity and max traffic of antenna in some region:
Analysis 2: For given customer in region connected to some antenna show speed and traffic in some period of time:
File AntennaDistributionReports.pbix given in this repository can be opened in Power BI application. Power BI application can be used for presenting analysis to management by using the diagrams and other graphical elements. Some examples are shown below:
Analysis 3: Show various customer information:
Analysis 4: Show various antenna information:
See the LICENSE file. For every question, write to kurelic@sanjin.eu