Posted on in Power BI

Move your pbix datamodel to Azure Analysis services – #powerbi

After the Azure Analysis Services web designer was discontinued per march 1 2019 – link – there is no official tool to do a move of a PBIX datamodel to Azure Analysis Service. But by using a few different tools we do have ways of doing it anyway.

Step 1 – DAX Studio

Open your PBIX file in the Power BI Desktop and then open the DAX Studio (link to download) and connect DAX studio to the PBI model

In the status bar you will see the local port of the Analysis model running on your machine

Step 2 SQL Management Studio

Connect to Analysis Services using the server address from Step 1

This will give you a connection to the model

Now right click the Database and choose to script the Database to a new Query Editor window (or the clipboard)

Step 3 Connect to Azure Analysis Services

Use the SQL Server Management Studio to connect to Azure Analysis Services

Select to run a New XMLA Query on the server

And paste the query created in Step 2 in the new Query window – You can specify a model name in the highlighted area

Run the Query – and after a few seconds you should get this result – that the query has completed successfully.

And after a refresh of the server object you should see the newly scripted data model

Step 4 Finish the move

Now depending on the data sources in your model you need to setup the necessary connections and gateway.

And use your favourite Analysis Services tool to modify model – for instance the Tabular editor ( link )

You will have to go through all your data sources and Tables to make sure the connections and M-Scripts are functioning.

Tip

Before you script your PBIX data model – turn off the Time intelligence in your current file – otherwise you could get a lot of extra date/time tables in your model

27 thoughts on “Move your pbix datamodel to Azure Analysis services – #powerbi”

  2. Hi Erik,

    Thanks for this great workaround.
    It’s mostly working well but not in case of models having many to many relationship.

    The JSON DDL request failed with the following error: Failed to execute XMLA.
    Error returned: ‘Relationship between columns has the TO end cardinality set to Many. TO end cardinality must always be set to One.

    1. Hi haven’t tried it with a model with m2m relationsships – are you deploying to azure analysis services or on prem ? If its on prem ot might be a version issue /Erik

      1. I tried both. Azure AS and OnPrem (SQL Server 2017).
        From what I remember when you have m2m relationships in your model it’s becoming a composite one in PBI.

  3. Ok so my workaround ..
    -Saved my PBI as a template
    – Opened in Tabular Editor
    – Saved it as bim file
    -Edited the bim file and chande the compatibility version for 1470
    – Opened again the bim file in Tabular Editor
    – Deploy it to Azure Analysis Services.

    And it worked

  4. Pingback: From Power BI to Azure Analysis Services – Curated SQL

  5. HI ERIk
    Thanks for the Post , when I tried migrate the model to AAS i am getting the fallowing error “Object reference not set to an instance of an object. (Microsoft.AnalysisServices.Controls)” . Please let me know what is the issue .

  6. Hi Erik
    The issue is resolved when convert the PBI file to Template and then converting Bim file and updating compatibility.

    After deploying the Model to SSAS 2019 server , and trying to process the database we are getting the fallowing error

    A connection could not be made to the data source with the Name of ‘256dbe19-539f-4ad0-ab78-a1a38ea01ede’.
    COM error: System.Configuration; An error occurred loading a configuration file: The parameter ‘exePath’ is invalid.
    Parameter name: exePath.
    A connection could not be made to the data source with the Name of ‘f4b77470-f83b-4eda-aa72-4ec0a7c88c78’.
    COM error: System.Configuration; An error occurred loading a configuration file: The parameter ‘exePath’ is invalid.
    Parameter name: exePath.

  7. Hi Erik,

    Can I use this trick in reverse to migrate some legacy SSAS tabular models into a PBIX file?
    We are finding some small onprem tabular models are not agile enough for our needs.

    1. Hi Tim – I haven’t tried it. But I guess no – I would try and use the tabular editor instead – so create an pbit file open that in TE and then open a second TE and connect to your legacy model – and then use copy and paste the dimensions and measures between the two.
      Let me know if that works 🙂
      Erik

  8. THANK YOU Erik, this is great info! I have successfully extracted and reloaded the xmla tmsl code form a PBIX to Azure Analysis Services… but the connections are a mess and I’m having trouble determining next step to convert from powerbi to analysis services. can you share any references or details to convert the gobbledygook data sources that come out of PBI so that analysis services can take advantage? I have many sharepoint lists and files as sources.

    1. Hi Samuel,

      I haven’t really found any good references my self.

      If you have many sharepoint lists in your model the Model from pbix is properly filled with connections and that will be a pain to clean up when moving it to Azure Analysis Services.

      Auto time date should also be turned of in your PBIX to avoid many internal datetables.

      I would recommend that you streamline your PowerQueries to use a referenced connection to your SharePoint and then perhaps be able to limit the number of connections.

      Another way could be to change your PBIX to a Power BI Template and then use the Tabular editor to generate the script

      Hope this can help you further

      Erik

  9. Hi Erik,
    When or why would you chose Azure Analysis Service over Import model to PowerBI Datamodel?
    Is there any performance advantages? The reason why Im asking is that we are not 100% sure of this, and maybe you can help highlight it for me.. Thanks in advance

      1. Thanks a alot for your quick response and thanks for the link 🙂 Its a shame we need something from both worlds. We need pagniated reports, and then we need some good devops/deployment options. Hopefully they will soon make some better integration to devops or at least make it possible to deploy reports automatically (changing connectionstrings to specific enviornments)

  10. Thank you for your reply. In the March release of PBI there is a preview feature that looks promising “Using enhanced dataset metadata” (https://docs.microsoft.com/en-us/power-bi/desktop-enhanced-dataset-metadata) What do you think of this? After turning on the feature and resaving/converting the PBI files, the xmla looks like tabular TMSL! I am trying to troubleshoot the remaining error

    The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: ‘Power BI datasets using M based data source format are only supported in Power BI services.

    and am hoping to find a reference that shows Power Query in PBI vs Power Query in Analysis services.
    ‘

  11. Hi SAMUEL FEINBERG, I’m facing the same issue. Were you able to over come this error.
    The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: ‘Power BI datasets using M based data source format are only supported in Power BI services.

  12. Hi Erik, great post. I managed to create my model on AAS. I’m pulling data from an online source (API), but I’m being asked to install a gateway to refresh my data in SSMS. Any idea why?

      1. Hi Erik,
        Thanks for the quick reply. Yes – using SQL server management studio. I am using service account credentials in SSMS, but effectively the API is open (so in Power BI desktop I selected anonymous). Curious as gateways are only required for on prem, so I wondered if the Analyis Services model was somehow still connected to my Power BI desktop dataset? The error I get is “Failed to save modifications to the server. Error returned: ‘On-Premise Gateway is required to access the data source. Please install a unified gateway for the server or disable AlwaysUseGateway setting if it’s enabled.” I’ll see what I can figure out.
        Adam

  13. Any idea how to resolve this issue. not getting anything on internet
    The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: ‘Power BI datasets using M based data source format are only supported in Power BI services.

Leave a comment