Migrating Azure Analysis Services (AAS) models to Power BI Premium simplifies resource maintenance and drives modern, efficient business intelligence environments. Analysis Services is the backend engine for Power BI. Power BI datasets are Analysis Service models at their core, which allows for a very smooth transition between services. Tools such as Visual Studio, Tabular Editor, ALM Toolkit, and SSMS simplify the deployment to a Power BI workspace. Gone are the days when Azure Analysis Services was the go-to option for large datasets. The robust performance and scale options of Power BI Premium Gen2 have surpassed AAS. By shifting away from using AAS, users can leverage the ever-increasing capabilities of Power BI Premium. Migration allows users to centralize development of assets in Power BI Service and save time maintaining disparate environments.
Benefits of Migration
Power BI is becoming a superset of AAS. Microsoft is shifting its focus to Power BI as the source of business intelligence by expanding upon the functionalities of AAS. To facilitate the migration process, new features are being released regularly for Power BI, such as XMLA endpoints’ support for Azure Active Directory authentication with service principals.
By migrating to Power BI Premium, users can combine the capabilities of Azure Analysis Services, such as object-level security, support for large models, and the use of XMLA endpoints, with the innovative functions of Power BI. Power BI Premium can scale up or down based on organizations’ evolving business needs and can provide flexibility in reporting. Power BI Premium Gen2 bolsters enhanced performance capabilities for all capacity sizes and unlimited refresh on concurrences. Using Premium features like aggregations or composite models can drastically improve performance for large models. Premium workloads offer powerful additional services like dataflows, deployment pipelines, and paginated reports that allow users to enrich their existing reporting structures. Integration with other features like Azure Purview and Microsoft Information Protection labels ensure security is maintained through the organization.
Migration to Power BI Premium allows users to decrease maintenance overhead and centralize all their business intelligence assets. Users will no longer need to manage the Analysis Service environment alongside their self-service Power BI environment, which could have significantly different configurations and maintenance processes. Instead, users can save time and integrate all the resources for a streamlined BI environment.
Potential Drawbacks
Security
Although there are numerous benefits of shifting to Power BI Premium, migration may not be the ideal solution for every organization. A prominent reason could be security. If your organization has strict data governance and protection requirements, migrating to Power BI Premium may not be recommended due to potential differences in Microsoft’s data protection standards. Similarly, the loss of administrative power over your datasets could be a deciding factor. With AAS models, you have access to DMVs and Content Level Security settings that are absent in Power BI Premium, and you can control data and job executions at a more granular level than in Premium. Once a dataset is on a Power BI Premium Capacity, the datasets are stored in AAS models in WABI data clusters. Microsoft owns the WABI clusters, and you only have read/write permissions over them. Therefore, organizations that generate finite detail logging or use linked servers with their AAS models lose the capability to do so due to the loss of complete admin control.
Feature Parity
Feature parity between the two platforms could drive decisions. Organizations that want to keep their main data warehouse on-premises may want to continue using their existing on-premises reporting applications, such as SQL Server Reporting Services (SSRS), rather than spending time shifting to Paginated Reports. Further, utilizing the cloud requires a strong, consistent network connection, so some organizations may not have the speeds necessary to leverage Power BI Premium.
Cost
Another important factor is cost. Azure Analysis Services and Power BI Premium have different licensing models. For example, AAS has fixed monthly costs, and PPU has a fixed monthly cost per user. AAS has three tiers: Developer, Basic, and Standard. On the other hand, Power BI Premium is sold per capacity, and you pay for however much capacity you need. Thus, it can be challenging to compare costs between the services. However, Microsoft has stated that they are working on a plan to address cost transitions in the future. For now, it is recommended to estimate how many users will be accessing your content in order to compare costs effectively.
Prerequisites
There are a few prerequisites for migrating to Power BI Premium. First, ensure that you have access to the Azure portal and the AAS model in question. Make sure that you are assigned either the Admin, Member, or Contributor role in the Power BI workspace to be able to publish datasets to the workspace. Additionally, you will need the login credentials for the backend data source.
Regarding Power BI Service, you will need a Gen 2 Premium or Premium Per User (PPU) Workspace. Make sure the workspace is running on a dedicated capacity before migrating resources. Also, ensure that XMLA Endpoints are enabled for the entire organization by going to the Admin Portal.
Finally, ensure that the XMLA Endpoint is set to Read Write. In the Admin Portal, go to the Capacity Settings > Power BI Premium > Select your capacity name and select “Read Write” from the XMLA Endpoint drop down.
Deployment Options
Migration between Analysis Services and Power BI Premium requires a few manual steps. You can use popular tools such as Visual Studio, Tabular Editor, ALM Toolkit, or SSMS to facilitate the process and ensure a smooth deployment to the Power BI workspace.
Tabular Editor
Tabular Editor is the recommended method of deployment. Tabular Editor allows you to control what objects to deploy and what options to include. You can avoid overwriting existing partitions by selecting the appropriate objects to deploy. Additionally, users can take advantage of the tool’s Best Practice Analyzer to address common sources for performance problems, such as unused columns or using floating integer data types. Tabular Editor allows users to improve modeling practices before deploying to optimize performance and facilitate report creation for end users.
First, get the server name for the Azure Analysis Service resource. To do so, go to the Azure Portal, navigate to the Analysis Service resource, and copy the read-write Management Server name from the Overview section.
Next, open Tabular Editor. In the top ribbon, go to File > Open > From DB.
Connect to the Tabular Server (the Analysis Service server) and click OK.
Select the necessary Database and click OK. Note that the Compatibility Level is shown here. It is recommended to change the level to 1500 or higher to align with Power BI Premium.
Next, from the top ribbon, click Model > Deploy.
In the next window, insert the Workspace Connection to the destination Premium Workspace.
To find the Workspace Connection, go to the Premium Workspace in the Power BI Service. Select the Settings gear icon > Premium section > Workspace Connection. Copy the URL and paste it in the window in Tabular Editor.
Choose the destination you want to deploy to. This can either be an existing model in the workspace, or you can create a new one by typing a name in “Database Name”. Click Next.
Specify deployment options by selecting which elements you want to deploy and click Next. The “Deploy Model Structure” option protects against overwriting partitions, etc. You can also select to deploy the connections, table partitions, roles, or role members.
Review the settings you have configured and click Deploy when satisfied.
We have now deployed the metadata to the Power BI Premium workspace. In the workspace in Power BI Service, the content appears as a dataset, with no reports attached to it yet. Click the ellipsis next to the dataset name > Settings. Here, open the sections for Gateway Connection and Data Source Credentials, and configure the settings appropriately. The data gateway needs to be configured correctly in order to bridge the gap between on-premises and cloud data sources. Power BI also requires data source credentials to be able to access the protected data source.
Next, refresh the dataset in the Power BI Service. Now you can live connect to this dataset in Power BI Desktop and build reports from the content.
Visual Studio Deployment
When using Visual Studio, the tool overwrites everything that has been previously deployed. Take this into consideration in case there are differences between the Visual Studio dataset and the production dataset. For example, if there are partitions in your production tables which are not present in the Visual Studio dataset, the tool will overwrite those partitions when you deploy the Visual Studio model. However, using the BISM Normalizer extension with Visual Studio allows you to control what options to deploy. The BISM Normalizer also displays a visual comparison between the source and target datasets.
The process for deploying with Visual Studio is simple. Open the project in Visual Studio, and on the right-hand Solution Explorer pane, right click the project name > Deploy.
Review the Deployment Options and Deployment Server information. A useful tip is to change the Processing Option to “Do Not Process” so the tool does not process the tables while deploying the changes, which can be time consuming depending on the number of tables. Finally, click Deploy.
ALM Toolkit Deployment
Using the ALM Toolkit is a similar experience as using Visual Studio with the BISM Normalizer extension. To use the ALM Toolkit, download the program. Open the PBIX in Power BI Desktop, and go to the External Tools tab. Click “ALM Toolkit” to open the program.
When ALM Toolkit opens, ensure the correct PBIX file is selected and the appropriate PBI Workspace connection string and Dataset name are entered. Click OK.
The ALM Toolkit allows you to select which items to deploy. Additionally, you can view differences between reports and compare changes. Like using the BISM Normalizer with Visual Studio, you can specify not to overwrite partitions if needed.
SQL Server Management Studio (SSMS) Deployment
The final option is to use SSMS for deployments. To connect to the workspace, click Connect > Connect to Server. Select Analysis Services from the drop down. Under Server Name, paste the Workspace connection string. Select Azure Active Directory – Universal with MFA from the Authentication drop down. Enter your email address for the Username and click Connect.
When you expand the Databases section on the left side, the datasets in the workspace will appear as Databases. You can expand the Tables section to view tables in the datasets. Right click on the Database name to process the entire database.
For finer control in deployment, right click a table name to process only that table.
Right click the table name and click Partitions to view specific partitions. Click the green circular arrows icon to process the partitions separately if needed.
Considerations
Power BI does not allow you to change a report’s connection from an AAS model to the newly created Power BI dataset. For example, if you have a Power BI report that was previously connected to the Analysis Service machine, you cannot natively switch it to a Live Connection on the newly created Power BI dataset. In order to swap report connections, users can use external hot swap tools or hot swap scripts using PowerShell.
In Conclusion
Migrating Azure Analysis Services models to Power BI Premium offers enhanced performance capabilities and reduces manual overhead. As Power BI Premium becomes a superset of AAS, users can harness Microsoft’s new, innovative features and leverage the flexible scaling structure of Premium architecture. Migrating to Power BI Premium is a massive step forward in forging an efficient, powerful modern business intelligence environment.
Contact Us
3Cloud has strong experience migrating Azure Analysis Services models to Power BI Premium to create a more efficient Microsoft Azure ecosystem. If you’re wondering how these tools could benefit your business outcomes, contact us today!
