Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
A previous blog explains how to write DAX queries. This one shows how you can use DAX queries in the following applications:
Application | Notes |
---|---|
SSMS | A refresher on how to run DAX queries within SQL Server Management Studio. |
SSRS | How to create data sources in Reporting Services based on the output from DAX queries. |
SSIS | Creating a source to import data from a DAX query in SQL Server Integration Services. |
Excel | Running DAX queries in Excel to import data from tabular models. |
PowerPivot | Importing data into a PowerPivot data model using DAX queries. |
Let's start with the first of these, and work our way down the list!
So you've deployed a tabular model. How can you write DAX queries to get information out of it within SSMS (SQL Server Management Studio)?
I've already mentioned this in my previous blog on how to write DAX queries, but thought it would be useful to explain this in more detail here.
The first thing to do is to make sure you're using your Analysis Services database, by connecting to it:
Choose to connect to Analysis Services.
Now list your databases, and choose to create a new query in the model of interest:
Choose this option (even though you actually want to create a query in DAX, not MDX).
From now on, you're on your own! You can type in your DAX query, but you'll need to get the syntax right:
Here I'm summarising total sales by species.
Note that you can't drag columns in from the cube on the left, since SSMS will assume that you're writing MDX and use MDX format, not DAX, to type in the column names.
When you've finished writing your query, you can execute it to see the results as shown above:
Click on the usual button to run your query.
Next up - including DAX queries within SSRS reports.
This page shows how to use DAX queries within SSRS.
I've assumed that you already know Reporting Services (if you don't, you could do worse than follow my colleague Andy Gould's excellent video tutorial, or book on a Wise Owl SSRS course).
The first step, as ever in SSRS, is to create a new data source:
Right-click on Shared Data Sources to create a data source.
Choose to base your data source on Analysis Services:
Choose SSAS as the type, then click on the Edit button (half-shown here).]
Now choose which deployed or current SSAS Tabular model you want to connect to:
Type in the server name, then choose your SSAS tabular database from the list as shown. The Wise Owl server shown here includes both workspace and deployed models.
When you choose OK a few times, you'll see your new data source:
I wish I'd renamed it, but hey ho ...
You can now create a report based on this data source (I've missed out all of these stages), and in this report create a dataset:
Right-click on your data source to create a new dataset.
In the dialog box which appears, choose to go into the Query Designer:
Click on the button shown to go into the query designer.
Now the weird bit - tell SSRS that you want to write a DMX query (even though you don't):
Click on the Command Type DMX tool shown (you'll get a scary message asking if you want to proceed).
Now switch to Design Mode:
Click on this tool to change your query view.
You can now either type your query into this window, or paste it in:
As for Management Studio, the onus is on you to make sure that your query makes sense. The best way to achieve this is to write the query in a DAX editor like DAX Studio first.
Here is this query, should you want to try this yourself:
EVALUATE
SUMMARIZE(
'Transaction',
Species[SpeciesName],
Quadrant[QuadrantName],
"Total sales",
SUM('Transaction'[Quantity])
)
You can now choose OK to return to SSRS, where you'll see your new dataset:
The column names aren't very snappy!
I've then renamed the dataset and all of the columns in it, to make them easier to work with:
The same dataset, but with better names for everything.
As things stand, you have no idea whether your dataset actually returns any data! The easiest way to test this is to create a tablix item, such as a table or (as below) a matrix:
A matrix showing total sales by species and quadrant.
When you preview your report, you should see data!
Things are looking good! SSRS ran your DAX query to show data.
For the next part of this blog I'll show how to import data from Integration Services using DAX queries based on a tabular model.
This page shows how to import data using SQL Server Integration Services (SSIS) and DAX queries.
As for SSRS, this page assumes a good level of familiarity with SSIS. If you're a newbie, you could follow my YouTube tutorial or enrol on our two-day introduction to SSIS course.
The first thing to do is to add a data source into a data flow task:
Add an OLEDB data source into a new data flow task within a new package.
Choose to create a new connection for this data source:
Click on this button when editing the source to add a connection.
Now choose to create a new connection:
Click on this button at the bottom right of the dialog box which appears, to add a new connection.
You can now choose the appropriate provider:
I've got SQL Server 2014 on my laptop, but I've chosen the provider for Analysis Services 11.0, which is the internal name for SQL Server 2012.
Now type in the server you want to connect to:
Type in the Analysis Services server name (yours will be different). The Location seems irrelevant for our purposes!
You should now be able to choose the model to which you want to connect:
Choose a tabular model from the drop list, ignoring the odd Initial catalog label to the left of it.
You should now have a dialog box looking something like this:
You've created a connection; time now to choose how you want to use it.
Choose the data access mode as an SQL command (even though it isn't), and type or paste in your DAX query:
Choose SQL command, and type or paste in the query.
I've gone for the following query again:
EVALUATE
SUMMARIZE(
'Transaction',
Species[SpeciesName],
Quadrant[QuadrantName],
"Total sales",
SUM('Transaction'[Quantity])
)
Even though it's not SQL, the Parse Query button still works:
You can parse this query to check you've got the syntax right.
The Preview button confirms whether you've got the right data:
Click Preview... | ... to run the DAX query. |
Time now to move from using DAX in SQL Server to retrieving data into Excel.
You can write DAX queries and use them to bring data into Excel, but you need to jump through a few hoops first!
A quick commercial: in addition to training in DAX, we also train in Excel.
The first step is to create a new connection (a file with extension .odc) in Excel:
On the Data tab of the ribbon, choose to link to Analysis Services.
Choose the Analysis Services server to connect to:
Your SSAS server will be different.
Now choose the model to which you want to connect:
Choose a model from the drop list, then click on any perspective it contains (here I have no perspectives, so I'm linking to the entire Model).
You can now give your connection a name, and finish it:
Give your connection a memorable name. Where did that Z come from, I wonder?
Choose to create the connection only:
Choose just to create the connection (we don't create a pivot table at this point).
Having created your connection, locate it in Windows Explorer:
A good place to look is the My Data Sources subfolder of My Documents.
Choose to edit the connection in a text editor:
Right-click on the new connection, and choose to edit it in a package like NotePad.
Search for the text CommandType:
Searching for CommandType takes you to the required line.
Change the word Cube to Query:
Overtype the word Cube with Query. This tells Excel to run a query (not surprisingly).
You now have a choice of two ways of proceeding. The easier way (if you're comfortable with editing text files) is to find and replace the command text for the query:
Here I've searched for the phrase CommandText, and replaced this property with the words EVALUATE Species, a legitimate (if simple) DAX Query.
You can now save this text file, and use the connection to import data into Excel.
Although this method may involve less mouse clicks, I think the alternative method shown below is easier to use and understand.
The other way to change the DAX query for a connection is as follows. First create a new Excel workbook, and choose to look at your existing connections:
Don't use your existing workbook, as you want to make sure that the connection isn't cached.
The easiest way to find the connection you've just created is to browse for it:
Click on the Browse for More... button to find a connection.
Find and double-click on your connection:
This is the connection I just created, complete with misprint.
Choose to bring data in from your connection as a table (say):
Choose to import the data into your existing worksheet (it won't work!).
You should now get the following error message:
You need to specify the DAX query (as below) in order to proceed.
Now choose to edit the underlying query for this connection:
Right-click on your failed import, and choose to edit the underlying query for it.
Type or paste in your DAX query command:
We'll use the same query again (no point re-inventing wheels, eh?).
Lo and behold, the table refreshes!
The table brings in the results from the DAX query.
It's a lot of effort to go to, when you could just base a pivot table on the underlying tabular model ...
For the final part of this blog, I'll show how to import data into PowerPivot using a DAX query.
This page shows how to import data into a PowerPivot data model by running a DAX query.
From Excel, go into PowerPivot and choose to import data as follows:
Choose to import data from Analysis Services.
Choose a server (I've gone for .\sql2012, but yours will be different):
Type in the name of your server.
Further down in the same dialog box, you can now choose the model from which you want to extract data:
We'll take data from our tutorial model.
On the next step of the Table Import Wizard, you can type or paste in your DAX query:
Here I've not only typed (OK, pasted) in a DAX query, but I've clicked on the Validate button (not shown in the diagram) to check it's OK. Reassuringly, PowerPivot tells me that my MDX statement is valid, even though it isn't actually an MDX statement!
PowerPivot now loads the data just like for any other table:
The results of running the DAX query shown above.
You can obviously use the same trick to import data into PowerPivot from an Analysis Services multi-dimensional model cube, using MDX.
And with that, I have run out of applications in which to use DAX queries!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.