Import Power BI Desktop Model to SSAS Tabular 2016

Import Power BI Model to SSAS Tabular

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

    Power BI Desktop Get Data

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

    Power BI Desktop Get Data

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]

    Power BI Desktop Table

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

    Power BI Desktop Query Editor

    Power BI Desktop Split Column

    Power BI Desktop Split Column

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

    SSMS Connect to Power BI Desktop

  • Script the database

    Script Power BI Desktop Model in SSMS

  • 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

    Script Power BI Desktop Model in SSMS

    Script Power BI Desktop Model in SSMS

  • 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

Import Power BI Desktop Model to SSAS Tabular

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

Error returned: 1400 is not a valid value for this element

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

image

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’,

error: Unrecognized JSON property: 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’

error: Unrecognized JSON property: 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 OKImport SSAS Tabular Model to SSDT
  • Enter SSAS Tabular server name in “Tabular Model Designer”
  • Test connection then click OKImport SSAS Tabular Model to SSDT
  • Select the database you recently imported from Power BI Desktop and click OKImport SSAS Tabular Model to SSDT

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? Smile

Import SSAS Tabular Model to SSDT

Import SSAS Tabular Model to SSDT

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

Split Column

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

Import SSAS Tabular Model to SSDT

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

SSAS Tabular Table Properties

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

SSAS Tabular 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.

SSAS Tabular 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.

Calculated Table in SSAS Tabular

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.

Relationships in SSAS Tabular

Let’s move forward and fix all the issues.

Fixing the Issues

  • Click “Existing Connections” from “Model” menu

SSAS Tabular Existing Connections

  • Select a connection from the list

SSAS Tabular Existing Connections

  • 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

SSAS Tabular Existing Connections

  • Repeat the same process for all other connections

SSAS Tabular Existing Connections

  • Select a table then open “Table Properties”

SSAS Tabular Edit 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

SSAS Tabular Edit Table Properties

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

SSAS Tabular Edit Table Properties

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.

SSAS Tabular Edit Table Properties

SSAS Tabular Data View

After fixing all issues we can process the database.

SSAS Tabular Process Full

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.

23 thoughts on “Import Power BI Desktop Model to SSAS Tabular 2016

    1. very nice post!
      I have a created initial Power BI Desktop report from connecting to Power BI Service using Live connect method.
      Is it there any way I can push this to SSAS?

      1. Hi Kranthi,

        Welcome to BIInsight.com.
        When you create a report on top of a dataset in Power BI Service you’re treating the Power BI dataset as your semantic model. Just like when you connect live to an instance of SSAS Tabular model. In that sense you’re using Power BI Desktop as a report authoring tool only while the data model kept in the dataset in Power BI Service.
        So it makes sense to say you’d like to import the dataset in Power BI Service to SSAS Tabular. In that sense, you need to download the dataset from Power BI Service and using the method explained in this post you can import the model into SSAS Tabular.

        Hope that helps,
        Cheers

        1. Thanks for your reply. If possible could you please give the process to download the dataset from Power BI Service to SSAS Tabular

          1. Hi Kranthi,

            Welcome to BIInsight.com.
            If your Workspace is backed by a Premium capacity then one easy way is to connect to the dataset using SSMS through XMLA endpoints, then rest would be quite similar to waht you’ve learned in this blogpost, scripting the model and rest…
            If it is NOT backed by a premium capacity then one way is to download the report (if you have sufficient rights to do so), open the PBIX file in Power BI Desktop then the rest is the same as the procedure explained in this blogpost.

            Hope that helps.
            Cheers.

  1. Well even in 2019 still a very nice post!
    I still got a question here: In the section “Import Power BI Desktop Model to SSAS Tabular 2016” followed by the Updates to that passage you say:

    Could you please tell me what is meant when talking of “Variations” blocks? I’m really lost! I have to deal with exact that problem…

    1. Ok, I got trolled by remarking 😀 don’t use XML remarks in the replies, lol.

      Here is the missing part I was refering to:

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

    2. Hi there,

      Welcome to BIInsight.com.
      Happy to see you found this post helpful.
      In regard to “Veriations”, as explained in the post, when you use Auto DateTime option in Power BI Desktop, it automatically creates hidden date tables.
      So your model end up having a few hidden date tables to support Time Intelligence in Power BI.
      The number of those hidden date dimensions depends on the number of columns with Date or DateTime data type.
      Power BI generates a separate table for each Date or DateTime column.
      Now what “Variation” is, it is a features that allows you to create Date hierarchies on top of a Date column.
      This feature wasn’t available in SSAS Tabular 2016 therefore you get that nasty error.
      Have a look at “Hierarchy & column reuse” section of this article.

      Hope I didn’t make you even more confused. 😉
      Cheers.

    1. Hi Justas.

      Welcome to BIInsight.
      Well, I haven’t tried with Power BI Report Server, but I expect it to work as the principle is the same.
      As you can also connect to an opened Power BI Desktop Report Server, this method should work.

      Cheers

  2. When I am trying to open the new XMLA query it asks me to connect to SSAS again even thou I did connect to it. When I do past and try to execute Query it gives me error “The following syntax error occurred during parsing: Invalid token, Line 178, Offset 13, ].” On that line in script there is only ], that is all. Anyone know what could be cause of the problem is it connection or script or maybe both?

  3. Hi,

    Thanks a lot for this.
    I managed to generate my script but when I execute this script in my local instance of SSAS Tabular I get this error message:
    The following system error occurred: The request is not supported.
    Session cancelled per end session header in user request.

    Any idea what I might be doing wrong?

    Thanks,
    Sammy

  4. Hi.
    My powerbi projects link to a mysql localhost DB.
    I imported powerbi scripts; now I’ve to change the links to the DB
    How can I dow, please?

  5. well done!
    but I’ve 2 problems:
    1) in .pbix I used mysql tables on localhost server
    2) I can’t develop to my localhost ssas
    I’m searching on www but nothing…(I’m a newbie)

  6. Hi Soheil Bakhshi
    I have followed your steps above.
    Opened the PBIX file.
    Genereated the create script
    Connected to my local SSAS Tabular instance
    Modified the DB Name & CompatibilityLevel

    When I try and create the DB in SSAS Tab, I get the following message:
    Executing the query …
    Power BI datasets using M based data source format are only supported in Power BI Premium services.
    Run complete

    Can you assist me with getting this tab model created on my local SSAS?

    1. Hi Daryll,

      Welcome to BIInsight.com.
      This is an old post and as mention at the very first paragraph, this method works with Power BI Desktop June 2017 and SSAS Tabular 2017.
      But I can imagine that we need to take some extra steps to either get it done or fail.
      After all, there are heaps of new DAX functions and Power Query capabilities available in Power BI Desktop that are NOT available in SSAS 2019.
      I’ll add a notice line on top of this post to give the visitors a heads up that this method might NOT work anymore.

      Thanks for bringing this to my attention.
      Cheers.

  7. Hi,
    We were able to connect to Power BI Desktop model from SSMS 2017 but wasn’t able to generate the script – getting the error message “Object reference not set to an instance of an object. (Microsoft.AnalysisServices.AppLocal.Tabular)”.

    Noticeably as well that after connecting the PBI model, we cannot see any connections.

    Can you assist me on this?

    Thanks in advance.

    1. Hi there,

      Welcome to BIInsight.com.
      What is your Power BI Desktop version?
      Have you enabled enhanced metadata?
      What’s the target SSAS Tabular model version?
      Can you use the latest SSMS?

      1. Hi,

        PBI version July 2020.
        Enhanced metadata is enabled.
        SSAS tabular model version 15
        SSMS is version 17.9.1

        Thanks,

        Imelda

        1. Hi Imelda,

          Please note that the method I explained in this blogpost is not supported by Microsoft.
          There are many new functions in DAX and M that are not supported even in SSAS 2019.
          Having said that, let’s try the upgrading your SSMS to the latest version which is currently at 18.6. You can download it from here.
          You have to change the compatibility level to 1500, then find and remove remove “defaultPowerBIDataSourceVersion”: “powerBI_V3” line and “sourceQueryCulture”: “en-NZ” from the scripts.
          Please let us know how you progress.

          Cheers.

          1. Hello Soheil, nice write up and well explained. I am also using SSAS 2019 on the latest release 15.0.34.22. Followed your steps to edit the script and then it executed fine. One problem is the data connections do not exist. Understand this method is unsupported. Would be nice if SSAS 2019 was also on the same compatibility level as PBI desktop 1520.

Leave a Reply

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


The reCAPTCHA verification period has expired. Please reload the page.