Managing SharePoint on-premises is tedious and problematic, and I can't wait to completely abandon on-prem and move to SharePoint Online. But basic Power Pivot support isn't even close as a "Corporate" BI solution. Excel Services & Power View without external data refresh has just been a deal-breaker.

Now we (will sometime fairly soon) have "Power BI for Office 365", but I can't see where it's going to change anything for me. My understanding is that Power BI includes the "Data Management Gateway", which enables Power Pivot models to refresh against on-prem SQL Server relational data sources. But I can't see any reasonable way to employ this architecture to deliver dozens of workbooks (each containing PivotTable, PivotChart, and/or Power View reports built against a central-but-continuously-evolving data model), to dozens of users with varying permissions.

I've pondered some crazy scenarios, but fundamentally, I can't come up with any solution that isn't some ridiculous concoction of hundreds or thousands of Power Pivot workbooks, all of which have to be modified whenever I want to make a change to the model that they all share.

What I want can be summed up like this:

1) build and continuously evolve a model in one place 2) design PivotTable and Power View reports against that model. if i make a breaking change to the model, then i'll have to update a lot of these reports, but only once, not for each user's instance of each report... 3) when users view those reports, they see only the regions, territories, products, etc that they are authorized to see 4) deliver this through SharePoint Online

Am I missing something, or does Power BI get me no closer whatsoever to that utopia?

2 Answers 2


I completely agree with you. I'm having the same dilemma. Microsoft strategy for corporate BI seems very unclear and is pushing clients towards other front-end solutions like Tableau and Targit. The focus is too much on self-service BI which is only usable by a few specialist inside every company.


I made a new database with views of my factual data (invoices, orders, etc), and my dimension data (costumers, regions, dates), and a few table functions to calculate most of my data (those get inside a job that I run every night to populate historical tables).

This way, I manage a single base model, and all my Pivot data comes from it. Now I set the Data Gateway to auto update, thus if I make a change to my model, all the pivots are updated and I can better maintain everything from a central location.

As for the security, I've heard you can add security by means of Analysis services Tabular Models, but I haven't tried that part yet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.