Power BI Premium Registered Servers

XMLA endpoint connectivity for Power BI Premium represents one the most significant steps in Power BI becoming an enterprise BI platform as it exposes Power BI datasets to the same broad set of BI management and client tools as Analysis Services. For a quick review of XMLA endpoints, which are currently in preview and read-only, you can review the announcement from earlier this year.

In this post we’ll look at how Registered Servers in SQL Server Management Studio (SSMS) can be used to take advantage of XMLA endpoints to become more productive with datasets hosted in Power BI Premium capacity.

Registered Servers

Registered servers have been around for a long time as a simple but useful feature for A) persisting server connection information and B) organizing server connections into groups and subgroups.

You can review the articles on registered servers in MS Docs for greater detail but essentially if you find yourself regularly connecting to multiple servers you’ll probably instinctively look for a more efficient way of establishing these connections. For example, let’s say you routinely connect to three Azure SQL Databases, two on-premises SQL Server instances, two Azure Analysis Services servers, and several Power BI Premium workspaces (via XMLA endpoint). In this case you likely wouldn’t be able to memorize all of the server names and various authentication methods used by each and you also wouldn’t want to to regularly type in these values. If this common scenario describes your workflow you’ll definitely want to look into registered servers as a way of organizing and quickly accessing these resources.

Once you’ve installed SSMS, you can get started by exposing the Registered Servers pane (View – Registered Servers) or Ctrl+Alt+G.

Registered Premium Workspaces

In the following example, I’ve created a Server Group titled ‘Power BI Premium Workspaces’ and then, within this group, created four additional server groups to store server registrations for Power BI Premium app workspaces owned by four different teams:

The Power BI Premium workspaces and the datasets they contain can be accessed just like models deployed to Analysis Services servers. Per the MS Docs article on connecting to datasets with client applications and tools, the premium workspace servers can be addressed via the following URL format:

powerbi://api.powerbi.com/v1.0/[tenant name]/[workspace name]

From the Analysis Services type (cube icon), you simply right-click the parent folder ‘Local Server Group’ to expose the ‘New Server Group’ and ‘New Server Registration’ options. Also advised in the MS Docs article, you should use Azure Active Directory – Universal with MFA as the authentication method when registering a server (or premium workspace in this case) per the following example:

For the registered server name I just use the name of the app workspace – there’s no need to display other parts of the URL. Once you’ve registered the servers you believe you’ll need to access into the server group structure that make sense for you, you’ll now just be a couple clicks away from establishing a connection to these resources in future sessions of SQL Server Management Studio.

If you have issues with connecting to your premium workspaces you might check that the XMLA Endpoint workload setting of the given Premium capacity is enabled (value = 1). You’ll also want to make sure you’re mapped to a workspace role of Contributor, Member, or Admin for the given V2 workspace or Member (with edit rights) or Admin for the given V1/O365 workspace.

Use Cases for Registered Servers

If you’re a Power BI administrator in an organization that’s invested in Power BI Premium capacity or you’re a BI developer that routinely works with several app workspaces hosted on premium capacity you would likely benefit from the convenience of quickly and easily connecting to the workspaces relevant to you and optionally submitting DAX and other queries against these datasets.

As just one simple example, after using a registered server to connect to a Power BI Premium workspace, you can right-click a database (a Power BI dataset) to open the Properties page per the following image:

Per the above image, the Last Data Refresh property, the size of the database in terms of MBs of memory, and other potentially useful properties are exposed via Database Properties.

As another example, you could review the tables, row counts, and any Row-level security roles and their definitions built into the database. For the row counts, just right-click one of the tables to view the table partition(s) or write a simple DAX query against the dataset. The tables and roles metadata are exposed in SSMS like the following example:

Because you have connections to several other premium workspaces saved as registered servers, you could quickly compare datasets across workspaces such as the row count, refresh time, and database size of a dataset on a dev workspace versus the same attributes of a dataset on a production workspace.

Two additional use cases for connecting include A) troubleshooting issues with a dataset such as writing DAX queries to return the rows with blank/null values on certain columns and B) writing the DAX queries to be used in paginated (SSRS) reports against the given Power BI premium dataset.

Yes, you could write DAX queries against premium datasets in DAX Studio but, as much as I appreciate and utilize DAX Studio, it doesn’t support registered servers or Analysis Services scripting projects like SQL Server Management Studio. Therefore, when you’re working at scale (many servers, many code files) with a mixture of administrative and development responsibilities it seems that SSMS is a better option.

Yes, you could write paginated report DAX queries directly into Power BI Report Builder or use the graphical interface in this tool to define the required DAX query. However, unlike SSMS, Power BI Report Builder doesn’t provide syntax highlighting or intellisense for DAX queries and in many cases the DAX graphical tool can’t define the requirements of a moderately complex query. Therefore, DAX queries developed in SSMS and preferably stored in Analysis Services Script projects is likely the better option here as well.

Wrapping Up

Like many other tools and features relevant to Analysis Services, registered servers in SSMS is something that ‘just works’ with datasets hosted in Power BI Premium capacity. According to the Release Wave 2 Plan (features releasing between October 2019 and March 2020), XMLA read/write will be in public preview in December and this will open up much more interesting and powerful scenarios.

If you haven’t looked into ways of leveraging XMLA endpoints and want a fairly lightweight starting point you might consider registered servers. In future blog posts I’ll share more detailed examples of how I use other features in SSMS, such as Analysis Services Scripts projects, with XMLA endpoints.