562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
How to get data from tabular models using DAX queries in various programs
Part five of a six-part series of blogs
Knowing how to write DAX queries is one thing, but where are you going to use them? This blog shows how you can integrate DAX queries into SSMS, SSRS, SSIS, Excel and PowerPivot.
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.
|Parts of this blog|
25 Aytoun Street