Analyze in Excel for the End User

[TheOckieMofo]

So I'm really hopeful about the analyze in excel feature. I can click on the "Analyze in Excel" option on any report/dataset in my power bi workspace and/or use the Power BI add in for Excel to tap directly into them. Either way, it's awesome to be able to leverage power bi as a server, thus replacing SSAS tab and/or sharepoint in certain situations.

My question is, though, how do I leverage this for the end user? The documentation wasn't clear. I want the end user, i.e. the receiver of a dashboard, to be able to view the data behind a dashboard as an excel pivot table. But when i share a dashboard with another user, they don't see the report behind the dashboard. So, is there a way to do this? Is there a way that the end user, on an ad hoc basis, can utilized the "analyze in excel feature"?

[MattAllington]

They must be able to see the data set, so that eliminates sharing a dashboard. You can set up a work group and add the users to that.  Then create an Excel File and configure it. You can then share the Excel with anyone that also has access to the workgroup

I cover sharing tips here http://exceleratorbi.com.au/top-tips-for-sharing-content-using-power-bi/

[TheOckieMofo]

Can I just say that it's awesome to have you respond to one of my posts? I have your book as well as Collie's and the Italians' books on my bookshelf right behind me at work. Add in Chris Webb, and you guys are the mount rushmore of modern excel. Ok, I should include Puls as well for Power Query. 🙂

So this feature seems pretty limited to me for the end user, no? In my company, we primary get sales data via SSAS MDX cubes. However, they do not have per day metrics nor do they divide the data how the business actually wants to see it. So there's a big gap there. Of course the best answer would be an SSAS Tab instance and I can go to town. I'm shown proof of concept to IT, but they aren't believers. I was hoping this feature would be an easy alternative to supplement the Power BI views with thin front end excel reports without the infrastructure committment of SSAS Tab. Basically, if you don't have a pro account with the dataset shared amongst the user base, the analyze in excel feature is unuseable.

[MattAllington]

I hope you liked my book. If you did, I can always do with a book review on Amazon.  It is tough finding new readers when you live in the shadow of Collie/Singh 🙂

You comments about Analyze in Excel are techncially correct, however I have a different perspective on the topic.  I am sure Microsoft has spent (and continues to spend) Billions of dollars building the technologies and tools of Power Query, Power Pivot and Power BI.  Somewhere along the way, it needs to charge customers that extract value from these tools as clearly it can't continue to invest if there is no income generated.  The price is dirt cheap.  It is $120 per year for 1 person to do what you are talking about ($12,000 for 100 people).  As a comparison, my recollection is that a Tableau license is $1,600 +20% annual maintenance per person ($160k + $32k maintence per year), and it would probably cost more than $100k to set up a new tabular server (hardward, software) and that is before you build a solution.  

Now of course the real challenge is getting someone in your company to agree to pay the additional money, and normally it is IT that controls the subscription - and that can be a problem (see my blog post about that problem here http://www.powerpivotpro.com/2016/08/enterprise-bi-flawed-strategy-2016/)

I recommend that you prove your solution by doing.  See if you can get some budget from somewhere to get a pilot up and running.  You won't get a cheaper BI pilot for (say) 10 people at $100 per month, and then you can show them what you can do.