Migration of Power BI Desktop to Analysis Services

The Azure Analysis Services web designer, currently in preview, 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.

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

 

  1. Click Import to begin the migration process

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

Create an Analysis Services model from a Power BI Desktop File

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:

Migrated Model accessed from Azure as server in Power BI Desktop

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 as described in the Provision Azure Analysis Services section earlier.

  1. 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:
Context menu in Azure Portal for a model
  1. Save the zip file provided by Azure Analysis Services to a secure local network location.
  2. Extract the files from the zip file to expose the Analysis Services project and .bim file, as shown in the following screenshot:
Folder contents downloaded from Azure Analysis Services
  1. 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. 
  2. 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:

Migrated model opened as Analysis Services Project

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, but this can be revised as was described in the SSAS to Azure AS Migration section earlier. 

Since the ability to import a Power BI Desktop file directly in SSDT is not yet available, BI teams with on-premises SSAS environments could temporarily provision an Azure Analysis Services server to support migrations. Once the project file is downloaded from Azure, the Azure AS server could be paused or deleted and the deployment server property in the project could be revised to an SSAS 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):

Tabular Model Explorer

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.