Quick Tips: Export Power BI Desktop and Power BI Service Model Data In One Shot with DAX Studio

Exporting Model Data to CSV 
or SQL Server in One Shot

In some of my old posts, which are most popular ones, I explained how to Export data Power BI Desktop or Power BI Service data to different destinations like CSV, Excel and SQL Server. In this quick tip I explain a very easy way to export the model data as a whole to either CSV or SQL Server with DAX Studio.

Daniil from XXL BI well explained this method, but I’d rather quickly explain how it works and add some more information.

After release 2.8 of DAX Studio, you can now quickly export the whole model to CSV and SQL Server in one shot.

Enabling Export All Data in DAX Studio

  • Open DAX Studio
  • Click “File”
  • Click “Options”
  • Click “Advanced”
  • Tick “Show Export All Data button”
DAX Studio Export Power BI Model Data Settings

Export Power BI Model Data to CSV

DAX Studio Export Power BI Model Data to CSV

Export Power BI Model Data to SQL Server

Follow the steps explained above, but this time select “SQL Server” as destination. For the “Connection String” follow the below steps to get it right straight away.

Generate Connection String with a UDL File

  • Create a text file on your machine, you can simply right click in any desired folder then “New” then click “Text Document”
Create a TXT file
  • Rename the file and swap .txt extension with .udl
Rename TXT file to UDL
  • Open the UDL file (double click)
  • From “Providers” page, select SQL Server OLE DB Provider for SQL Server then click Next
  • Enter Server Name
  • Type in your SQL Server User Name and Password
  • Tick “Allow saving password”
  • Select the destination database
  • Click “Test Connection” button to make sure the connection works then click OK
Create Connection String with UDL
  • Now open the UDL file in Notepad

User Connection String in DAX Studio

Now that we’ve generated the connection it is time to use it in DAX Studio.

  • Open the UDL file in Notepad
  • Copy the connection string starting from after the “Provider” section
  • Paste it in DAX Studio in “Connection String” box
  • Enter a schema name (it is dbo in my case)
Export Power BI Model Data to SQL Server

Considerations

  • When connecting to a Premium workspace you may face export failure due to query timeout
  • When exporting data to SQL Server
    • if you leave the “Schema Name” blank you’ll get an error that empty schema is not allowed
    • if you enter an existing schema name the data will be exported to tables with exact same name as they have in your model
    • if you enter a new schema name then DAX Studio creates a new schema then generate the tables in that schema then exports the data
    • whether you tick the “Truncate Tables” or not the existing data will be synchronised with the source data in Power BI. (it doesn’t append data)
  • As you probably guessed, this method also works perfectly for exporting SSAS Tabular model and Azure Analysis Services data
Exporting SSAS Tabular Model data to SQL Server with DAX Studio

4 thoughts on “Quick Tips: Export Power BI Desktop and Power BI Service Model Data In One Shot with DAX Studio

  1. Thanks for the guide, using the Export button in the Advanced tab exported all reports in the Power BI file. Is there a way to export specific table / report?

  2. Hi, thanks for the guide. Very much appreciated. Is there a limit to the amount of data that can be exported or all the data will be exported to CSV and SQL?

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.