{"id":70148,"date":"2017-02-28T09:16:06","date_gmt":"2017-02-28T09:16:06","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=70148"},"modified":"2026-03-09T10:46:00","modified_gmt":"2026-03-09T10:46:00","slug":"using-r-package-within-sql-server-real-time-analysis-power-bi","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-r-package-within-sql-server-real-time-analysis-power-bi\/","title":{"rendered":"R in SQL Server: Forecasting with Power BI"},"content":{"rendered":"\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/machine-learning\/sql-server-machine-learning-services?view=sql-server-ver17\" target=\"_blank\" rel=\"noopener\">SQL Server&#8217;s Machine Learning Services<\/a> (formerly R Services, available since SQL Server 2016) lets you execute R scripts directly inside the database engine using sp_execute_external_script. This means you can leverage R packages for statistical analysis &#8211; forecasting, anomaly detection, classification &#8211; while keeping your data inside SQL Server&#8217;s security and performance framework. Combined with <a href=\"https:\/\/learn.microsoft.com\/en-us\/power-bi\/connect-data\/desktop-directquery-about\" target=\"_blank\" rel=\"noopener\">Power BI&#8217;s DirectQuery mode<\/a>, you get a complete pipeline: SQL Server stores and secures the data, R performs the analysis, and Power BI visualizes the results in near real-time. This article demonstrates the full workflow using a sales forecasting example.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-introduction\">Introduction<\/h2>\n\n\n\n<p>SQL Server 2016 allows us to pass filtered data to R to perform complex statistical analysis, graphing and calculation, and even enables us to run the many R packages. Power BI is a powerful tool for data analytics and visualization. The integration of the three components SQL, R and Power BI can provide us with a complete BI solution.<\/p>\n\n\n\n<p>Although it has long been possible to connect to a SQL database from R so that an R script can fetch the data, SQL Server 2016 allows us the alternative of running an R script from SQL Server. In the example that follows, we will demonstrate how to do this. We\u2019ll use Sales data stored in a SQL Server database to calculate an \u2018employee sales forecast\u2019 using R. We will execute R script within SQL Server to utilize SQL Server database features such as data security, multi-threading and the handling of a large data set. The R package will perform a statistical analysis on the data. We will finally use Power BI desktop to analyze and visualize the employee sales and forecast data. PowerBI has a direct query feature to view and analyze the real-time data.<\/p>\n\n\n\n<p><strong>Prerequisite Software <\/strong><\/p>\n\n\n\n<p>To run this example, we will need to have the following installed before we start.<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>SQL Server 2016 with Microsoft R services<\/li>\n\n\n\n<li>SQL Server 2016 Management Studio<\/li>\n\n\n\n<li>Power BI desktop.<\/li>\n<\/ul>\n<\/div>\n\n\n<p>In our example, we will follow these steps.<\/p>\n\n\n<div class=\"block-core-list\">\n<ol class=\"wp-block-list\">\n<li>Enable External Script(R script ) in SQL Server<\/li>\n\n\n\n<li>Run Test script to check R and SQL connectivity.<\/li>\n\n\n\n<li>Install Forecast and other dependent packages inside Microsoft R service folder<\/li>\n\n\n\n<li>Calculate the Forecast in R<\/li>\n\n\n\n<li>Using PowerBI , create Relation between two tables and visualize real time data using Power Bi desktop.<\/li>\n<\/ol>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-allow-the-running-of-external-scripts-the-r-script-in-sql-server\">Allow the running of External Scripts, the R script, in SQL Server.<\/h2>\n\n\n\n<p>To run R scripts from SQL Server, we need to change the value of the configuration item \u201cexternal script enabled\u201d from 0 to 1. Otherwise when you run R script from SQL Server, you will get an error.<\/p>\n\n\n\n<p>Open SQL Server Management Studio and then run the following command to check the current value.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">EXECUTE sp_configure 'external scripts enabled';<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"417\" height=\"181\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-48.png\" alt=\"\" class=\"wp-image-70149\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>The default configuration value is 0, and the run value is 0. Now, run below command to change \u2018<em>external script enabled\u2019<\/em> value from 0 to 1.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">EXECUTE sp_configure 'external scripts enabled', 1;\nGO\nRECONFIGURE;<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"416\" height=\"151\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-49.png\" alt=\"\" class=\"wp-image-70150\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now, close the Management Studio, Restart the SQL Service and re-open SQL Server Management Studio to run this command.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">EXECUTE sp_configure 'external scripts enabled';<\/pre>\n\n\n\n<p>Now the <strong>config_value<\/strong> should be 1 and the <strong>run_value<\/strong> should be 1.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"559\" height=\"244\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-50.png\" alt=\"\" class=\"wp-image-70151\"\/><\/figure>\n\n\n\n<p><strong>Read also:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/sql-server-database-growth-and-autogrowth-settings\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server database configuration<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-run-the-test-script-to-check-r-and-sql-connectivity\"><strong>Run the test script to check R and SQL connectivity.<\/strong><\/h2>\n\n\n\n<p>The <strong>sp_execute_external_script<\/strong> procedure is a built-in stored procedure in&nbsp; SQL Server. This stored procedure will call an R script. The meaning of each parameter is explained later on &nbsp;in this article when we come to run our &nbsp;R script rather than a sample test script .<\/p>\n\n\n\n<p>Open SQL Server Management Studio and run this minimal test script .<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">execute sp_execute_external_script      \n      @language = N'R'      \n    , @script = N' OutputDataSet &lt;- InputDataSet;'      \n    , @input_data_1 = N' SELECT 1 as Test ;'  \n    WITH RESULT SETS (([Col1] int NOT NULL));<\/pre>\n\n\n\n<p><em>Result<\/em><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"204\" height=\"178\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-51.png\" alt=\"\" class=\"wp-image-70152\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>After successfully getting this result, we can be confident of being able to run any R script from SQL Server, unless of course it references a package. Next we\u2019ll show you how to install an R package.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-install-forecast-and-other-dependent-packages-inside-microsoft-r-service-folder\"><strong>Install Forecast and other dependent packages inside Microsoft R service folder<\/strong><\/h2>\n\n\n\n<p>In this example, we will calculate the sales forecast using an R script. R is able to run packages for a wide range of purposes, and R run from SQL Server is no exception, so we need to be able to install the necessary R package in our machine. Because we are running the R script from SQL Server, we need to install the \u2018Forecast\u2019 package that it references. It is placed in a subfolder of the Microsoft SQL Server folder.<\/p>\n\n\n\n<p>Here are the steps to install the \u2018Forecast\u2019 library in SQL Server.<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>First locate SQL Server 2016 installation folder in your computer<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>For example, C:\\Program Files\\Microsoft SQL Server\\ MSSQL13.MSSQLSERVER2016<\/li>\n<\/ul>\n<\/div><\/li>\n\n\n\n<li>Inside MSSQL13.MSSQLSERVER2016 folder, you can see R_SERVICES\\library subfolder.<\/li>\n<\/ul>\n<\/div>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"995\" height=\"661\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-52.png\" alt=\"\" class=\"wp-image-70153\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now, we know the SQL server R service library folder location. The next step is to install Forecast library in the library folder that is used by SQL server R service.<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Go to C:\\Program Files\\Microsoft SQL Server\\ MSSQL13.MSSQLSERVER2016\\R_Services\\bin\\X64 location to find the R GUI application.<\/li>\n\n\n\n<li>Right-click on the R GUI and click on \u2018<em>Run with administrator\u2019<\/em>. This step will open the R GUI to run an R script. The purpose of our R script is simply to install the \u2018forecast\u2019 library.<\/li>\n<\/ul>\n<\/div>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"973\" height=\"568\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-53.png\" alt=\"\" class=\"wp-image-70154\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Now, we will run the following lines of code in the R GUI to install the \u2018forecast\u2019 package. We also need to install some dependent libraries to run the \u2018forecast\u2019 package.<\/li>\n<\/ul>\n<\/div>\n\n\n<p>In this code, lib.SQL is a variable to store our SQL server R service library folder path. In this path, we want to install the library<\/p>\n\n\n\n<p>The <strong>install.packages<\/strong> command is to install the R package.<\/p>\n\n\n\n<p>The First argument of <strong>install.packages<\/strong> is the name of the package .<\/p>\n\n\n\n<p>The second argument is to specify the folder location, where we want to install the library.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">lib.SQL&lt;-\"C:\\\\Program Files\\\\Microsoft SQL Server\\\\MSSQL13.MSSQLSERVER2016\\\\R_SERVICES\\\\library\"\ninstall.packages(\"forecast\",lib=lib.SQL)\ninstall.packages(\"timeDate\",lib=lib.SQL)\ninstall.packages(\"zoo\",lib=lib.SQL)\ninstall.packages(\"colorspace\",lib=lib.SQL)\ninstall.packages(\"fracdiff\",lib=lib.SQL)\n<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1137\" height=\"178\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-54.png\" alt=\"\" class=\"wp-image-70155\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Once we have followed these steps to install the Forecast library, it is time to check the R \u2018Forecast\u2019 library connectivity in SQL Server.<\/p>\n\n\n\n<p>Run this command in SQL Server Management Studio to make sure that all \u2018Forecast\u2019-related packages are installed properly.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">execute sp_execute_external_script      \n      @language = N'R'      \n    , @script = N'\t\tlibrary(\"forecast\") \n  \t\t\t\tOutputDataSet &lt;- InputDataSet;'      \n    , @input_data_1 = N' SELECT 1 as Test ;'  \n    WITH RESULT SETS (([Col1] int NOT NULL));<\/pre>\n\n\n\n<p>If you get the result in SQL Server Management Studio as shown below, it means that we have properly installed the R forecast package in SQL Server. If you receive some error message about a missing library, please install the missing library.<\/p>\n\n\n\n<p>Result<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"204\" height=\"178\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-55.png\" alt=\"\" class=\"wp-image-70156\"\/><\/figure>\n\n\n\n<p><strong>Read also:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/reporting-services-basics-overview-and-installation\/\" target=\"_blank\" rel=\"noreferrer noopener\">SSRS overview for report delivery<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-calculate-the-forecast-in-r\"><strong>Calculate the Forecast in R<\/strong><\/h2>\n\n\n\n<p>So far, we have set up our R environment in SQL Server. Now, we will run a program to calculate the sales employee forecast of sales, using some test data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-sql-script-to-create-employee-table\">SQL Script to create employee table. :<\/h3>\n\n\n\n<p>The <strong>EmpInfo<\/strong> Table contains Employee Sales data. Run this script in SQL Server Management Studio.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE [dbo].[empInfo](\n  \t[Name] [varchar](50) NOT NULL,\n  \t[Year] [int] NOT NULL,\n  \t[Sales] [int] NULL,\n  \t[Month] [nvarchar](50) NULL\n  )<\/pre>\n\n\n\n<p><strong>Read also:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/powershell\/how-to-use-parameters-in-powershell\/\" target=\"_blank\" rel=\"noreferrer noopener\">PowerShell parameters for script automation<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-sql-script-to-insert-data-in-employee-table\">SQL Script to Insert data in employee table.<\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">  Insert into empinfo ([name],Year,Sales,Month) values('Alex',2016,1300,1)\n    insert into empinfo ([name],Year,Sales,Month) values('Alex',2016,1500,2)\n    insert into empinfo ([name],Year,Sales,Month) values('Alex',2016,1700,3)\n    insert into empinfo ([name],Year,Sales,Month) values('Alex',2016,1800,4)\n    insert into empinfo ([name],Year,Sales,Month) values('Alex',2016,1830,5)\n    insert into empinfo ([name],Year,Sales,Month) values('Alex',2016,1820,6)\n    insert into empinfo ([name],Year,Sales,Month) values('Alex',2016,1700,7)\n    insert into empinfo ([name],Year,Sales,Month) values('Kim',2016,400,1)\n    insert into empinfo ([name],Year,Sales,Month) values('Kim',2016,300,2)\n    insert into empinfo ([name],Year,Sales,Month) values('Kim',2016,600,3)\n    insert into empinfo ([name],Year,Sales,Month) values('Kim',2016,650,4)\n    insert into empinfo ([name],Year,Sales,Month) values('Kim',2016,700,5)<\/pre>\n\n\n\n<p>Now, EmpInfo table will look like below<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"544\" height=\"361\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-56.png\" alt=\"\" class=\"wp-image-70157\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-stored-procedure-to-calculate-the-forecast\">Stored procedure to calculate the forecast:<\/h3>\n\n\n\n<p>The <strong>Sp<\/strong>_<strong>EmpInfo<\/strong> Stored procedure will calculate the employee Forecast using R. <strong>dbo.empInfo<\/strong> table contains all of the employee data. Here, we will filter the employee data for an employee and calculate the forecast for an employee.<\/p>\n\n\n\n<p>What the stored procedure covers:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><strong>@inputquery:<\/strong> Contains filtered SQL data.<\/li>\n\n\n\n<li><strong>@input_data_1<\/strong>: Pass the SQL data table to R data frame InputDataSet.<\/li>\n\n\n\n<li><strong>InputDataSet:<\/strong> <strong>@input_data_1<\/strong> is mapped to the R input dataset \u201cInputDataSet\u201d<\/li>\n\n\n\n<li><strong>@script:<\/strong> Consumes SQL data and runs the R script. In this example, <strong>@script<\/strong> uses the forecast library to calculate the forecast and return the output data to SQL.<\/li>\n\n\n\n<li><strong>OutputDataSet:<\/strong> Pass the R script output result set to SQL Server. R script output needs to be a data frame.<\/li>\n\n\n\n<li><strong>StructTS: <\/strong>this function fits the structural model for a time series.<\/li>\n\n\n\n<li><strong>Forecast <\/strong>function: this calculates the point in time forecast with 70 to 90 percent accuracy result.<\/li>\n\n\n\n<li><strong>RESULT SETS<\/strong> : Result sets have SQL Server output. If you are not sure for the output type, you can write WITH RESULT SETS UNDEFINED;<\/li>\n<\/ul>\n<\/div>\n\n\n<p>In the stored procedure below, first we will filter the data in month order and then we convert the salary field into a numeric one.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE  procedure [dbo].[sp_EmpInfo]       \n    @name varchar(500)  \n  AS\n  BEGIN       \n  declare @inputquery nvarchar(max)\n  set @inputquery='select Sales from [dbo].[empInfo] where Name='''+@name +'''' +' order by Month;'\n  Execute sp_execute_external_script        \n      @language = N'R'        \n      ,@script = N' inp_dat=InputDataSet \n  \t\t      library(\"forecast\")\n  \t\t     SalesData &lt;-as.numeric(inp_dat$Sales)\n  \t\t     fit &lt;- StructTS(SalesData, \"trend\")\n  \t\t     SalesForecast&lt;- forecast(fit,h=1, level = c(70, 90))\n  \t\t     OutputDataSet &lt;- data.frame(SalesForecast)\n  ;'         \n  \t,@input_data_1=@inputquery\n  WITH RESULT SETS((PointForecast Float,Low70 float,high70 float,Low90 float,high90 float)) ;   \n   END<\/pre>\n\n\n\n<p>Now execute the <strong>dbo.sp_EmpInfo<\/strong> stored procedure.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">[dbo].[sp_EmpInfo]   'Kim'<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-processing-the-result-of-the-forecast\">Processing the Result of the Forecast.<\/h3>\n\n\n\n<p>From this set, we will focus on the <strong>PointForecas<\/strong>t,. <strong>Low 70-90<\/strong> and <strong>High 70-90<\/strong> fields to provide various accuracy levels.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"732\" height=\"228\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-57.png\" alt=\"\" class=\"wp-image-70158\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>SQL script to create forecast table : Create the <strong>dbo.empForecast <\/strong>table to store the forecast data.<\/li>\n<\/ul>\n<\/div>\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE [dbo].[empForecast](\n  \t[name] [varchar](50) NOT NULL,\n  \t[forecast] [float] NULL,\n  \t[Month] [int] NULL\n  )<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-stored-procedure-to-store-forecasted-data\">Stored procedure to store forecasted data:<\/h3>\n\n\n\n<p>This stored procedure will call the <strong>sp_EmpInfo<\/strong> stored procedure and capture the R result set into SQL table variable .After some processing, we will store the table variable data in <strong>empForecast<\/strong> table.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Create  Procedure [dbo].[sp_EmpForecast]\n  @name varchar(100)\n  as\n  Begin\n  declare @userData TABLE(\n                          Forecast float  NULL,\n  \t\t\t   LowBottomPoint float null,\n  \t\t\t   HighBottomPoint float null,\n                          LowUpperPoint float null,\n  \t\t\t   HighUpperPoint float null\n                         )\n  INSERT INTO @userData EXEC [sp_empInfo]  @Name\n  select * from @userdata\n  declare @forecast float\n  select @forecast= max(Forecast) from @userData\n  delete from dbo.empForecast where [name]=@name\n  declare @Month float\n  select @Month=Max(Month)+1 from dbo.empInfo where name=@name\n  insert into dbo.empForecast(name,forecast,Month) values( @name,@forecast,@Month)\n  End\n  GO<\/pre>\n\n\n\n<p>Now we are ready to execute stored procedure to calculate the forecast for an employee. Run below lines of code to calculate the forecast for employee Alex and Kim.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Exec [dbo].[sp_EmpForecast] 'Alex'\n  Go\n  Exec [dbo].[sp_EmpForecast] ' Kim'\n  go<\/pre>\n\n\n\n<p>Run the SELECT statement below to verify the employee forecast data.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from dbo.empForecast<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"705\" height=\"454\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-58.png\" alt=\"\" class=\"wp-image-70159\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-power-bi\">POWER BI<\/h2>\n\n\n\n<p>So far, we have calculated the Forecast in SQL server using R . Now we need a tool to analyze and visualize the forecast data. PowerBi is an interactive visualization tool to analyze the data. PowerBI will support multiple data sources. In PowerBI, we can define the relationship between two tables. In the current example, we will use a SQL server database as a data source.<\/p>\n\n\n\n<p><strong>Read also:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-the-dax-calculate-and-values-functions\/\" target=\"_blank\" rel=\"noreferrer noopener\">DAX CALCULATE for Power BI measures<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-create-relation-between-two-tables-and-analyze-real-time-data-using-power-bi-desktop\">Create Relation between two tables and analyze real time data using Power Bi desktop.<\/h3>\n\n\n\n<p>Connect the Data Source and Load the Data :<\/p>\n\n\n\n<p>Open Power BI desktop. Click on \u2018<em>Get data<\/em>\u2019 and select SQL Server as the data source.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"528\" height=\"343\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-59.png\" alt=\"\" class=\"wp-image-70160\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now, select \u2018<em>Direct Query Option\u2019<\/em> and click \u2018<em>Ok<\/em>\u2019. This option will hit the database table real time. By this I mean that in general, when we connect to a data source, PowerBI loads the data into memory and displays the data. To reflect any data changes at source in the PowerBI application, we need to reload the PowerBI application.<\/p>\n\n\n\n<p>DirectQuery is a feature to display real-time data in a PowerBI application. Using the directquery feature, the PowerBI report will always have the latest real-time data. There is no need to reload the application to refresh the data.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"855\" height=\"411\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-60.png\" alt=\"\" class=\"wp-image-70161\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>During forecast calculation, we have created two tables; <strong>empinfo<\/strong> and <strong>empforecast<\/strong>. We will use those tables to visualize the forecast data.<\/p>\n\n\n\n<p>From the list as shown below, Select the <strong>empInfo<\/strong> and <strong>empForecast <\/strong>table and click <em>\u2018load\u2019<\/em>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"926\" height=\"848\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-61.png\" alt=\"\" class=\"wp-image-70162\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-define-relationship-join-between-tables\">Define Relationship (Join) between tables:<\/h3>\n\n\n\n<p>So far in PowerBI, we have selected the forecast-related tables. Now, we will create a relationship between tables. We will select key column names from each table to join the two tables. PowerBI allows you to create various types of joins. One to One, One to Many or many to One. We can also add new calculated measure.<\/p>\n\n\n\n<p>Click on Manage Relationship and click New<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1269\" height=\"791\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-62.png\" alt=\"\" class=\"wp-image-70163\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now select both the tables. Decide your key columns between two tables. In this case, I will join the tables using the <strong>Name<\/strong> field from <strong>empinfo <\/strong>table and <strong>name<\/strong> field from <strong>empforecast <\/strong>table.<\/p>\n\n\n\n<p>Click on the column \u2018<em>name<\/em>\u2019 on each table. The selected columns will be highlighted with a grey color and the join will be defined on gray highlighted fields. Once the relationship is defined, click \u2018<em>OK\u2019<\/em>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"765\" height=\"641\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-63.png\" alt=\"\" class=\"wp-image-70164\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now, if you click on \u2018<em>Manage Relationship\u2019<\/em>, it looks like the screenshot below. We have defined the join on the <strong>Name <\/strong>column between two tables. If I click on any value in the <strong>empinfo(name)<\/strong> field, the data in the <strong>empForecast(name)<\/strong> table will also get filtered.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"907\" height=\"295\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-64.png\" alt=\"\" class=\"wp-image-70165\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-create-visualization-in-powerbi\">Create Visualization in PowerBI:<\/h3>\n\n\n\n<p>After connecting to the data source, selecting the forecast tables and defining the relationship between the table fields in PowerBI, we are now all set to visualize our forecast data. PowerBI offers a rich visualization tool set to view and analyze the data.<\/p>\n\n\n\n<p>In this example, we will use a straight table object. We need two straight table objects. One straight table object is used to see the <strong>empinfo<\/strong> table fields and second object to see the <strong>EmpForecast<\/strong> table fields.<\/p>\n\n\n\n<p>Click on the first straight table object and drag fields from the <strong>empInfo <\/strong>table in the \u2018<em>Rows\u2019<\/em> and \u2018C<em>olumns\u2019<\/em> section.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"928\" height=\"560\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-65.png\" alt=\"\" class=\"wp-image-70166\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Click on the second straight table object and drag fields from the <strong>empForecast<\/strong> table in the \u2018<em>Rows\u2019<\/em> and \u2018C<em>olumns\u2019<\/em> section.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"840\" height=\"632\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-66.png\" alt=\"\" class=\"wp-image-70167\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now we can visualize data from two tables. Select the <strong>EmpForecast<\/strong> table and from the visualization panel drag the name field to the report filter. Now if you select \u2018Alex\u2019 from the filter pane, both tables in the report will show data only for Alex. By clicking on the top right corner of table, you should be able to export data in Excel.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"407\" height=\"115\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-67.png\" alt=\"\" class=\"wp-image-70168\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>In the visualization panel, there are options to Format the visualization object and define filters.<\/p>\n\n\n\n<p><img loading=\"lazy\" decoding=\"async\" width=\"48\" height=\"37\" class=\"wp-image-70169\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-68.png\"> This allows us to define columns, Rows and filters in report.<\/p>\n\n\n\n<p><img loading=\"lazy\" decoding=\"async\" width=\"40\" height=\"35\" class=\"wp-image-70170\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-69.png\"> This option allows us to format the table. I have enabled the title option and given a title to the object. In a similar way , you can change the background color, font color , position of object and many more things using the format option.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"763\" height=\"678\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-70.png\" alt=\"\" class=\"wp-image-70171\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Because we are using the Direct Query feature, any change in table values will, in real time, reflect in dashboard, without refreshing the dashboard.<\/p>\n\n\n\n<p>Let\u2019s see how DirectQuery feature is performing real time update.<\/p>\n\n\n\n<p>Open SQL server management studio and run below command to update kim\u2019s employee data for 5<sup>th<\/sup> Month, from Sales 700 to 1500 and re-calculate the forecast.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">update empinfo set sales=1500 where Name='Kim' and Month=5\n  go\n  Exec [dbo].[sp_EmpForecast] 'Kim'\n  go<\/pre>\n\n\n\n<p>Go back to PowerBI report and select the <strong>EmpForecast <\/strong>table. From the report filter ,select \u2018<em>Employee<\/em> <em>Name<\/em>=<em>Kim\u2019<\/em>. You will see that the Forecast value for Kim is updated.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1239\" height=\"731\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-71.png\" alt=\"\" class=\"wp-image-70172\"\/><\/figure>\n\n\n\n<p><strong>Read also:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/creating-a-business-intelligence-dashboard-with-r-and-asp-net-mvc-part-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">BI dashboard with R and ASP.NET MVC<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-conclusion\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<p>In this article, we have covered in detail how to run an R script from SQL Server. We\u2019ve demonstrated how to run the \u2018Forecast\u2019 package in R, but you should be able to run any algorithm, package or script in R within SQL Server and send the results back to SQL database. We have covered an overview of using PowerBI as an advanced visualization tool to do real time data analysis. I hope that this provides you enough information to start using these tools, and to use them to create a complete BI solution.<\/p>\n\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: Using an R Package within SQL Server with real-time analysis in Power BI<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do you run R scripts inside SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Use the sp_execute_external_script stored procedure with @language = N&#8217;R&#8217; and your R script as the @script parameter. You must first enable external scripts using sp_configure &#8216;external scripts enabled&#8217;, 1 and restart SQL Server. Input data comes from a SQL query via the @input_data_1 parameter, and results return as a SQL result set.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What R packages work with SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Any R package installed in SQL Server&#8217;s R library can be used. The standard library includes base R packages, and you can install additional packages (like forecast, ggplot2, tidyverse) into the R_SERVICES library path. Use install.packages() targeting the SQL Server R library directory, or use sp_execute_external_script to install from within SQL Server.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. How do you connect Power BI to R results in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Create a stored procedure that calls sp_execute_external_script to run your R analysis and return results as a table. In Power BI Desktop, use DirectQuery mode to connect to SQL Server and point to your stored procedure or a view wrapping it. DirectQuery ensures Power BI always shows the latest R analysis results without manual refresh.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Run R packages inside SQL Server using sp_execute_external_script for sales forecasting, then visualize results in Power BI with DirectQuery for real-time analysis.&hellip;<\/p>\n","protected":false},"author":281524,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[42439],"class_list":["post-70148","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70148","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/281524"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=70148"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70148\/revisions"}],"predecessor-version":[{"id":109024,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70148\/revisions\/109024"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=70148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=70148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=70148"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=70148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}