Using X4R package in R to connect to an SSAS (loca

Question

I am trying to figure out how to use X4R package to load a local data cube into R. I can load the cube into Excel, but am failing to get this to work with R.

My cube filename is "\scrsvr\Users\\Documents\Projects\Raw data\data.cub".

I tried the following code:

library(X4R)
handle<-xmlaConnect(url="\\scrsvr\\Users\\<MyName>\\Documents\\Projects\\Raw data\\data.cub")

which produces no error. However, trying to use xmlaDiscover results in the following error:

first argument is not an open XMLA handle

Can anyone help me to connect to my local cube? I am totally new to this, so perhaps someone has a working example that I could follow (with example data cube file)?

Thanks!

Answer 1

Ok, just in case anyone else needs to do this in future, I managed to solve my problem

# use some necessary packages
require(RDCOMClient)
require(data.table)
library(plyr)
library(Hmisc)

#create connection to SSAS datacube
con <- COMCreate("ADODB.Connection")
con[["ConnectionString"]] <- paste(
  "Provider=MSOLAP.5",
  "Data Source=\\\\scrsvr\\Users\\<MyName>\\Documents\\Projects\\Raw data\\data.cub",
    "Persist Security Info=True",
  sep = ";")
con$Open()

# define the MDX query here:
query = "SELECT ..."

rs <- COMCreate("ADODB.RecordSet")

# submit the MDX query to the cube
rs$Open(query, con)

rs$MoveFirst()    # move to the first row of the record set
nc <- rs$Fields()$Count()     # define number of columns

# get the data into a data array:
dd <- vector("list", length=nc)
dd <- rs$GetRows()  # get the raw data from the results

For the connection, I used the Provider ("MSOLAP.5") that was configured when I set up a data connection from MS Excel to the data cube. It all works and I can now query the database using MDX directly.