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

Speaking at SQLSaturday Nepal SQLSaturday#692 and Nepal Power BI User Group Meetup.

Firstly I am happy and excited that I will be speaking at SQLSaturday#692 and Nepal Power BI User Group Meetup on Dec 23 2017. This is my fourth time speaking for these International events and I’m really excited that I will be speaking this time also.

I will be speaking on ‘PowerBI: Data Visualization SQL Saturday with R’ here is my abstract detail:

This session is mainly use to learn more about Power BI and R visualization chart. From this session one can learn how can we make some simple and quick visualization using Power BI desktop taking real SQL Saturday data and publish in PowerBI cloud service and also publish those visualization reports to publicly in web. Also, this session mainly helps to tell the story of SQL Saturday by using Power BI and R visualization chart.

Overall, by using Power BI Visualization how one can find out which SQL Saturday is conducted in which state or country in which year and a particular Day along with total sessions conducted. Also one can find SQL Saturday trends year on year and mostly in which month most of SQL Saturday is conducted as per SQL Saturday data history. So, this session will surely help one to learn regarding Power BI and its capabilities and SQL Saturday Stories.

So, What is SQLSaturday?

SQLSaturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence, Power BI and Analytics.Please register soon as seating is limited, and let friends and colleagues know about the event.

This event will be held on Dec 23 2017 at Hotel Yellow Pagoda, Kantipath, Kathmandu, Nepal.

Please do register and be a part of this great event.

http://www.sqlsaturday.com/692/EventHome.aspx

One can join our local SQL Server User Group ‘Himalayan SQL Server User Group’.

http://www.sqlpassnepal.org/

So, What is 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.

One can join our local Power BI User Group ‘Nepal Power BI User Group’

http://www.pbiusergroup.com/kathmandu

Hope to see you there and don’t forget to say hello to me in the event.

Thanks,

Anil Maharjan

Senior BI Engineer

http://np.linkedin.com/in/maharjananil

Installing SQL Server 2016 Developer Edition and trying out Telco Customer Churn with R services.

During SQL Server Geeks Annual Summit 2016, #SSGAS2016 I was really impressed by Wee Hong Tok for his session on ‘SQL Server R services’ where I got to know more about SQL server 2016 and R services and the demo he presented in that particular session regarding Telco Customer Churn.

Also, another session that I am really impressed by Jen Stirrup for her session on ‘Delivering Practical Analytics and Results with Cortana Analytics’. And also impressed by Andreas Wolter Amit Bansal ,Other Speakers & SQL Server Geeks Community for such a friendliness and hospitality.

Since, I also work in Telco sector so first thing I want to try it out is what Wee Hong Tok had shown in his demo and thanks to Jen Stirrup where she introduce me with Cortana Analytics, I don’t know much of this SQL Server 2016 with R services and regarding Cortana Analytics now I want to know more on these.

One can find regarding Cortana Analytics and Telco Customer Churn from below links.

https://gallery.cortanaintelligence.com/Experiment/Telco-Customer-Churn-5

So, if you also want to try out this then all you need is firstly download the SQL Server 2016 Developer Edition or SQL Server 2016 Evaluation version for 180 Days.

https://www.microsoft.com/en-us/cloud-platform/sql-server-editions-developers

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

For step by step installation you can follow below link

http://www.sqlcoffee.com/SQLServer2016_0001.htm

Before installation SQL Server 2016, you need to install Java Development Tool kit. http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

Also, don’t forget to check the R Services (In-Database) tab during installation, also there is separate R Server (Standalone) during installation just prefer R Services ( In-Database) option.

You can find more on R Services (In-database) and R Service (Standalone) from below links.

https://msdn.microsoft.com/en-us/library/mt696069.aspx

https://msdn.microsoft.com/en-us/library/mt695941.aspx

Once installation compete, in order to test Telco Customer Churn just go to below link to get the backup file for Telco Customer churn in GitHub link below,

https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/r-services/Telco%20Customer%20Churn/SQL%20Server

Get the teloedw2.bak file and restore in SQL Server 2016 database, now read the Read.md file which contains:

Instructions

Restore the database provided (telcoedw2.bak)

Run the code in TelcoChurn-Main.sql

Description

TelcoChurn-Main.sql – Use this T-SQL script to try out the telco customer churn example.

TelcoChurn-Operationalize.sql – T-SQL scripts to create the stored procedures used in this example.

while going through above scripts and trying out Customer Churn I got few errors, so I am sharing my finding here, anyone if gets same error then this post might be helpful.

After installing SQL Server 2016 developer edition and trying for this Telco Customer churn SQL server scripts. I got certain error while running scripts TelcoChurn-Main.sql and TelcoChurn-Operationalize.sql. For first, after google I found out we need to change the Memory_Limit_percent in order to run above sq. scripts. So, just add MEMORY_LIMIT_PERCENT=50 into the config file for Rlauncher.config which can be found in below location:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Change as below;

RHOME=C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES MPI_HOME=C:\Program Files\Microsoft MPI INSTANCE_NAME=MSSQLSERVER TRACE_LEVEL=1 JOB_CLEANUP_ON_EXIT=1 USER_POOL_SIZE=0 WORKING_DIRECTORY=C:\PROGRA~1\MICROS~3\MSSQL1~1.MSS\MSSQL\EXTENS~1 MEMORY_LIMIT_PERCENT=50

While running this script TelcoChurn-Operationalize.sql we need to firstly install the required R packages, I have also uploaded one SQL File which installed required R packages before running the scripts of telco customer churn into my folk repository for sql-server-samples. The file contains enabling sp_execute_external_script to run R scripts in SQL Server 2016 and installing required R packages in order to run the Script TelcoChurn-Main.sql and TelcoChurn-Operationalize.sql successfully. Since we need to install those R packages before we run the scripts in order to avoid the error. https://github.com/maharjananil/sql-server-samples/blob/master/samples/features/r-services/Telco%20Customer%20Churn/SQL%20Server/Enabling%20R%20scripts%20to%20run%20and%20Installing%20required%20R%20Packages.sql

After that all the scripts run successfully and now you can learn more about R Services and R scripts & algorithm used and then test along with your own Telco data. Which I am planning to try out and will try out for sure.

There is always something new to learn into the world of DATA so called BIG DATA & now DATA Science/Analytics and Machine Learning.

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.

Developing Data Products using R language and Shiny App.

Learning new things and playing around with new tools, apps is what we always want and which helps us to grow our carrier and knowledge.

In my free time this month, I took one of the course from Coursera. https://www.coursera.org/

Coursera is an education platform that partners with top universities and organizations worldwide, to offer courses online for anyone to take, for free as well as you can earn or achieve certified certificate too.

Beside than certificate, the knowledge we learn from here is a step towards the success in our carriers.

This time I took a course as ‘Developing Data Products’ which is provided by Johns Hopkins University.

About the Course

A data product is the production output from a statistical analysis. Data products automate complex analysis tasks or use technology to expand the utility of a data informed model, algorithm or inference. This course covers the basics of creating data products using Shiny, R packages, and interactive graphics. The course will focus on the statistical fundamentals of creating a data product that can be used to tell a story about data to a mass audience.

You can find more about this course through:

https://www.coursera.org/course/devdataprod

For this, you need to install R-Studio, R –language, Shinny package and other related packages. Also, you can learn more related to R-language, R-studio, and Shiny app from different courses available in Coursera.

R-Studio:

You can download R-studio,

http://www.rstudio.com/products/rstudio/download/

R version, currently we have R V 3.1.3. You can download from cran.

http://cran.r-project.org/bin/windows/base/

GitHub:

Also, you should open github account which is mostly useful to share codes, docs & as well as you can share ideas and involve in the Projects, also can browse interesting projects & solves all types of interesting problems.

https://github.com/

I have opened my github account as

https://github.com/maharjananil

Shiny & Shiny App:

Then you can open shinyapps account. You can directly login by using the Github same account.

https://www.shinyapps.io/

I have published one app after learning this course as:

https://anilmaharjan.shinyapps.io/FacebookAnalysis/

This shiny app is mainly used to analysis or to find out Facebook site visitor’s vs. other sites. How much time a person spent his/her time in Facebook or in other sites based on per hour, day, and months. Detail analysis can be found out if we can have exact site visit data. This is just a sample data which is not accurate, we can compares trends of site statistics if we can have exact data. .

Thinking of the project in Shinny apps as: Let’s drill into your Facebook data to extract your story. :http://www.slideshare.net/anil_maharjan/using-power-query-to-tell-your-story-form-your-facebook-data

R Markdown / RPubs

Also, you can prepare the documents / presentation as directly publish in web from R. R Markdown documents that you have written in R-studio can be directly published into the web where RPubs comes. Where you can directly publish the R Markdown documents into web RPubs and share among all.

http://rpubs.com/

Summary:

Through this course I am able to learn these new tools, languages, data products, assessments, Quizzes, Projects. It feels like, I have been reading back to my engineering classes again. I must say that course lectures are great and have great knowledge in their respective fields.

This course helps me, how we can directly analysis the data and can tell the story behind the data using different data products, by creating shiny app and pushing in web where anyone can go through the app easily and can understand what the story behind the data is by themselves. Here, we don’t need to load the data into database, create any data models & use any reporting tools to play around data & make decision. Here we can directly play around with raw data using R-language & using shiny app along with different r-charts we can easily tell the story behind data quickly and effectively.

While learning this course I got some issue which I have shared along in forum /discussions.

https://class.coursera.org/devdataprod-012/forum/thread?thread_id=50

https://class.coursera.org/devdataprod-012/forum/thread?thread_id=93

Reference:

http://shiny.rstudio.com/tutorial/lesson1/

http://www.r-project.org/

Regards,

Anil Maharjan

BI Engineer

http://np.linkedin.com/in/maharjananil