Replace 2021 March:
Now you can export the info direct from Energy BI Desktop utilizing my device, Energy BI Exporter. Learn extra right here.
In a few of my previous posts, that are the preferred ones, I defined tips on how 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 an easy method to export the mannequin knowledge as a complete to both CSV or SQL Server with DAX Studio.
After launch 2.8 of DAX Studio, now you can shortly 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
Comply with the steps defined above, however this time choose “SQL Server” as vacation spot. For the “Connection String” comply with 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’ll be able to 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 “Permit 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 identify (it’s dbo in my case)
- When connecting to a Premium workspace you might face export failure because of question timeout
- When exporting knowledge to SQL Server
- in case you depart the “Schema Identify” clean you’ll get an error that vacant schema just isn’t allowed
- in case you enter an current schema identify the info might be exported to tables with very same identify as they’ve in your mannequin
- in case you enter a brand new schema identify 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 present knowledge might 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