How to migrate Power BI datasets to Microsoft Analysis Services models [Tutorial]

The Azure Analysis Services web designer, supports the ability to import a data model contained within a Power BI Desktop file. The imported or migrated model can then take advantage of the resources available to the Azure Analysis Services server and can be accessed from client tools such as Power BI Desktop. Additionally, Azure Analysis Services provides a Visual Studio project file and a Model.bim file for the migrated model that a corporate BI team can use in SSDT for Visual Studio.

In this tutorial, you will learn how to migrate your Power BI data to Microsoft Analysis Services for further self-service BI solutions and delivering flexibility to a huge network of stakeholders.

This article is an excerpt from a book written by Brett Powell titled Mastering Microsoft Power BI.

The following process migrates the model within a Power BI Desktop file to an Azure Analysis Server and downloads the Visual Studio project file for the migrated model:

1. Open the Web designer from the Overview page of the Azure Analysis Services resource in the Azure portal
2. On the Models form, click Add and then provide a name for the new model in the New model form
3. Select the Power BI Desktop File source icon at the bottom and choose the file on the Import menu

4. Click Import to begin the migration process

The following screenshot represents these four steps from the Azure Analysis Services web designer:

In this example, a Power BI Desktop file (AdWorks Enterprise.pbix) that contains an import mode model based on two on-premises sources (SQL Server and Excel) is imported via the Azure Analysis Services web designer.

Once the import is complete, the Field list from the model will be exposed on the right and the imported model will be accessible from client tools like any other Azure Analysis Services model. For example, refreshing the Azure AS server in SQL Server Management Studio will expose the new database (AdWorks Enterprise). Likewise, the Azure Analysis Services database connection in Power BI Desktop (Get Data | Azure) can be used to connect to the migrated model, as shown in the following screenshot:

Just like the SQL Server Analysis Services database connection (Get Data | Database), the only required field is the name of the server which is provided in the Azure portal.

5. From the Overview page of the Azure Analysis Services resource, select the Open in Visual Studio project option from the context menu on the far right, as shown in the following screenshot:

6. Save the zip file provided by Azure Analysis Services to a secure local network location.
7. Extract the files from the zip file to expose the Analysis Services project and .bim file, as shown in the following screenshot:

8. In Visual Studio, open a project/solution (File | Open | Project/Solution) and navigate to the downloaded project file (.smproj). Select the project file and click Open.
9. Double-click the Model.bim file in the Solution Explorer window to expose the metadata of the migrated model.

All of the objects of the data model built into the Power BI Desktop file including Data Sources, Queries, and Measures are accessible in SSDT just like standard Analysis Services projects, as shown in the following screenshot:

The preceding screenshot from Diagram view in SQL Server Data Tools exposes the two on-premises sources of the imported PBIX file via the Tabular Model Explorer window. By default, the deployment server of the Analysis Services project in SSDT is set to the Azure Analysis Services server.

As an alternative to a new solution with a single project, an existing solution with an existing Analysis Services project could be opened and the new project from the migration could be added to this solution. This can be accomplished by right-clicking the existing solution’s name in the Solution Explorer window and selecting the Existing project from the Add menu (Add | Existing project).

This approach allows the corporate BI developer to view and compare both models and optionally implement incremental changes, such as new columns or measures that were exclusive to the Power BI Desktop file.

The following screenshot from a solution in Visual Studio includes both the migrated model (via the project file) and an existing Analysis Services model (AdWorks Import):

The ability to quickly migrate Power BI datasets to Analysis Services models complements the flexibility and scale of Power BI Premium capacity in allowing organizations to manage and deploy Power BI on their terms.

By now, you have successfully migrated your Power BI datasets to Analysis Services and can enjoy the complete flexibility of making further edits to your model for mining much better insights out of it. If you found this tutorial useful, do check out the book Mastering Microsoft Power BI and start producing insightful and beautiful reports from hundreds of data sources and scale across the enterprise.

Read Next:

How to use M functions within Microsoft Power BI for querying data

Building a Microsoft Power BI Data Model

How to build a live interactive visual dashboard in Power BI with Azure Stream