Yes, you read that right, it is possible where you have shared Power BI semantic model with an external (guest user in Entra ID) they can now connect to the semantic model using Excel.
This has been around for quite a while I just did not know about it, here is the Microsoft documentation: Semantic model connectivity and management with the XMLA endpoint in Power BI – Power BI | Microsoft Learn
The first thing to note is that this currently only works in the Excel Desktop App.
I did try using Excel on the Web and unfortunately that did not work.
I then created my Excel file, uploaded to SharePoint Online and tried to interact with the Excel Pivot table and that too did not work.
Ok enough of the limitations let me show you how to get it working.
The first step is to share the semantic model and configure it with the right permission.
I am going to share my semantic model called “A-HC”
- I then right clicked on my semantic model
- Then clicked on Manage Permissions
Next, I then shared the semantic model with the build permissions as shown below and clicked on Grant access.
I could then see the permissions were granted.
Now to connect to the Semantic Model I need to get the XMLA End Point from my App Workspace.
I clicked on Workspace Settings.
- I then clicked on License info.
- And then copied the connection link.
This is where the change needs to be made for an external user to connect to my semantic model.
NOTE: This is what you need to change to ensure that your external user can connect to your Semantic model.
I take the existing connection link “powerbi://api.powerbi.com/v1.0/myorg/PPU%20Space%20Testing”
And I modify the “myorg” to my domain name, which in my example is fourmoo.com, so the new connection link will be:
powerbi://api.powerbi.com/v1.0/fourmoo.com/PPU%20Space%20Testing
I then went into Excel.
- I clicked on Data in the Ribbon.
- Then I selected “From Database”
- And selected “From Analysis Services”
I was then prompted to put in the server’s name which will be my modified URL as highlighted below.
Once I had successfully authenticated, I could then see my semantic model in the database name as shown below.
NOTE: If you do not see your semantic model in the list, make sure that if Row Level Security is enabled that the External user belongs to a Role.
I then connected to the semantic model and created a pivot table as shown below.
In this blog post I have shown you how an external user can connect to your semantic model using Excel.
I hope you have found this useful as I know it will open a lot of opportunities to share data with external customers.
Be the first to comment