Analytics with R, Microsoft SQL Server, Power BI, and Azure Machine Learning
Length: 5 days
Dates Monday 9.00AM to – Friday 4.30PM
Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.
Instructor: Dr. Leila Etaati
You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.
In 5 days course, you will learn how to do machine learning in cloud using Azure ML, learn about basics of R, how to use R inside Power BI and R in SQL Server 2016:
Azure ML Studio and Azure ML Workbench (1.5 day )
The basics of Machine Learning
Some basics concepts will be explained like Process of machine learning,
What is descriptive, predictive and prescriptive, what is Cortana and how Azure ML can be used
Import data: The main component for importing data from local PC, how to import data from other work space, how to import data from html website, how to import data from cloud such as Azure SQL DB.
Data Cleaning: data cleaning is the main process that we should done before any machine learning process. I will explain the available component in Azure ML. Cleaning missing value, remove duplicate data, select column, clip value (remove outliers), group data into bins, create indicator for data, how to normalize the data, how to use SQL Statement for data transformation, how to use enter data manually, how to edit the data type, change the column name with edit metadata component, how to join data from different data resource , how to increase the number of low incidence in a dataset which unbalanced.
4- Feature Selection Data Sampling: the process of feature selection will be explained, how to split data, how to partition data using sampling approach, how to create different folds for aim of cross validation.
5- Models: a bit talk about the available models in Azure mL for predictive, descriptive, prescriptive and anomaly detection. An example on prediction a group, a value, clustering data and anomaly detection will be shown. For each scenario, an example will be presented, different algorithms will apply for a problem. the main concepts of k-mean , how to use elbow chart to identify the number of cluster will be discussed, the PCA chart will be explained. The trained model for recommendation “match box recommender” will be explained through a scenario. The main concepts of collaborative filtering and content based filtering and hybrid approach for recommendation will be discussed.
5- Training and Scoring models: how to choose algorithms and how to train model and test model, what is cross validation, how to do cross validation to apply a model to different folds of data, how to check the different values for an each parameters and see the related accuracy to that.
6-Evaluate: how to evaluate a classification problem concepts of Accuracy, Recall, Precision, AUC will be discussed, the evaluation criteria for regression algorithm MAE, RMSE and so on will be show. How to evaluate and see the result of more than three algorithms on one dataset will be shown. Using different evaluate model, enter data manually component and add row component.
7-Publish to Web: the process of creating a web service from a model will be discussed, how to check it in Excel will be shown, also how to use it in Stream Analytics as a function for data will be shown.
8-Sharing workspace, create projects: the process of how to share the workspace with others will be discussed, how to create a project for each experiment, how to create a trained model component for reuse, and a data transformed dataset, how to export datasets to csv and other formats will be shown.
9-Using Azure ML Workbench for deploying the models for Data wrangling and data visualization
10- Model and experiment Managment in cortana
11- A brife explanation on Python
Introduction to R (0.5 days)
The basics of R will be shown in class
R basics: the basics of R and data structure, like vector, list, data frame and factor will be discussed. The R studio environment will be shown how to write the R code, what is packages, how we can import data set into R using RStudio environment, how to import data from SQL Server, how to write SQL statement for data wrangling inside R studio,
Statistics: some of the main statistics computation will be explained, str, summary command, what is mean, median, first quarter, third quarter, standard deviation, and so on, how to show them using boxplot, and normal distribution. How to analysis data via these simple statistics.
Packages: how to install packages by writing code and without writing code, how to manage the packages, how to use dplyr package for data cleaning and wrangling, how to use ggplot2 package to draw pictures. Introduce some of the packages for drawing maps like leaflet and so on.
R and Power BI for Visualization and Machine Learning (2.5 days)
In this session, I will talk how to empower BI using R. this has four main section from how to draw R visual that we do not have normally in Power BI, how to create a R custom Visual for better usage and sharing with other, how to do machine learning and do different algorithms inside Power BI and finally how to use the available custom visual in office store.
Draw R visual in Power BI: in this session, I will show how to set up power BI to use R, how to create R visual inside power bi, how to debug and run code there, how to avoid some usual mistake to get better visualization. Some of the visual from ggplot2 package will be introduced. The Facet chart to show 5 different variables in a chart, how to show different types of chart in facet chart, how to create slope chart just to show the difference of a variable in two dimension (e.g. two time). How to draw a column width chart to show a bar chart with different weight, how to draw polar chart from our bar charts in different ways, how to draw a correlation chart for correlation analysis, how to draw a calendar chart, how to draw a map chart with desire sub chart in it and so on.
Create a Custom Visual chart: the main process of how to create a custom R visual will be explained. From installing desired component to writ e the code in command prompt to create a template. How to change the r files, how to change the name of chart using json files, how to create pbiviz file so you able to import it into power bi. Moreover, the plotly package will be shown and how it makes the visual more interactive than before.
Machine Learning Algorithms with R and Power BI: in this part I am going to show how to do machine learning inside Power BI with a brief explanation on different algorithms.
I will show how to do machine learning inside power query using R transformation component, how to write R scripts to create some functions inside Power Query for more data transformation. Moreover the brief description of some algorithms with their R codes and required packages in R will be discussed.
K-Nearest neighbour: the main concepts for using KNN, how it works the statistic behind it, how to enhance and improve it, how to write the relevant code inside Power BI. Moreover, a discussion on overfitting and under fitting will be done. And how identify the best K value can help us in KNN algorithm.
Clustering with k-mean: what is the k-mean already discuss in first day. How to identify the number of cluster using elbow chart. How to analysis a clustering problem in power bi using power BI visuals.
Decision tree: the concepts behind it, how to specify the best value for decision tree depth and other parameters. How decision tree can be good for predicting a group, predict a value and also do the feature selection. How to draw a decision tree inside power bi and how to use it just for prediction.
Neural Network: the main concepts behind the neural network, the different structure for neural network, how to identify the best number of the hidden node, how to do prediction a group and a value using NN. How to show a NN structure in power bi.
Regression: the concepts behind the regression and some simple statistics will be provided. How to use if for predicting a value and how to evaluate the result of a regression will be shown. The liner and none linear regression also will be discussed
Market Basket Analysis: the main concepts for market basket analysis, the concepts of supports, confidence and lift will be shown, and how to show it in power bi will be discussed.
Time series: a detail concepts for time series will be presented. From simple time series without trend and seasonality and stationary one with no correlation between residual to Arima model with trend and seasonality (almost 6 different mode) will be discussed. The composite chart the exponential smoothing approach, the ACF and PCAF chart to identify the autocorrelation will be shown. Also, audience will learn how to draw a relevant time series chart using R codes in Power BI.
The optimization problem: a very brief introduction into optimization problem will be done, people will be familiar with linear programming. How to do it in Excel using Solver and how to do it in R and Power BI.
Power BI Office Store visual: in this part, I will show some of existing power BI custom R visual that Microsoft provided them in Office store such as decision tree charts, accusative rules charts, clustering, time series for Arima model, Time series for exponential smoothing, decompose time series and so on. I will show how to setup the parameters and how to use them.
Using R services and ML services in SQL Server and Azure Data Lake(0.5 day)
How to set up: There is a possibility to do machine learning inside SQL Server 2016 using R codes. In half day audience, will be familiar how to set their SQL Server to access the R server for doing the machine learning. They learn how to call a store procedure for using an external code like R. moreover, they learn how to use external scripts for doing machine learning writing R codes inside SQL Server, how to pass the data to R and how to get the output.
Best practice and Examples: Audience will learn the best practice for doing machine earning inside the SQL Server, create separate store procedures. A customer churn example will be shown. This example is for prediction.
Evolutionary Package: there is a package that most of Microsoft algorithms are there. I will show how to use these algorithms and what algorithms are exist in this package.
Draw R charts in SSRS: I will show how to create a R chart inside SQL Server Management studio and store it as binary value in a table. Finally, how to show it in SSRS.
This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.
Instructor: Dr. Leila Etaati
Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.
What others say about the training and trainer
Kenny McMillan, Sports Physiologist / Data Analyst, Frankfurt, Germany:
I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualizations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.
Martin Catherall (Microsoft Data Platform MVP):
As part of SQL Saturday Auckland 2016 I attended an “Analytics with Power BI and R.” pre-con with Leila. Leila took the class’s knowledge from rudimentary to competent in a day. We first looked at R, the language and the software. Once these skills were obtained we started to look at Power BI and the integration that it has with R. We worked through a few examples and Leila answered all of the class’s questions and offered to provide supplementary material for some of the more advanced questions. I left the class feeling that my R and Power BI knowledge was at a competent level and ready to dive into some of the more advanced material that Leila provided.
Cancellation:
Cancellation up to 5 weeks before the event: fully refund minus administration fee ($50) and credit-card processing fees (if applicable).
Cancellation from 5 weeks to 2 weeks before the event: 50% cancellation charge, 50% refund
Cancellation from 2 weeks before the event: 100% cancellation charge, 0% refunded.
Transfer:
Transfer fee to another event date* (up to 2 weeks before event): 25% of the standard price of the event to transfer
Transfer fee to another event date* (from 2 weeks to 1 day before event): 40% of the standard price of the event to transfer
Transfer fee at the day of event*: 60% of the standard price of the event to transfer
*transfer can be done only once, and it can be only transferred to another date not later than 6 months from the original event.
No Show:
No fee will be refunded for no show.
