I would like to access the Power BI data model/cube (not sure if this is the right term). I would like to access the underlying data model, so that I get access to the measures and calculated models. We have Power BI Premium P1.
I can already do this when we use the analyze in excel option through .odc file. I have also accessed this data in R but I cannot make it work in the Power BI when I use the R Script option.
The script below works in the R Studio. It would be great if I can make this work in the Power BI because that would make accessing other reports easy and avoid duplication of work. The script below is from Microsoft. https://docs.microsoft.com/en-us/machine-learning-server/r-reference/olapr/olapr
library(olapR)
# Connect to a local SSAS default instance and the Analysis Services Tutorial database.
# For named instances, use server-name\\instancename, escaping the instance name delimiter.
# For databases containing multiple cubes, use the cube= parameter to specify which one to use.
cnnstr <- "Provider=MSOLAP;Integrated Security=ClaimsToken;
Persist Security Info=True;
Initial Catalog=*********************************;
Data Source=pbiazure://api.powerbi.com;MDX Compatibility=1;Safety Options=2;
MDX Missing Member Mode=Error;
Identity Provider=https://***************************,
https://******************,
**************************************;Update Isolation Level=2"
olapCnn <- OlapConnection(cnnstr)
# Approach 1 - build the mdx query in R
qry <- Query()
cube(qry) <- "Model"
#columns(qry) <- c("[Measures].[Internet Sales Count]", "[Measures].[Internet Sales-Sales Amount]")
#rows(qry) <- c("[Product].[Product Line].[Product Line].MEMBERS")
#slicers(qry) <- c("[Sales Territory].[Sales Territory Country].[Australia]")
#result1 <- executeMD(olapCnn, qry)
# Approach 2 - Submit a fully formed MDX query
mdx <- "SELECT { [Measures].[my_measure] } ON 0 ,
{ [Table].[dim].AllMembers } ON 1
FROM [Model] "
result2 <- execute2D(olapCnn, mdx)
The error I get when I run this script in Power BI is in the second image
