Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Accesing the Power BI data cube

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

 

 

2 REPLIES 2
dax
Community Support
Community Support

Hi darshan, 

I am not familiar with R , I will discuss this with other engineers, I will inform you as soon as I get it. So if possible, could you please inform me the detailed error information? 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Zoe,

 

Thank you for the reply. I had attached an image but dont see it now. 

Anyway please see the image below

1st image is the input

2nd image is the error

r script olap  input.PNGr script olap  error.PNG

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors