Power BI Premium support for the XML for Analysis (or XMLA) protocol is in public preview. XMLA is an industry standard for data access for analytic client and management applications (and has been an open standard for almost 20 years). Azure Analysis Services and SQL Server Analysis Services both use the XMLA protocol for communications between client applications and an Analysis Services instance. With the XMLA endpoint, you can use client libraries such as ADOMD.net, AMO, and MSOLAP to perform queries and operations against premium workspaces.
Now that Power BI Premium supports XMLA, it supports the same client and management applications as AAS and SQL Server Analysis Services. This includes a rich set of third party data visualization applications such as Tableau, and management applications such as SQL Server Management Studio (SSMS), SQL Server Profiler, Tabular Editor, Dax Studio, and SQL Server Data Tools.
This means you now have the option to move your models from Azure Analysis Services or SQL Server Analysis Services to Power BI Premium. Should you? How? Read on!
Choosing between Analysis Services and Power BI Premium
Power BI Premium has a broader scope of functionality than Azure Analysis Services (AAS) including dataflows, AI workload, and paginated reports. When making a choice, consider the entire scope of your organization’s requirements.
For datasets alone, Power BI Premium and AAS are close in capabilities but with some differences that could influence your decision. These capabilities are available in Power BI Premium but not in AAS:
- Unlimited Power BI Desktop authoring
- Aggregations – pre-aggregated tables in Power BI optimize query performance for direct query models on large data.
- Multi-model memory management – The total size of models in AAS cannot exceed the memory available in the chosen configuration. In Power BI Premium, models are loaded in memory as required and evicted when not in use providing a more efficient use of memory resources.
- Composite models are individual datasets that support tables with direct query, import, or both – all in a single dataset.
- Incremental refresh policies – no-code incremental refresh can be configured simply and easily.
- Scheduled refresh is available in Power BI Premium with a couple clicks – this requires code in Analysis Services.
- Bring Your Own Key gives you the ability to control the encryption keys for your Power BI data at-rest.
There are several capabilities in AAS not yet available in premium that might preclude a migration. These need to be considered with respect to the advantages of Power BI when making a decision:
- Object Level Security
- Scale-out Read Replicas
- Pause/restart (available in Power BI embedded)
- Alias server names
- Asynchronous Refresh (via REST APIs)
Before beginning, make sure your Power BI Premium capacity has the processing power and memory to support your Analysis Services model. The table below has the Power BI Premium SKUs that are roughly comparable in processing power and with the same memory capacity:
|Azure AS SKU||Memory (GB)||Power BI Premium SKU|
Premium manages its memory differently than AAS. While neither permits a single model from exceeding available memory, premium lets the memory for all models exceed the total available and juggles which stay in memory based on usage. AAS models are always in memory and the sum of the memory consumed by the models cannot exceed the instance’s capacity.
XMLA endpoints are not enabled by default. In the Admin portal, scroll down to the dataset properties and enable XMLA Read Write:
This enables admins, members and contributors to update datasets in workspaces assigned to this capacity via the XMLA endpoint.
To retrieve a workspace’s endpoint, click the “Settings” button in the workspace page and click the “Premium” tab”:
A Power BI workspace (in a premium capacity) is similar to an AAS Server (the comparison is not exact – there are differences). In the example above, the workspace name is richtkASMigration and the endpoint is powerbi://api.powerbi.com/v1.0/myorg/richtkASMigration from within the Power BI tenant. For B2B users (users outside the tenant that have been given permissions to view content within the tenant), replace myorg with the tenant name; eg, powerbi://api.powerbi.com/v1.0/contoso.com/richtkASMigration.
An endpoint can also be retrieved from the dataset settings page as well. A dataset maps to what would be a database in Analysis Services. From the dataset in the workspace list, select More Options -> Settings, and copy the contents of the Connect string textbox:
Your Analysis Services instance can be migrated to Power BI Premium with SSDT, SSMS and Tabular Editor. From SSDT, deploy your existing project to the workspace endpoint. From the Visual Studio Analysis Services Project Extension, this looks like the following:
Note that the compatibility level of the project must be 1500 or above.
Credentials will have to be redefined in the service after the first deployment but need not be reentered for subsequent deployments.
You can also connect to the endpoint as a server and deploy a TMLS or XMLA script from SQL Server Management Studio (SSMS). Be sure to use either the “Azure Active Directory – Password” or “Azure Active Directory – Universal with MFA” authentication option.
An existing project can be opened in Tabular Editor and deployed to the XMLA endpoint as any other server.
The following restrictions apply to deployment:
- After deploying to the Power BI endpoint, the “Import from Server (Tabular)” option for creating new projects in SSDT is not supported. Retain the original projects for edits and redeploy as required. (This restriction does not apply to Tabular Editor nor SSMS).
- Power BI Premium cannot be used as a workspace server for SSDT development.
- Any reports built on datasets deployed via the XMLA endpoint cannot be subsequently downloaded as pbix files.
- Power BI Premium supports compatibility level 1500. Compatibility levels 1400 and lower are not supported.
After deploying, test connectivity with the new database with SSMS – the new dataset will appear as an Analysis Services database in the SSMS object explorer:
If the model exceeds or may exceed 10 GB, the large model setting needs to be enabled. Instructions for how to do so are here. This is an optional step for smaller models, but performance for XMLA write operations will be much faster if large models are enabled regardless of model size.
After a model is migrated to Power BI, existing client applications must be updated with the new server name.
After migration, any migration scripts will need to be converted.
The Analysis Services Deployment wizard is supported in its entirety.
Azure Analysis Services database management powershell cmdlets are supported except for those pertaining to functionality not yet supported with Power BI Premium:
The Azure Analysis Services REST API and Server resource management powershell tasks for operations such as creating or deleting a server in Azure, suspending or resuming server operations, or changing the service level (or tier) are not supported.
Service Principals are not supported just yet – this is coming soon.
Database scoped DMVs are supported. Server-wide DMVs and XEvents requiring server admin in Analysis Services are not supported in Power BI Premium as there is no server admin role equivalent in Power BI. However, those DMVs requiring DB admin permissions are supported for members of the Power BI workspace admin and builder roles.
SQL Server Profiler is supported. To create a trace, specify the database at connection time:
If the database is not specified, the attempted connection to the server will fail as such connections require server admin permissions.
Members of the workspace Admin, Member, and Contributor roles can publish a dataset to a workspace. Members of these roles automatically have Build permissions on the dataset as well (i.e., permission to create new reports on the dataset). Individuals without Admin, Member, and Contributor workspace permissions can be assigned the Build permission on the dataset to be able to create new reports on the published dataset.
Read permissions with or without Row Level Security (RLS) are supported. RLS can be assigned in Visual Studio Analysis Services Projects Extension:
The Analysis Services Process permission does not exist within Power BI. Roles with this permission cannot be deployed to Power BI Premium.
While security roles can be defined within management tools outside the service prior to publishing to the service, role membership can only be updated in the service.
Object level security is not supported. All tables and columns are visible to users with read access to the dataset.
Power BI Premium’s support for XMLA is a big step in the journey to unify Analysis Services and Power BI within a single offering. I hope you get a chance to try this out and let me know about your progress in the comments.