How to create Microsoft Azure SQL Database and Visualize along with Power BI.

This blog is mainly about creating Microsoft Azure SQL DB and visualizing with the help of Power BI Desktop.

Firstly, we need to have a Microsoft Azure subscription in order to create the azure sql database. One can try for free for one month worth $200 azure subscription for free. You can find more on this from below link:

https://azure.microsoft.com/en-us/free/?v=18.03

Then, you need to create Azure SQL database. For this please follow below link which will help us to create Azure SQL database step by step. I have gone through each step in detail.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-design-first-database

Below are some of steps snap which you will see while creating Azure SQL database.

Meanwhile, It will help if you know more about Database Transaction Units (DTUs) and elastic Database Transaction Units (eDTUs). Once can know more on below link: which helps to figure out the Azure SQL database monthly subscription charges.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu

Also, one can know more about the elastic database pool.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu

Once you successfully create the Azure SQL DB then you will see as similar to below snap. Here I have create AzureSQLDB as database name and with AdventureWorks test data sample tables. I have set my server name as anilmaharjan.database.windows.net with 2GB in total disk storage.

After that I tried to connect my Azure SQL database ‘AzureSQLDB ‘ but got the below error. Which means we need to allow client IP address to access the windows azure server SQL DB. So for this we need to add Firewall settings for this . Once success Firewall setting, now you are good to go to connect Power BI Desktop to windows Azure SQL DB you just created.

Do visualization by using Power BI Desktop and then can upload in Power BI cloud service too. Find more on different kind of Power BI visualizations:

https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-types-for-reports-and-q-and-a

Now, you had successfully created Azure SQL database and do different visualization using Power BI . Also, it’s good to see the data explore option with Azure and we can direct query and analysis of Azure SQL database in Azure portal itself. Which is great. One can setup Azure cost management in order to optimize your cloud spend and which is also a quite good feature.

#AzureSQLDatabase ,#WindowsAzure, #PowerBI,In summary, you have successfully setup Azure SQL database and do different kinds of visualization by using Power BI Desktop.

Thanks,

Anil Maharjan

Senior BI Engineer | Nepal Power BI User Group Leader

Happy and excited to be a Program committee member for DATA and BI Summit 2018.

Happy and excited to be a Program committee member for DATA and BI Summit 2018 in Visualize Track focusing on Building and Designing Dashboards and Reports.

Thanks to the DATA and BI Summit 2018 Program committee team for giving me an opportunity to work as a Volunteer Program committee member for upcoming DATA and BI Summit 2018 in Visualize Track focusing on Building and Designing Dashboards and Reports.

I had selected Visualize track (Focus: Building and Designing Dashboards and Reports) and regarding top 10 topics that should be covered in this track at DATA & BI Summit are:

How to get story/insights from DATA using Power BI.
Power BI Desktop and it’s features.
R on Power BI.
Different types of Visualization(bar charts ,trends, scatter plot, Geo-spatial , maps ,drill up, drill down, sub-reports ) we can create just by drag and drop approach and also custom visuals.
Power BI service , dashboards, reports , data sources , APIs.
workspaces , Power apps, how can end users be benefit by using different workspaces and power apps and helps in decision making.
Roles and privileges in different workspaces , apps, reports , dashboard.
scheduling, refresh data source , publishing in web, mail subscription, subscriber different reports , dashboards to different teams and end users.
Q&A , Cortana , mobile power BI app, APIs, Gateways.
Phone Layout, Power Query, Data modeling, Get Apps Microsoft App store, Real time data analysis and analytics.

You can find more about application proposal for Data and BI Summit Program committee. Deadline is 12^th Jan 2018

https://www.pbiusergroup.com/engage/volunteeropportunities/volunteer-opportunity-details?VolunteerOpportunityKey=3791f992-2f6d-4eb7-b558-3ad62022dc74

Also, if you want to speak in this conference then you can find more about call for proposal for DATA and BI Summit below: Deadline is 12^th Jan 2018.

https://www.pbiusergroup.com/engage/volunteeropportunities/volunteer-opportunity-details?VolunteerOpportunityKey=c2bb2d9e-ddab-4be1-9fcd-7204a4e6226d

So What about DATA and BI Summit 2018 ?

Data & BI Summit is hosted by The Power BI User Group (PUG). With over 38,000 members, PUG has established itself as the go-to for Data Professionals and Business Analysts who are eager to collaborate and deepen their expertise in Microsoft business intelligence tools.

DATA and BI Summit is going to be held on 24-26 April 2018 | The Convention Centre Dublin | Dublin, Ireland.

What to expect at the Data & BI Summit:

Exceptional, quality content: Learn how to bring your company through the digital transformation by gaining new understandings of your data and deepening your knowledge of the Microsoft Business Intelligence tools. Products will include: Power BI, PowerApps, Flow, SQL Server, Excel, Azure, D365 and more!
Answers to your questions: Network with the Microsoft Power BI team, dig-in onsite to find immediate answers with industry experts, Data MVPs, and User Group Leaders while taking advantage of the opportunity to engage in interactive sessions, workshops and labs.
Network with your peers: Enjoy countless opportunities to create lasting relationships by connecting and networking with user group peers, partners and Microsoft team members.
Stretch your skill set: Advance your career by learning the latest updates and how they can help you and your business.

You can find more on DATA and BI Summit 2018 by going through below link.

https://www.databisummit.com/home

Thanks,

Anil Maharjan

Senior BI Engineer | Nepal Power BI User Group Leader

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:

( New Microsoft Power BI User Group Link )

Nepal Power BI User Group – Microsoft Power BI Community

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:

Nepal Power BI User Group – Microsoft Power BI Community ( New Microsoft Power BI User Group 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 9^th 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:

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

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.

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 link: http://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