Power BI Ideas Admin on 27 Dec 2019 19:05:27
When users connect to a data model using Analyze in Excel, the model is queried using MDX.
However, all the performance tuning tools available are geared towards DAX.
I think the ability to navigate a PBI model using Excel is great, and helps get stubborn uses on to using curated data sets without having to get them to make the even bigger jump to moving away from their beloved excel.
However, we're noticing performance issues when MDX queries are hitting the model. If I write a query in DAX I can return my desired results in around 2 seconds, where as it can be minutes when MDX is used.
Is there any scope/desire for the Analyze in Excel functionality to be re-engineered to query the PBI data model using DAX queries instead of MDX?
- Comments (4)
RE: Analyze in Excel using DAX
This is a much needed feature we have been waiting for. Often DAX queries can perform 10x or better than MDX in some cases. We have resorted to making users run queries in Power BI reports and manually export them because the MDX from Excel to get the same data can take so long in some cases. Yes there are things we can do to the model and performance tuning but when the result is the exact same data but taking 10x or more time using MDX then something needs to be done.
RE: Analyze in Excel using DAX
While Excel is a popular choice for business data analysis, using it with a Power BI semantic model currently faces performance challenges. This is due to the reliance on MDX queries, which are not optimized for Power BI. To address this, it's critical to prioritize the development and release of an Analyze in Excel Add-in that utilizes DAX queries instead. This will significantly enhance performance and provide a more efficient solution for users.
RE: Analyze in Excel using DAX
Excel is the favorite tool for the business to crunch Data but today the performance of Analyze in Excel connected to PBI sementic model is very bad because of MDX queries not optimized for PBI.It's urgent to propose the Analyze in Excel Addin with DAX version !?
RE: Analyze in Excel using DAX
many users still use excel to access data from tabular models. I have personally observed several ocasions where MDX generated in excel pivot tables underperforms when compared to DAX generated in power BI table or matrix visual with the same output..some cases are as described: from seconds with dax to minutes with MDX. It would be great if Microsoft reworked their analyse in Excel to use dax or optimize the MDX generated.