Unleashing Insights: Integrating Azure Machine Learning and Power BI for Smarter Analytics

Introduction

In today‘s data-driven world, organizations are constantly seeking ways to harness the power of their data to drive better decision making. Two of Microsoft‘s leading platforms – Azure Machine Learning and Power BI – offer a compelling solution when used together.

Azure Machine Learning (Azure ML) enables data scientists and developers to build, train, and deploy advanced machine learning models at scale in the cloud. Power BI provides business analysts and users with interactive data visualization and business intelligence capabilities to explore data and share insights.

By integrating Azure ML and Power BI, you can infuse AI into your business analytics and empower users with intelligent, predictive insights right within their Power BI reports and dashboards. Gartner predicts that by 2025, AI will be used in 75% of enterprise applications, up from 10% in 2020[^1]. Adopting Azure ML and Power BI together can help your organization get ahead of this trend.

In this article, we‘ll take a deep dive into Azure ML and Power BI integration. You‘ll learn how to deploy your machine learning models as web services in Azure and consume them in Power BI to enhance your data. We‘ll walk through the process step-by-step and share best practices along the way.

Whether you‘re a data scientist, analyst, or business user, understanding how to leverage machine learning in Power BI can take your analytics to the next level. Let‘s get started!

Azure Machine Learning Concepts

Before diving into the integration with Power BI, let‘s review some key concepts in Azure ML:

Concept	Description
Workspace	The top-level resource for Azure ML, used to store all your machine learning assets
Compute	The machines and clusters used to train and deploy your models
Datasets	The data used to train and validate your models
Experiments	The iterative process of building and training models
Pipelines	Reusable workflows for training and scoring models
Models	The trained machine learning models
Endpoints	The deployments used to serve model predictions

In a typical Azure ML workflow, you start by creating a Workspace in the Azure portal. Within the workspace, you provision the necessary Compute resources like GPU clusters for training or Kubernetes clusters for inference.

Next, you register Datasets to use for model development. Using the Azure ML SDK or visual interface, you author scripts for training and evaluating models as Experiments.

To standardize the machine learning process, you can orchestrate your steps as a Pipeline. A pipeline can include data preparation, training, and scoring steps with dependencies between them.

After you have a model you‘re satisfied with, you register it in the Model Registry for versioning and deployment. Finally, you create an Endpoint to serve predictions using the model, either as a real-time web service or batch inference pipeline.

Azure ML integrates with popular open source frameworks and tools so you can use the libraries and development environments you‘re familiar with. The platform also enables collaboration between data scientists and app developers with support for VS Code, Jupyter, and GitHub.

Deploying an Azure ML Model for Power BI

To use an Azure ML model in Power BI, you first need to deploy it as a web service endpoint. Here are the high-level steps:

Train and register the model
Define an inference pipeline
Create a scoring script
Deploy the model as a web service
Test the web service

Let‘s walk through an example in Python using the Azure ML SDK v2:

from azure.ai.ml import MLClient, Input
from azure.ai.ml.entities import Model, Environment, CodeConfiguration

# Connect to workspace 
ml_client = MLClient.from_config()

# Register model
model = Model(path="model.pkl")
model = ml_client.models.create_or_update(model)

# Define inference pipeline  
pipeline_job = Pipeline(
    description="inference pipeline",
    workspace=ws,    
    steps=[
        PythonScriptStep(
            name="score",
            script_name="score.py",
            compute="cpu-cluster",
            inputs=[
                Input(type="pipeline_model", model=model),
            ]
        )  
    ]
)

# Create scoring script
%%writefile score.py

import json
import joblib
import numpy as np

def init():
    global model
    model = joblib.load(‘model.pkl‘)

def run(data):
    try:
        data = json.loads(data)
        data = np.array(data[‘data‘])
        result = model.predict(data)
        return {‘data‘: result.tolist()}
    except Exception as e:
        error = str(e)
        return error

# Deploy model 
deployment_config = AksWebservice.deploy_configuration()
service = Model.deploy(ws, "myservice", [model], deployment_config)
service.wait_for_deployment(show_output=True)

# Test web service
import requests

x_new = [[0.5, 0.7, 0.2]]
input_json = json.dumps({"data": x_new})

headers = {‘Content-Type‘:‘application/json‘} 
resp = requests.post(service.scoring_uri, input_json, headers=headers)

print("Prediction:", resp.text)

In this example, we first connect to our Azure ML workspace. We then register our trained model in the workspace.

Next, we define a pipeline with a single step to run the scoring script. The scoring script loads the model and defines an init and run function. The run function accepts JSON input and returns the model predictions.

We then deploy the model as a web service endpoint on Azure Kubernetes Service (AKS). Finally, we test the web service by sending a JSON request with input data and print the response.

Consuming the Azure ML Web Service in Power BI

Now that we have an Azure ML model deployed as a web service, let‘s see how to use it in Power BI. We‘ll explore two scenarios – batch scoring and real-time scoring.

Batch Scoring

For batch scoring, we can utilize the Invoke Custom Function in Power Query to call the Azure ML endpoint and score our Power BI data.

Here are the steps:

Connect to your data source in Power BI Desktop and launch Power Query Editor
Create a new blank query
Define a function to invoke the web service
Apply the function to your data
Expand the scored results

Here‘s an example M code snippet for the Invoke Custom Function:

let
    apikey = "abc123",
    endpoint = "https://myservice.azureml.net/score",
    jsontext = Json.FromValue([data]),
    jsonbinary = Text.ToBinary(jsontext),
    bytestream = Web.Contents(endpoint,[Headers=[#"Authorization"="Bearer " & apikey, #"Content-Type"="application/json"], Content=jsonbinary]),
    jsonresponse = Json.Document(bytestream),
    scores = jsonresponse[data]
in
    scores

In this code, we define variables for the API key and endpoint URL. We then convert our input data to JSON and invoke the web service using Web.Contents. Finally, we parse the JSON response and return the scores.

To apply this function to our data, we edit the query settings and set the data type to Any. We then invoke the function and expand the results.

Figure 1. Invoking Azure ML web service in Power Query

Now we have a new column in our data with the predicted scores. We can use this column in our Power BI report visualizations and calculations.

Real-Time Scoring

For real-time scoring, we can use the Azure ML visual custom visual in Power BI to directly embed our model.

Here are the steps:

Install the Azure ML visual from AppSource
Add the visual to your report canvas
Configure the endpoint URL and API key
Define the model input parameters
Set up the output to display

The Azure ML visual accepts user input and sends it to the endpoint to get a predicted score. The score is then displayed in the visual.

Figure 2. Azure ML model visual in Power BI

By embedding the Azure ML model in the report, business users can interact with the model and perform what-if analysis to make informed decisions.

Business Use Cases and Benefits

So what kind of business problems can you solve by integrating Azure ML and Power BI? Here are a few examples:

Customer Churn Prediction: Use Azure ML to build a churn prediction model and deploy it to Power BI. Business users can then see which customers are at risk of churning and take proactive steps to retain them.
Sales Forecasting: Embed a sales forecasting model in Power BI to enable business users to input different assumptions and see the impact on projected sales. Combine the predictions with actuals to track performance.
Predictive Maintenance: Deploy an anomaly detection model to monitor IoT sensor data in real-time and surface predicted issues in Power BI. Engineers and technicians can use the insights to schedule proactive maintenance and avoid costly failures.
Fraud Detection: Integrate an Azure ML fraud detection model with streaming Power BI data to identify suspicious transactions. Risk analysts can investigate the flagged transactions and take swift action to prevent losses.

By operationalizing machine learning models in Power BI, you can achieve significant benefits:

Democratize AI: Make AI accessible to business users in a familiar, low-code environment
Accelerate decisions: Provide intelligent insights in real-time to inform data-driven decisions
Boost productivity: Automate manual analysis and free up users to focus on strategic initiatives
Increase accuracy: Leverage ML models to make more accurate predictions than traditional BI methods

One Microsoft customer, Rockwell Automation, used Azure ML models in Power BI to predict equipment maintenance issues and avoid $5M in downtime costs[^2]. The supply chain manager said "Having the ability to visualize data from IoT sensors to understand asset health and predict failures before they happen helps us deliver the highest quality product."

Considerations and Limitations

While the Azure ML Power BI integration opens up powerful possibilities, there are some factors to keep in mind:

Performance: The performance of your Power BI reports can be impacted by the latency and throughput of the Azure ML web service endpoint. If the endpoint takes a long time to respond or can only handle a limited number of concurrent requests, it may slow down the refresh of visuals.

One way to mitigate this is to deploy the Azure ML model on a cluster with sufficient nodes and resources to handle the expected load. Use Azure Monitor to keep an eye on endpoint metrics like response time and requests per second.

If you have a slow-running or complex model, consider pre-scoring the data in a batch process and importing the results into Power BI rather than scoring on the fly. You can use Azure Data Factory or Azure Databricks to operationalize the batch scoring pipeline.

Data Consistency: Another thing to watch out for is potential data drift between the data used to train the Azure ML model and the Power BI data being scored. If the model was trained on a snapshot of historical data but the Power BI report is showing the latest data, the model‘s accuracy may degrade over time.

To detect data drift, use Azure ML‘s dataset monitors to compare the statistical properties of the training data with new inference data. Set up alerts to notify the model owner if drift exceeds a certain threshold. Retrain the model on the latest data to keep it current.

In the Power BI report, be sure to include data freshness and quality indicators so users are aware of any potential inconsistencies. Add a timestamp showing when the model was last trained.

Security: When integrating an Azure ML web service with Power BI, it‘s critical to maintain security over the data being exchanged. Use Azure AD authentication to secure access to the endpoint. Encrypt data in transit using HTTPS and apply role-based access control in Power BI to limit who can view and interact with the model.

Also consider data privacy and compliance when using Azure ML models with sensitive data. Use data masking and encryption to protect personal identifiable information. Ensure your Azure ML workspace and Power BI environment meet regulatory requirements like GDPR and HIPAA.

Looking Ahead

The integration between Azure ML and Power BI will only get deeper and richer going forward. At Microsoft Ignite 2020, CVP of Power BI Arun Ulag shared plans for a more seamless experience to create ML models in Azure and consume them in Power BI[^3].

Soon, you‘ll be able to use AutoML to build models directly in Power BI and deploy them with a single click. The scoring and retraining process will be fully automated so your Power BI insights stay up to date.

Power BI will also leverage Azure ML‘s explainable AI capabilities to auto-generate plain language explanations of model results. Users will be able to easily interpret factors influencing the predictions.

In addition, expect to see more AI visualizations and dashboards in Power BI powered by Azure ML. The recently announced Smart Narratives feature is a hint of what‘s to come – natural language summaries highlighting key takeaways from the data.

As Gartner advises, "Organizations should increase investments in AI capabilities to drive business transformation."[^4] The combination of Azure ML and Power BI is a key way to infuse AI across your analytics and empower users with predictive insights.

Conclusion

Integrating Azure Machine Learning with Power BI opens up a world of possibilities for enhancing business intelligence with data science. By deploying your machine learning models as web services and consuming them in Power BI, you can democratize AI and boost the accuracy of your BI insights.

The batch scoring and real-time scoring scenarios enable you to leverage Azure ML models for both large datasets and interactive what-if analysis. Following best practices around performance, data consistency, and security will ensure your integrated solutions deliver maximum impact.

As Microsoft continues to innovate in this area, the future is bright for organizations looking to adopt machine learning and advanced analytics in a fast, intuitive way. Staying on top of the latest Azure ML and Power BI capabilities will be key to your digital transformation efforts.

Hopefully this in-depth article gave you the knowledge and inspiration to get started with Azure ML and Power BI integration. The only limit is your creativity in applying AI to your most pressing business challenges.

References

[^1]: Gartner, Forecast Analysis: Artificial Intelligence, Worldwide
[^2]: Microsoft Customer Story, Rockwell Automation
[^3]: Microsoft Ignite 2020, Power BI and Azure Machine Learning Integrations Roadmap
[^4]: Gartner, Top 10 Data and Analytics Technology Trends for 2021

Unleashing Insights: Integrating Azure Machine Learning and Power BI for Smarter Analytics

Introduction

Azure Machine Learning Concepts

Deploying an Azure ML Model for Power BI