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

image

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

Leave a Reply