Wednesday, March 09, 2016

PowerPivot is a great product, and PowerQuery can be fairly awesome as well, but when you run out of steam running your spreadsheet in Excel/SharePoint you are a bit screwed, because if you convert a PowerPivot model to SSAS Tabular (using the SSDT ‘Import from PowerPivot’ project) then all your PowerQuery tables are converted into pasted data because SSAS can’t talk PowerQuery.

So in order to ‘upsize’ between PowerPivot and SSAS Tabular you have to:

Re-author all your PowerQueries as traditional SSIS ETLs (or similar)

Land the data in a relational database

Change your PowerPivot model to source from those tables, rather than from PowerQuery.

This actually involves recreating them all, since for tables sourced from PowerQuery, the connection type can’t be changed.

And the calculated columns and measures

And the formatting and sort orders

etc…

That looks a lot like ‘rewrite from scratch’ to me, which is a pretty poor option (and a major gotcha with the PowerPivot/PowerQuery approach). So I was pleased to read (somewhere I can’t find now) that this will be addressed in the SQL 2016 timeframe, with PowerQuery supported as a data source for SSAS[1], SSRS[2] and SSIS[3].

Only… seems like it’s actually not.

I’ve been doing a trial of SQL 2016 using CTP3.3 and RC0, to determine if this fixes an issue we had with PowerPivot KPIs, and it seems like it does. However, if SSIS or SSAS can source from PowerQuery I’m blowed if I can see where that functionality is, and the release notes have been very quiet on this front.