Change Excel report from PowerPivot to SSAS Tabular

Question

Hello,

I have several reports in Excel 2013 that were designed using PowerPivot models.

However our requirements have outgrown PowerPivot and we have since then replicated the PowerPivot models in a SSAS 2016 Tabular Model.

We would like to be able to change the sveral Pivot tables and other objects that we have in Excel to get data from the new SSAS Server rather than the built-in PowerPivot model, but we are not sure on how to do this.

When I select the PowerPivot table and click on the ANALYSE tab at the Ribbon at the top, the option to change the data connection is grayed out. How can this change be performed?

I mean, I am sure this must be supported, as MSFT bangs on-and-on about the BI Semantic model, and how it is easy to move up the ladder form Excel, to PowerPivot, to PowerPivot in SharePoint, and then a full-blown SSAS Server :)

Thanks in advance for the help and support.

Regards,

P.

Answer 1

I have not tried it, but there is a commercial solution : http://poweronbi.com/power-connect/

There is also a free solution, not tried either: https://code.msdn.microsoft.com/Migrate-PowerPivot-pivot-72560981

Hope this will help.

Answer 2

Hi Pmdci,

According to your description, you need to convert a PowerPivot data model to SSAS tabular data model, right?

When creating SSAS tabular project, there is a option that allow us to import from PowerPivot.

Or you can restore PowerPivot data model as a database.

Reference
https://joshuafennessy.com/2012/04/01/converting-a-powerpivot-model-to-a-tabular-cube/
http://www.powerpivotpro.com/2015/02/power-pivot-to-ssas-tabular-in-less-than-30-minutes/

Regards,

---

Charlie Liao
TechNet Community Support

Answer 3

According to your description, you need to convert a PowerPivot data model to SSAS tabular data model, right?

Wrong.

I want to update an Excel report so it stops looking into the PowerPivot model as its data source and look into the SSAS Tabular model instead.

Did you even care to read my original question?

Answer 4

Thanks for the reply, Bertrand.

During my tests:

1. The commercial tool does not really do what I want. It moves the PowerPivot from the Excel report into an SSAS Tabular model, and updates the Excel report to point to this tabular model. The problem with this is that I already have an SSAS Tabular model (I conformed many PowerPivot models into one big SSAS Tabular model). 

What I thought of doing is go ahead with the conversion, which will create a new tabular model in the SSAS Server. Since it will update the Excel report to use the new tabular model, I could then edit the connection and have it point to the tabular model I created instead.

However the tool is non-intuitive and returns a lot of errors. After spending 2 hours with it I just called it quits. I might as well spend this time recreating the Excel reports.

2. As for the free solution, that one seems more appropriate to what I want. I compiled it and I had a go. However it runs without and closes without returning an error. I think it is too outdated.

In the end I had to go ahead and recreate these reports.

What shocks me is that MSFT did not think ahead on such an obvious scenario. Personally I consider the whole marketing around the "Semantic Model" to be disingenuous to say the least.

Answer 5

Thanks for sharing your experience with these tools. Too bad they do not provide a viable solution.

I once faced the same issue, and came to the same conclusion: you need to recreate the report from scratch. And I full agree that considering the amount marketing material published on the scalability of Power Pivot to Tabular, this is a painfully missing feature.

Answer 6

Thanks Bertrand.

Even worse than a 'missing feature', I consider it misleading since MSFT keeps banging on the fact that you can 'easily' scale up from Excel, to PowerPivot, then to SSAS.

Answer 7

Hey, let's keep things polite in here, OK?

I'm sure Charlie's got to read a LOT of questions a day...

Answer 8

PMDCI, have you made any progress with this?

You can send a Frown to the Power Pivot team if you run into any more troubles. Thanks!

---

Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Answer 9

And you are?

Answer 10

Hi Ed,

Thanks for reaching out. Unfortunately there has been no progress. There was no solution for this problem. Microsoft does not provide an appropriate upgrade path in this case. We had no other option but to recreate the entire reports from scratch. Took us a week as we had a lot of reports.