Still no migration path from PowerPivot + PowerQuery to SSAS Tabular in SQL 2016 (RC0)

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.

The only concrete thing I’ve found is this tantilizing (and presumably unintentional) bit in the SQL 2016 Preview site’s Deeper Insights Across Data white paper:

I stress that’s not my highlighting :-(

 

[1] Can’t find where I originally got this idea from. May have just got the wrong end of the wrong stick
[2] See https://gqbi.wordpress.com/2015/05/07/bi-nsight-sql-server-2016-power-bi-updates-microsoft-azure-stack/. That being said, SSRS can use SSIS as a source, so if that was to be implemented…
[3] See https://social.msdn.microsoft.com/Forums/en-US/68a06982-4166-4ac9-93c0-3c247a9c64a7/powerquery-within-ssis-in-sql-2016?forum=sqlintegrationservices and http://sqlmag.com/blog/what-coming-sql-server-2016-business-intelligence or just vote for https://connect.microsoft.com/SQLServer/Feedback/Details/1046883