Migrate a Power Query or Power BI file to a local SSAS instance

Edit: This feature has been deprecated since 1st March 2019 unfortunately: https://azure.microsoft.com/en-us/updates/azure-analysis-services-web-designer-to-be-discontinued/ – Microsoft is working on replacement of the functionality to move pbix-files to SSAS-instances, but no solution yet.

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher:

The steps:

  1. Import the Excel file in Power BI Desktop, save and close the pbix-file
  2. Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
  3. Open that model with Visual Studio (this will actually create a download that holds the VS-file)
  4. Open that file in Visual Studio, load the data, build and change the deployment target from Azure to you local SSAS-database before deploying.

See how it goes:

Warning: There are some limitations for the M-functionalities in SSAS (see here for example: General Overview by Microsoft or Use your own SQL … by Chris Webb), so you might want to give it a thorough test before rolling out. There are missing a lot of data sources currently, like web-queries for example who will hopefully soon be added as well.

This method has been described by Soheil Bakhshi here before: http://biinsight.com/import-power-bi-desktop-model-to-ssas-tabular-2017-using-azure-analysis-services/ 

Enjoy & stay queryious 😉

Comments (10) Write a comment

  1. Hi,

    Thanks for the clever solution. I would like to know if this workaround is breaching any licensing if you have a developer edition?
    Due to my limited amount of Analysis Services, I would like to know if other people would be able to connect to this model is it’s localhost?

    Thanks

    Eric

    Reply

  2. Hi Imke,

    While importing from Excel to Power BI Desktop – there is a problem if you have a local table that has “\” in any cell for Example in a Parameter table have the file Path C:\DATA

    So you must remove the “\” in Excel, and import it in to Power BI Desktop and then put the “\” again in Power BI Desktop

    Reply

    • Thanks sam for mentioning this.
      This doesn’t give a problem on my machine, but it could well be that others run into this problem as well.
      Cheers, Imke

      Reply

  3. @Imke – download the 2 excel files from the below link – Keep the Report file on your desktop and the DB file in C:\DATA
    Now open Power BI Desktop – Import the Queries and Data model from the Excel file called Report.
    Go in to Edit queries and click on a Table called “P” – you will get a Error
    “We found an invalid escape sequence in JSON input”

    https://1drv.ms/f/s!AiKBTsYfZw-vgpMTeciL5VTfGzziDg

    The issue disappears moment you remove the “\” in the Table P in Cell B2 of the the Excel file Report
    and manually put it back in Power BI Desktop

    Reply

  5. BTW, since Aug 2018, AAS has a compatibility level of 1465, so before you try to open the Model.bim file, you’ll want to View Code, and change Compatibility level from 1465 to 1400. Then you should be able to open it. Otherwise you get an error: “This file cannot be opened. You can only open files that are compatible with Microsoft SQL Server 2012 SP1 or later”

    Reply

  6. Hey man love your site. This feature has been deprecated by Microsoft with no replacement. I found out the hard way after setting up an azure instance.

    Reply

Leave a Reply