I didn’t understand why getting to the star schema was so out of reach so often until I was able to see it from another perspective. So, if everyone agrees on this point, why do so many resist this advice? This is a hard fact that every expert promotes, and self-taught data analysts either have learned or will learn through experience. If you do any legitimate research about Power BI (reading blogs, books or training from reliable sources), you will quickly learn that a lot of basic functionality requires a dimensional model, aka “Star Schema”. Lame that tabular Excel support is essentially abondonded.There is no secret about this. 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. This is going to a hard sell for my users. 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. 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. The dimension has roughly 100K members which I do not consider that large. Running queries in Power BI are almost instantaneous compared to around one minute to pull Sales by Zip returning around 20K members. But there are also still some types of models that still work better in multi-dim than in tabular. 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. 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.īut the performance is severly lacking compared to the multidimensional counterpart Power BI Desktop is the main tool available at the moment which will generate DAX queries. My question is what options are there for "end user" (no code) tools to efficiently query tabular models with DAX? 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. This is incorrect, queries against tabular models NEVER run against on-disk storage. This appears to be running against disk rather than in-memory since it is running MDX instead of DAX? I am really hoping for a plug in or some other way for Excel to generate DAX queries instead of MDX. Power BI will come eventually but I have a large portion of users that prefer to live only in Excel. 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. I know that Power BI generates DAX queries regardless of SSAS mode so I gues this isn't suprising. 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. It returns the correct answers and behaves as expected but I am surprised that it is sending MDX queries to the server. The SSAS server is 2019 and Excel is 2016. I have a relatively small 57MB tabular model that I would like to query with Excel Pivot table connections.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |