SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.
Last week we conducted the SQL Server 2016 Discovery Day –Release Event Kathmandu, Nepal successfully on July 9th 2016. SQL Server Launch 2016 Event and Discovery Day is a free, one-day event where individuals come together, learn about SQL Server 2016 and solve a pre-determined problem.
I got a chance again to speak in this great event and it was fun and share knowledge along with other SQL Geeks, I had given presentation on
SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.
You can find my slides using below link:
Also, we had a small solution development competition in the event where we use Power BI Desktop free tool in order to create some visualization and tell some story behind data.
Below is some quick visualization that I had created in the event by using PASS SQL Saturday real Data.
Steps for Making Data visualization by using Power BI Desktop.
Step 1: Firstly, download and install the Microsoft Power BI Desktop tool, which is free from the link below also for R related charts and play around with R codes we need to download R , RStudio IDE and install too. Below is link for Power BI Desktop, R, and RStudio IDE.
https://powerbi.microsoft.com/en-us/desktop/?gated=0&number=1
https://www.r-project.org
https://cran.r-project.org/src/base/R-3/
https://www.rstudio.com/products/rstudio/download/
Step 2: Get the data related to SQL PASS, SQL Saturday from the link below:
https://drive.google.com/file/d/0BzlPwGX6UtxUNnlfZ01KczF0NHc/view
This zip data files contains the different .sql files with data included within scripts.
Step 3: Load those scripts into SQL Server Database and then use Power BI Desktop ‘Get DATA’ tab to load the Data into Power BI Desktop and do visualization.
Alternative: If you haven’t install the SQL server Database then you can use a single file such as ‘dbo.SQLSatSessions.Table.sql’ and then Excel file to manipulate the data in your required format.
Step 4: Load the only data part from the file ‘dbo.SQLSatSessions.Table.sql’ into excel as below
Then use Text to Columns tab in DATA tab section as below to make the data into proper column format.
Once you prepare your data as below format then now you can start analysis using Power BI Desktop.
Step 5: Use Power BI step 3 or Step 4 to load the data into Power BI. We have use step 4 here so now use Power BI to load data from Excel source then you will get data columns in right hand side as below.
Step 6: Now start visualization using Power BI Desktop free tool, here we are using data of SQL Saturday session details and prepare different line chart, tree map, filled map , Table , R script Visual different charts that we have used to show different visualization details as below.
One can learn how to create these different charts by going through below links
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-visualizations-for-reports/
Step 7: For now just for sample how we can simply create a line chart by just drag and drop feature of Power BI Desktop.
Just go to right side of Power BI Desktop and select EventDate and SQLSATURDAY filed and drop Eventdate in Axis and SQLSATURDAY into Values section and change the SQLSATURDY value as count by just clicking on SQLSATURDY field in Values section.
Just simple so you can try these different charts by simply drag and drop feature.
For R script Visual you need to know some R language first in order to create R visualization chart in Power BI . One can learn R from https://www.r-project.org/
Step 8 :Publish these reports on Power BI cloud service by just clicking Publish button from Power BI Desktop , also now one can also publish these reports to publicly in web. Once you publish your report in Power BI service go to Reports -> Your Report -> File -> Publish to web . After that anyone can go through these reports publicly in the web.
Also, URL that you get through publish to web can be embedded to your website along.
Summary:
In Summary, from these above Power BI Desktop Visualization it helps to tells us that which SQL Saturday is conducted in which state, country in which year and a particular Day along with total sessions conducted. It also helps us to tell us that SQL Saturday trends year on year and mostly in September month most of SQL Saturday is conducted as per SQL Saturday data history.
Also, it helps to tell the distribution of state wise SQL Saturday conducted states .which shows most of SQL Saturday happens in North America.