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 most well-liked ones, I defined learn how to Export knowledge Energy BI Desktop or Energy BI Service knowledge to totally different locations like CSV, Excel and SQL Server. On this fast tip I clarify an easy technique to export the mannequin knowledge as a complete to both CSV or SQL Server with DAX Studio.
Daniil from XXL BI effectively defined this methodology, however I’d slightly rapidly clarify the way it works and add some extra data.
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”
Export Energy BI Mannequin Knowledge 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” comply with the beneath steps to get it proper right away.
Generate Connection String with a UDL File
- Create a textual content file in your machine, you possibly can 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 Title
- Sort in your SQL Server Consumer Title and Password
- Tick “Enable saving password”
- Choose the vacation spot database
- Click on “Check Connection” button to verify the connection works then click on OK
- Now open the UDL file in Notepad
Consumer 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)
Concerns
- When connecting to a Premium workspace it’s possible you’ll face export failure as a consequence of question timeout
- When exporting knowledge to SQL Server
- in case you depart the “Schema Title” clean you’ll get an error that vacant schema is just not allowed
- in case you enter an present schema title the info will likely be exported to tables with very same title as they’ve in your mannequin
- in case 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 most likely guessed, this methodology additionally works completely for exporting SSAS Tabular mannequin and Azure Evaluation Companies knowledge
