Import Power BI Desktop Model to SSAS Tabular 2017 Using Azure Analysis Services

image

NOTE: This method is no longer available (from 1st March 2019) as Microsoft discontinued supporting Web Designer in Azure portal. Microsoft is working on an alternative solution, but there is no timeframes or details to announce yet. Read more here. In the meantime, you still can import your Power BI Desktop Models to SSAS Tabular following the method described here

A while ago I wrote a blog post on how to import you currently existing Power BI Desktop models to SSAS Tabular 2016. However, the method I explained is NOT supported by official Microsoft BI tools like SSDT, so you may consider it as a WORKAROUND only until Microsoft supports imploring Power BI models in SSDT. In this post, I show you how to import Power BI Desktop Model to SSAS Tabular 2017 using Azure Analysis Services. It is easy and hassle free.

Requirements

Notes:

  • In this post I do NOT explain how to install Azure Analysis Services
  • This method works only for SQL Server Analysis Services 2017 Tabular

How it works

As mentioned earlier it is really easy in compare with other methods I explained in my previous post. Azure Analysis Services is capable of importing Power BI Desktop files creating a Tabular model version of your Power BI model in the cloud. Then you can simply download Visual Studio project file and redeploy it in your on-premises instance of SSAS Tabular 2017. Let’s go through the steps…

  • Open Power BI Desktop
  • Import data from WorldWideImportersDW from any desired combination of fact tables and dimensions. I imported
  • Create some simple Measures like:

Total Sales = SUMX(‘Fact Sale’, ‘Fact Sale'[Unit Price] * ‘Fact Sale'[Quantity])

  • Save your Power BI Model and close the file
  • Login to your Azure PortalBrowse to your instance of Azure Analysis Services
  • Click on “Open” under “Web designer—preview”

image

  • This opens a new page for fabulous “Azure Analysis Services Web Designer”. You’re right, a web designer for tabular models. How cool is that? Smile
  • Click “Add” button under “Models” section
  • Yes, you got it, enter a name for your model and click “Power BI Desktop” button
  • Click “Browse” and select the Power BI file you saved earlier then click “Import”

image

Now you successfully imported your Power BI Desktop model to Azure Analysis Services. The next steps show you how to export Visual Studio project file, open it in SSDT and deploy it to your on-premises instance of SSAS Tabular 2017.

As you already noticed, you can write DAX queries, you can create new measures etc…in the Web Designer. This is awesome isn’t it?

  • Click “Open in” button
  • Click “Visual Studio Project” to download a ZIP file which contains Visual Studio project

image

  • Extract the ZIP file, you’ll see a “Model.bim” and a *.smproj file which Visual Studio Project file
  • Open SSDT
  • Click File –> Open –> Project/Solution and open the *.smproj file
  • Select “Integrated workspace” or “Workspace server” then click OK

image

  • From “Solution Explorer” right click the project and click “Properties”

image

  • Modify the server by entering your on-premises SSAS Tabular 2017 instance then click OK

image

  • Double click “Model.bim” to open the model
  • You may (or may not) see a bunch of hidden Date dimensions that you haven’t created in Power BI. Those tables are automatically created in Power BI Desktop if you tick “Auto Date/Time” option in Power BI Desktop.

image

  • You can delete the date dimensions if you want
  • From “Tabular Model Explorer” expand “Data Sources”
  • Right click on the data source then click “Change Source” to make sure your Tabular model data source is pointing to the right server. If everything’s right then click OK

image

image

  • You may want to check the “Impersonation” setting by right clicking the data source and click “Edit Permissions”

image

  • Under “Credentials” click “Edit”

image

  • You can now select “Impersonation Mode” from the drop down

image

Note: Make sure your SSAS Tabular model can access the underlying data source using the impersonation mode you select under the “Credentials” section. For instance, my SSAS Service Account has db_datareader access to WorldWideImportersDW database in SQL Server.

  • Now you can deploy the model to SSAS Tabular by clicking “Build” menu and clicking “Deploy Solution”

image

All done!

I just want to bring your attention to two hidden Date dimension which originally created by Power BI Desktop automatically with more than 2 million rows. This is ridiculous, and in Power BI it can cost you an unnecessary large PBIX file. So it’s best practice to disable “Auto Date/Time” in Power BI Desktop from Power BI Desktop Options.

image

6 thoughts on “Import Power BI Desktop Model to SSAS Tabular 2017 Using Azure Analysis Services

  1. BTW, since Aug 2018, AAS has a compatibility level of 1465, so before you try to open the Model.bim filein Visual Studio, 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”

  2. This web service has been deprecated by Microsoft. The ability to import pibx files to analysis services is no longer supported. Its a real bummer because they depreciated it on March 1 2019.

    1. Hi Tucker,
      Thanks for the update, I confirm the MOdel Designer has been removed from Azure Portal due to compliance requirements it has been discontinued.
      Microsoft is working on a more robust way to support all and even better the functionalities than the Web Designer.
      In the meantime, you still can import your Power BI Desktop models to Analysis Services Tabular model using the method explained here.
      Cheers

    1. HI Dan,
      Thanks for your feedback.
      It is interesting that I received your feedback right after I updated the blog post with a notice about the discontinuation of the Web Designer.
      Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.