May 3, 2018 at 7:59 am
My first attempt in R Server using SSIS to run R code predictive modeling. I'm running the following code inside an Execute SQL Task:
INSERT INTO [Staging].[Models].[Stg_TempRcodeOutputResults]
EXEC sp_execute_external_script
@language = N'R'
,@script = N'
library(zoo, warn.conflicts = FALSE);
library(xts, warn.conflicts = FALSE);
library(forecast, warn.conflicts = FALSE);
library(RODBC, warn.conflicts = FALSE);
suppressWarnings(zoo);
callStats <- data.frame(InputDataSet);
dayJobFuncStats <- callStats[callStats$NumDay == 1,];
PredictDataSet <- dayJobFuncStats;
my.ts <- ts(PredictDataSet$CallsOffered,frequency = 7);
ts.fit <- tslm(my.ts ~ trend + season);
fcast <- forecast(ts.fit,newdata = PredictDataSet);
OutputDataSet <- data.frame(fcast);
LastDate <- dayJobFuncStats$Date[length(dayJobFuncStats$Date)];
StartDate <- LastDate + 7;
EndDate <- (LastDate + (length(dayJobFuncStats$Date) * 7));
pDates <- seq(from = StartDate, to = EndDate, by = week);
OutputDataSet <- cbind(OutputDataSet, Date = rep(pDates, each = 1));
OutputDataSet <- cbind(OutputDataSet, NumDay = rep(PredictDataSet$NumDay, each = 1));
OutputDataSet <- cbind(OutputDataSet, JobFunction = rep(PredictDataSet$JobFunction, each = 1));
OutputDataSet <- data.frame(OutputDataSet);'
,@input_data_1 = N'SELECT * FROM [Staging].[Models].[Stg_TempMainFactData] fct WHERE fct.[JobFunction] = '''';'
,@output_data_1_name = N'OutputDataSet';
The line of code that's triggering the error:
Error in ts(PredictDataSet$CallsOffered, frequency = 7) :
'ts' object must have one or more observations
Calls: source -> withVisible -> eval -> eval -> ts
Error in ScaleR. Check the output for more information.
Error in eval(expr, envir, enclos) :
Error in ScaleR. Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
I'm lost on how to resolve this because the same R code works fine within Rstudio. Essentially, I'm running a Time Series model on a column called "CallsOffered" inside a dataset called "PredictDataSet". Thanks!!!
May 3, 2018 at 12:14 pm
oroman1026 - Thursday, May 3, 2018 7:59 AMMy first attempt in R Server using SSIS to run R code predictive modeling. I'm running the following code inside an Execute SQL Task:INSERT INTO [Staging].[Models].[Stg_TempRcodeOutputResults]
EXEC sp_execute_external_script
@language = N'R'
,@script = N'
library(zoo, warn.conflicts = FALSE);
library(xts, warn.conflicts = FALSE);
library(forecast, warn.conflicts = FALSE);
library(RODBC, warn.conflicts = FALSE);
suppressWarnings(zoo);
callStats <- data.frame(InputDataSet);
dayJobFuncStats <- callStats[callStats$NumDay == 1,];
PredictDataSet <- dayJobFuncStats;
my.ts <- ts(PredictDataSet$CallsOffered,frequency = 7);
ts.fit <- tslm(my.ts ~ trend + season);
fcast <- forecast(ts.fit,newdata = PredictDataSet);
OutputDataSet <- data.frame(fcast);
LastDate <- dayJobFuncStats$Date[length(dayJobFuncStats$Date)];
StartDate <- LastDate + 7;
EndDate <- (LastDate + (length(dayJobFuncStats$Date) * 7));
pDates <- seq(from = StartDate, to = EndDate, by = week);
OutputDataSet <- cbind(OutputDataSet, Date = rep(pDates, each = 1));
OutputDataSet <- cbind(OutputDataSet, NumDay = rep(PredictDataSet$NumDay, each = 1));
OutputDataSet <- cbind(OutputDataSet, JobFunction = rep(PredictDataSet$JobFunction, each = 1));
OutputDataSet <- data.frame(OutputDataSet);'
,@input_data_1 = N'SELECT * FROM [Staging].[Models].[Stg_TempMainFactData] fct WHERE fct.[JobFunction] = '''';'
,@output_data_1_name = N'OutputDataSet';The line of code that's triggering the error:
Error in ts(PredictDataSet$CallsOffered, frequency = 7) :
'ts' object must have one or more observations
Calls: source -> withVisible -> eval -> eval -> ts
Error in ScaleR. Check the output for more information.
Error in eval(expr, envir, enclos) :
Error in ScaleR. Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution haltedI'm lost on how to resolve this because the same R code works fine within Rstudio. Essentially, I'm running a Time Series model on a column called "CallsOffered" inside a dataset called "PredictDataSet". Thanks!!!
Chances R (pardon the pun :hehe: ), that you're making an assumption somewhere in that code that relies on RStudio, and that SSIS does not reproduce. Whatever the reference named "ts" is, that's where your grief is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 12:36 pm
Well, has anyone ran a time series model in R Server? I would think so! Are there any examples of R code that would run in Rstudio but not in R server? That would be news to me.
May 3, 2018 at 12:48 pm
oroman1026 - Thursday, May 3, 2018 12:36 PMWell, has anyone ran a time series model in R Server? I would think so! Are there any examples of R code that would run in Rstudio but not in R server? That would be news to me.
Have you validated that the Time Series is readily available to the code in SSIS in any way other than trying to reference it in this code? Is there any chance that there's some form of context issue, meaning that an expression or reference in the code would not be quite the same under SSIS execution? Something has to be causing the error, and while I know pretty much nothing about R, I'm pretty good at asking questions and questioning assumptions. Was hoping that might help you find the cause and be able to fix it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 1:11 pm
Do you know of a method to test if certain R code functions are working properly on the server?
May 3, 2018 at 1:17 pm
oroman1026 - Thursday, May 3, 2018 1:11 PMDo you know of a method to test if certain R code functions are working properly on the server?
Create an SSIS package and implement code that uses such functions and just outputs something super simple. If it fails, you'll know. Only test one thing at a time.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply