CODEWITHSUNDEEP
rlinuxolap-cube
fulz
fulz

Reputation: 41

How to access Olap-Cubes from R on Linux

I want to access an Olap-Cube from R on Linux. Specifically I have a SQL-Server 2016 with Analysis Services and Cubes that I would like to access.

We have been accessing the cubes from R on windows using microsoft R and the olapR-package. And while there is a linux version of microsoft-R, it does not include the olapR-package.

We have installed the R-Services for our SQL-Server. And we did install the olapR package there (using the R-Server Binding), but it seems that it doesn't come with support for mrsdeploy.

What other options do I have to access the cubes from R on linux? We heard about the Revoscaler Package, but don't know how to use it on Cubes. Maybe we could post an sql-script containing r-code to the server and have the sql-server execute the r-code? I didn't find any help on this approach though.

Any help is appreciated.

Upvotes: 3

Views: 2101

Answers (1)

fulz
fulz

Reputation: 41

In our case it does work by embedding the olapR-code within T-SQL within RODBC-Code.

library(RODBC)
my_server="<server>"
my_db="<database>"
my_username="<myusername>"
my_pwd="<mypassword>"

db <- odbcDriverConnect(paste0("DRIVER={SQL Server};
                               server=",my_server,";
                               database=",my_db,";
                               uid=",my_username,";
                               pwd=",my_pwd))

sql="exec sp_execute_external_script
    @language =N'R',
    @script=N'
    library(olapR)

    cnnstr <- \"Data Source=<server>; Provider=MSOLAP; initial catalog=<AnalysisService>; User Id=<domain>\\\\<user>; Password=<myPassword>\"
    olapCnn <- OlapConnection(cnnstr)
    mdx <- \" <MDX-Query> \"
    OutputDataSet <- execute2D(olapCnn, mdx)';"

df <- sqlQuery(db,sql)

Please note the quadruple backslash in domain\user.

Please note that the analysis services login is not necessarily the same as the SQL-login

Please note that the SQL user must have the rights to execute external scripts: 

GRANT EXECUTE ANY EXTERNAL SCRIPT TO [<user>]

It could be improved some more by using a "with result sets" statement, but it works.

Upvotes: 1

Related Questions