BI Reporting Solutions For Senior Executives

BI Reporting Solution Development

The concern of environmental pollution is growing at major rate. Not only is the developed country the issues also prevalent in developing countries as well (Reijonen et al., 2015). However the developed country are at more danger end as the pollution is at higher level due to the rapid development growing at constant rate. This constant rate of development is not to be confused with the developed country. The term developed country does not mean that there are no need of development, even the developed country is doing the urbanization, industrialization (Bocken & Short, 2016). However the scope and focus are different than that is being done in the developing country.

Now with the aim for increased industrialization there comes the risk of environmental pollution. One of the major problem of the developing countries are the water pollution (Laukkanen, 2015). Now water being the topmost important natural resources, it is still not maintained well in various countries around the world. The problem is more in the developing country than the developed country due to the poor resource management and waste water emission. Air pollution can also be regarded as the prime factor for the water pollutions as it contaminates the air which pollutes the rain, contributing to the water pollution.

Although Hungary is regarded as one of the major developing economy in the world, it is ranked as one of the largest pollutant country in the world and about 90 percent of the water is polluted which itself confirms the level of pollution danger in the country (Porterfield , 2015).

Problem Statement

Water pollution is becoming a major issue around the world. Yet it is the most neglected factor when it comes to the concept of water quality maintenance (Warren, 2017). However before identifying the strategies for the water quality maintenance, it is important to identify the root cause of the pollution (Chan 2014). Without proper knowledge of the same, it is not possible to devise the solution that addresses the issue of the pollution (Rule, 2015).

In this paper the factors will be identified that relates to the water pollution. The study of the pollution has been conducted in the four major cities in Hungary with special focus being on Budapest as it is the most polluted city in the china.

The data collected for the study will then be analysed for more insight about the cause of the pollution. For analysis of the data a predictive model will also be developed that will be further validated with proper validation model for accuracy and authenticity of the data.

Objective &Scope of Report

Objective:

The essential goals of the investigation are:

• Collection of water sample for four cities in Hungary
• Analysis of the water for finding the major water pollution element
• Analysis of the air quality for four major city of the Hungary as air quality happens to have a significant impact on water quality

 Scope:

• Study of air quality in major vehicle crowded areas in Beijing
• Study of industrial wastage production in the four city selected for the project
• Study of sewage waste for water pollution

Data Source:

The data for the Project was obtained from the website of Prevention and Control of environmental Pollution Board of Hungary. Currently, the pollution board does not have any rigorous plan for water quality assessment. The water quality measurement is currently performed on the basis of industrial wastage. The air quality measurement has not been yet integrated with the water quality measurement. Hence along with the data for industrial wastage, sewage water wastage, the air quality information is also collected for the water quality information. Water sample from the four major cities of the Hungary has been collected.

Mobile Application Designing with QR Code

 For the measurement of the air quality the environmental and pollution control board assembles data dimension (variables). Day wise, hour wise (for some variables). Data is available across the dimensions:

• Nitric Oxide (NO)
• Carbon Monoxide(CO)
• Suspended Particulate Matter/RPM/PM10
• Nitrogen Dioxide (No2)
• Ozone
• Sulphur Dioxide (SO2)
• PM 2.5 (DUST 5)
• Oxides of Nitrogen (Nox)
• PM10 DUST
• PM10 RSPM

Not all the stations the pollution control board has across the country measures on the above mentioned parameters. When data have been collected from various stations it is seen that the among the parameters that are mostly considered by the pollution control board are Sulphur dioxide (SO2), oxides of nitrogen (NOx), suspended particulate matter (SPM) and respirable particulate matter (PM10) & (PM 2.5).

Analysis of water sample data:

Insight from the data:

Following result obtained from the water sample analysis:

The water contains the following pollutant materials:

• Gases like H2S, NH3, and Co2
• Minerals such as Ca, Mg, Ar
• Organic wastes
• Industrial wastes
• Agricultural wastes
• Pollutants from the Sewage

Sewage, industrial wastage and air pollution are some of the major contributor for the water pollution

 Air pollution data analysis:

Vehicle density

The vehicle density per hour basis has been collected to measure the level of air pollution. Additionally the vehicle volume which denotes the average car on the road per day basis. The data collection was spread throughout the month of January to have accuracy in the data collection.  However one interesting thing here to note that the vehicle density does not comply with the volume of vehicle in the cities. For the vehicle volume as well as for the vehicle density some major roads in all the four cities have been identified. The data was collected with the help of the road officials from the central database. 

City	Vehicle density (vehicles/hour)
Budapest	21
Miskolc	32
Gy?r	30
Pécs	22

Vehicle volume:

City	Vehicle volume (vehicles/per day )
Budapest	1240
Miskolc	1120
Gy?r	1190
Pécs	1050

For each city the air pollution data is shown below:

City 1: Budapest

Pollutants	Quantity (PPM)
XYLENE	0.2
TOLUNE	0.63
BENZENE	0.05
OZONE	46.5
CARBOON MONOXIDE	1.66
SULPHER DIOXIDE	25.86
PM2.5	24.4
PM10	95
OXIDES OF NITROGEN	49.84
NITROGEN DIOXIDE	15.36
NITRIC OXIDE

City 2: Miskolc

Pollutants	Quantity (PMM)
AMMONIA	2.1
OZONE	7.6
CARBOON MONOXIDE	0.98
SULPHER DIOXIDE	0.3
PM2.5	13
PM10	27
OXIDES OF NITROGEN	5.8
NITROGEN DIOXIDE	8.1
NITRIC OXIDE	1.9

City 3: Gy?r

Pollutants	Quantity
PM10	20.83
OZONE	21.1
SULPHER DIOXIDE	9.84
OXIDES OF NITROGEN	91
NITROGEN DIOXIDE	71.55
NITRIC OXIDE	18.22

City 4: Pécs

Pollutants	Quantity
AMONIA	16.27
OZONE	22.9
CARBOON MONOXIDE	0.63
SULPHER DIOXIDE	1.04
PM2.5	45.28
PM10	164.44
OXIDES OF NITROGEN	23.16
NITROGEN DIOXIDE	15.23

Insight from the data:

The data included above gives a very interesting fact to consider. While analysing the vehicle volume vs vehicle density for the pollutants in the air it is seen that , although Budapest is not having the highest vehicle density the number of vehicles in the major roads of the Budapest present throughout the day is highest than the other 3 cities. When mapping this information against the quantity of different air pollutants in the air, it is seen that the PM 2.5 is highest in the air of Budapest. Those it can be conferred that the vehicle density does not have significant impact on this particular air pollutant. However the vehicle volume does have a significant impact on the quantity of the PM 2.5

Study of air pollutant in 4 region in Hungary and the air quality measurement:

 Region 1: Várkerület

Pollutants	Quantity (PMM)
carbon dioxide	357.82
oxides of nitrogen	50.2
nitrogen dioxide	38.84
nitric oxide	11.18
carbon monoxide	0.4
PM 2.5	88.5
PM 10	166.19

Region 2: Rózsadomb

Pollutants	Quantity (PMM)
Methane	273.5
oxides of nitrogen	23.26
nitrogen dioxide	24.42
nitric oxide	13.01
carbon monoxide	0.67
PM 2.5	92.3
Ammonia	45.47
Sulphur dioxide	0

Region 3: Óbuda-Békásmegyer

Pollutants	Quantity (PMM)
carbon dioxide	357.82
oxides of nitrogen	50.02
nitrogen dioxide	38.84
nitric oxide	11.18
carbon monoxide	0.4
PM 2.5	88.5
PM 10	166.19

Region 4: Újpest

Pollutants	Quantity (PMM)
carbon dioxide	52
oxides of nitrogen	43.33
nitrogen dioxide	20.88
nitric oxide	22.45
carbon monoxide	2.5
PM 2.5	119.52
PM 10	154.43

Insight from the data:

Here air sample of four most vehicle crowded area of the Budapest has been considered. It is seen there although the pollutant content is different in different area of Budapest, the major contributor for the air pollution is the Co2. The reason for that can be credited to the poor vehicle emission management and excessive use of petrol and diesel for the cars. Hence reduction of Co2 can be cited as one of the major step for having control over the air pollution which means better control over water pollution as well.

Justification of BI Reporting Solutions

Industrial pollution data analysis:

Industrial density:

City	Density of industry ((number of house/KM))
Budapest	2
Miskolc	3
Gy?r	4
Pécs	3

Industrial volume:

City	Volume of industry (total number of industry)
Budapest	2342
Miskolc	2210
Gy?r	1220
Pécs	1143

Wastage generation:

City	Month 1 (mg/litre)	Month 2 (mg/litre)	Month 3 (mg/litre)	Month 4(mg/litre)
Budapest	42	40	43	47
Miskolc	31	28	25	21
Gy?r	23	22	17	27
Pécs	15	10	11	8

Insight from the data:

Form the data it is seen that there are not significant difference on the industrial density vs industrial volume for the generation of the industrial waste. However the generation of industrial waste is more where the volume of industry is more. Hence proper plants or projects should be taken up in those areas for recycling the wastage water so that the pressure on water resources are reduced for reducing the water pollution.

Sewage water data analysis:  House density

City	Density of house (number of house/KM)
Budapest	10
Miskolc	9
Gy?r	8
Pécs	8

Four month waste water production

City	Month 1 (mg/litre)	Month 2 (mg/litre)	Month 3 (mg/litre)	Month 4(mg/litre)
Budapest	27	23	20	27
Miskolc	22	21	17	22
Gy?r	17	15	15	17
Pécs	12	10	11	11

Insight from the data:

From the above data it is seen that generation of waste water is more in the areas where house higher than areas where house density is higher. Hence effective measures should be taken in those areas where there are more number of hose per Kilometre for significant reduction in water pollution

Predictive Model Development

The predictive model was developed to do data analysis for anticipating key pollution such as PM 2.5, PM 10, SO2, Co2 and many related factor as well. The estimation was aimed to be based on level of these pollutants for the following days

The Model Development of the model was done in various level to produce an accurate and a reliable model. The first level of the development was done Multi Linear Regression (MLR) with two arrangement. The first arrangement considered genuine accessible factors while the second arrangement considered an extra factor specific pollutant level of the preceding day. The second factor was mapped as dependent variable.   

Model Validation:

The cross validation method was considered as the tool for validating the model developed for the data analysis. Root Mean Square Error (RMSE) Value technique has also been considered for the relative execution of the predictive model. In order to ensure that the model is accurate enough, a series of statistical validation have also been performed on the sample data. 

Model Development Conclusions

• The scope of data variation level in the linear regression model is close to 76% where in the case of the neural network model it is close to 82%. hence the neural network model appears to be a better fit for the data analysis
• Neural Network has the ability to reduce the RMSE esteems for PM 2.5 as well as for the PM 10 crosswise.
• Model Fit estimated to be complex for both PM 2.5 and PM 2.10. this was same for linear regression as well for the neural network development
• However the neural network was better in analysis of the data where large variation was observed in the previous day value with the recent measurement however it was challenging to provide correct data in the input layer of the developed predictive model. Hence neural network was considered for this along with the linear regression model.
• Overall performance of the Neural Network was better in forecasting the values of toxic element in both air as well as in the water.

For future work, PCA Analysis, Factor Analysis and Discriminant Analysis is needed to be considered and there is scope of development in this area which must be taken into account for improving the analysis and predict the amount of toxins in air as well as in the water for better and accurate result such that these data can be successfully mapped for the effect of the air and water pollution at different location.

Recommendations

With reference to the database analytics performed to study the data of water pollution some recommendations have been made which are:

• More effort for water quality measurement and water pollution control
• Regulation of air quality measurement and integrating it with the water pollution control
• Smart industrial wastage management system for better control of the industrial wastage. Recycling zone should be set up across the manufacturing areas. The waste water should be sent to water recycling zones so that it can be again used for the other purpose
• Smart control for the vehicle emission.
• Large number of plants should be set up in the vehicle crowded areas so that air pollution in those areas are reduced. It is well known that tress help to absorb toxic elements from the air and help to keep the air cleaner, thus contributing to the reduction of air quality.
• Use of filter in the vehicle so that the toxic gases are filtered before it is mixed with the air. The use of the filter will help to eliminate toxic element from the emission of the vehicle and hence it will be helpful for the control of the air pollution.
• Implementing regulation to encourage natural gases instead of petrol and diesel for reducing air pollution
• Implementation of smart household wastage control for the water pollution
• In order to reduce the water pollution from sewage recycling of water should be considered. If the waste water are recycled not only it will ensure better management of the water but reduce the pollution to large extent

Conclusion

Water pollution is having adverse effect on the environment and the quality of the living for the every living being, not just the human. However the creatures that lives in the water are at more danger due to water pollution. Hence successful measurement of the water quality is necessary for maintaining the quality and help to make the environment pollution free. However the first step to ensure that is to ensure that the data that is collected in this regard is not only needs to be accurate, but it should be analysed with proper data analysis tools.

It is of prime importance that the data that is collected regarding the water pollution will only be helpful if insight about the data is correct. Only correct and insightful information from the collected data can provide the right guidance about what steps need to be taken and where improvement is needed. Hence it can be concluded only correct information collection along with the smart and accurate data analysis with proper model will ensure that the water pollution , and in large the environment pollution is kept in control.  As the major contributors to the water pollutions as identified in this paper are air pollution, industrial wastage and the sewage water, special attention is needed in this section.

Reference:

Bocken, N.M.P. & Short, S.W. (2015). Transport and Urban Air Pollution in Hungary. Environmental Management, 36(2), 195-204.

Chan, C. (2017). Role of meteorology in seasonality of air pollution in megacity Budapest, Hungary. Environmental Monitoring and Assessment, 184(5), 3199-3211.

Chen, Xiaoyun. (2015). sample analysis of the environmental pollution characteristics. Atmospheric Environment, 107, 194-203

Dan, A. (2015). Analytical techniques for trace organic compounds – IV: Tandem mass spectrometry for organic trace analysis (Technical Report). Pure and Applied Chemistry, 65(5), 1021-1027.

Datta, Tyagi, & Chandrasekharan. (2017). Factors controlling stable isotope composition of rainfall in Budapest, Hungary. Journal of Hydrology, 128(1), 223-236.

Gupta, R., Somanathan, E., & Dey, S. (2017). Global warming and local air pollution have reduced wheat yields in Hungary. Climatic Change, 140(3), 593-604.

Kumar, Eswari, Sampath, & Lavanya. (2015). Predictive Methodology for Big Data. Procedia Computer Science, 50, 203-208.

Laukkanen, T. (2015). Applying GRI reports for the investigation of environmental management practices and company performance in Hungary. Journal of Cleaner Production, 98, 36-46.

Porterfield, T.E. (2015). A step by step method for conceptual data analysis. Software Engineering: Education and Practice, 2015. Proceedings. International Conference, 42-49.

Qiang, Wu. (2015). algorithm for environmental pollution data analysis. Atmospheric Environment, 107, 194-203

Raisch, W (2017). Study of air pollution impact for water quality: a data driven analysis.

Reijonen, H., Hirvonen, S., Nagy, G., Laukkanen, T. and Gabrielsson, M. (2015). A clustering algorithm for sample data based on environmental pollution characteristics. Atmospheric Environment, 107, 194-203.

Ritzema, Froebrich, Raju, Sreenivas, &Kselik. (2016).Using participatory modelling to compensate for data scarcity in environmental planning: A case study from Hungary. Environmental Modelling and Software, 25(11), 1450-1458.

Rizwan, S., Nongkynrih, B., & Gupta, S. (2017). “Air pollution in Delhi: Its Magnitude and Effects on Health”. Hungaryn Journal of Community Medicine, 38(1), 4-8.

Warren. N. (2017). “Water pollution in Hungary:  A predictive analysis, 37(1), 4-5.