Using SQL Server External Languages for Advanced Analytics and ML Integration in Power BI

As we dive into this chapter, you may find the topic a bit tangential at first. The primary focus of this book is on using the Python and R analytics engines within Power BI to add valuable insights to our reports. However, in this chapter, we will explore a different avenue by examining how we can use the Python and R analytic engines configured within SQL Server (or Azure SQL Managed Instance). Why explore this alternative approach? Well, when you’re dealing with Microsoft’s relational database management system (RDBMS) as one of your report data sources, it’s worth considering because integrating these analytics directly into the database layer can significantly streamline your data processing workflows, enhance the performance of your data analytics, and provide a more seamless and efficient way to generate insights directly from your data source, reducing the need for...

Technical requirements

This chapter requires you to have a working internet connection and Power BI Desktop already installed on your machine (version: 2.121.644.0 64-bit, September 2023). You must have properly configured the R and Python engines and IDEs as outlined in Chapter 2, Configuring R with Power BI, and Chapter 3, Configuring Python with Power BI. In addition, you must have properly configured SQL Server Express as outlined in Chapter 8, Logging Data from Power BI to External Sources.

Introducing SQL Server Machine Learning Services

SQL Server Machine Learning Services is an in-database technology that enables advanced analytics and machine learning directly in Microsoft SQL Server. By integrating advanced algorithms and machine learning models into SQL Server, users can process and analyze large volumes of data without having to move it to a separate analytics platform.

Prior to the introduction of Machine Learning Services, the only way to integrate external data processing via Python or R into a transformation pipeline was to run external code that would read the data from the SQL Server instance via open database connectivity (ODBC) and then write the output to a staging table in case the processed data was needed for further processing in SQL Server. An example of a framework that arose to integrate processing done with R and SQL Server before the advent of Machine Learning Services is the one developed by Tomaz Kastrun, which is available at this link...

Installing Python and R custom runtimes for SQL Server

In Chapter 8, Logging Data from Power BI to External Sources, you installed an instance of SQL Server 2019 to test its ability to use Python and R to log information from Power BI. Our goal now is to install the latest Python and R engines on SQL Server 2019. As mentioned in the previous section, we need to have at least SQL Server 2019 with at least CU3 installed. We assume that you have system administrator privileges on your machine. So, let’s take a look at how to do it.

Updating SQL Server

Referring to the SQL Server 2019 builds table (available at https://bit.ly/sqlserver2019-build-numbers), you can see that the minimum allowed version corresponding to CU3 is 15.0.4023.6:

Figure 18.2: Minimum SQL Server build version number to install

Therefore, the first step is to check which SQL Server build version you have installed. To do this, follow these steps:

1. Open the SQL Server Management...

The need for external languages with Power BI

If we have introduced the possibility of using SQL Server external languages to apply advanced analytics techniques to data, it is because sometimes it may be necessary to do so instead of using the standard Python and R integration feature in Power BI. The first case is related to the policies of the company that needs to use this feature. Let’s see what that is.

Architectural and security policy constraints

So far, we have taken for granted a basic requirement for using Python and R in Power BI that turns out to be a bit of a stretch in the context of enterprise architecture: we are talking about using the Power BI on-premises data gateway in personal mode. As you may already know, Power BI limits each user to running only one data gateway in personal mode. This means that if a user installs another data gateway in personal mode, even on a different computer, the latest installation will replace any previous installation...

Using external languages with Power BI

In this section, we will try to solve the problem of publishing to the Power BI service the report that shows the value predicted by the Titanic Survival machine learning model based on the parameters selected in the report itself, which you saw in Chapter 17, Using Machine Learning without Premium or Embedded Capacity.

In this particular case, any change to the parameters in the report will update the value predicted by the model without having to update the Power BI dataset. If we think about how this translates to the use of external languages, it means that each time the parameters are changed, a query to SQL Server must be launched that runs the model scoring script, passing the various parameters selected in the report. In other words, we need the DirectQuery mode to have real-time data. Power BI doesn’t store any data. Instead, every time you create a visualization or run a query, Power BI sends a request directly to the data...

Summary

In this chapter, you learned about using external languages in Power BI to integrate advanced analytics and machine learning capabilities directly into SQL Server. External languages allow you to execute Python or R scripts within SQL Server, eliminating the need to transfer data to separate analytics platforms. This provides benefits such as improved data security, faster processing, and easier deployment and integration.

You also learned about the SQL Server Extensibility Framework, which enables the execution of Python or R code using external engines. The framework allows for the execution of scripts in an external runtime environment, facilitating communication between SQL Server and the external languages.

Additionally, you learned about the challenges of managing multiple Python or R environments in SQL Server and the benefits of using external languages in scenarios where Power BI’s default integration may not be suitable due to architectural and security...

References

For additional reading, check out the following books and articles:

• Bring Your Own R & Python Runtimes to SQL Server Extensibility Framework (https://nielsberglund.com/post/2020-12-29-bring-your-own-r--python-runtimes-to-sql-server-extensibility-framework/)
• Install a Python custom runtime for SQL Server (https://learn.microsoft.com/en-us/sql/machine-learning/install/custom-runtime-python)
• Install SQL Server 2022 Machine Learning Services (Python and R) on Windows (https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022)
• Fix: Getting R and Python to actually work on SQL Server 2022 (https://blog.greglow.com/2024/03/04/fix-getting-r-and-python-to-actually-work-on-sql-server-2022/)
• SQL Server on Windows: Isolation changes for Machine Learning Services (https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-server-machine-learning-services-2019)
• Overview and...

Acknowledgements

I would like to thank my technical editor, Art Tennick, for his timely feedback on the topics covered in this chapter, of which he is an unabashed advocate!

Test your knowledge

1. What is SQL Server Machine Learning Services?
2. What were the challenges before the introduction of Machine Learning Services?
3. What is the role of the SQL Server Extensibility Framework in executing Python or R code with Machine Learning Services?
4. Which products have Machine Learning Services available?
5. What is the purpose of the Launchpad service in SQL Server Machine Learning Services?
6. How do you give SQL Server’s Launchpad service access to folders where you have downloaded external language DLLs or folders that need to be accessed by scripts?
7. Why would you need external languages with Power BI?
8. How do you call a stored procedure in DirectQuery mode in Power BI?
9. How is the SQL Server machine learning model table used in the examples shown in this chapter?
10. Why do you need to use OPENROWSET to execute a stored procedure? Wouldn’t it be enough to use EXEC?
11. What are the benefits of running...