How to use R script in Power BI

In this post, I have embedded the video link to my you tube channel and in the video I have showed how to use R script in Power BI. I have also posted below the scripts shown in the video.

In the video, I have explained:

1)How to R script in Power BI
2)Example dataset - COVID-19 (2019-nCoV) github data repository by Johns Hopkins University. How to invoke this data dynamically using Rcurl.
3)How to configure to specific R version in R Studio & Power BI

4)Sample visualization in Power BI using R dataset





SQL Server Import/Export to Excel using Python script

Introduction

In previous article, we saw how to import/export excel to/from SQL Server by executing R script within T-SQL
In this post, let us see another similar approach to import excel into SQL Server and export SQL server data to excel by executing Python script within T-SQL.

There are some existing methods to do this using BCP, Bulk Insert, Import & Export wizard from SSMS, SSIS, Azure data factory, Linked server & OPENROWSET query and SQLCMD.

BULK INSERT statement, the BCP tool, or Azure Data Factory can't read Excel files directly   
BCP - Workaround has to be done to include the header while exporting
SSIS - Though it supports exporting to excel, with dynamic source & destination, handling mapping between source to target increases the complexity of the package
SQLCMD - Cannot export output in Excel file format


R & Python language extension was introduced in SQL Server 2016 & 2017 as part of machine learning. With support of R in Azure SQL database   and Java language extension support in SQL Server 2019 , this new approach can be used extensively as it easy, fast and flexible.


T-SQL : Data profiling in On-premise SQL Server / Azure SQL database

In this post, let us see how we can do data profiling on On-premise SQL Server / Azure SQL database tables using T-SQL script.

Data profiling provides below high level information about data:

  • Number of rows and size of the data in the object, date for the most recent update of the data and the object schema
  • Number of null records, distinct values for columns and data type of column
  • Minimum, maximum values for numeric columns and check for existence of special characters, leading/trailing spaces for columns of string data type 
Two stored procedures are created to generate data profiling output. Inside the stored procedure, iteration on each table columns are performed using system catalog views to apply the MIN, MAX and other functions on each column to get the data profiling output



1) usp_DataProfiling

2) usp_DataProfiling_Metadata