With the release of SQL Server 2016 CTP3 the inclusion of R has been a powerful and welcome addition. As R can now be directly queried from inside SQL Server Management Studio (SSMS) one can now use the data outputs from R in SQL Server Reporting Services (SSRS) thereby utilising the power of R in the convenience of SSRS.
This blog will guide you through the process of creating a simple report in SSRS using data from R.
As demonstrated in a previous blog, it is very easy to begin using R within SQL Server and this is no different.
First of you will need your SQL R Script, for which I’m producing a simple K Means cluster of employees in the Adventure Work Data Warehouse. Then you will want to wrap that query inside a stored procedure.
CREATE PROCEDURE dbo.spKMeansEmployee
AS
BEGIN
EXECUTE sp_execute_external_script
@language = N’R’,
@script = N’ClusterCount <- 4;
df <- data.frame(InputDataSet);
ClusterFeatures <- data.frame(df$BaseRate, df$VacationHours, df$SickLeaveHours, df$SalaryFlag);
ClusterResult <- kmeans(ClusterFeatures, centers = ClusterCount, iter.max = 10)$cluster;
OutputDataSet <- data.frame(df, ClusterResult);’,
@input_data_1 = N’SELECT
EmployeeKey,
BaseRate,
VacationHours,
SickLeaveHours,
CAST(SalariedFlag AS VARCHAR(1)) AS SalaryFlag
FROM dbo.DimEmployee;’
WITH RESULT SETS (( EmployeeKey INT NOT NULL,
BaseRate MONEY NOT NULL,
VacationHours INT NOT NULL,
SickLeaveHours INT NOT NULL,
SalaryFlag VARCHAR(1) NOT NULL,
ClusterResult INT NOT NULL
));
END
The next step is to create a new report in Visual Studio and add a new Data Source.
Then create a dataset.
And link that dataset to a new report.
Then build the report how you want, using that dataset. This is the quick output I’ve opted for as you can quickly analyse employees based on the cluster they are in.
As you can see, and hopefully reproduce, it’s a very quick and relatively easy process that allows you to make business decisions by utilising the combined powerful capabilities of R and SQL.
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb
Pretty Power BI – Creating Dynamic Histograms
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb
Top Tips to Pass the Databricks Certified Data Engineer Professional Exam
Having recently passed the Databricks Certified Data Engineer Professional exam, this blog post covers some
Jan
Python vs. PySpark Navigating Data Analytics in Databricks – Part 1
Introduction When it comes to conquering the data analytics landscape in Databricks, two heavyweights, Python
Jan
Impact of AI on Business Analysis
Artificial intelligence (AI) is rapidly transforming our world, and this blog post concentrates on the
Jan
Creating Clickbait Using Python
In 2023, about 5 billion people used the internet. With so many people contributing and
Dec
A Brief Overview of Security in Microsoft Fabric
Where Fabric Sits in the Hierarchy As you are probably aware, Microsoft Fabric is Microsoft’s
Dec