Azure Machine Learning in Power BI Dataflows

[sagarnarla]

Azure empowers you with the most advanced machine learning capabilities to quickly and easily build, train, and deploy your machine learning models using Azure Machine Learning (Azure ML). Power BI lets you visually explore and analyze your data. In this post we will see how we have made it easy to use the power of Azure ML in Power BI. Once you build your models in Azure ML and deploy them as Web-services, then in just a few clicks you can use them in Power BI to make predictions on your data.

Creating a web-service using Azure ML Studio

Azure ML Studio is a fully-managed cloud service that enables you to easily build, deploy, and share predictive analytics solutions. To start off sign up and login to Azure ML Studio. For this blog we simply used one of the out of the box samples.  The sample builds a binary classification model to predict if the income levels of adult individuals would be greater or less than $50,000. You can learn more about the sample here. After running the Predictive Experiment from the sample we deploy the model as web-service using the “Deploy Web Service [New] Preview” option. “Classic” web service option is not supported in Power BI.

In the next page you will asked to give your web-service a name and select a Price plan. The “Standard DevTest” plan is not supported so make sure to use one of the other “Standard” plans.

Note: Power BI optimizes the number of requests sent to your web-service by sending batches of your data to the web-service. The number of transactions to your web-service originating from Power BI will be much lower than your data size (number of rows).

Creating a web-service using Azure ML Services

Azure Machine Learning service provides SDKs and services to quickly prep data, train, and deploy machine learning models. You can find all the documentation regarding Azure ML services here. You can follow the steps outlined here to create a web-service. In the scoring python script we must specify the schema the web-service expects using the inference_schema.schema_decorators from the azureml python SDK. This step is mandatory for the Web-service to the discovered in Power BI as an AI Function.

Here is the sample scoring python script used in our sample.

import json
import pickle
import numpy as np
import pandas as pd
import azureml.train.automl
from sklearn.externals import joblib
from azureml.core.model import Model

from inference_schema.schema_decorators import input_schema, output_schema
from inference_schema.parameter_types.numpy_parameter_type import NumpyParameterType
from inference_schema.parameter_types.pandas_parameter_type import PandasParameterType

def init():
    global model
    model_path = Model.get_model_path('linearregmodel')
    # deserialize the model file back into a sklearn model
    model = joblib.load(model_path)

input_sample = pd.DataFrame(data=[{
              "age": 39,
              "workclass": "State-gov",
              "fnlwgt": 77516,
              "education": "Bachelors",
              "education-num": 13,
              "martial-status": "Never-Married",
              "occupation": "Adm-clerical",
              "relationship": "Not-in-family",
              "race": "White",
              "sex": "Male",
              "capital-gain": 2174,
              "capital-loss": 0,
              "hours-per-week": 40,
              "native-country": "Cuba"              
            }])

output_sample = pd.DataFrame(data=[{
              "Scored Labels": "<=50",
              "Scored Probabilities": 0.20
            }])

@input_schema('data', PandasParameterType(input_sample))
@output_schema('data', PandasParameterType(output_sample))
def run(data):
    try:
        result = model.predict(data)
        # you can return any datatype as long as it is JSON-serializable
        return result.tolist()
    except Exception as e:
        error = str(e)
        return error

 

Sharing Azure ML web-services

In order for other users to use the web-service in Power BI you must to share it with them. Sharing is done through Role Based Access Control in the Azure portal. You can find the steps to do that here.

Using your Web-services in Power BI

Now for the easy part! With your web-services deployed you can login to your Power BI account and start using it in your dataflows!

1. Create a dataflow and add an entity which you want to score. You can refer here for the steps.
2. Click the AI Insights button in the PowerQueryOnline editor
3. Power BI will now enumerate all the Azure ML web-services that you have access to.
4. Find and select the appropriate web-service, the UI will auto map all the columns of your entity to the inputs of the web-service by matching their names and types.
5. Click Apply to see a preview of the scoring. You will receive a warning indicating that your data will be sent to a service (Azure ML) outside of Power BI.
6. A new column is added to your entity with the results of the scoring. The column is of “Record” type so the editor will not allow you to save as the type is not supported in dataflows. Simply expand the record as columns to add the fields returned by the web-service as additional columns to your entity
7. If the data type of the expanded columns is not auto-detected, then simply change them manually before trying to save. Dataflows validation will not allow you save a query with untyped columns.
8. Now refresh the dataflow to run the model prediction on your entire data
9. Create and share reports using this data via dataflows connector.

Points to remember

• Use the “Deploy Web Service [New] Preview” and a non DevTest Plan when using Azure ML Studio web-service
• Specify the schema decorator in the scoring python script when using Azure ML Services. This helps Power BI discover the input schema that the web-service expects.
• Power BI expects the web-service to return a scored output for every input row.
• Power BI will warn you when using any Azure ML web-service that your data is being sent outside of Power BI. In addition to that note that web-services setup through the Azure ML Studio are secured to use HTTPS but not for Azure ML Services. You will need to follow the additional steps here to setup HTTPS. If you don't use HTTPS for your web-service, data that's sent to and from the service might be visible to others on the internet and open to other security risks.
• While it is possible to share web-services across Azure subscriptions, the user you are sharing with should be in the same Azure Tenant.
• “AI Insights” functionality is not available when using data that requires a Power BI Gateway.