Note: This article has been updated on June 2017 to support latest versions of Power BI Desktop and SSAS Tabular 2017.

Update July 2020: While this method still works with Power BI Desktop June 2017 release and SSAS 2017, it potentially doesn’t work with the later versions of Power BI Desktop (Jul 2020) and SSAS 2019 as there are new DAX functions that are available in the Power BI Desktop but not in SSAS 2019. So use this method as an experimental method AT YOUR OWN RISK!

Have you created a robust model in Power BI Desktop and you are looking for a way to import it to an instance of SQL Server Analysis Services Tabular? Hmm, it would be highly beneficial if you could import Power BI model to SSAS Tabular and it potentially saves lots of development time and costs. The good news is that with SQL Server 2016 and SQL Server Data Tools for Visual Studio 2015 it is possible. In this post I show you how to import Power BI Desktop model to SSAS Tabular 2016. Unfortunately, you cannot do the job in any prior versions of SQL Server, SQL Server Management Studio or SSDT.

Requirements

• SQL Server 2016 Tabular: You can download SQL Server 2016 Developer Edition for free. Check this out for more information
• SQL Server Management Studio (SSMS) 2016: Down SSMS 2016 from here
• SQL Server Data Tools for Visual Studio 2015 (SSDT 2015): You can download it here
• Power BI Desktop: Download Power BI Desktop from here

How it works?

The idea is to

1. Connect to Power BI Desktop model from SSMS 2016
2. Script the model
3. Modify the script
4. Execute the scripts on your on-premises instance of SSAS Tabular 2016
5. Open the new SSAS Tabular database in SSDT 2016
6. Modify the model
7. Redeploy and process the model

Note: Do not close Power BI Desktop until we completely import the model to SSAS Tabular.

Assumptions

I assume

• You’re familiar with all required tools listed above
• You’re familiar with SQL Server Analysis Services Tabular models and any corresponding concepts, security settings and so forth
• You’re familiar with DAX and Power Query

Creating a New Power BI Desktop Sample Model

Frist of all, I’d rather creating a new sample Power BI Desktop model. If you already have an existing model, ignore this part and go to the next section. To create the sample, I use the new SQL Server sample database called “Wide World Importers DW”. You can download it from here and restore it in an instance of SQL Server 2016.

• Open Power BI Desktop
• Get data from SQL Server

  

• Select “Fact.Sales”, click “Select Related Tables” then click “Load”

  

Note: Realistically, in a real project, we always have lots of DAX formula, calculated measures and calculated columns and so on in our Power BI Desktop model. We also have some transformations in Power Query like added conditional columns, split columns etc… So I add some simple calculated measures, a calculated column using DAX. I also split a column into two columns in Power Query during the next steps.

• Add the following measures to the “Fast Sales” table:
  • Total Profit = sum(‘Fact Sale'[Profit])
  • Total Quantity = sum(‘Fact Sale'[Quantity])
  • Total Sales Tax Exc = sum(‘Fact Sale'[Total Excluding Tax])
  • Total Sales Tax Inc = sum(‘Fact Sale'[Total Including Tax])
• Add a calculated column as below:
  • Sales = ‘Fact Sale'[Quantity]*’Fact Sale'[Unit Price]

  

• Open “Query Editor”
• Click “Dimension Employee”
• Split “Employee” column to separate employee first name and employee last name

  

  

  

• Close & Apply
• Save the model as PBIX

Import Power BI Desktop Model to SSAS Tabular 2016

So far we created a very simple model. Now we’re going to import it to SSAS Tabular using SSMS 2016.

• Connect to Power BI Desktop model from SSMS 2016. If you don’t know how to do that check this out.

  

• Script the database

  

• As you see the database name is a random GUID and you don’t want to create a SSAS Tabular database with a GUID name. So replace the database name and id with a meaningful string

  

  

• Copy the scripts
• Connect to an instance of SSAS Tabular
• Open a new XMLA query
• Paste the scripts (the generated script is NOT XMLA anymore, it is JSON indeed)
• Execute the scripts

Voila!

We successfully imported the Power BI Desktop model to SSAS Tabular 2016, but, we are not done. If you process the database it fails so we need to resolve the issues. As you might guessed the first things we need to change are the data sources. Of course you can modify the data sources from SSMS 2016, but, remember, we made some changes in the “Dimension Employee” table as well. These sort of Power Query changes lead the database processing to fail. To resolve these sort of issues the best way is to import the SSAS Tabular model to SSDT 2015, fix the issues, deploy and process the database.

Important Updates (June 2017):

Update 1:

After you copy and paste the scripts, take extra attention to “CompatibilityLevel” line. This is important as the later versions of Power BI Desktop are supporting CompatibilityLevel of 14. So if you want to run the scripts in a SSAS Tabular 2017 instance, you don’t need to change the CompatibilityLevel. But, if you run the scripts in a SSAS Tabular 2016 instance, then you need to change the CompatibilityLevel to 12 which is supported by SSAS 2016. If you don’t change the CompatibilityLevel to 12 and you run the generated scripts in SSAS Tabular 2016 then you’ll get the following error message:

“The JSON DDL request failed with the following error: Failed to execute a JSON script. Error returned: 1400 is not a valid value for this element.”

After changing the CompatibilityLevel to 12 the scripts should run successfully.

Update 2:

If you’re scripting the database using SSMS 2017 and you try to run it on a SSAS Tabular 2016 instance, then you  might get the following error message:

“The JSON DDL request failed with the following error: Unrecognized JSON property: isPrivate. Check path ‘model.tables[0].isPrivate’, ”

As you see the error message complains about “isPrivate” JSON property. Apparently “isPrivate” is a new JSON property which is NOT supported in older versions of SSAS Tabular models.

So, you need to search “isPrivate” in the scripts and remark that line, then run the scripts and hopefully you don’t get any other error messages.

The other finding on generating scripts in SSMS 2017 is that if you enable “Auto Date/Time” under “Time Intelligence” setting in Power BI Desktop option, it automatically creates hidden Date dimension for each field in the model that has Date or Date/Time data type. So when select a column with Date or Date/Time data type, Power BI automatically picks the related date column from the auto-generated hidden Date dimensions. It gets more interesting when you select a date column in Power BI Desktop, it creates a date hierarchy automatically. This is made possible using a features known as “Variation”. This feature is also available in SSAS Tabular 2017. So as far as you generate the scripts of your Power BI Desktop model in  SSMS 2017, it generates JSON codes for “Variation” feature. This feature is NOT available in SSAS Tabular 2016, so when you run the scripts in an instance of SSAS Tabular 2016, you get the following error message:

“The JSON DDL request failed with the following error: Unrecognized JSON property: variations. Check path ‘model.tables[0].columns[1].variations’”

To resolve this problem you need to remove all “Variations” blocks from the scripts which might be a time consuming process.

If you run the same scripts in an instance of SSAS Tabular 2017 then you don’t get any of the above error messages.

Conclusion:

If you like to import your Power BI Desktop model to SSAS Tabular 2016 then I recommend you use SSMS 2016 to generate the scripts and then run it in SSAS Tabular 2016. But, if you would like to import the model to SSAS Tabular 2017 then using SSMS 2017 would be alright.

Import SSAS Tabular Model to SSDT 2015

• Open SSDT 2015
• Create a new project
• Select “Import from Server (Tabular)
• Enter a name for the project then click OK
• Enter SSAS Tabular server name in “Tabular Model Designer”
• Test connection then click OK
• Select the database you recently imported from Power BI Desktop and click OK

What is in the Model

So far we successfully imported the model from SSAS Tabular server. In the next steps we resolve all the issues, redeploy the model to the server and process the database. But, let’s see what we have in the model that was imported from Power BI Desktop. You remember we created some calculated measures and a calculated column in “Fact Sales” table. Let’s see what happened to those calculated objects after we imported the model to SSAS Tabular server. Opening “Fact Sales” table you’ll immediately notice that all calculated measures are there. Scrolling to the right you’ll see the calculated column is imported successfully as well. That’s really cool isn’t it?

Let’s see what happened to the Power Query changes we made in “Dimension Employee”. Open “Dimension Employee”. We split the “Employee” column to two columns, “First Name” and “Last Name”.

Well, both “First Name” and “Last Name” exist in the SSAS Tabular model.

Let’s see what happens behind the scene in “Dimension Employee” table. Click “Table Properties” from “Table” menu.

Looking at the SQL statement you’ll quickly see the problem regardless of the error message we get. We haven’t modified the data source so it is trivial to get error message in “Table Properties”.

It’s a “SELECT * FROM” query so we’ll get all columns from the source table and the source table doesn’t include “First Name” and “Last Name”. Therefore, what ever we’ve done in Power Query, are useless as we need to redo all of them.

Switch to “Diagram” view to see the model diagram.

As you see there are lots of hidden tables that weren’t in the original Power BI Desktop model either. But, where they came from? Click of those tables and look at the formula.

Well well! We have several date dimensions created to support automatic date hierarchies which announced at Power BI Desktop November update. The reason we have that much hidden date tables is that Power BI Desktop creates a calculated date table for each date column we have in the model. Look at the model diagram to see each of these date dimensions have a one-to-many relationship with a column of date/time data type. For instance, select “Dimension City” table and have a look both hidden tables linked to it.

Let’s move forward and fix all the issues.

Fixing the Issues

• Click “Existing Connections” from “Model” menu

• Select a connection from the list

• Click “Edit” and modify the connection to point to you source database in SQL Server. In my sample it is “Wide World Importers DW”
• Do not forget to setup “Impersonation”
• Save the connection

• Repeat the same process for all other connections

• Select a table then open “Table Properties”

• Click “Validate”
• You might get an error that indicates there is something wrong with the SQL statement. Review the SQL statement, fix the issue then click OK

• In my sample the issue is the table name. As you see in the above screenshot the SQL statement is querying [Dimension City] which doesn’t exist in the “Wide World Importers DW” database. This is how I originally named the table in Power BI Desktop. Replacing the query with the correct one fixes the issue.

You need to repeat the same process for all tables. As I mentioned before, all changes you made in your model using Power Query are useless in SSAS Tabular. So here is the SQL statement I had to write to do the same thing on the “Employee” table to split the “Employee” column to “First Name” and “Last Name”. This will be different for different scenarios.

After fixing all issues we can process the database.

You can now deploy the fixed model to SSAS Tabular server.

Conclusion

Although importing Power BI Desktop to SSAS Tabular 2016 is doable, but, it is NOT officially supported by any Microsoft BI tools. So, importing Power BI Desktop model to SSAS Tabular should be considered as an alternative way to developing a SSAS Tabular 2016 model from scratch. At the end of the day and as always, it really depends on your case. The reasons that this method might not suit your case are:

• Power BI Desktop is updated more regular than SSAS Tabular 2016, So in some point you might face DAX version incompatibility between the Power BI and SSAS Tabular 2016
• As mentioned before, Power Query transformations won’t get imported to SSAS Tabular 2016 model. Therefore, you’ll end up with developing a new SSAS Tabular model from scratch if you have complex Power Query transformations in the model like several user defined functions, query parameters and so forth

On the other hand, if your project budget is too tight or you do not have enough recourses assigned to the project and you do not have lots of complex transformations in your Power BI Desktop model then using this method to import the model to SSAS Tabular 2016 will save you lots of time and budget.