Tabular Queries in Excel 2013, Good news, Bad news,DAX Queries and the missing DAX visual query builder
July 24, 2012 1 Comment
(update: SmartPivot for Excel version 2.3 – a much improved version- has just been published, please visit SmartPivot Product Page for the latest version Excel BI features including instant search, quick connect , a stunning pivot viewer visualization for Excel tables & many others)
The never-ending thread… :) Anyway, I just finished some tests with Excel 2013, like the title says, good news, and bad news. (that is, imo).
So, bad news first, so that I can finish this post in a good mood!
Bad News-“Tabular” Pivot Table on OLAP cubes, the scalability/performance problem is still there
Not that I was expecting improvements here, but it’s sad to note that for now I get the same very bad experience both with 2010 & 2013.
Just load an olap pivot table with 11 columns, a tiny rowset of 244 rows and I had to wait more than a minute for the data. (In the process I also crashed SQL Man Studio and Excel due to the abnormal number of cells returned)…
Fortunately, I was glad to see that SmartPivot tabular report works perfectly on Excel 2013 Preview.
Same 244 rows in a native table, 11 columns, ~1.6 secs… No dumps.
Anyway, it would be great that native excel cube pivot tables could be fixed to enable this scenario. Let’s see in the RTM…
So, now for the good news…
Good News-Edit Dax Query in Excel tables shows potential, but we’ll need a friendly query editor soon!
If there’s one thing I always hoped that the tabular model could fix, (and as the tabular suggests… ), were real tabular queries, with real excel tables. A single semantic model (read *real* semantic, not bism…) to rule them all (both pivot and table queries).
Yes, there’s more to data science than pivot tables and low grain aggregated values.
For a lot of (good) reasons it’s very common for users to need that style of data layout. Why the hell can’t users use the same platform/solution to get a simple list of customers by country, or products by categories (yes, there are no metric involved)
And although I don’t feel the problem is properly solved in the Excel 2013 preview, there’s a new table menu option that immediately caught my attention: Edit DAX.
That will allow to edit the underlying DAX model query being used, switching from table style query to a full DAX query.
So for example, I can create a table from one of my data model existing tables,
and then changing the table query to a DAX query, and craft the query to return an additional calculated column (ex: score based on average rating)
I can only guess (hope!) that anytime soon, the old field list/pivot layout that today supports most of pivot table/pivot chart data analysis will be finally replaced to a full grown olap/tabular friendly query editor, that can target lots of data layouts (pivot/table style). Like the field list in a pivot table, there should be a field list for a data model based table query. That would be pretty amazing.
Because, and I’ll have to disagree with some msbi most valuable guys :), DAX (like MDX) is extremely difficult to craft manually beyond the basic stuff. For pivot based layouts we can live (hardly…) with the drag & drop field list. For tabular queries (particularly for DAX queries) users will need a new (very easy & very visual) query editor tool that explores the full potential of tabular backends.
That reminds me that although I can’t say that DAX is a lot more complex that MDX (I’m tempted to…:) ), or the way way around, one thing is for sure, Excel OLAP users never needed to know MDX to work with OLAP, I and hope that they’ll be able to get the full power of tabular without knowing DAX too!
Final note – Slicers also work with DAX query tables, but they use visual filtering in the Excel table.
It was rather intriguing to note that I could use a table slicer (new feature of Excel 2013), also when using a custom DAX query. In fact the newly added DAX column was immediately available to use as a slicer. But the reason for that become pretty obvious: the slicer uses the Excel table column filters to filter the visible rows only, it doesn’t change the underlying table query. Ex:
So, that’s it for now. Take care,