December 16, 2015 at 1:12 am
nick.dale.burns (12/15/2015)
chris.smith 3432 (12/15/2015)
Hi Nick,1. Embedding R in a stored procedure seems to be really useful when there is limited (i.e. no) required interaction. For example, a defined model that needs to be trained.
Please, is it possible to do that in SQL 2012 as well or is it just possible in SQL 2016? What about SSIS? Is it possible to train a model in SSIS?
December 16, 2015 at 1:36 am
Kyrilluk (12/16/2015)
nick.dale.burns (12/15/2015)
chris.smith 3432 (12/15/2015)
Hi Nick,1. Embedding R in a stored procedure seems to be really useful when there is limited (i.e. no) required interaction. For example, a defined model that needs to be trained.
Please, is it possible to do that in SQL 2012 as well or is it just possible in SQL 2016? What about SSIS? Is it possible to train a model in SSIS?
Hi Kyrilluk,
Unfortunately it is not possible to have R integrated with SQL Server 2012. You can connect between R and SQL Server 2012 with an ODBC connection (just like in this tutorial), but the integration of R is a new feature of SQL Server 2016.
SSIS - that's an interesting question! I am quite sure that you cannot train a model in SSIS. Though I imagine there would be some way to hack something together. But I can't imagine why you would want to... SSIS is a data pipeline, if you want to model data then you are better off doing it either end of the pipeline, not within it directly.
Hope this answers your questions??
Nick
December 16, 2015 at 2:53 am
nick.dale.burns (12/16/2015)
Kyrilluk (12/16/2015)
nick.dale.burns (12/15/2015)
chris.smith 3432 (12/15/2015)
Hi Nick,1. Embedding R in a stored procedure seems to be really useful when there is limited (i.e. no) required interaction. For example, a defined model that needs to be trained.
Please, is it possible to do that in SQL 2012 as well or is it just possible in SQL 2016? What about SSIS? Is it possible to train a model in SSIS?
Hi Kyrilluk,
Unfortunately it is not possible to have R integrated with SQL Server 2012. You can connect between R and SQL Server 2012 with an ODBC connection (just like in this tutorial), but the integration of R is a new feature of SQL Server 2016.
SSIS - that's an interesting question! I am quite sure that you cannot train a model in SSIS. Though I imagine there would be some way to hack something together. But I can't imagine why you would want to... SSIS is a data pipeline, if you want to model data then you are better off doing it either end of the pipeline, not within it directly.
Hope this answers your questions??
Nick
Yes, thank you.
December 16, 2015 at 3:38 am
Thanks Nick,
I have another Error:
> by_region_filter <- with(majorSalesData, table(SalesTerritoryRegion, ProductKey))
> results_filter <- CA(by_region_filter, graph=FALSE)
Error in data.frame(value, row.names = rn, check.names = FALSE, check.rows = FALSE) :
row names supplied are of the wrong length
>
Thanks,
Yogs
December 16, 2015 at 10:56 am
yogmangela (12/16/2015)
Thanks Nick,I have another Error:
> by_region_filter <- with(majorSalesData, table(SalesTerritoryRegion, ProductKey))
> results_filter <- CA(by_region_filter, graph=FALSE)
Error in data.frame(value, row.names = rn, check.names = FALSE, check.rows = FALSE) :
row names supplied are of the wrong length
>
Thanks,
Yogs
Grrrr! Thanks Yogs - there was a line missing in the original article - oops! Working code is below for you (note the melt() function).
Regards,
Nick
strength <- data.frame(results$col$contrib[, 1:2])
strength$ProductKey<- rownames(strength) #product_contributions
strength <- melt(strength, "ProductKey")
major_products <- strength[strength$value > 1.5, "ProductKey"]
idx_major <- which(salesData$ProductKey %in% major_products)
majorSalesData <- salesData[idx_major, ]
by_region_filter <- with(majorSalesData, table(SalesTerritoryRegion, ProductKey))
results_filter <- CA(by_region_filter, graph=FALSE)
products <- data.frame(results_filter$col$coord)
regions <- data.frame(results_filter$row$coord)
## get the product names for visualisation
product_names <- unique(salesData[, c("ProductKey", "EnglishProductName")])
get_product_names <- function (key) product_names$EnglishProductName[which(product_names$ProductKey==key)]
## Finally, visualise
ggplot(products, aes(x=Dim.1, y=Dim.2)) +
geom_text(data=products, aes(x=Dim.1, y=Dim.2),
label=sapply(rownames(products), get_product_names),
colour="darkblue",
size=2,
alpha=0.7) +
geom_point(data=regions, aes(x=Dim.1, y=Dim.2), colour="red", shape=4) +
geom_text(data=regions, aes(x=Dim.1, y=Dim.2), label=rownames(regions), colour="red", size=4, vjust=-1,alpha=0.4) +
theme_bw() +
ggtitle("Region : Product")
December 17, 2015 at 4:36 am
Thanks Nick,
This is great,
I had a question: Is it possible to extract Windows-log from event log and visualize some short of graph. (i.e login/ logoff time, Domain, LogonID etc)
December 17, 2015 at 7:47 am
I was able to immediately think of two use cases in my field of work, from your demonstrated market basket analysis, applied it, and found it to be quite useful. I will be digging into FactoMineR even further to see what other goodies the package has that I may stumble onto. Thanks for the intro.
😎
December 17, 2015 at 12:08 pm
yogmangela (12/17/2015)
Thanks Nick,This is great,
I had a question: Is it possible to extract Windows-log from event log and visualize some short of graph. (i.e login/ logoff time, Domain, LogonID etc)
Hi Yogs,
I have no doubt that something along the lines of what you described is possible. I imagine there would be a lot of people interested in data mining event logs and finding informative ways to visualise the information. Let me know if you come up with anything cool!
Regards,
nick
December 17, 2015 at 12:10 pm
INCREDIBLEmouse (12/17/2015)
I was able to immediately think of two use cases in my field of work, from your demonstrated market basket analysis, applied it, and found it to be quite useful. I will be digging into FactoMineR even further to see what other goodies the package has that I may stumble onto. Thanks for the intro.😎
Hi,
Great to hear of other people picking this stuff up and applying it to their own problems!
Regards,
Nick
December 18, 2015 at 10:05 am
Created a view in SQL 2014 but when I access it via the sqlfetch command it raises a "table not found on channel".
Pasting the same sql code used in creating the view into the R script works as expected.
SSMS refreshes, RStudio workspace clearing, shutting down and restarting both RStudio and SSMS have no effect.
Any clues, hints etc. ?
December 18, 2015 at 11:15 am
Try an alternative method, sqlQuery, just for giggles, and see what happens.
salesData <- sqlQuery(SQLconnection, paste("select * from dbo.MyViewName"))
December 18, 2015 at 11:43 am
Did that. Mentioned in the original post
December 18, 2015 at 11:56 am
maris 99142 (12/18/2015)
Did that. Mentioned in the original post
Must be missing it somewhere? I don't see where you said you used sqlQuery rather than sqlFetch and that you performed a select query from the view. I do see you mentioned using the sql code matching the sql inside the view, but that's not the same thing as selecting from the view. My apologies if it's in some other post and I'm missing it.
December 18, 2015 at 12:48 pm
Sorry. Shoulda said sqlquery not fetch. Query access works great, view fetch doesn't. The article does reference SQL Server 2016 which is the next thing I will try.
December 18, 2015 at 1:29 pm
maris 99142 (12/18/2015)
Sorry. Shoulda said sqlquery not fetch. Query access works great, view fetch doesn't. The article does reference SQL Server 2016 which is the next thing I will try.
I tested 2008r2 and 2012 without issue. I created a simple to test with;
create view dbo.frog as select this = 1
Then I ran this fetch, and it worked;
t2 <- sqlFetch(mysrvr, "dbo.frog")
Maybe you have special character in your view name? Maybe a reserved word or something? gl.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply