Fast Suggestions: Export Energy BI Desktop and Energy BI Service Mannequin Knowledge In One-Shot with DAX Studio

Exporting Model Data to CSV
or SQL Server in One Shot

Replace 2021 March:

Now you can export the info direct from Energy BI Desktop utilizing my software, Energy BI Exporter. Learn extra right here.

In a few of my previous posts, that are the preferred ones, I defined the right way to Export knowledge Energy BI Desktop or Energy BI Service knowledge to completely different locations like CSV, Excel and SQL Server. On this fast tip I clarify a very simple solution to export the mannequin knowledge as a complete to both CSV or SQL Server with DAX Studio.

Daniil from XXL BI nicely defined this methodology, however I’d slightly rapidly clarify the way it works and add some extra info.

After launch 2.8 of DAX Studio, now you can rapidly export the entire mannequin to CSV and SQL Server in a single shot.

Enabling Export All Knowledge in DAX Studio

Open DAX Studio
Click on “File”
Click on “Choices”
Click on “Superior”
Tick “Present Export All Knowledge button”

DAX Studio Export Power BI Model Data Settings

Export Energy BI Mannequin Knowledge to CSV

DAX Studio Export Power BI Model Data to CSV

Export Energy BI Mannequin Knowledge to SQL Server

Observe the steps defined above, however this time choose “SQL Server” as vacation spot. For the “Connection String” observe the under steps to get it proper right away.

Generate Connection String with a UDL File

Create a textual content file in your machine, you may merely proper click on in any desired folder then “New” then click on “Textual content Doc”

Rename the file and swap .txt extension with .udl

Open the UDL file (double click on)
From “Suppliers” web page, choose SQL Server OLE DB Supplier for SQL Server then click on Subsequent
Enter Server Identify
Kind in your SQL Server Person Identify and Password
Tick “Enable saving password”
Choose the vacation spot database
Click on “Take a look at Connection” button to ensure the connection works then click on OK

Now open the UDL file in Notepad

Person Connection String in DAX Studio

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

Open the UDL file in Notepad
Copy the connection string ranging from after the “Supplier” part
Paste it in DAX Studio in “Connection String” field
Enter a schema title (it’s dbo in my case)

Export Power BI Model Data to SQL Server

Concerns

When connecting to a Premium workspace chances are you’ll face export failure as a consequence of question timeout
When exporting knowledge to SQL Server
- in the event you go away the “Schema Identify” clean you’ll get an error that vacant schema is just not allowed
- in the event you enter an current schema title the info will likely be exported to tables with very same title as they’ve in your mannequin
- in the event you enter a brand new schema title then DAX Studio creates a brand new schema then generate the tables in that schema then exports the info
- whether or not you tick the “Truncate Tables” or not the prevailing knowledge will likely be synchronised with the supply knowledge in Energy BI. (it doesn’t append knowledge)
As you in all probability guessed, this methodology additionally works completely for exporting SSAS Tabular mannequin and Azure Evaluation Providers knowledge