WELCOME TO NEPAL POWER BI USER GROUP

It’s happy and existing to know that Nepal Power BI User Group has been setup finally 🙂
Welcome all, Feel free to join this User Group.

You can sign up and join Nepal Power BI User group by using below link:

http://www.pbiusergroup.com/kathmandu

We are a group of Power BI users and enthusiasts in Kathmandu, Nepal area, looking to connect with others to have interesting discussions and exchange ideas.  We meet quarterly along with Himalayan SQL Server User Group to go over the latest updates to Power BI & SQL, help new users get started, and explore specific topics in detail.  All are welcome, from beginners to experts.

Power BI User Groups (PUG): PUG offers online and in-person communities where you can share best practices, take part in exclusive training opportunities, and connect with other passionate Power BI users from various professions and industries. Get involved in your local user group today, and gain a better understanding of data that will enable you to excel in your role.

Connect with Power BI Users in the PUG Exchange where you can instantly share what you’re working on in Power BI and in your local user groups.

To know more about PUG Power BI User Group you can check out below link:

www.pbiusergroup.com/home.

To know more about Himalayan SQL Server User Group you can follow below link:

http://www.sqlpassnepal.org/

Feel free to join Nepal Power BI User group.

Thanks,

Anil Maharjan

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.

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.

https://www.eventbrite.com/e/sql-server-2016-discovery-dayrelease-event-kathmandu-nepal-tickets-25888946536?aff=efbnreg#

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.

blog1

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

blog2

Then use Text to Columns tab in DATA tab section as below to make the data into proper column format.

blog3

Once you prepare your data as below format then now you can start analysis using Power BI Desktop.

blog4

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.

blog5

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.

blog6

blog61

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.

blog7Just 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.

Using Power Query to tell your story form your Facebook Data.

Using Power Query to tell your story form your Facebook Data.

Most of the time of this weekend, I spent my time to extract the story behind my Facebook data by using Power Query. Power Query can connect data across a wide variety of sources, where facebook is one of the data source. By using Power Query you can extract your facebook data easily and do analysis your own story by using your own facebook data.

Talking about Power Query: Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration.

Microsoft Power Query for Excel, is a new add-in that provides a seamless experience for data discovery, data transformation and enrichment for Information Workers, BI professionals and other Excel users. With Power Query you can:

  • Identify the data you care about from the sources you work with (e.g. relational databases, Excel, text and XML files, OData feeds, web pages, Hadoop HDFS, Facebook Data etc.).
  • Discover relevant data from inside(*) and outside your organization using the search capabilities within Excel.
  • Combine data from multiple, disparate data sources and shape it in order to prepare the data for further analysis in tools like Excel and Power Pivot, or visualization in tools like Power View and Power Map.

You can easily download from the link below:

http://www.microsoft.com/en-us/download/details.aspx?id=39379

You can find more about Power View, Power Map, Power BI and Q&A from the official Microsoft Power BI site. http://www.microsoft.com/en-us/powerBI/default.aspx

Now, I think you have installed Power Query add-in in Excel 2013 professional edition,

So, let’s drill into your Facebook data to extract your story .!!!

Firstly you need to open Excel sheet and go to the Power Query ribbon bar in Excel, then go to the ‘From Other Source’ section where you will see the ‘From Facebook option’ from dropdown list.

 

Due to the so many slides or pages, the detail step by Step guide have uploaded into my Slideshare account. You can download or go through the section in order to know how to use Power Query and how to extract your Facebook Data and how to do analysis using Power View to tell the story. Here is the Slideshare:

Slideshare linkhttp://www.slideshare.net/anil_maharjan/using-power-query-to-tell-your-story-form-your-facebook-data

Where I have use Power View to analyse the facebook data and give us some story to view.

Here I have used three Power Query Analysis, I think you will find these easy after you once go through the slides in above link where I have listed detail step by step method to do these analysis or if you have some knowledge of Power View then it must be quite easy to do analysis.

Power Query Analysis 1 using Power View:

  • Let’s see when you have added your friends.
  • Through this analysis you can find out the total friends added in facebook, in which year, month.
  • First and recent friends connected within facebook.
  • You can know when you are connected with which friend.

Happy to see the results. 🙂

Now you want to know first and recent friends connected within Facebook along with which time they have added . You can get it by using  period field in Tile By and story field in rows with friend added filter field in column . This shows the first friends that you have added in facebook. Happy to see the result. 🙂

If you need to find out the recent added friends in Facebook then just click the last period.

 

Here as per chart I can clearly see that I have added a lot of friends in 2009 and 2010. Since that is the year when I had completed my computer engineering course and most of my time I was spending in facebook. Quite amazing it does remind my past college life. Power Query along with Power View actually can tell us story of our own. Happy to find out my past.

This is such a cool tool, Power Query and Power View along with Power BI, You can visualize the things you just want to see.

Power Query Analysis 2 using Power View:

  • Ø Now, let’s see how many of your friends are male and female. 

I think after going through the slides you will surely know how to get the data from Facebook using Power Query for friend list and how to use Power View to analysis. You can go through detail steps again from slides to do this analysis.

Power Query Analysis 3 using Power View:

  • Let’s see your facebook activities from past to present.

This will help you to track down how much time have you spent in facebook and how frequent you post or update your feeds.

This will helps facebook to track down the facebook user usage trend.

  • How and when user is spending the time in facebook.
  • Why the facebook user usage trend is decreasing or increasing.
  • What is the reason behind the feeds data of user is decreasing or increasing.

For the facebook activities analysis, you can use the same feed information worksheet and by creating new Power View and line chart option you can start analysis of facebook activities from different perspective.

From graph we can clearly see that there is a huge spike in 200907 to somewhat up to 201003 this is still because this is the time when I had completed my computer engineering course and most of my time I was spending in facebook.

Also, going on next year in 201109 there is another spike which is due to that month is my birthday on 04thSeptember, where there is more feeds regarding my birthday wishes.

Now, let’s see the current year facebook activates by using similar Power View and same drag and drop features and by scroll down to last month date 201405 you can see the trend of your current facebook activities.

Here from line trend analysis it seems that the facebook activates from past year to current is frequently decreasing in my case. This is due to that I am spending less time in facebook as compares to previous year where I use to spend most of my time.

Reference:

http://www.microsoft.com/en-us/powerBI/default.aspx 

http://www.slideshare.net/murrayfife/using-microsoft-power-query-to-analyze-facebook-data

http://office.microsoft.com/en-001/excel-help/introduction-to-microsoft-power-query-for-excel-HA104003940.aspx

SUMMARY:

Power Query along with Power View actually can tell us story of your own. This is how Power Query along with Power View can tell your story by using your own facebook data. Happy to find out my past.

Quite amazing it does remind my past college life.

This is such a cool tool, Power Query and Power View along with Power BI, You can visualize the things you just want to see.

Also, I am thinking that if facebook provides our friends location data then it will be so easy to find out the friends and family around the world. Where Power Map will be more useful to track down the friends whom we have no idea where she or he is but we do connected with facebook in virtual world. If we can think of creating facebook friends and family location track app which will helps us to find out where exact is our friends and family are located based on the current location information of facebook data.

Thanks,

Anil Maharjan