Tabular Queries in Excel 2013, Good news, Bad news,DAX Queries and the missing DAX visual query builder

(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)

SmartPivot Latest Version for Excel 2007, 2010, 2013 & PowerPivot

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!

Btw, regarding DAX queries, be sure to check Paul te Braak promising DAX Studio and his latest posts on DAX:

DAX Querying Part I

DAX II – Extending the use of Evaluate

DAX Studio @codeplex

 

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,

 

Rui