Read SQL Server OLAP cube from R stats compatibility 1400

0
votes

I have 2 tabular cubes on SSAS - one created at compatibility 1200 (Sql2016) and one created with compatibility 1400 (Sql2017).

I can browse 1200 cubes with the following code :

library(olapR)
cnnstr <- "Data Source=localhost; Provider=MSOLAP;"
olapCnn <- OlapConnection(cnnstr)

#Exploring Cubes
explore(olapCnn)

explore(olapCnn, "Wide World Importers Model")

qry <- Query()
cube(qry) <- "[Wide World Importers Model]"


mdx <- " SELECT NON EMPTY { [Measures].[Total Excluding Tax] } ON COLUMNS, NON EMPTY { ([Employee].[Employee Name].[Employee Name].ALLMEMBERS ) }  ON ROWS FROM [Wide World Importers Model]"

result2 <- execute2D(olapCnn, mdx)

I'm trying to query the 1400 cube with the MDX below, note the cube is called DemoCube but the MDX query created with SSMS refers to "Model" in the FROM CLAUSE.

With the 1200 cube the cube name is in the FROM clause.

SELECT NON EMPTY { [Measures].[Sum of Profit] } ON COLUMNS, NON EMPTY { ([Dimension City].[City].[City].ALLMEMBERS ) } ON ROWS FROM [Model]

Note, I can't event find the DemoCube with 

explore(olapCnn)

Note, query above works fine in SSMS.

I'm thinking I need to tweak the connection string to get this working, any ideas?

1
rssas

1 Answers

0
votes

I found a solution by putting the cube name in the connection :

cnnstr <- "Data Source=localhost;Provider=MSOLAP;Initial Catalog=DemoCube"

I then had to change : 

cube(qry) <- "Model"

This got things working.