Jan 11, 2021

Advanced Analytics in Power BI with R and Python

Ryan Wade Posted by Ryan Wade

Are you interested in leveraging AI and ML in Power BI without the need of Premium Capacity? If so, you should check out my new book titled Advanced Analytics in Power BI with R and Python.

Blog_BI with R and Python

This book educates you on how to perform tasks in Power BI with R and Python that are much harder to do using Power BI native functionality. Some of the added functionality you gain by leveraging R and Python in Power BI includes:

  • The ability to enhance Power BI data with ML and AI models for self-service and enterprise solutions without the need of Premium Capacity
  • The ability to create ML based data visualizations in Power BI.

Let’s describe each of those topics in more detail:

Enhancing Power BI data with ML and AI models (self-service solutions)

Power BI has built-in functionality that enables you to include AI in your solutions relatively easy if you are on Premium Capacity. If you are not on Premium Capacity, then you are out of luck. This book will teach you how to overcome that limitation in Power BI with R and Python. The book illustrates how with the following three examples:

  • Sentiment analysis via Microsoft Cognitive Services: The book provides a Python recipe that can be used to obtain the sentiment of a corpus of text using the same Microsoft Cognitive Services API that is used by Premium Capacity. The recipe is coupled with verbose explanations to make it clear to the reader how the code works.

    In addition to using Python to interact with Microsoft Cognitive Services, you can also use it to perform the same types of data wrangling traditionally done in Power BI using Power Query. For instance, you can repurpose the sentiment scores returned from Microsoft Cognitive Services into more meaningful categories such as negative, neutral, and positive. Doing so would make the sentiment information more useful for reporting.

  • Tone Analysis via IBM Watson Natural Language Understanding: When you use AI in Power BI while on Premium Capacity, you are limited to services offered by Microsoft. That is not the case when you leverage Python. Python is a general-purpose programming language, so you can easily use it to interact with APIs from providers other than Microsoft. For instance, you can easily access the Tone Analyzer API from IBM Watson Natural Language Understanding to perform tone analysis on text prior to ingestion using a Python script in GetData.

  • Scoring data using custom machine learning models created in R and Python: You can easily use custom data science models that were previously built by your data scientist with R or Python to score Power BI data. You can use R and Python to reshape your data if it is not in the shape it needs to be in. Both R and Python enable you to perform data transformations that are often required by your models, such as normalizing your data, which is hard to do in Power Query but easy to do in both R and Python.

Enhancing Power BI data with ML and AI models (enterprise solutions)

The above example works great for self-service scenarios but it does not scale very well to enterprise solutions. The reason is because R and Python are not a direct option with the enterprise version of the on-premises data gateway. The book shows how to get around this limitation by leveraging SQL Server Machine Learning Services (SSMLS) from Power BI. SSMLS gives you the ability to wrap Python and R scripts in T-SQL stored procedures that can be used as the data source for Power BI datasets. This makes the scripts available to the enterprise version of the on-premises data gateway. ML & AI enterprise examples are listed below with descriptions:

  • Perform Sentiment Analysis using pre-built models in SSMLS via R or Python: SSMLS has pre-built models that can be accessed via R or Python scripts that are wrapped inside T-SQL stored procedures. One of the available pre-trained models is a sentiment analysis model. This model can be used free of cost if you are using SQL Server 2017 or a later version of SQL Server. The book provides detailed instructions on how to implement this functionality.

  • Scoring Power BI data prior to ingestion with a custom R or Python model via SSMLS: You can use custom models built in R or Python to score data inside the database via SSMLS. The same machine learning models that were discussed in the self-service examples can be imported into the database and can be used to score data for enterprise solutions. The scored data can be a data source in an ETL strategy that uses the enterprise version of the on-premises data gateway because the scoring is done inside a T-SQL stored procedure.

Creating ML based data visualizations in Power BI

The book also covers how to create machine learning based visualizations in Power BI using R. The example used in the book is a forecast visualization. The forecast visualization example used in the book is different than common forecast visualization because it not only visualizes the forecast, but also other components of the forecast such as the trend and seasonality of the underlying data. This is accomplished in the book by leveraging the Prophet package in R that was developed by Facebook.

BlueGranite will create a series of vlogs where we will cover some of the topics mentioned above. Specifically, there will be vlogs on:

  • How to use R and Python custom models to score Power BI data prior to ingestion: We will demonstrate how to use a model built in R or Python to score data prior to ingestion in Power BI. The model demonstrated will be a model that predicts house prices based on attributes about the houses.

  • How to use R to create custom R visualization in Power BI: This will be a multi-part vlog that will illustrate how to build a R custom visual for Power BI with best practices via the ggplot2 package.

You will be able to find the vlogs on BlueGranites YouTube channel. Please subscribe to get notified when new content is created.

How can BlueGranite help?

With a rich history of building data warehousing solutions and modern data lake-centric architectures in the Azure cloud, BlueGranite is set to help your organization modernize and strategize around data. Our end-to-end approach with Modern Business Intelligence (with Power BI) and Modern Data Platform (with Azure Synapse, Data Lakes, and Databricks) provides the perfect setup for increasing your analytical power.

In addition, our expertise in the integration of data to support AI use cases will help you transform your strategy by increasing your predictive power to understand what will happen next. Contact us to learn how.

New call-to-action
Ryan Wade

About The Author

Ryan Wade

Ryan is a data analytics professional that has an advanced understanding of R, Python, DAX, SQL, VBA, and M. He knows how to leverage those programming languages for on-prem and cloud-based data analytics solutions using the Microsoft Data Platform. He is also the author of the book Advanced Analytics in Power BI with R and Python (Apress Summer 2020).

Latest Posts

New call-to-action