Azure Analysis Services, BISM Tabular, Power BI, Power BI Desktop, SQL Server Analysis Services - SSAS, Uncategorized

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”

  • 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?
  • 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”

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

  • 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

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

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

  • 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.

  • 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

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

  • Under “Credentials” click “Edit”

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

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”

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.