560 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 three 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.
This page shows how to use DAX queries within SSRS.
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:
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.
|Parts of this blog|
25 Aytoun Street