DAX Studio is a great external tool to write, execute and analyze DAX queries in Power BI. A user now has the ability to not only analyze data using DAX but also export data from the Power BI report to SQL tables and CSV files.
In this tutorial, we will be learning how to export data from the Power BI report to the SQL server to perform analysis using SQL. This feature is great to use when we have the PBI report but we can’t access the data source directly in SSMS to perform analysis on the data.
Follow the instructions to export data from the Power BI report to SQL server to perform analysis using SQL
1. Download DAX Studio version 2.13
To begin with the process, you first need to have the latest version of DAX studio as this feature is not available in the older versions.
2. Open PBIX file
In order to connect DAX studio to Power BI, open your Power BI file.
3. Connect DAX Studio with PBI Report
Now open DAX Studio and in Data Source settings select PBI/SSDT Model option. In the dropdown menu, you‘ll be able to see the report you opened in the previous step.
Select the desired report from the dropdown.
Connect to the report.
4. Exporting Data to SSMS To Analyse Data Using SQL
From the main stage, navigate to the toolbar and select the Advanced menu.
Select Export Data option.
In Export Data Wizard, select SQL Tables.
5. Provide Connection to SQL Server
Provide a server name. It will be localhost in our case. It is compatible with Windows authentication and SQL server authentication. We will be using Windows authentication.
Provide DB name you want to store data into. You need to create a separate database if you don’t have one already.
Let schema be dbo.
6. Select Tables to import into SSMS
In this step select the tables you need to import into SSMS.
We are deselecting the Include Internal tables option as we don’t want to load any system generated table.
We don’t have any hidden tables so deselect the option.
Export the selected tables.
Export successful.
7. Analysing data in SSMS using SQL
Go to SSMS and navigate to Databases.
Expand the tables in your DB and you’ll be able to see your imported tables.
Analyze your data in SSMS.
Exporting Power BI data to SQL Server provides a scalable and structured way to analyze and manage business intelligence at an enterprise level. By using tools like DAX Studio and the SQL Server Import and Export Wizard, users can efficiently migrate their Power BI PBIX data into a relational database environment. This process enhances data accessibility, supports complex queries, and allows integration with other systems.
If you’re looking to streamline your Power BI workflows or need expert guidance on implementing SQL Server solutions, reach out to our Power BI specialists for tailored support.