Data & Analytics

Azure Machine Learning Integrated with Power BI

Get familiar with Machine Learning Workspace. Predict Diabetes Score using Linear Regression.

Prerequisites:

• Requires Azure Subscription for creating Automated ML Workspace
• Install Python on your system
• Understanding of Linear Regression algorithm

Let’s first have a quick overview on Linear Regression and then we will deep dive into the process of creating Automated ML model and this model will get integrated into Power BI. Linear regression is a linear approximation of a relationship between two or more variables. Regressor models are highly used by data scientists to make predictions over continuous numerical values.

Basically, the process of linear regression is mentioned as below:

• Come up with the dataset as per your choice but it should follow some objective of making predictions
• Design Machine Learning Model that works on the dataset
• Make predictions on the dataset (based on Linear regression algorithm)

There is a dependent variable which is called Y being predicted and independent variables X1, X2, X3……….Xn. Here x is a predictor and Y is a function of X variables. The simple equation of linear regression is:

Random forest regression is a bagging technique where parts of the main dataset get distributed among multiple Decision Trees that will predict the best model. Based on the root mean square error (RMSE), it will aggregate the best model or choose the best predictive model.

In Random Forest Process, we have some base learner models like M1, M2, M3 .. Mn. These base learner models are called Decision Trees. Each decision tree will randomly pick up the number of rows and columns from the main dataset; the process is called Row Sampling for rows distribution and Feature Sampling for columns distribution. In this way, every base learner/decision tree will have D’ dataset. This will form a bootstrap model which will be aggregated according to the bagging process.

The workspace is the top level of resource which you need to build to work in Machine Learning environment. Azure provides different types of workspace; according to the needs and requirements, users will create the workspace. In our case, we will create Machine Learning Workspace by following the below steps:

• Go to Azure Portal
• Search for Machine Learning on search bar
• Click on Create to create the workspace
• Provide the workspace name, rest of the details will be set to default as new
• Click on blue button review and create

To Launch the workspace, go to the workspace and click Launch Studio. Here we will get the Machine Learning workspace where we will define the dataset and train the model based on Linear Regression.

Creating Experiments using Automated ML:

Follow the below instructions to create the experiment using Automated ML:

• Click on automated ML option
• Click on New Automated ML run
• Click on the Create New Option and select Automated ML run
• Next step is to choose the dataset, click on Create datasets and select From open datasets
• Now, search for diabetes in the search box and select sample: Diabetes
• Click on Next
• Give the name of the dataset and click on Create button

Now, we have successfully created the dataset. Next, step is to configure the Model:

• Select the Sample: Diabetes and click on Next
• In the next step, we are required to provide some details as below:

• Give the name of the experiment
• Select Target Column Y (Actual value on which the model will make predictions)
• Select compute type as compute cluster
• Select Azure ML compute cluster compute1 (if not pre-built, you need to create a new one)

Now, you may relax and see the magic what Automated ML will prepare for you. This is a code-free platform where you need not to worry about the calculations and the logic behind the model. But basic understanding of the algorithm is required to understand and interpret the results.

Note: It will take approx. 30 min to train the model. When you create an experiment, Automated ML will create multiple models for you. Based on the normalized root mean squared error, we will select our best model i.e. Random Forest and deploy as a web service. Here you need to provide some details like name of the model and compute type. Click on Deploy.

Note: If we do not deploy the model, it will not be visible to Power BI.

Integrate the model into Power BI:

Before integrating the model into Power BI, we will make our Power BI engine compatible with Python. In Power BI Desktop, go to File -> Options and Settings -> Options -> Python Scripting. Now, under the option Detected Python Home Directories, give the folder location where your Python is installed. Install Pandas, Numpy and Matplotlib library using command prompt.

Note: To integrate the model into Power BI, first we need to get the same dataset columns which were passed to our model. Here, records of the table can be different but headers will need to be the same as per our model because our model is trained on those headers and only knows the same column name.

Now, go to the Home Tab -> Transform Data, Power Query Editor window will appear. In Power Query Editor go to Home Tab -> Azure Machine Learning Option. You will get the list of models which have been previously built. Select the model which you have deployed. You can see the Created date and last modified date of the model. As soon as you click on OK button, your model will get loaded into Power BI with the Predictions. You can check the results by changing some records of the dataset. Now, you can see the predicted value as a column in your dataset.

Click on Close & Apply Option. In the Power BI Desktop under the visualization view, drag the python scripting from visuals. In the Fields, select Y and the model value which was loaded into the power query editor. Rename Y as Actual Value and AzureML:DiabetesPrediction as Predicted Value for better understanding of the visual.

Below is the small code which you will need to write under the python script editor to plot the above Line chart.