In this blog post, I am going to show how to use the Azure ML web service in Power BI (Power Query).
You need to create a model in Azure ML Studio and create a web service for it.
The traditional example in Predict a passenger on Titanic ship is going to survived or not?
we have a dataset about passengers like their age, gender, and passenger class, then we are going to predict whether they are going to survive or not
Open Azure ML Studio and follow the steps to create a model for predicting this. Navigate to Azure ML Studio.
Then download the dataset for titanic from here
click on the Database in Azure ML and upload the Titanic dataset from your local machine into the Azure ML.
Then import from your local PC
Then, you able to see the imported dataset in your Azure ML dataset list
Next, we start to create an experiment in Azure ML by clicking on the Experiment in the left side of the window.
as have been explained before, the Azure ML is a drag and drop environment. in the blank experiment click on the dataset and choose the Titanic and drag and drop into the Experiment area
Now you have a dataset in the experiment and you able to create a model there. you able to explore the dataset by clicking on the output node and choose the visualization option.
Next, we are going to select some of the columns not all of them, we do not need the price, far, cabin and some descriptive data. we only need Survived, Age, Sex, and Passenger class from all 11 columns. There is a module under the transformation tab in the left side.
then click on the select Columns in Dataset and on the right side of the window, there is a property panel. Choose the four required column
Then, we need to put a model for machine learning on, in this experiment I choose a model name two-class decision forest which is an algorithm help me to predict whether will be survived or not.
Then based on the machine learning process we need to split the data into train and test dataset.
after splitting the data, then you need to train the mode using a module name Train Model. drag and drop this module into the Azure ML experiment area, then connect it as shown in the below picture. finally, click on the Train Model module and in the right side choose the Select Column to choose the Survived column.
Then, we need to to test the result using Score Model.
Now out simple model is created we need to run it first
After running the code, we able to create a web service that we then using it in Power BI (Power Query).
Create Web Service
Now we need to create a web service for this model, by clicking on the Setup Webservice.
Next, the web service is created and we have a node for input and other for output.
then click on the Deploy Webservice option to deploy the web service to be used for prediction. However, before doing that, we need to set up better input and output.
The input should be just
age, Sex, and Pclass
The output should be just: Score and the probability
to do that we need to change the experiment a bit by adding the select column for both input and output node. To this aim, the final diagram for machine learning looks like below picture.
After creating the diagram, there is a need to run the model again and then push the Deploy web Service bottomn.
Next, you have a web service for the titanic problem. Now, you will navigate to a new page that shows the detail information about the web service.
Now we have the API, the next step is to get some identification from the Azure ML environment.
the first parameter is Workspace ID
Then the name of the service
now we created a model, besides we have the workspace and authentication id as well.
Now we are going to apply this model to the Data.
first, import the data into Power BI, Powe Query
Now we need to navigate to Run R Script,
In the R editor write the below
the first line is allocating the workspace id from Azure environment to a variable name wisd
Next, it is going to store the Authentication to the auth.
finally, the web service name will store in the variable name Service Name
Next, using some R packages will be used and the service will be called to connect to Azure ML environment
wsid = "<Workspace ID from Azure environment >" auth = "<Authentication from Azure environment>" serviceName = "Titanic Video API [Predictive Exp.]" library("AzureML") ws <- workspace(wsid,auth) ds <- consume(services(ws, name = serviceName),dataset) ds<-data.frame(ds,dataset) #output<-data.frame(ds$Sentiment,ds$Score)
Next, hit the OK bottom. now you able to see the result