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

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”

Export Power BI Model Data to CSV

Connect to your Power BI Desktop or connect to your Power BI Premium capacity datasets in Power BI Service

Click “Export Data” from “Advanced” tab

Select “CSV” as destination

Enter the output folder path

Click “Export”

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”

Rename the file and swap .txt extension with .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

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)

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

Like this: Like Loading...