You can go to the
link of the webinar to re-view the
session given by Christian Wade last week.
Theory
If you just want to
see read/write XMLA endpoints in action, skip to the Practice section of this post.
Otherwise, if you
never heard of it or just wanna read up on the specifics, keep reading here and
we'll get there eventually :-)
Preview Feature
To start with a side-note disclaimer:
XMLA Endpoint
If you're not
familiar with XMLA, you can read all about it at the
XML for Analysis (XMLA) Reference. Basically, it's the protocol for
communicating between client applications and the engine that manages the
workspaces and datasets.
These communications
are through what are commonly referred to as XMLA endpoints. XMLA is the same
communication protocol used by the Microsoft Analysis Services engine, which
under the hood, runs Power BI's semantic modeling, governance, lifecycle, and data
management.
Additional Functionality
Through these
endpoints, Visual Studio and open source community tools like Tabular Editor
are enabled to provide additional data modeling and deployment capabilities
(supported by the Analysis Services engine, but not yet in Power BI Desktop),
like:
Enable Read Write
Follow the steps
outlined
here
to enable XMLA Read Write in the Power BI Admin Portal for your Premium
capacity.
In Practice
To
test the new read-write functionality I started with an existing Tabular
project in Visual Studio (SSDT) and see if I can deploy this to a Power BI
Premium workspace. This project currently has a compatibility level of SQL Server 2014 (1103).
One thing to
remember is the current SSAS-version of my localhost, this is 1400. We'll come
back to this later, as it seemed rather important…
Upgrade Existing Model
So I started
with opening the model (.bim-file) to change it to SQL Server 2019 / Azure Analysis Services (1500).
That leaves
me with the following error:
A bit
strange at first, because I don't know why this wouldn't be possible. I tried
upgrading one compat-level at a time. 1200 worked fine, as did 1400. But 1500
kept failing. Then it struck me that I set up the Workspace Server to my localhost,
which is level 1400.
So to fix
this, in the properties of the model, I changed the setting Integrated Workspace Mode to True. And after that I could change the
compatibilitiy level to 1500.
If you
haven't disabled the pop-up, you can also set this at the startup of your
project:
Deploy to Premium Workspace
If you don't know
yet how to connect to a workspace on a dedicated capacity read up on it
here
and then come back.
In the properties of
the project file you can set the Deployment Server to the Power BI URL of your
workspace.
Security
In
addition to the XMLA Endpoint property being enabled to read-write by the
capacity admin, the tenant-level Export data setting in the Power BI Admin
Portal, also required for Analyze in Excel, must be enabled.
Furthermore,
it's good to know that when accessing workspaces through the XMLA endpoint,
group memberships (workspace and app access rights) are still honored.
The highest
access rights that are currently possible are equivalent to Analysis Services
database admins. So anything that requires server admin permissions
(server-level traces, certain DMVs) is not yet supported through XMLA
endpoints.
Model Roles
When I
started my deployment I ran the following error:
As stated in the
docs,
the following limitations apply when working with dataset roles through the
XMLA endpoint:
- During the
public preview, you cannot specify role membership for a dataset by using
the XMLA endpoint.
Instead, specify role members on the Row-Level Security page for a dataset
in the Power BI service.
- The only permission for a
role that can be set for Power BI datasets is the Read permission. Build
permission for a dataset is required for read access through the XMLA
endpoint, regardless of the existence of dataset roles. Use the Power BI
security model to control permissions beyond RLS.
- Object-level security (OLS)
rules are not currently supported in Power BI.
Obviously, I
ran into the first point. And it turns out I had 3 XMLA-scripts for role
assignments in my project, which are not supported yet in preview mode, as also explained
here.
I had to delete the defined roles in my model itself, as shown here:
Success (sort of)
After fixing the
roles I tried to deploy again and I got myself a little cheer, because the
metadata was successfully deployed! However, I was seeing some errors at the
table level of the deployment.
Looking at the error
details quickly showed me what is was, because I read that, again, in the
docs:
