- We have previously seen how to direct the output
of a DAX query to a CSV file or to Excel.
But that is not all DAX Studio has to offer you.
You also have a wizard that helps you export
the entire content of a database or of specific tables
both as CSV files or using SQL Server tables.
And this is the exporting data feature.
You start from the export data button
that launches a wizard from where you can choose
either CSV or SQL tables
and that goes through two different paths.
One, if you want to export data using a CSV file
and the other one if you want to export data
over a SQL Server database.
We are going now to see both features step-by-step.
Let's start.
As I said, you can direct the output
of this query to a file
but if you want to export large amount of data,
then the export data wizard is the tool for you.
You can choose to use a CSV file.
You provide the path, let's as usual go to C: Temp.
You choose the format to use in order to do the exporting,
just culture default list separator,
or comma, tab or any other,
whether you want to quote strings or not,
then you click on next and you choose the tables
that you want to export.
Let's say that we are not interested in everything
but only in customer, dates and product
and finally we click on Export.
Once that is done it starts exporting
all the data inside CSV files.
Now as it has finished,
we only need to open the folder which I need to do,
let me open the temp and see the result.
You see that we have customer, date and product,
the three files that we generated.
If I open date for example,
we can just look at it using Notepad
and this contains the full content
of your table as a CSV file.
This might be useful if you need
to perform large exports of data
to move data back and forth in different places.
Now this is good if you want to create CSV files
but you also can use the export data button
in order... to export to SQL tables.
In that case you need to provide the server,
demo, then the database, which I don't remember,
I need to go and check it.
The database is called DAX Studio,
DAX Studio whether you want to use Windows authentication
or SQL Server authentication,
I'm gonna use Windows authentication, click on next.
And again, you choose what you want to export.
Let's go for customer, date, product as we did earlier,
and then you just click on export.
This starts a connection with the SQL Server database,
creates the table for you
and outputs all the information inside SQL Server.
Once that is finished,
you can open with SQL Server Management Studio
the model, I'm now connected to the database DAX Studio
on this machine and I need to refresh the tables
because they were not yet there.
And we have customer, date and product.
Let's look at customer, we select the top thousand rows
and here we are.
You have all the information available,
customer key, customer code, the name, birth date,
marital status, all the information
that were previously stored in the Power BI file
are now stored in SQL Server.
This feature is extremely useful
because it gives you the option of exporting data
for example, from inside Power BI to SQL Server
to do further processing later.
And it's, I think the only tool available on the market
that makes it so simple to move data
outside of Power BI or a Tabular model
inside CSV or SQL Server databases.