Power BI server and OOS. Make your Excel’s reports great again

It has been a while since my last post; and I will take the opportunity of this new post to switch from French to English…Indulge me…

I am actually working on different BI projects… I like BI because I consider it as one of the most exciting way to play with data.  At ShareQL with Serge Luca, we have done a lot of work in the era of SharePoint BI ecosystem. One of the top opportunity was Excel Services, and someway the early age of Self-BI, .  Remember when Power Pivot appeared and changed the way to use Excel (Lookups GO HOME!!! )….

Of course now we are  to the sexiness of Power BI visuals and to the comfort and performances of Tabular model in SSAS … Still a lot of customers have those Excel in their inventory, sometimes even plenty of them.. Not everyone wants to use Power BI Service (Clouuuuud)  nor have access to a SharePoint. I am still convinced that Excel reporting is definitively a good and even a natural tool to explore data and to share some results, side by side with more visuals solutions. This only works if they are connected eventually to a nice datamodel #OneVersionOfTheTruthMantra …

SO…

The good news is that you can now upload Excel files into Power BI on premise and really « publish » them by making your report server works with an instance of OOS aka Office Online Server—BTW it is called online but it is designed to run at your location. #KeepCalm&DeepBreath

There is an excellent article/tutorial made by Adam Saxton on the PowerBI blog who will explain you how to set it up.

And if you are impatient, lazy or do not have that resources nor time, you can use a Azure virtual machine to  quickly start a lab : The Power BI Report Server template is already pre-installed, and in a few step it will be functional with OOS. Just follow the steps — I used the post of Adam to find what was still to be configured into this template.

First deploy

Then connect to your new server and open PwShell or  ISE in admin mode

Add the needed features:

Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,Web-Common-Http,Web-Default-Doc,Web-Static-Content,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,Web-Asp-Net45,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,NET-Framework-Features,NET-Framework-45-Features,NET-Framework-Core,NET-Framework-45-Core,NET-HTTP-Activation,NET-Non-HTTP-Activ,NET-WCF-HTTP-Activation45,Windows-Identity-Foundation,Server-Media-Foundation

Deploy the farm

New-OfficeWebAppsFarm -InternalURL "http://servername" -AllowHttp

Map the farm into the server properties:

Use the Excel sample or upload one of yours… I’ve used the « old but nice » one for Excel 2013

Now I need to test the refresh of data and I have to install it on my local environment. I already know that -like in the SharePoint configuration- User authentication and Live connection will be the point…

I am pretty excited because this is just the perfect answer for one of my project: they have dozens of old reports in read-only mode in a shared folder –eerrr…Because at the time their SharePoint Team didn’t to deploy the SharePoint BI features on a very deeply customized SP farm (side effects?). They are developing new stuff with the new Power Bi server to use paginated, Pbix and Excel reports connected to an unified model…One model, multiple ways to use it is my line of conduct.

In my next posts I will go deeper into this adventure. Stay tuned.