Power BI On Premise – Not in the Cloud!

Microsoft has recently released a technical preview of Power BI that can be installed on premise inside a company firewall and the Power BI reports can be securely published over the Intranet (not Internet) via SSRS.  Today I am sharing my experiences setting up the technical preview for the first time.  I installed the software on my PC however if you are going to use it for your company, you need to install it on a server.  Time to get one of your friendly IT folk to help you when you are ready to set this up and demonstrate what can be done to your business leaders.

Guy in a Cube is Your Friend

Adam Saxton shares a truck load of information at his Guy in a Cube YouTube ChannelI watched Adam’s video on how to install this technical preview and followed his instructions.  I wont repeat all the details here as he has done a great job of that already, however I wanted to share my experience and observations.

My Install Experience

For the record, I installed the 64 bit version on my local PC.  I already had SQL Server 2012 installed as well as SSAS Tabular 2012.

1. I downloaded the Jan 2017 technical preview of Power BI Desktop (SSRS edition) PBIDesktopRS_x64.msi and SQL Server Reporting Services SQLServerReportingServices.exe (both available here).  1 minute to download both files.

image

2. I installed and ran Power BI Desktop installer for SSRS first.  After installing, I ran the new software.  It looks, loads and behaves just like Power BI Desktop except for the reference to SSRS.  There was no conflict with my existing copy of Power BI Desktop, so now I have both versions installed side by side.  I assume the special SSRS features will find their way into the main version of Power BI Desktop in time.

image

3. I then installed the SSRS Technical Preview software (I note it has a short life – 180 days, which is to be expected).  1 minute to install.

image

4. I was then prompted to configure SSRS.  I already have a SQL Server instance running on my PC (2012), so I used that (I didn’t need to download a SQL Server Evaluation Edition) from the link shown above.

5. I then clicked the configuration button and gave it the name of my localhost server. I then got the following message.

image

6.  I then had to refer back to Adam’s video as I wasn’t sure what to do next.  Adam instructed to navigate to a URL in a browser (MachineName\Reports).   I found the exact URL for my SSRS server in the configuration tool as shown below.

image

Note you don’t need the :80 port number as port 80 is the default.  So for me the URL was simply

http://desktop-1sckel7/Reports

But when I went to that address in Google Chrome, it didn’t work.

image

On a hunch I decided to reboot my PC, then it worked just fine.

image

7. I noticed in the SSRS configuration manager that there is an option to register Power BI Integration.  This seems to be related to users being able to pin tiles from an On Premise Power BI report onto a Cloud based Dashboard, although I am not 100% clear on this.  I didn’t touch anything here and it all worked fine.

image

8.  This preview only works with a live connection to SSAS (Tabular or Multi Dimensional).  I have SSAS Tabular running on my PC but I didn’t have a Tabular Data Model I could use, so I decided to restore a Power Pivot model from Excel to my server.  I launched SSMS to connect to Analysis Services Tabular (2012 in my case).

image

I right clicked on Databases (shown as 1 below) and then selected Restore from Power Pivot (2 below).

image

I followed the instructions to restore the file.  At first I couldn’t restore the file due to a file access permission error. I have struggled with this forever (I am not a SQL Server Pro) but I quickly found this article that told me how to give SQL Server permission to access the folder that contained my Power Pivot workbook.

After restoring I couldn’t see the database.  I had to first right click (1 below), then select refresh (2 below).

image

9. Using my new version of Power BI Desktop, I created a new workbook and connected it to my SSAS server using connect live.

image

I created a quick demo report and then saved the report.  I navigated to the SSRS Reports Page in my Browser (shown below) and uploaded my new report.

image

Bingo – On Premise Power BI

And bingo – there it is, working through a browser like a charm.

image

Observations

  • The first report was slow to render.  The second and subsequent reports were fast.  This could be related to my SSAS Server on my PC not being hot.
  • Now when I double click on a PBIX file, the SSRS version of Power BI Desktop launches by default.  I tried to manually change this back but couldn’t get it to change.  I decided to reinstall the latest production version of Power BI Desktop (repair install) and that fixed it.  The SSRS version still worked but was now not the default.
  • The URL for the report page is in the format MACHINENAME/Reports/PowerBI/FileNameWithoutThePBIXextension.  As you can see in the image above, my file name was “Power BI On Premise” including spaces.  That meant that the URL was actually http://desktop-1sckel7/Reports/powerbi/Power%20BI%20On%20Premise  I hate all those %20 that indicate a space.  I renamed the file using underscore characters instead of spaces, and that gave me a much neater URL

image

  • You should also note that this is not a fully featured on-premise copy of PowerBI.com.  There is no Dashboards section and hence no quick insights, no Q&A.  It most closely resembles the public sharing feature of PowerBI.com

Summary

All in all I have to say this is pretty exciting.  I have lots of current and potential clients that I have spoken to that are not willing to proceed with a cloud based solution.  A production ready on-premise solution seems very close now – can’t wait.

Share?

Comments

  1. Great Matt. I was waiting for this since last year. Is this install going to conflict with currently installed SSRS or It is just the evaluation version of fresh copy of SSRS getting installed.

    • I haven’t tested it so I am reluctant to say it definitely won’t affect what you have. It is my expectation that is will work alongside what you have – that is what a preview is designed to do. I suggest you ask Adam on his YouTube channel to be sure.

  2. Thanks for sharing Matt – exciting times ahead if you can have Power BI installed and distributed in-house. I think most organisations want complete control over their data and the cloud isn’t a great solution at the moment.

    • I agree – there are significant numbers of significant Microsoft customers that wont move forward without this. Maybe if MS closed its eyes and dreamed of Utopia then it would be different – but I don’t see that happening any time soon.

  3. Thanks for the clarification.
    I managed to configure with my current SSRS 2014 Version as I think it is required to configure the new reporting services database on your existing reporting services. Great to test the new functionality. Absolutely Loved it. Waiting to roll out for the full production version.

  4. Matt,
    I don’t want to bother you but I followed your instruction and when I click on the report its looks like I’m downloading it with the message do you want to save it or open it
    Where I can submit a ticket for that?
    Also now we need two versions of Power BI?
    Thanks,
    Oded Dror

  5. @Matt – Can we connect to the Data model of the On Perm Power BI – using Analyse with Excel – If yes then we have a winner.

    • Sam & Matt

      correct me if i am wrong, as far as i can understand right now, there is no model at all loaded, as only pbix with connection to SSAS are supported, now the real question is how Microsoft will implement this functionality, something needs to host the data model 🙂

      I suspect the model will be eventually hosted in SSAS instance, unless they integrated an SSAS server with SSRS which seems unlikely,

      so basically SSRS will just render the reports included in a pbix file.

      ps: pbix that connect live to SSAS does not have data, only metadata and reports

      • You are right. I guess it is an alpha version and it is worth getting out “as is” prior to having the PBIX hosting version built. I can only go on what Adam said – “this version only supports…” so I guess the rest is coming.

        Definitely the PBIX needs to be hosted in an SSAS instance. When you host a Power Pivot workbook on SharePoint Enterprise this all happens transparently behind the scenes. The same happens when you run Excel or Power BI Desktop – there is a runtime instance of SSAS in spawned in the background. My best guess is they will deploy some variation of one of those three.

    • Great point Sam. There is no sign of it yet. I have consistently heard Microsoft use the language “Better with Excel” over the last 12 months, and I have to believe that Microsoft will continue to leverage this competitive advantage into the future. You should ask Adam and see what he says.

  6. I installed the preview version side by side alongside an existing SSRS 2016 installation without any problems, and being able to load/view both SSRS report managers without any problems.

  7. Hi Matt, PBIDesktopRS works with SSRS 2016? Any different configuration is needed? I’m having troubles while “Save as > SQL Server Reporting Services”. The message is “This URL is not a valid Report Server address”. However Reportin Services is running properly.

  8. Matt

    we are very excited about this option, but i have a strange feeling Microsoft will make the solution very expensive, did you heard anything about the licensing model ?

    thanks

Leave a Reply to pasquale Cancel reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x