You can execute R language scripts in Bold BI by creating a stored procedure in the Microsoft SQL Server. Bold BI allows you to connect the SQL stored procedure with the R script by following these steps.
You need to create a stored procedure with R scripts in the Microsoft SQL Server using the following steps to execute the script in Bold BI.
Launch the Microsoft SQL Server Management Studio
in Administrator
mode and execute the following queries to enable running R scripts in the Microsoft SQL Server.
EXEC sp_configure 'show advanced options', 1
GO
sp_configure 'external_scripts_enabled', 1
GO
RECONFIGURE;
After executing the above queries, open Services
and Right-click the SQL Server
, and click Restart
to restart the Microsoft SQL Server.
Now, you can create stored procedures with R scripts in the Microsoft SQL Server.
Here, a stored procedure is created with an R script for fetching records from the Releases
table.
Example query:
CREATE PROCEDURE ReleaseDetails
AS EXECUTE sp_execute_external_script @language = N'R',
@script = N'OutputDataSet<-InputDataSet',
@input_data_1 = N'SELECT ReleaseId, ReleaseVersion, StartDate FROM Releases;'
WITH RESULT SETS(([ID] INT, [Version] VARCHAR(MAX), [Date] DATETIME));
GO
Refer to the Connecting Bold BI to SQL data source to successfully connect to it.
Drag and drop the created stored procedure from the stored procedure schema in the data design view page.
While dragging the stored procedure, select the Internal Cache
option in the Parameters
window and click OK.
You can view the fetched data in the data grid by clicking Update.
Click Save
to save the data source with a relevant name to proceed with designing a dashboard.