- Now that we learned how to run a query, it's time to
understand, how can I change the output of a query?
So where can I send the result of a query?
Indeed, DAX Studio offers you several options.
Let's look at them together.
You can send the output to a grid
and this is exactly what we have done so far.
You run a query, you immediately look at the result.
But you can also send the result to time.
If you do that, then you are basically trashing the result.
You will not see the result and it is just thrown away.
And this is useful for performance analysis.
You can send the output to a file, a CSV file
or a file of which you can change the format.
And finally, a feature that I bet most
of you will really love, Excel.
You can send the output of a query to an Excel file,
either a static Excel file or a linked file.
Let's see all the features together.
Here we still have the same query we were using earlier
and it's running a SUMMARIZECOLUMNS by year
and producing the amount.
If I run the query, I see the result here.
This is just the query,
the result of a query sent on a grid.
But I can change the output.
For example, I can choose timer.
Let's add something, like for example,
the customer name.
Now I'm retrieving by year and name the sales amount.
And I chose time as the output.
If I run the query, you see that nothing happens.
I don't see the result.
And if I look at the result here, there is nothing.
So the query has been executed but I don't see the result.
So why this might be useful?
Well, the reason is,
sometimes, you are doing a performance analysis
and when you are doing performance analysis,
you are not interested in the actual result of a query,
but only in the time that it takes to execute the query.
Beside, if your query generates a large result,
as a consequence, DAX Studio needs to read this information
and this operation just reading the data takes time.
You don't want to pollute your timings
with this time to read the data
that you are not interested into.
That's why using time might be useful
because you trash the result
in order to have precise measurements about the time
needed to only execute the query
without having to worry about the time required
to gather the information.
So time is useful mainly for performance analysis
but there are other outputs.
I can send the output to a file.
Now, if I send the output to a file and I run the query,
you see that DAX Studio ask me a name.
Let me override, let's me delete this
and save again my query.
So I just click on save and this generated
a file named my query, which contains the result.
Let me open that folder.
Here I have my file.
I can just edit it
and you see that this contains the calendar year,
the name and the amount in a comma-separated file
that I can then use,
either to send it to somebody or to read it
through some other software.
This is a way to save the file.
By default, it uses a comma-separated file
but if you go to file, options,
under custom export format,
you have the option of choosing the delimiter
and whether you want to quote string fields or not.
There are other outputs.
I can send the output to the clipboard.
It's not worth making a demo of it
but it just sends the output to the clipboard
and from there, you can paste
in whatever software you might want,
Excel, Word or whatever.
Now, speaking about Excel,
you don't need to use copy and paste
in order to move the data to Excel
because there are two specific outputs designed for Excel.
You can generate a linked table
or a static table.
Let's start with a static table.
I chose it, now I run the query.
Again, a dial ask me to provide the name
and I do that, it's My Query in Excel.
Let me save it.
I now go back to my folder.
You see that I have My Query,
the file that I generated earlier and My Query in Excel.
If I double click My Query in Excel,
Excel starts and I have a file containing
the same information returned by the query
but in an Excel file.
From there, I can do whatever I want.
I mean, it's Excel, so you are free
to use this data to do whatever you like.
The thing is, this result
is a file containing static data.
What if I want a file
that can be refreshed?
In that case, I can use a linked output.
Run it again.
And I have a warning.
Let's read it together.
The warning says that there is a data connection.
Basically what is happening is that
DAX Studio is creating an Excel file
with a connection to the Tabular engine
which DAX Studio is connected to
and it will save both the result of the query
but also the text of the query
in a table that can be refreshed later.
Of course, we enable it because we want to see the result.
And it says that there is an error.
Let me try that again.
Enable, okay, now it worked.
The result is a table that contains the calendar year,
the customer name and the amount
but if you right-click on this table
and you go to table, edit query,
you see that it contains a query
that is EVALUATE, SUMMARIZECOLUMNS,
date calendar year, customer name, amount and sales amount.
This is the same query we generated
with a connection to the data model
to which DAX Studio is being connected.
So this is an Excel file
that is linked to the database
to which DAX Studio is connected
and can be refreshed later.
As you have seen, there are a lot of different options
that you can use in order to save your data.
There are still more.
We will see also how to export data,
not only on CSV files
but also on SQL Server
but let's save that for later.