python

ByHariharan Rajendran

PowerBI – Python Support

Microsoft introduced Python support from Power BI on August 2018 update.

PowerBI is supporting R visuals and PowerBI producing custom R visuals too. PowerBI team extended the data science capability of PowerBI by supporting python.

In this post, I am explaining the simple python plot in Power BI.

Download the latest version of Power BI. As python support in preview support, we need to enable the python support preview feature.

 

You should install the Python on your machine so that you can point the python home directory which is mandatory to run any python scripts inside PowerBI.

Once you have configured the setup then you need to test with simple code. Use the below code.

import matplotlib

import numpy as np

import matplotlib.pyplot as plt

x = np.linspace(0, 3*4, 500)

plt.plot(x, np.sin(x**2))

plt.title(‘Test Plot’)

plt.show()

 

The above code works perfectly and produced the plot on jupyter notebook and need to get the same result on PowerBI also.

Drag and drop the python visual into the canvas area. In the below script editor, reference any dummy column and then copy and paste the above script.

Finally, execute the script and it will produce the plot as like below.

 

Now, you can play with your python scripts.

ByHariharan Rajendran

Python scatter plot in SQL Server 2017

As you know, Microsoft released the latest version of SQL Server which is SQL Server 2017. If you worked with SQL Server 2016 then you could realize that Microsoft SQL Server is not just a relational database anymore because it started to support big data and so many options to handle the non-relational data.
R Language is very popular to work with data science-related projects. It was integrated with SQL Server 2016 and we can run the R Scripts in SQL Server Management Studio itself.
It helps us to avoid the data movement between the relational database to R server and process. The same way, Microsoft now introduced Python integration with SQL Server. These 2 languages are coming from machine learning services in SQL Server.
To run Python scripts in SQL Server management studio, you need to enable the external script stored procedure.
Run the below command in your SSMS and see whether you are getting the “hello world” as an output.
In case, you are getting an error message then the configuration part was not properly done.
execute sp_execute_external_script
@language = N’Python’,
@script = N’
print(“hello world”)’
The below script is to generate the scatter plot.
DECLARE @Query nvarchar(max) = N’SELECT Year, Sales from [dbo].[SalesByYear]’

execute sp_execute_external_script

@language = N’Python’,

@script = N’

import matplotlib.pyplot as myplot

X = myplot.figure()

myplot.scatter(InputDataSet.Year,InputDataSet.Sales)

myplot.xlabel (“Year”)

myplot.ylabel (“Sales”)

myplot.title (“Sales by Year”)

myplot.savefig (“D:\Win – 8\myfig.png”)

‘,

@input_data_1 = @Query
Check the below step by step procedure to create a scatter plot using python.

1