In my previous blog I have the benefits of Power BI Premium per user already discussed. In this blog post, I want to delve a little deeper into the use of datasets within Power BI Premium. Power BI Premium—and especially its XMLA endpoint functionality—offers a number of great advantages.
XMLA Endpoint
The XMLA Endpoint allows you to connect to the Power BI environment using an open standard. This makes it possible to use third-party tools to, for example, edit and refresh datasets. XMLA is the same communication protocol used by the Microsoft Analysis Services engine, which you can now also use to communicate with your Power BI Premium or Premium per user workspace. That is why there are many similarities between the two environments. The use of external tools makes Power BI Premium even more powerful and flexible than it already is.
By default, read-only connectivity with the endpoint is enabled. In the settings, this can be changed to read and write permissions.
Edit Dataset – TabularEditor
Once read and write permissions have been enabled, you can connect to the workspace using a tool such as TabularEditor. You can find the URL for connecting to Power BI in the workspace settings.
The main advantage of the Tabular Editor tool is that it allows you to easily and quickly edit the metadata of SSAS models (and now Power BI datasets as well). Support for this feature is regularly expanded.
Please note that after modifying a dataset using an external tool, it can no longer be downloaded as a .pbix file. Of course, it can still be edited using the external tools as well as Visual Studio.
Dataset – Partitions
An important consideration with large Tabular Models—and therefore also with large Power BI Datasets—is data refresh. Partitions are frequently used to refresh portions of the data, reduce processing time, or periodically reload new data. Using tools such as Tabular Editor, it is now also possible to create partitions within your Power BI dataset. These partitions can then be refreshed separately. I’ll explain this in more detail below.
Dataset Refresh
Refreshing a dataset within Power BI itself actually offers few options for customizing the process to your liking. Using third-party tools, which we describe below, makes this much easier.
SQL Server Management Studio
If you work with SSAS frequently, you’re probably familiar with refreshing data via SSMS. Using XMLA endpoint connectivity, you can also connect directly to your Power BI workspace and, for example, refresh specific tables there.
Data Factory
You can use Data Factory to automatically refresh specific tables or partitions in your dataset at regular intervals. Using the Power BI API, you can—just as you would with an SSAS model—execute the desired XMLA through Data Factory to refresh your dataset.
PowerShell
It is also possible to connect to Power BI workspaces and datasets using PowerShell.
Conclusion
XMLA endpoint connectivity provides Power BI Premium (per user) users with a great deal of additional functionality that is not yet available within Power BI itself. As a result, it may also be worthwhile for companies with larger datasets to choose Power BI Premium over, for example, a Tabular Model on Analysis Server (on-premises or as-a-service). In terms of managing and maintaining your Power BI datasets, this can certainly offer many advantages.
