This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 51%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECD%3C/text%3E%3C/svg%3E)
I have a relatively small 57MB tabular model that I would like to query with Excel Pivot table connections. The SSAS server is 2019 and Excel is 2016. It returns the correct answers and behaves as expected but I am surprised that it is sending MDX queries to the server. This appears to be running against disk rather than in-memory since it is running MDX instead of DAX? I tried the same thing from browsing the model in SSMS and it generates MDX as well. I know that Power BI generates DAX queries regardless of SSAS mode so I gues this isn't suprising.
My question is what options are there for "end user" (no code) tools to efficiently query tabular models with DAX? My user base is all on Excel and I would like to build tabular models for them but the performance is severly lacking compared to the multidimensional counterpart. Power BI will come eventually but I have a large portion of users that prefer to live only in Excel. I am really hoping for a plug in or some other way for Excel to generate DAX queries instead of MDX.
Thanks for any help!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 9%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELY%3C/text%3E%3C/svg%3E)
Hi,
This a setting that you can change in Excel Connection Properties. You could create and modify odc file to specify use DAX instead of MDX to retrieve data.
You could find detail steps in Marco Russo's article : Import Data from Tabular Model in Excel Using a DAX Query
Regards,
Lukas
I ran across this article when reseraching this. It appears it allows DAX queries to be written but Excel is not generating the DAX itself. I reallly am searching for a no code option so business users can access data via drag and drop.
Ok, understood.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 85%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDG%3C/text%3E%3C/svg%3E)
This appears to be running against disk rather than in-memory since it is running MDX instead of DAX?
This is incorrect, queries against tabular models NEVER run against on-disk storage. An MDX query may run slightly slower than an equivalent DAX query as MDX has slightly different query semantics, but regardless of the query language used all queries against tabular models are resolved against the in memory storage.
My question is what options are there for "end user" (no code) tools to efficiently query tabular models with DAX?
Power BI Desktop is the main tool available at the moment which will generate DAX queries. Or if you are not interested in pretty charts and just want to view tables of data the Query Builder in DAX Studio can do this.
but the performance is severly lacking compared to the multidimensional counterpart
Have you compare the performance of an equivalent DAX query? It is possible that you have a general performance issue with one or more of your measures which is unrelated to the query language being used. But there are also still some types of models that still work better in multi-dim than in tabular.
I am fairly new to DAX so I am not sure if I would be writing equivalent MDX/DAX statements to compare performance. I will download DAX Studio to build them and compare to the MDX SSMS or Excel generates.
It is good to hear that both run in memory. I was very surprised that such a simple lightweight model renders members so slowly. 20K members aggregating 1M rows does not seem like something Excel should struggle with (especially if it is all in memory).
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
With your SSAS/Excel setup, the calculations are mostly done on the server. Excel's job, once the query has been sent to the server, is to receive the resulting set and to populate the worksheet with it. This could take some time if the result set is huge, but is usually the smaller part of the total workload.
As to the model's performance, even at your size it is possible to have a combination of data model and measures that'll perform poorly. It appears that you tried to replicate an existing MD cube with a tabular model - the two engines are quite different, and could well require vastly different designs.
Running queries in Power BI are almost instantaneous compared to around one minute to pull Sales by Zip returning around 20K members. The dimension has roughly 100K members which I do not consider that large. I am baffeled at how Microsoft does not offer similar optimizations for Excel users - who I believe are still the majority of Power Users/Analysts that keep the gears of the organization turning.
I found a few articles regarding "MDX Fusion" which offers optimizations if running Azure Analysis Services but it doesn't appear there are any plans to do so for on prem software.
https://ideas.powerbi.com/ideas/idea/?ideaid=6b331f0e-52b9-4367-b2b3-ded3b9608a13
This is going to a hard sell for my users. Every release (since 2014) I make an effort to move to tabular models but there always seem to be show stoppers that are not being addressed. Lame that tabular Excel support is essentially abondonded.
I did some search and ran across several articles , sees like Excel had not provided yet a DAX generator for the query to Tabular model. Also those suggestions, in this aspect, are mainly focus on writing optimal DAX query manually. Perhaps this is not a satified answer to you.
I have found similar request as yours have been raised to Excel team, and I upvoted it. You could upvote it too, hope Excel product team could update this feature eventually : Create Pivot Tables connected to DAX Native Provider
Regards,
Lukas
Thank you for this. I'm glad there are at least a few others that are facing the same issue.
If my choices are to 1) Abandon Excel due to unacceptable performance with Tabular or 2) Use cumbersome multidimensional models with acceptable Excel performance - I am considering taking a break from Microsoft altogether.
The user community over the last 5 years at my current and prior jobs are not impressed with the Power BI capabilities we have demonstrated and refuse to do Analysis outside of Excel.
Really dropped the ball on this one Microsoft.
Actually I quite understand your disappointment on this case, our product team are trying their best to deliver good product and customer experience. Currently Power BI is very active to change and adding new features. I hope Excel or Power BI could commit some break through on this issue and provide you excellent solution soon :)
Reviving this thread. I was speaking with a colleague about new features being released and thought for a second that this capability was being offered with this new feature... https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel
I was able to install and get it to work but lo and behold it still runs old MDX queries against the SSAS tabular models. I had a user contact me about a very very slow query yesterday that he could not return results via Excel. I built the equivalent pivot in Power BI and it is nearly instantaneous.
Still very disappointed in the Microsoft BI stack for abondoning new features for Excel users (if nearly 10 years old is new).