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
I. 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.
II. Open PBIX file
- In order to connect DAX studio to Power BI, open your Power BI file.
III. 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.
IV. 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.
V. 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.
VI. 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.
VII. 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.
We hope that you were successful in exporting data from the Power BI report to the SQL server to perform analysis using SQL. For more such content and regular updates, follow us on LinkedIn and Facebook.