CODEWITHSUNDEEP
ssaspowerpivotpowerbipowerquery
PeddiePooh
PeddiePooh

Reputation: 403

PowerPivot publish to SSAS with Power Query Connection

I use Power Query to do ETL work then load data into PowerPivot model. Then later I imported PowerPivot to SSAS cube.

I successfully imported the model into SSAS cube. Silly question really but I supposed I couldnt refresh my data from now on?

I actually couldnt deploy the cube without it throwing an error Microsoft.Mashup.OLE error.

Alternatively I can put my model on a sharepoint site and connect my ssas cube but im just exploring if there's another solution without sharepoint.

So I guess but questions are:

  1. Once import powerpivot - is there a way to refresh data being that it connects to power query queries?
  2. Any alternatives to load powerpivot to ssas cube apart from sharepoint?

Any thoughts? Thank you very much in advance.

Ped

Upvotes: 1

Views: 227

Answers (1)

ImkeF
ImkeF

Reputation: 1588

This is one possible workaround-strategy:

1) Duplicate your workbook

2) In workbook 1 schedule Power-SQL-update to “divert” all queries that currently feed the PP-datamodel to SQL-server-tables. This will be your leightweight SSIS-server-alternative (http://www.powerpivotpro.com/2015/09/power-query-as-an-ssis-alternative/).

3) In workbook 2 delete your data-tables and recreate them connected directly from Power Pivot to your new SQL-datasource. Re-establish the table-connections: Ready to be deployed to SSAS.

Provided that you keep your measures in different tables than your "real data tables" (as many Power Query users already do, due to the bugs in the past) and write your calculated columns in Power Query instead of Power Pivot (otherwise you would need to re-create them, once you re-create your SQL-server-connected tables)

Or you take the SSAS-in-the-cloud alternative that is supposed to come out soon: http://blog.crossjoin.co.uk/2016/03/23/thoughts-on-the-power-bi-announcements-at-the-ms-data-insights-summit/?utm_content=bufferdc3e2&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

Upvotes: 1

Related Questions