For nice presentation of Data - Tableau #2

Idea : To use Tableau for presentation of data from Risoe of air-polution.
Background: Exercise one

Problem definition:

From Risoe (Risø) we have Keld comming and speak of how to collect data in Denmark of diffrent types of AIR materiale like particles, NOx etc.

For more background see documentation in the supplied papers https://www.dropbox.com/sh/u1n0nuz7e7dquiu/AAB5bzhmYeDE0Bu1ElUMV3PJa/Docs?dl=0

• Filedescription.rtf -- description of information in the provided datafiles
• Monitoring+air+quality+2018.pptx -- The Power point from the presentation Day
   
• AqReport2016.pdf -- the annual report of 2016 of the Air quality
• Denmark+air+pollution+fact+sheet+2014.pdf -- Air pollution fact sheet 2014 – Denmark
• AirQualityDirective.pdf -- the EU formal description of measuring air quality
• EU_Air_Quality_ThresholdValues.PNG -- EU threshold for different parameters in Air quality

The Data provided see / download from https://www.dropbox.com/sh/u1n0nuz7e7dquiu/AADrbpGJBvOcP421zzFb6BEXa/Data?dl=0
REMEMBER do not publish to every one these figures are only for our internal use :-)

• DMPS.xml        --  Particle distribution HCAB and HCØ            !!! BIG 1.1 GB
• LVS.xml           -- Particle concentration in μg/m3
  
• NOX-NO.xml    -- Gas concentration of NO and NOx. ½ hour means values.
• SMPS.xml       -- Particle distribution HCAB (only data from 2017)    !!! BIG 1.3 GB
• SonicMet.xml  --  Meteorology from station HCAB
• Stations.xml   -- StationId, names and coordinates.

You can download all of them by clicking at the 'Download' box (it will be compressed before sending)

You are to load these figures into tables and then into Tableau to make some presentations.

Step 1: Load XML into database tables

After you have downloaded (or accesed the datafiles) you should load these into tables in a database.

Here are two very different ways depending of your choice of database manager:

MySQL:

Example - LVS.XML

--------------------------

-- Load XML into a temporary table

CREATE TABLE LVSDataTmp(
    MaalestedId int,
    Maalested nvarchar(45),
    DatoMaerke DateTime,
    StofId int,
    StofNavn nvarchar(35),
    EnhedId int,
    Enhed nvarchar(10),
    Resultat float,
    UdstyrId int,
    Navn nvarchar(35)
);

LOAD XML LOCAL INFILE 'M:/uv/2018f-dbweb/prog/Risoe/Data/LVS.xml'
         INTO TABLE LVSDataTmp
         ROWS IDENTIFIED BY '<Data>';

SELECT * FROM LVSDataTmp;

-- load temporary table into a 'real' table

CREATE TABLE LVSDataTmp(
    id int auto_increment primary key,
    MaalestedId int,
    Maalested nvarchar(45),
    DatoMaerke DateTime,
    StofId int,
    StofNavn nvarchar(35),
    EnhedId int,
    Enhed nvarchar(10),
    Resultat float,
    UdstyrId int,
    Navn nvarchar(35)
);

INSERT INTO LVSData(MaalestedId, Maalested, DatoMaerke, StofId, StofNavn, EnhedId, Enhed, Resultat, UdstyrId, Navn)
SELECT DISTINCT MaalestedId, Maalested, DatoMaerke, StofId, StofNavn, EnhedId, Enhed, Resultat, UdstyrId, Navn FROM LVSDataTmp;

SELECT * FROM LVSData;

drop table LVSDataTmp;

-------------------------

MSSQL:

-------------------------

-- create table with one column of type of XML.
CREATE TABLE LVSData(
Id int IDENTITY(1,1) PRIMARY KEY,
LVS xml,
);
GO

-- insert XML data to data base

INSERT INTO LVSData(LVS)
SELECT * FROM OPENROWSET(BULK 'C:\Users\EASJ\Documents\2017-2018\DB\Data\Data\LVS.xml', SINGLE_BLOB) AS x;

-- Select all data
-- SELECT * FROM LVSData; //display data

-- select single field.
-- SELECT LVS.query('/DocumentElement/Data/Resultat') FROM LVSData; // display data direct from xml file
GO

-- Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll),
-- and provides the parsed document in a state ready for consumption.
-- This parsed document is a tree representation of the various nodes in
-- the XML document: elements, attributes, text, comments, and so on.

DECLARE @x xml
SELECT @x=LVS FROM LVSData
DECLARE @hdoc int -- keep the reference to handler
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x -- system extended stored procedure

SELECT * INTO Particles FROM OPENXML (@hdoc, '/DocumentElement/Data', 2)
WITH(
DatoMaerke dateTime,
StofNavn varchar(10),
Resultat float,
Enhed varchar(10)
) -- only some of the data fields

EXEC sp_xml_removedocument @hdoc

select * from Particles;

CREATE TABLE ParticlesWithId(
Id int IDENTITY(1,1) PRIMARY KEY,
DatoMaerke dateTime,
StofNavn varchar(10),
Resultat float,
Enhed varchar(10)
);
GO

INSERT INTO ParticlesWithId(DatoMaerke,StofNavn,Resultat,Enhed)
SELECT DISTINCT DatoMaerke,StofNavn,Resultat,Enhed FROM Particles;

select * from ParticlesWithId;

DROP TABLE Particles;
-- could also drop the LVSData table

GO

-------------------------

So after loading into tales you are ready to take the next step.

Step 2: Start Tableu and load tables

You now have the tables ready for use. Now start the Tableau application.

You should connect to your server (MySQL or MSSQL or ....).

Start a new Data-source and drag and drop your tables to the data-source-area.

Switch to the Sheet and start present the data from the tables.

You could start look for Particles(LVS) combinded Meteorology(SonicMet) to se if there is any correlations.

In general try some of these combinations

• Nox - Particles
• Nox - Meteorology
• diff. particles - diff. locations
• nox - time of day, day of month, month of year
• or what you could think of. -- with 6 files you have 6! combinations i.e. 720 different combinations
  

Step 3: Present your investigation and visualisation

Pick 1-3 of your visualisations and present them for the class.

You should think of the class students i.e. think of

1. What will you present,
2. How will you present it
3. What is the outcomes of the presentation

Step Additional: Publish your 'workbook'

You can publish your result so you could show it in a web-page by following the guidelines from this link:

https://onlinehelp.tableau.com/current/pro/desktop/en-us/publish_workbooks_howto.html